What is Instead of Triggers?

Instead of Triggers:-
                    With the help of INSTEAD OF TRIGGER we perform DML on complex view

Examples:-

CREATE OR REPLACE VIEW XXC05_EMP_DETAIL1
AS
    (
      SELECT E.EMPLOYEE_ID,
             E.SALARY,
             MAX(D.DEPARTMENT_ID)
      FROM EMPLOYEES E,
      DEPARTMENTS D,
      LOCATIONS L
      WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
      AND D.LOCATION_ID = L.LOCATION_ID
      GROUP BY (E.EMPLOYEE_ID,SALARY)
      );
   
create trigger XXC05_INSTEAD_OF_TRI
instead of update on XXC05_EMP_DETAIL1
begin
    update employees
    set employee_id = :new.employee_id
    where employee_id =198;
end XXC05_INSTEAD_OF_TRI;


UPDATE XXC05_EMP_DETAIL1
SET SALARY = SALARY+(SALARY*COMMISSION_PCT)
WHERE EMPLOYEE_ID = 198;

Note:-  When we execute update command it update view.

What is Authid Current User?

Authid Current_User:-
                    The authid current_user is used when you want a piece of code (PL/SQL) to execute with the privileges of the current user, and NOT the user ID that created the procedure.  This is termed a "invoker rights", the opposite of "definer rights".

The authid current_user is the opposite of authid definer.

In the same sense, the authid current_user is the reverse of the "grant execute" where the current user does not matter, the privileges of the creating user are used.

PL/SQL, by default, run with the privileges of the schema within which they are created no matter who invokes the procedure. In order for a PL/SQL package to run with invokers rights AUTHID CURRENT_USER has to be explicitly written into the package.


Examples:-

CREATE PROCEDURE IN SCOTT SCHEMA

CREATE OR REPLACE PROCEDURE XXC05_AUTHID_CURENT_USER
IS
BEGIN
insert into XXC05_TEST values(7,'G ');
COMMIT;
END XXC05_AUTHID_CURENT_USER;



CREATE OR REPLACE PROCEDURE XXC05_AUTHID_CURENT_USER1 AUTHID CURRENT_USER
IS
BEGIN
insert into XXC05_TEST values(10,'I ');
COMMIT;
END XXC05_AUTHID_CURENT_USER1;



EXECUTE IN HR SCHEMA

procedure XXC05_AUTHID_CURENT_USER work normally.

But procedure XXC05_AUTHID_CURENT_USER1 give error 'table or view does not exits'.
If we give permission on all object which is declare in that procedure then it work normally.





Thanks
Sajal Agarwal

Triggers and its examples....

Trigger:-    Trigger is a pl/sql block or procedure that implicitly execute when some event occur.

Application Trigger:- Fire whenever an event occurs with a particular application.

Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or 
                                 shutdown) occur on a schema or database

Trigger Timing
    For table:- Before, After
    For View:- Instead of
   
Trigger Event:-   
    Insert, Update or Delete

Trigger Name:-
    On Table, View
   
Trigger Type:-
        Row Level
        Statement Level
       
Trigger Body:-
            What Action perform
           

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
    IF TO_CHAR(SYSDATE,'DY') = 'SAT'
    THEN
        RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
    END IF;
END XXC05_TRIGGER_TEST;   




CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
    IF TO_CHAR(SYSDATE,'DY') = 'SAT'
    THEN
        IF INSERTING
        THEN
            RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
        ELSIF UPDATING
        THEN
            RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');   
        ELSIF DELETING
        THEN
              RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE'); 
        END IF;       
    END IF;
END XXC05_TRIGGER_TEST;





CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
    IF TO_CHAR(SYSDATE,'DY') = 'SAT'
    THEN
        IF INSERTING
        THEN
            RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
        ELSIF UPDATING
        THEN
            RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');   
        ELSIF DELETING
        THEN
              RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE'); 
        END IF;       
    END IF;
END XXC05_TRIGGER_TEST;

Save Exception with examples....

SAVE EXCEPTIONS:-

Since version 9 it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows - the exceptions - are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.


DECLARE
 USER_EXCE EXCEPTION;
 PRAGMA EXCEPTION_INIT(USER_EXCE,-24381);
 TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
 T2 T1;
 CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
        FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
            INSERT INTO XXC05_OBJECT VALUES T2(I);
        EXIT WHEN T2.COUNT=0;
        END LOOP;
    EXCEPTION
    WHEN USER_EXCE
    THEN
        FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
    END LOOP;
END;

What is Index and its types with example?

Index:-
        Index is a schema object. Index is a performance-tuning method. Using Index retrieval of data from database is fast.
       
Syntax:-
        CREATE INDEX index_name
        ON table_name(column_name1,column_name2,column_name3,...);
       
       
Rename an INDEX:-
                Alter INDEX old_index_name
                RENAME TO new_index_name;

Drop an INDEX:-
                DROP INDEX index_name;
               
               
Type of Index:-

1. Normal Index(B-Tree Index)
2. Bitmap Index
3. Function-based Index

Normal Index:-
                It is also called B-TREE Index, By Default B-TREE index is created. It manage data in tree form.
               
Bitmap Index:-
                Bitmap index put on that column which contain low cardinality. Low Cardinality means more duplicate data and less unique data.
               
Function-Based Index:-
                         The index expression can be an arithmetic expression or an expression that contains a SQL function, PL/SQL function, package function.
                           
                       
Example of Normal Index:-

                    CREATE INDEX xxc05_vendor_id
                    ON xxc05_ap_supplier(vendor_id);


Example of Bitmap Index:-

                    CREATE BITMAP INDEX xxc05_emp_gender
                    ON xxc05_employees(gender);

                   
Example of Functional Index:-

                    CREATE INDEX xxc05_vendor_id
                    ON xxc05_ap_supplier(UPPER(vendor_name));


Thanks
Sajal

What is Function and its examples

Function:-
            Function is named PL/SQL block. It must return a single value. We can call function in SELECT Statement.
           
Syntax:-
        CREATE OR REPLACE FUNCTION function_name
        RETURN data_type
        IS
        BEGIN
            STATEMENT;           
            RETURN (value/variables);
        END function_name;
   
After creation of FUNCTION, we call it for the output.

There are many different ways for execute the procedure
   
1.
    SELECT function_name FROM table_name;

2.   
    DECLARE
    variable_name data_type;
    BEGIN
        variable:= function_name;
    END;   
   
We can use different parameter mode in function

1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
   
Note:- *a) If we use OUT, IN OUT mode in function's parameter, we can not call that function in
                 SELECT statement.
             b) By default parameter mode is IN.
             c) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
                 IN OUT mode.
       
Examples:-

1.    

    CREATE OR REPLACE FUNCTION XXC05_FUNC
    RETURN NUMBER
    IS
     ID NUMBER;
    BEGIN
        ID:=100;
    RETURN 100;
    END XXC05_FUNC;
   
   
    SELECT XXC05_FUNC FROM DUAL;
   
   
2.

    CREATE OR REPLACE FUNCTION XXC05_FUNC
    RETURN NUMBER
    IS
     L_SAL NUMBER;
    BEGIN
        SELECT SALARY
        INTO L_SAL
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID=199;
    RETURN L_SAL;
    END XXC05_FUNC;
   
    SELECT XXC05_FUNC FROM DUAL;
   
   
3.

    CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER)
    RETURN NUMBER
    IS
     L_SAL NUMBER;
    BEGIN
        SELECT SALARY
        INTO L_SAL
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID=P_ID;
    RETURN L_SAL;
    END XXC05_FUNC;   
   
    SELECT XXC05_FUNC(198) FROM DUAL;
   

4.

    CREATE OR REPLACE FUNCTION XXC05_FUNC(P_ID NUMBER,P_DEPT_ID OUT NUMBER)
    RETURN NUMBER
    IS
     L_SAL NUMBER;
    BEGIN
        SELECT SALARY,DEPARTMENT_ID
        INTO L_SAL,P_DEPT_ID
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID=P_ID;
    RETURN L_SAL;
    END XXC05_FUNC;   
   
Note:- This type of function can not call in SELECT Statement. This type of function call within BEGIN block;
For Example:-

    DECLARE
     L_DEPT_ID NUMBER;
     L_SAL NUMBER;
    BEGIN
        L_SAL:= XXC05_FUNC(199,L_DEPT_ID);
        DBMS_OUTPUT.PUT_LINE('Department id:- '||L_DEPT_ID||CHR(9)||'Salary:- '||L_SAL);
    END;

What is Procedure and its Examples

Procedure:-
            Procedure is a named PL/SQL block. It may or may not return value.
           
Syntax:-
            CREATE OR REPLACE PROCEDURE procedure_name
            IS/AS
            BEGIN
                STATEMENT;
            EXCEPTION;    --(optional)   
            END procedure_name;
           
After creation of procedure, we execute it for see the output.

There are many different ways for execute the procedure
   
1.
    EXECUTE/EXEC procedure_name;

2.   
    DECLARE
    BEGIN
        procedure_name;
    END;

   
We can use different parameter mode in procedure

1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
   
Note:-    a) By default parameter mode is IN.
              b) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
                   IN OUT mode.


Examples:-

1.
   
    CREATE OR REPLACE PROCEDURE XXC05_PROC
    IS
      ID NUMBER;
    BEGIN
        ID:=10;
        DBMS_OUTPUT.PUT_LINE('ID:- '||ID);
    END XXC05_PROC;
   
   

2.

    CREATE OR REPLACE PROCEDURE XXC05_PROC
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME
                FROM EMPLOYEES;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
     END LOOP;
    END XXC05_PROC;

   

3. We can use cursor within Procedure....

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
     END LOOP;
    END XXC05_PROC;
   
    exec XXC05_PROC(100);
   
   
4.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME,P_SAL;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
     END LOOP;
    END XXC05_PROC;   
   
   
Note:-  If we use OUT or IN OUT mode parameter in procedure then it call only within BEGIN block.
        Example given below.

    DECLARE
    L_SAL NUMBER;
    BEGIN
        XXC05_PROC(100,L_SAL);
        DBMS_OUTPUT.PUT_LINE('Salary:- '||CHR(9)||L_SAL);
    END;
   
5.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NOCOPY NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME,P_SAL;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
     END LOOP;
    END XXC05_PROC;
   
6.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN OUT NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO P_EMP_ID,L_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||P_EMP_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
     END LOOP;
    END XXC05_PROC;
   
   
7.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
    IS
     CURSOR C1 IS
                SELECT *
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_EMP_REC EMPLOYEES%ROWTYPE;
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_EMP_REC ;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_EMP_REC.EMPLOYEE_ID||CHR(9)||'Last Name:- '||CHR(9)||L_EMP_REC.LAST_NAME);
     END LOOP;
    END XXC05_PROC;   
   

   
   
   
Some Important Point for Procedure..........

   
Ques:- Can we create procedure within procedure?
Ans:- YES, Refer below example.

Example:-
   
    CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
    IS
     PROCEDURE XXC05_INNER_PROC
     IS
     BEGIN
        DBMS_OUTPUT.PUT_LINE('INNER PROEDURE');
     END XXC05_INNER_PROC;                    
    BEGIN
     DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE');
     XXC05_INNER_PROC;
    END XXC05_OUTER_PROC;
   

   
Quese:- Can we use RETURN clause within procedure?
Ans:-  YES, But no statement are executed which written after the RETURN clause.

In below example only first message print.


Example:-

    CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
    IS
    BEGIN
     DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 1');
     RETURN;
     DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 2');
    END XXC05_OUTER_PROC;
   
    Output:-
                OUTER PROEDURE 1                   
               










               

What is Collection and its type with Example

Collection:-
            Collection is an Ordered group of elements, all of the same type.
           
Attributes of Collection

1.  FIRST
2.  LAST
3.  COUNT
4.  DELETE
5.  EXTAND
6.  TRIM
7.  NEXT
8.  EXIST
9.  PRIOR
10. LIMIT           
   
   
Types of Collection

1.  Varray
2.  Nested Table
3.  PL/SQL Table or Associate Array




Varray:-
        Varray stands of variable size array.Varray can be stored in the column of table.
       
Syntax of Varray:- Type type_name is VARRAY(length) of data_type;

Examples of Varray
   
   
1.

    DECLARE
     TYPE L_VAR IS VARRAY(10) OF VARCHAR2(3);
     L_VAR1 L_VAR;
    BEGIN
        L_VAR1:=L_VAR('A','B','C','D','E','F','G','H');
        FOR I IN 1..8
        LOOP
            DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
        END LOOP;
     END;
   
   
2.

     DECLARE
     TYPE L_VAR IS VARRAY(10) OF VARCHAR2(3);
     L_VAR1 L_VAR;
    BEGIN
        L_VAR1:=L_VAR('A','B','C','D','E','F','G','H');
        FOR I IN L_VAR1.FIRST..L_VAR1.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
        END LOOP;
     END;
   

3.   

    DECLARE
     TYPE L_VAR IS VARRAY(10) OF VARCHAR2(10);
     L_VAR1 L_VAR:= L_VAR();
    BEGIN
        L_VAR1.EXTEND;
        L_VAR1(1):='HELLO';
        L_VAR1.EXTEND;
        L_VAR1(2):= 'ORACLE';
        L_VAR1.EXTEND;
        L_VAR1(3):='JAVA';
        L_VAR1.EXTEND;
        L_VAR1(4):= 'OAF';
        FOR I IN 1..4
        LOOP
            DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
        END LOOP;  
     END;
   
   
4.

    DECLARE
     CURSOR C1 IS
            SELECT PO_HEADER_ID
            FROM PO_HEADERS_ALL
            WHERE ROWNUM<=100;
     TYPE L_HDR IS VARRAY(100) OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
     L_HDR1 L_HDR:= L_HDR();
     COUNTER NUMBER:=0;           
    BEGIN
     FOR I IN C1
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR1.EXTEND;
        L_HDR1(COUNTER):= I.PO_HEADER_ID;
        DBMS_OUTPUT.PUT_LINE('PO_HEADER_ID IS '||L_HDR1(COUNTER));
     END LOOP;
    END;
   
   
5.

    DECLARE
     CURSOR C1 IS
                SELECT PO_HEADER_ID
                FROM PO_HEADERS_ALL
                WHERE ROWNUM<=100;
     TYPE L_HDR IS VARRAY(100) OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
     L_HDR1 L_HDR:=L_HDR();
     COUNTER NUMBER:=0;           
    BEGIN
     OPEN C1;
     FOR I IN 1..100
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR1.EXTEND;
        FETCH C1 INTO L_HDR1(COUNTER);
        DBMS_OUTPUT.PUT_LINE('PO_HEADER_ID IS '||L_HDR1(COUNTER));
     END LOOP;
    END;
   
   
Nested Table:- 
                Nested table is like a one-dimensional array.
               
Syntax of Nested Table:-   TYPE type_name IS TABLE OF data_type;


Example of Nested Table

1.

    DECLARE
     TYPE L_TAB IS TABLE OF NUMBER;
     L_TAB1 L_TAB;
    BEGIN
     L_TAB1:= L_TAB(1,2,3,4,5);
     FOR I IN 1..5
     LOOP
        DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
     END LOOP;
    END;
   
   
2.

    DECLARE
     TYPE L_TAB IS TABLE OF NUMBER;
     L_TAB1 L_TAB;
    BEGIN
     L_TAB1:= L_TAB(1,2,3,4,5);
     FOR I IN L_TAB.FIRST..L_TAB1.LAST
     LOOP
        DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
     END LOOP;
    END;
   
   
3.

    DECLARE
     CURSOR C_HDR IS
                    SELECT PO_HEADER_ID
                    FROM PO_HEADERS_ALL
                    WHERE ROWNUM<=100;
     TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
     L_HDR L_TAB_HDR:= L_TAB_HDR();            
     COUNTER NUMBER:=0;
    BEGIN
     FOR I IN C_HDR
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR.EXTEND;
        L_HDR(COUNTER):= I.PO_HEADER_ID;
        DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
     END LOOP;
END;


4.

    DECLARE
     CURSOR C_HDR IS
                    SELECT PO_HEADER_ID
                    FROM PO_HEADERS_ALL
                    WHERE ROWNUM<=100;
     TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
     L_HDR L_TAB_HDR:= L_TAB_HDR();            
     COUNTER NUMBER:=0;
    BEGIN
     OPEN C_HDR;
     FOR I IN 1..100
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR.EXTEND;
        FETCH C_HDR INTO L_HDR(COUNTER);
        DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
     END LOOP;
    END;


5.

    DECLARE
     CURSOR C_HDR IS
                    SELECT *
                    FROM PO_HEADERS_ALL
                    WHERE ROWNUM<=100;
     TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL%ROWTYPE;
     L_HDR L_TAB_HDR:= L_TAB_HDR();            
     COUNTER NUMBER:=0;
    BEGIN
     OPEN C_HDR;
     FOR I IN 1..100
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR.EXTEND;
        FETCH C_HDR INTO L_HDR(COUNTER);
        DBMS_OUTPUT.PUT_LINE('HEADER ID'||CHR(9)||L_HDR(COUNTER).PO_HEADER_ID||CHR(9)||'PO NUMBER'||CHR(9)||L_HDR(COUNTER).SEGMENT1);
     END LOOP;
    END;

   
   
PL/SQL Table:-
            PL/SQL Table helps you moves bulk data. They can store column or rows od Oracle Data.
              
 Syntax:- TYPE type_name is TABLE OF data_type
                 INDEX BY NUMBER/VARCHAR2/PLS_INTRGER/BINARY_INTEGER;
              
Example:-

1.

    DECLARE
     TYPE L_TAB IS TABLE OF NUMBER
     INDEX BY PLS_INTEGER;
     L_TAB1 L_TAB;
    BEGIN
     L_TAB1(1):=1;
     L_TAB1(2):=2;
     L_TAB1(3):=3;
     L_TAB1(4):=4;
     L_TAB1(5):=5;
     FOR I IN L_TAB1.FIRST..L_TAB1.LAST
     LOOP
        DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
     END LOOP;
    END;              

   
2.

    DECLARE
     CURSOR C_HDR IS
                    SELECT PO_HEADER_ID,SEGMENT1
                    FROM PO_HEADERS_ALL
                    WHERE ROWNUM<=100;
     TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.SEGMENT1%TYPE
     INDEX BY PLS_INTEGER;
     L_HDR L_TAB_HDR;            
     COUNTER NUMBER:=0;
    BEGIN
     FOR I IN C_HDR
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR(COUNTER):= I.SEGMENT1;
        DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
     END LOOP;
    END;
   
   
3.

    DECLARE
     CURSOR C_HDR IS
                    SELECT PO_HEADER_ID,SEGMENT1
                    FROM PO_HEADERS_ALL
                    WHERE ROWNUM<=100;
     TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.SEGMENT1%TYPE
     INDEX BY PLS_INTEGER;
     L_HDR L_TAB_HDR;            
     COUNTER NUMBER:=0;
    BEGIN
     FOR I IN C_HDR
     LOOP
        COUNTER:= COUNTER+1;
        L_HDR(COUNTER):= I.SEGMENT1;
     END LOOP;
     FOR J IN 1..COUNTER
     LOOP
        DBMS_OUTPUT.PUT_LINE(L_HDR(J));
     END LOOP;
    END;



Thanks
Sajal

How to use lookup in Workflow

Step 1:- Create a new item type

            Internal Name:- X05_WF01

            Display Name:- XXC05 WF01 Simple

            Description:- XXC05 WF01 Simple

Step 2:- Now create a new Process.

            Right click on processes and create new process

                        Internal Name:- XXC05_WF01_PROCESS

                        Display Name:- XXC05 WF01 First Process

                        Description:- XXC05 WF01 First Process

Step 3:- Double Click on Process then open Process window.

Step 4:- Right click on Process Window and create new function

            Set Values:

                        Item Type:- Standard

                        Internal Name:- START

 Then Click Node Tab

            Label:-  START

            Start/End:-  Start

Then Click OK

.

 

Step 5:- Create another Function “END” same as “START” Function (2 End Function)

            Set Values:

                       Item Type:- Standard

                        Internal Name:- END

Then Click Node Tab

                        Label:-  END

                        Start/End:-  Start

Then Click OK

Step 6:- Create a New Message for Approve or Reject

                       Internal Name:- XXC05_MESSAGE

                       Display Name:- XXC05 Message

                       Description Name:- XXC05 Message

 

Step 7- Create 2 Another Message

       1.       Approve Message

       2.       Reject Message

First Message

                            Internal Name:- XXC05_APPROVE_MSG

                            Display Name:- XXC05 Approve Message

                            Description:- XXC05 Approve Message

                                

Second Message

          Internal Name:- XXC05_REJECT_MSG

          Display Name:- XXC05 Reject Message

          Description:- XXC05 Reject Message

      

Step 8:- Create a Main Notification

                      Internal Name:- XXC05_MAIN_NOTIFICATION

                      Display Name:- XXC05 Main Notification

                      Description:- XXC05 Main Notification

 

Step 10:-Create Approve Notification

                                  Internal Name:- XXC05_APPROVE_NOTIFICATION

                                  Display Name:- XXC05 Approve Notification

                                 Description:- XXC05 Approve Notification

Step 11:- Create Reject Notification

                          Internal Name:- XXC05_REJECT_NOTIFICATION

                          Display Name:- XXC05 Approve Notification

                         Description:- XXC05 Approve Notification

 

Now Drag All notification in Process Window.

Then Save it into DataBase And Run Workflow