ORACLE PROCEDURES AND FUNCTIONS

What are Procedure and Functions?

A Procedure or Function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A stored Procedure or Function is a named PL/SQL  code block that has been complied and stored in one of the Oracle engine’s system tales.

Procedures or Functions are made up of :-

1. Declarative Part.
2. Executable Part.
3. Optional Exception –handling part.

Advantages of using a Procedures or Functions

1. Security :- Stored Procedure and Function can help enforce data security.

2. Performance:- Amount of information sent over a network is less. No compilation step is required to execute the code.

3. Memory Allocation:- The stored Procedure or Function having shared memory capabilities. Only on copy of procedure or Function needs to be loaded for execution by multiple users.

4. Productivity:- By writing Procedure or Function  redundant coding can be avoided.

5. Integrity:- A Procedure or Function needs to be tested only once to guarantee that it returns an accurate result.

Procedure and Function Differences?

The procedure may or may not return a value. It will return the values by its own name and function must and should return a value and it will return values with function name.

Function is executed in select statement. Procedure is called using

Exec.

A procedure is capable of performing DML operations. Where as

A function is not capable of doing DML operations except SELECT.

A Procedure reduce the code redundancy using out parameter concept where as It cannot be avoided in a Function.

A Procedure is a complete executable statement of PL/SQL block where as A Function  is a part of statement of PL/SQL block.

ORACLE PLSQL EXCEPTIONS

EXCEPTIONS:-

What is Exception?

Exception is a error handing program. Which abnormally terminates

Named Exceptions or Pre-defined Exceptions:-

1. DUP_VAL_ON_INDEX   ------  ORA-00001

2. LOGIN_DENIED            -----  ORA-01017

3. NO_DATA_FOUND         ----- ORA-01403

4. NOT_LOGGED_ON         ----- ORA-01012

5. PROGRAM_ERROR         ----- ORA-06501

6. TOO_MANY_ROWS        ----- ORA-01422

7. VALUE_ERROR              ----- ORA-06502

8. ZERO-DIVIDE               ----- ORA-01476

9. INVALID CURSOR         ----- ORA-01001

10. CURSOR ALREADY OPEN --- ORA-06511

What is Pragma?

Pragma is a precompiler directive, which directs the compiler before compilation of the program.

Pragma Autonomous Transaction?

Used to split the whole transaction into two parts parent and child, which run parallely and both have the relation ship

Ex: ITEM ATTCHMENTS CONVERSION

Pragma Exception Init?

Used to associate a user-defined exception with oracle-defined   error.

Ex: Bank Account Min Balance

Mutating Error, Mutating table?

It will occur whenever we try to do DML operations on a table, which is effected by a trigger.  The table, which is affected by trigger, is known as Mutating Error.

RAISE STATEMENT:- Raise an exception only when an error makes it undesirable or impossible to finish processing. we can code a Raise statement for a given exception any where in the scope to that exception.

Ex:- DECLARE

        OUT_OF _STOCK   EXCEPTION;

 NUMBER_ON_HANDS NUMBER(4);

BEGIN

IF NUMBER_ON_HAND < 1 THEN

RAISE OUT_OF _STOCK;

(WE CAN RAISE THE PRE-DEFINED EXCEPTION EXPLICITLY)

END IF;

EXCEPTION

WHEN OUT_OF_STOCK THEN

HANDLE THE ERROR

END;

PLSQL TRIGGERS

TRIGGER’S:-

What is a Trigger ?

A piece of logic that is executed at or triggered by a SQL *forms event.

Types of Triggers?

1.   Row Triggers:- A row Trigger is fired each time a row in the table is affected by the triggering statement.

2.   Statement Triggers:- It is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects.

3.   Before Triggers:-  1. Before row Trigger  2. Before statement Trigger.

4.   After Triggers:- 1. After row Trigger 2. After statement Trigger.

Database triggers are event based objects which means, execution dependson the event attached to them. These events are categorized into 4 groups :

DML EVENTS.........INSERT, UPDATE, DELETE [DML TRIGGERS]

               These triggers are used in response  to  the

               Insert, Delete, Update statements on a table.

Syntax:

CREATE OR REPLACE TRIGGER <Trig.Name>

<execution time>

<event list>

ON <table name>

[FOR EACH ROW]      .... Trigger type

[WHEN <condition>]  ....   ,, condition

<plsql anonymous block> ;   

These triggers always are executed before the event and

are row level triggers.

DDL EVENTS.........CREATE, ALTER, DROP    [DDL TRIGGERS]

               These triggers allow you to write a code that

               is invoked in response to the DDL statements.

Syntax: 

------

 CREATE OR REPLACE TRIGGER <Trig.name>

 BEFORE | AFTER

 CREATE OR ALTER OR DROP ON {DATABASE | <Schema.SCHEMA>}

 <pl/sql block>;

INSTEAD-OF EVENTS..VIEW      [INSTEADOF TRIGGERS]

          These triggers allow you to write a code that

                is invoked when a user tries to insert, update

                or delete through a view.

Syntax:

CREATE OR REPLACE TRIGGER <Trig.Name>

INSTEAD OF <DML events> ON <view name>

FOR EACH ROW

[WHEN <condition>]

<pl/sql block>;

DATABASE EVENTS....STARTUP, SHUTDOWN, LOGON, 

                                      LOGOUT,  SERVER ERROR 

                                      [DATABASE EVENT TRIGGERS]

                    These triggers allow you to write a code that

          is invoked in response to database events such

          as startup, shutdown etc.

RAISE_APPLICATION_ERROR Procedure?

Oracle engine provides a procedure name RAISE_APPLICATION_ERROR that 

Allows programmers to issue user-defined error message.

Syntax:- RAISE_APPLICATION_ERROR(<error number>, <message>);

Where error number is a negative integer in the range -20000 to 

-20999. and message is a character string upto 2048 bytes.

RAISE_APPLICATION_ERROR is used in Data base Triggers. 

PLSQL CURSORS

CURSORS:-

Cursor is a  method to access more than one record from database. It will allocate an area of memory known as context area. It contains information necessary information including  the no of rows to be processed.

Types of CURSOR’S :-

1. Implicit Cursor:- It is used for all SQL Statements (DML). It is opened and closed by the PL/SQL Engine Implicitly.

2. Explicit Cursor :- It has to be define Explicitly we have to do

1.   Declare the Cursor.

2.   Open the Cursor for a Query.

3.   Fetch the records into the PL/SQL variables.

4.   Close the Cursor.

                               

STEPS REQUIRED FOR CURSOR MANAGEMENT

          1) Declaration Of Cursor.

           Cursor name and select statement for the cursor

           to be provided in DECLARE division. During this

           plsql keeps information about cursor only.

                CURSOR <cursor name> IS <query>;

        2) Opening of Cursor.

                   During this step, plsql performs the following:

                - allocation of memory for cursor.

                - retrieval of records into cursor from db.

                - Positioning record pointer at 1st record.

                OPEN <cursor name>;

        3) Fetching record values from cursor into plsql

           variables.

           Values from current record are retrieved from

           cursor into plsql variables and record pointer

           is moved to next record.

                FETCH <cursor name> INTO <variable list>;

        4) Closing of Cursor.

           During this, plsql removes memory reserved for

           cursor so thelink between db & cursor is gone.

                CLOSE <cursor name>;

       

CURSOR ATTRIBUTES

        These are system variables provided by plsql to

        keep the cursor status. These are :

1. %FOUND :- Keeps boolean value that indicate whether

                fetch is successful or failure. If TRUE

                then success and if FALSE failure.

2. %NOTFOUND :- reverse of the above.

3. %ROWCOUNT :- Keeps the number value that indicate how

                many records have been fetched. In other

                words current record number which always

                start from 1.

4. %ISOPEN :- Keeps boolean value to indicate whether

                cursor is open or close. True for Open &

                False for Not Open.

        These are used with cursor name. The syntax is:

                <cursor name><attribute name>;

                Ex: C1%Found;

LIMITATIONS:-

        1) CURSOR is READ only. That means DML operations

           are not allowed using CURSOR. Only records can

            be retrieved.

        2) CURSOR is FORWARD only. i.e. record pointer in

           cursor can not move back to previous records.

HR CREATE NEW EMPLOYEE

 DECLARE
  x_emp_num VARCHAR2(200) := null ;
  x_business_group_id NUMBER:=202;
 -- x_person_type_id    NUMBER:=13;
  x_validate_mode     BOOLEAN := FALSE; 
  x_person_id NUMBER ;
  x_assignment_id NUMBER ;
  x_per_object_version_number NUMBER;
  x_asg_object_version_number NUMBER;
  x_per_effective_start_date  DATE;
  x_per_effective_end_date    DATE;
  x_full_name                 VARCHAR2(300);
  x_per_comment_id            NUMBER;
  x_assignment_sequence       NUMBER;
  x_assignment_number         VARCHAR2(10);
  x_name_combination_warning  BOOLEAN := FALSE;
  x_assign_payroll_warning    BOOLEAN := FALSE;
  x_orig_hire_warning         BOOLEAN := FALSE;
BEGIN
  /*SELECT business_group_id
  INTO x_business_group_id
  FROM per_business_groups
  WHERE NAME = 'Vision Corporation';
  SELECT ppt.person_type_id
  INTO x_person_type_id
  FROM per_person_types ppt
  WHERE ppt.business_group_id = x_business_group_id
  AND ppt.user_person_type = 'Employee';*/
DBMS_OUTPUT.PUT_LINE('*** BEFORE CALLING ***');
 hr_employee_api.create_gb_employee(p_validate                  => x_validate_mode
                                    ,p_hire_date                 => SYSDATE -- In this case
                                    ,p_business_group_id         => x_business_group_id
                                    ,p_last_name                 => 'Suresh'
                                    ,p_sex                       => 'M'
                                   -- ,p_person_type_id            => x_person_type_id
                                    ,p_date_of_birth             => '12-JAN-83'
                                    ,p_employee_number           => x_emp_num
                                    ,p_first_name                => 'AQEELUDDIN'
                                    ,p_known_as                  => ''
                                    ,p_marital_status            => ''
                                    ,p_middle_names              => ''
                                    ,p_ni_number                 => '958643218'
                                    ,p_previous_last_name        => ''
                                    ,p_title                     => 'MR.'
                                    ,p_original_date_of_hire     => SYSDATE
                                    ,p_person_id                 => x_person_id
                                    ,p_assignment_id             => x_assignment_id
                                    ,p_per_object_version_number => x_per_object_version_number
                                    ,p_asg_object_version_number => x_asg_object_version_number
                                    ,p_per_effective_start_date  => x_per_effective_start_date
                                    ,p_per_effective_end_date    => x_per_effective_end_date
                                    ,p_full_name                 => x_full_name
                                    ,p_per_comment_id            => x_per_comment_id
                                    ,p_assignment_sequence       => x_assignment_sequence
                                    ,p_assignment_number         => x_assignment_number
                                    ,p_name_combination_warning  => x_name_combination_warning
                                    ,p_assign_payroll_warning    => x_assign_payroll_warning
                                    ,p_orig_hire_warning         => x_orig_hire_warning
                                    );
COMMIT ;
IF(x_person_id=NULL) THEN
DBMS_OUTPUT.PUT_LINE('*** GOT ERROR ***');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR '||SQLERRM);
END;

OKC PROCESSING DEFINATIONS

DECLARE
    rec1 okc_pdp_pvt.pdpv_rec_type;
    rec2 okc_pdp_pvt.pdpv_rec_type;
   
  
    x_api_version                  NUMBER       :=1.0;
    p_init_msg_list                VARCHAR2(200):=null;
    x_return_status                VARCHAR2(200):=null;
    x_msg_count                    NUMBER       :=null;
    x_msg_data                     VARCHAR2(200):=null;
  
    x_user_id                       NUMBER      :=Fnd_Profile.VALUE('USER_ID');--profile value
    x_resp_id                       NUMBER      :=fnd_global.resp_id;
    x_appl_id                       NUMBER      :=fnd_global.resp_appl_id;
    x_org_id                        NUMBER      :=fnd_global.org_id;
    x_login_id                      NUMBER      :=Fnd_Profile.VALUE('LOGIN_ID');
   
    BEGIN
    rec1                                        :=null;
    rec1.id                                     :=null;
    rec1.object_version_number                  :=null;
    rec1.sfwt_flag                              :='Y';
    rec1.pdf_id                                 :=null;
    rec1.name                                   :='Test3';
    rec1.user_name                              :=null;
    rec1.data_type                              :='CHAR';
    rec1.default_value                          :=null;
    rec1.required_yn                            :='Y';
    rec1.description                            :='Creating test1';
    rec1.application_id                         :=515;
    rec1.seeded_flag                            :='N';
    rec1.created_by                             :=1318;
    rec1.creation_date                          :=sysdate;
    rec1.last_updated_by                        :=1318;
    rec1.last_update_date                       :=sysdate;
    rec1.last_update_login                      :=5500646;
    rec1.jtot_object_code                       :=null;
    rec1.NAME_COLUMN                            :=null;
    rec1.description_column                     :=null;
  
   
    fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
    DBMS_OUTPUT.PUT_LINE('API Execution Started');
   
              FND_MSG_PUB.INITIALIZE;
   
    OKC_PDP_PVT.INSERT_ROW(
    p_api_version                  =>x_api_version
    ,p_init_msg_list               =>p_init_msg_list
    ,x_return_status               =>x_return_status
    ,x_msg_count                   => x_msg_count
    ,x_msg_data                    =>x_msg_data
    ,p_pdpv_rec                    =>rec1
    ,x_pdpv_rec                    =>rec2);
   
    COMMIT;
    IF (x_return_Status ='S')  THEN
    DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
    DBMS_OUTPUT.PUT_LINE(x_return_status);
    DBMS_OUTPUT.PUT_LINE(x_msg_count);
    DBMS_OUTPUT.PUT_LINE(x_msg_data);
    ELSE
                 IF x_msg_count > 0 THEN
                FOR l_index in 1..x_msg_count LOOP
                   dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
                END LOOP;
             END IF;
    DBMS_OUTPUT.PUT_LINE(x_return_status);
    DBMS_OUTPUT.PUT_LINE(x_msg_count);
   DBMS_OUTPUT.PUT_LINE(x_msg_data);
   DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
   END IF;
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
   END;