Navigate

DBMS_SQL PACKAGE In Oracle

DBMS_SQL Package In Oracle:

DEFINITION:

  • DBMS_SQL package provides an interface to use dynamic sql statement to execute various types of statements. Like DML, DDL statement, anonymous plsql block, stored procedure or function using PLSQL.
  • Native dynamic sql statement (EXECUTE IMMEDIATE) is an alternative to DBMS_SQL which is easier to use and perform better.
  • Native dynamic sql statement has some limitation and there are few task which can only be performed by DBMS_SQL.
  • DBMS_SQL package is owned by SYS and compiled with AUTHID CURRENT_USER. Any DBMS_SQL subprogram called from an anonymous PLSQL block is run using the privilege of the current user.

EXECUTION FLOW:

  • OPEN_CURSOR
  • PARSE
  • BIND_VARIABLE or BIND_ARRAY
  • DEFINE_COLUMN or DEFINE_ARRAY
  • EXECUTE
  • FETCH_ROWS or EXECUTE_AND_FETCH
  • VARIABLE_VALUE or COLUMN_VALUE
  • CLOSE_CURSOR

OPEN_CURSOR:

  • To process a SQL statement you must have a open cursor.
  • When you call OPEN_CURSOR function you receive a cursor id number for the data structure representing a valid cursor.

PARSE:

  • Every SQL statement must be parsed by using the PARSE procedure
  • Parsing the statement check the syntax for the statement and associates it with the cursor in your program.

BIND_VARIABLE or BIND_ARRAY:

  • When you define SQL statement that require data as input during run time, you must use placeholder that marks where the input data to be supplied.
  • For each placeholder in the SQL statement, you must call a bind procedure. They can be either BIND_ARRAY procedure or BIND_VARIABLE procedure to supply the value of a variable to your program to the placeholder.

DEFINE_COLUMN or DEFINE_ARRAY:

  • The columns of a select statement are identified by their relative position as they appear in the select list from left to right.
  • You must call a define procedure either DEFINE_COLUMN or DEFINE_ARRAY to specify the variable. These variable that are going to receive the select values just like INTO clause in static SQL.

EXECUTE:

Call the EXECUTE function to execute the SQL statement.

FETCH_ROWS or EXECUTE_AND_FETCH:

  • FETCH function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows. This will continue until all the rows are fetched.
  • Instead of calling EXECUTE and then FETCH, you can call EXECUTE_AND_FETCH if you are calling EXECUTE for a single execution.

VARIABLE_VALUE or COLUMN_VALUE:

  • Call COLUMN_VALUE to determine the value of column retrieved by FETCH_ROWS call.
  • For anonymous block which is calling a stored procedure or DML statement with RETURNING clause, call VARIABLE_VALUE to retrieve the values assigned to the output variables.

CLOSE_CURSOR:

When you no longer needed a cursor in a session you can close the cursor.

SIMPLE EXAMPLES:

EXAMPLE 1:

CREATE OR REPLACE PROCEDURE SP_UPDATE_EMP_SAL
(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE,
 P_SAL   EMPLOYEES.SALARY%TYPE)
IS
  EMP_CUR INTEGER;
  NO_OF_ROWS INTEGER;
BEGIN
  EMP_CUR := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR, 'UPDATE EMPLOYEES SET SALARY = :SAL WHERE EMPLOYEE_ID = :EMPID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR, ':SAL', P_SAL);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR, ':EMPID', P_EMPID);
  NO_OF_ROWS := DBMS_SQL.EXECUTE(EMP_CUR);
  DBMS_OUTPUT.PUT_LINE('NO OF RECORD IMPACTED - >'||NO_OF_ROWS);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR - >'||SQLERRM);
    DBMS_SQL.CLOSE_CURSOR(EMP_CUR);
END;
/
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
EXEC SP_UPDATE_EMP_SAL(100, 2650);
SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;

EXAMPLE 2:

CREATE OR REPLACE PROCEDURE SP_GET_EMP_INFO(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
  EMP_CUR_SRC      INTEGER;
  EMP_CUR_DEST     INTEGER;
  NO_OF_ROWS_SRC   INTEGER;
  NO_OF_ROWS_DEST  INTEGER;
  
  V_EMPID      EMPLOYEES.EMPLOYEE_ID%TYPE;
  V_FNAME      EMPLOYEES.FIRST_NAME%TYPE;
  V_LNMAE      EMPLOYEES.LAST_NAME%TYPE;
  V_SAL        EMPLOYEES.SALARY%TYPE;
  V_DEPTID     EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE EMPLOYEES_DBMS_SQL_TEST';
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = :DEPTID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_SRC, ':DEPTID', P_DEPTNO);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 1, V_EMPID);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 2, V_FNAME, 30);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 3, V_LNMAE, 30);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 4, V_SAL);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 5, V_DEPTID);
  
  NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE(EMP_CUR_SRC);
  
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'INSERT INTO EMPLOYEES_DBMS_SQL_TEST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID) VALUES (:EMPID, :FNAME, :LNAME, :SAL, :DEPTID)', DBMS_SQL.NATIVE);
  LOOP
    IF DBMS_SQL.FETCH_ROWS(EMP_CUR_SRC) > 0 THEN
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPID);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_FNAME);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 3, V_LNMAE);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 4, V_SAL);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 5, V_DEPTID);
      
      DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMPID', V_EMPID);
      DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':FNAME', V_FNAME);
      DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':LNAME', V_LNMAE);
      DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':SAL', V_SAL);
      DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':DEPTID', V_DEPTID);
      
      NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
    ELSE
      EXIT;
    END IF;
  END LOOP;
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
EXEC SP_GET_EMP_INFO(30);
SELECT * FROM EMPLOYEES_DBMS_SQL_TEST;

BULK DML EXAMPLE:

INSERT:
CREATE OR REPLACE PROCEDURE SP_GET_BULK_EMP(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
  V_EMPNO_ARRAY    DBMS_SQL.NUMBER_TABLE;
  V_FNAME_ARRAY    DBMS_SQL.VARCHAR2_TABLE;
  V_LNAME_ARRAY    DBMS_SQL.VARCHAR2_TABLE;
  V_SAL_ARRAY      DBMS_SQL.NUMBER_TABLE;
  V_DEPTNO_ARRAY   DBMS_SQL.NUMBER_TABLE;
  --
  EMP_CUR_INIT     INTEGER;
  EMP_CUR_SRC      INTEGER;
  EMP_CUR_DEST     INTEGER;
  NO_OF_ROWS_SRC   INTEGER;
  NO_OF_ROWS_DEST  INTEGER;
  V_MIN_COUNT      INTEGER := 0;
  V_MAX_COUNT      INTEGER;
BEGIN
  EMP_CUR_INIT := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_INIT, 'TRUNCATE TABLE EMPLOYEES_DBMS_SQL_TEST', DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_INIT);
  --
  SELECT COUNT(1)
  INTO V_MAX_COUNT
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = P_DEPTNO;
  --
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = :DEPTID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_SRC, ':DEPTID', P_DEPTNO);
  --
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 1, V_EMPNO_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 2, V_FNAME_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 3, V_LNAME_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 4, V_SAL_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 5, V_DEPTNO_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  --
  NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE(EMP_CUR_SRC);
  --
  LOOP
    NO_OF_ROWS_SRC := DBMS_SQL.FETCH_ROWS(EMP_CUR_SRC);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPNO_ARRAY);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_FNAME_ARRAY);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 3, V_LNAME_ARRAY);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 4, V_SAL_ARRAY);
      DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 5, V_DEPTNO_ARRAY);
      EXIT WHEN NO_OF_ROWS_SRC <> V_MAX_COUNT;
  END LOOP;
  --
  DBMS_OUTPUT.PUT_LINE('NO OF ROWS = '||V_EMPNO_ARRAY.COUNT);
  --
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'INSERT INTO EMPLOYEES_DBMS_SQL_TEST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID) VALUES (:EMPID, :FNAME, :LNAME, :SAL, :DEPTID)', DBMS_SQL.NATIVE);    
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':EMPID', V_EMPNO_ARRAY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':FNAME', V_FNAME_ARRAY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':LNAME', V_LNAME_ARRAY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':SAL', V_SAL_ARRAY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':DEPTID', V_DEPTNO_ARRAY);
  --
  NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
EXEC SP_GET_BULK_EMP(30);
SELECT * FROM EMPLOYEES_DBMS_SQL_TEST;
UPDATE:
CREATE OR REPLACE PROCEDURE SP_UPDATE_EMP_BULK
IS
  EMP_CUR_DDL           INTEGER;
  EMP_CUR_SRC           INTEGER;
  EMP_CUR_DEST          INTEGER;
  --
  V_EMPNO_ARRAY         DBMS_SQL.NUMBER_TABLE;
  V_JOBID_ARRAY         DBMS_SQL.VARCHAR2_TABLE;
  V_SAL_ARRAY           DBMS_SQL.NUMBER_TABLE;
  --
  V_MIN_COUNT           INTEGER := 0;
  V_MAX_COUNT           INTEGER;
  NO_OF_ROWS_SRC        INTEGER;
  NO_OF_ROWS_DEST       INTEGER;
BEGIN
  BEGIN
    EMP_CUR_DDL := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(EMP_CUR_DDL, 'DROP TABLE EMPLOYEE_UPDATE', DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  --
  EMP_CUR_DDL := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DDL, 'CREATE TABLE EMPLOYEE_UPDATE AS SELECT * FROM EMPLOYEES', DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
  --
  SELECT COUNT(1)
  INTO V_MAX_COUNT
  FROM EMPLOYEES;
  --
  DBMS_OUTPUT.PUT_LINE('COUNT -> '||V_MAX_COUNT);
  --
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, JOB_ID, SALARY FROM EMPLOYEE_UPDATE ORDER BY EMPLOYEE_ID', DBMS_SQL.NATIVE);
  --
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 1, V_EMPNO_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 2, V_JOBID_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 3, V_SAL_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  --
  NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE(EMP_CUR_SRC);
  --
  LOOP
    NO_OF_ROWS_SRC := DBMS_SQL.FETCH_ROWS(EMP_CUR_SRC);      
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPNO_ARRAY);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_JOBID_ARRAY);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 3, V_SAL_ARRAY);
    EXIT WHEN NO_OF_ROWS_SRC <> V_MAX_COUNT;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  DBMS_OUTPUT.PUT_LINE('NO OF ROWS = '||V_EMPNO_ARRAY.COUNT);
  FOR I IN V_EMPNO_ARRAY.FIRST..V_EMPNO_ARRAY.LAST
  LOOP
    IF V_JOBID_ARRAY(I) = 'SA_MAN' THEN 
      V_SAL_ARRAY(I) := V_SAL_ARRAY(I)*1.15;
    ELSIF V_JOBID_ARRAY(I) IN ('SA_REP', 'SH_CLERK', 'ST_CLERK') THEN
      V_SAL_ARRAY(I) := V_SAL_ARRAY(I)*1.2;
    ELSIF V_JOBID_ARRAY(I) = 'ST_MAN' THEN
      V_SAL_ARRAY(I) := V_SAL_ARRAY(I)*1.1;
    ELSE
      V_SAL_ARRAY(I) := V_SAL_ARRAY(I)*1.05;
    END IF;
  END LOOP;
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'UPDATE EMPLOYEE_UPDATE SET SALARY = :SALARY WHERE EMPLOYEE_ID = :EMPID AND JOB_ID = :JOBID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':SALARY', V_SAL_ARRAY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':EMPID', V_EMPNO_ARRAY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':JOBID', V_JOBID_ARRAY);
  NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  DBMS_OUTPUT.PUT_LINE('NO_OF_ROWS_DEST = '||NO_OF_ROWS_DEST);
  
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
EXEC SP_UPDATE_EMP_BULK;
SELECT * FROM EMPLOYEES
WHERE JOB_ID IN ('SA_MAN');
SELECT * FROM EMPLOYEE_UPDATE
WHERE JOB_ID IN ('SA_MAN');
DELETE:
CREATE OR REPLACE PROCEDURE SP_DELETE_EMP_BULK
IS
  EMP_CUR_DDL           INTEGER;
  EMP_CUR_SRC           INTEGER;
  EMP_CUR_DEST          INTEGER;
  --
  V_DEPTNO_ARRAY        DBMS_SQL.NUMBER_TABLE;
  --
  V_MIN_COUNT           INTEGER := 0;
  V_MAX_COUNT           INTEGER;
  NO_OF_ROWS_SRC        INTEGER;
  NO_OF_ROWS_DEST       INTEGER;
BEGIN
  BEGIN
    EMP_CUR_DDL := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(EMP_CUR_DDL, 'DROP TABLE EMPLOYEE_DELETE', DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  --
  EMP_CUR_DDL := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DDL, 'CREATE TABLE EMPLOYEE_DELETE AS SELECT * FROM EMPLOYEES', DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
  COMMIT;
  --
  SELECT COUNT(DISTINCT DEPARTMENT_ID)
  INTO V_MAX_COUNT
  FROM EMPLOYEES
  WHERE JOB_ID IN ('SA_MAN', 'SA_REP', 'SH_CLERK', 'ST_CLERK', 'ST_MAN');
  --
  DBMS_OUTPUT.PUT_LINE('COUNT -> '||V_MAX_COUNT);
  --
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEE_DELETE WHERE JOB_ID IN (''SA_MAN'', ''SA_REP'', ''SH_CLERK'', ''ST_CLERK'', ''ST_MAN'')', DBMS_SQL.NATIVE);
  --
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 1, V_DEPTNO_ARRAY, V_MAX_COUNT, V_MIN_COUNT);
  --
  NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE(EMP_CUR_SRC);
  --
  LOOP
    NO_OF_ROWS_SRC := DBMS_SQL.FETCH_ROWS(EMP_CUR_SRC);      
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_DEPTNO_ARRAY);
    EXIT WHEN NO_OF_ROWS_SRC <> V_MAX_COUNT;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  DBMS_OUTPUT.PUT_LINE('NO OF ROWS = '||V_DEPTNO_ARRAY.COUNT);
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'DELETE EMPLOYEE_DELETE WHERE DEPARTMENT_ID = :DEPTID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':DEPTID', V_DEPTNO_ARRAY);
  NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  DBMS_OUTPUT.PUT_LINE('NO_OF_ROWS_DEST = '||NO_OF_ROWS_DEST);
  
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
EXEC SP_DELETE_EMP_BULK;
SELECT COUNT(DISTINCT DEPARTMENT_ID) COUNT_DEPT
FROM EMPLOYEES
WHERE JOB_ID IN ('SA_MAN', 'SA_REP', 'SH_CLERK', 'ST_CLERK', 'ST_MAN');
SELECT COUNT(DISTINCT DEPARTMENT_ID) COUNT_DEPT
FROM EMPLOYEE_DELETE
WHERE JOB_ID IN ('SA_MAN', 'SA_REP', 'SH_CLERK', 'ST_CLERK', 'ST_MAN');

EXAMPLE OF RETURNING CLAUSE:

SINGLE ROW INSERT:
EXAMPLE 1:
CREATE TABLE EMPLOYEES_RETURN_TEST
AS
SELECT * FROM EMPLOYEES
WHERE 1 = 2;
CREATE OR REPLACE PROCEDURE SP_SINGLE_ROW_INSERT_RET(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
  EMP_CUR_SRC           INTEGER;
  EMP_CUR_DEST          INTEGER;
  --
  V_EMPLOYEE_ID         EMPLOYEES.EMPLOYEE_ID%TYPE;    
  V_FIRST_NAME          EMPLOYEES.FIRST_NAME%TYPE; 
  V_LAST_NAME           EMPLOYEES.LAST_NAME%TYPE; 
  V_EMAIL               EMPLOYEES.EMAIL%TYPE;  
  V_PHONE_NUMBER        EMPLOYEES.PHONE_NUMBER%TYPE;  
  V_HIRE_DATE           EMPLOYEES.HIRE_DATE%TYPE;          
  V_JOB_ID              EMPLOYEES.JOB_ID%TYPE; 
  V_SALARY              EMPLOYEES.SALARY%TYPE;   
  V_COMMISSION_PCT      EMPLOYEES.COMMISSION_PCT%TYPE;   
  V_MANAGER_ID          EMPLOYEES.MANAGER_ID%TYPE;     
  V_DEPARTMENT_ID       EMPLOYEES.DEPARTMENT_ID%TYPE; 
  --
  V_NO_OF_ROWS_SRC      INTEGER;
  V_NO_OF_ROWS_DEST     INTEGER;
  V_EMP_RETURN_INFO     EMPLOYEES.EMPLOYEE_ID%TYPE; 
BEGIN
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = :EMPID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_SRC, ':EMPID', P_EMPID);
  --
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 1, V_EMPLOYEE_ID);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 2, V_FIRST_NAME, 20);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 3, V_LAST_NAME, 25);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 4, V_EMAIL, 25);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 5, V_PHONE_NUMBER, 20);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 6, V_HIRE_DATE);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 7, V_JOB_ID, 10);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 8, V_SALARY);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 9, V_COMMISSION_PCT);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 10, V_MANAGER_ID);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 11, V_DEPARTMENT_ID);
  --
  V_NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE_AND_FETCH(EMP_CUR_SRC);
  --
  
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'INSERT INTO EMPLOYEES_RETURN_TEST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES('||
                               ':EMPLOYEE_ID, :FIRST_NAME, :LAST_NAME, :EMAIL, :PHONE_NUMBER, :HIRE_DATE, :JOB_ID, :SALARY, :COMMISSION_PCT, :MANAGER_ID, :DEPARTMENT_ID)'||
                               'RETURNING EMPLOYEE_ID INTO :V_EMP_RETURN_INFO', DBMS_SQL.NATIVE);
 
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPLOYEE_ID);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_FIRST_NAME);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 3, V_LAST_NAME);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 4, V_EMAIL);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 5, V_PHONE_NUMBER);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 6, V_HIRE_DATE);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 7, V_JOB_ID);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 8, V_SALARY);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 9, V_COMMISSION_PCT);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 10, V_MANAGER_ID);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 11, V_DEPARTMENT_ID);  
  --
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMPLOYEE_ID', V_EMPLOYEE_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':FIRST_NAME', V_FIRST_NAME);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':LAST_NAME', V_LAST_NAME);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMAIL', V_EMAIL);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':PHONE_NUMBER', V_PHONE_NUMBER);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':HIRE_DATE', V_HIRE_DATE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':JOB_ID', V_JOB_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':SALARY', V_SALARY);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':COMMISSION_PCT', V_COMMISSION_PCT);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':MANAGER_ID', V_MANAGER_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':DEPARTMENT_ID', V_DEPARTMENT_ID);
  --
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':V_EMP_RETURN_INFO', V_EMP_RETURN_INFO);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  --
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':V_EMP_RETURN_INFO', V_EMP_RETURN_INFO);
  --
  DBMS_OUTPUT.PUT_LINE('INSERTED EMPLOYEE DETAILS = '||V_EMP_RETURN_INFO);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
EXEC SP_SINGLE_ROW_INSERT_RET(100);
SELECT * FROM EMPLOYEES_RETURN_TEST;
EXAMPLE 2:
CREATE OR REPLACE PROCEDURE SP_SINGLE_ROW_INSERT_RET
(P_EMPLOYEE_ID         EMPLOYEES.EMPLOYEE_ID%TYPE,
 P_FIRST_NAME          EMPLOYEES.FIRST_NAME%TYPE,
 P_LAST_NAME           EMPLOYEES.LAST_NAME%TYPE,
 P_EMAIL               EMPLOYEES.EMAIL%TYPE,
 P_PHONE_NUMBER        EMPLOYEES.PHONE_NUMBER%TYPE,
 P_HIRE_DATE           EMPLOYEES.HIRE_DATE%TYPE,
 P_JOB_ID              EMPLOYEES.JOB_ID%TYPE,
 P_SALARY              EMPLOYEES.SALARY%TYPE,
 P_COMMISSION_PCT      EMPLOYEES.COMMISSION_PCT%TYPE,
 P_MANAGER_ID          EMPLOYEES.MANAGER_ID%TYPE,
 P_DEPARTMENT_ID       EMPLOYEES.DEPARTMENT_ID%TYPE,
 P_EMPID           OUT EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS
  EMP_CUR_DEST          INTEGER;
  --
  V_NO_OF_ROWS_DEST     INTEGER;
BEGIN
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'INSERT INTO EMPLOYEES_RETURN_TEST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)'||
                               ' VALUES(:EMPLOYEE_ID, :FIRST_NAME, :LAST_NAME, :EMAIL, :PHONE_NUMBER, :HIRE_DATE, :JOB_ID, :SALARY, :COMMISSION_PCT, :MANAGER_ID, :DEPARTMENT_ID)'||
                               'RETURNING EMPLOYEE_ID INTO :V_EMP_RETURN_INFO', DBMS_SQL.NATIVE);
 
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMPLOYEE_ID', P_EMPLOYEE_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':FIRST_NAME', P_FIRST_NAME);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':LAST_NAME', P_LAST_NAME);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMAIL', P_EMAIL);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':PHONE_NUMBER', P_PHONE_NUMBER);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':HIRE_DATE', P_HIRE_DATE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':JOB_ID', P_JOB_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':SALARY', P_SALARY);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':COMMISSION_PCT', P_COMMISSION_PCT);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':MANAGER_ID', P_MANAGER_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':DEPARTMENT_ID', P_DEPARTMENT_ID);
  --
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':V_EMP_RETURN_INFO', P_EMPID);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  --
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':V_EMP_RETURN_INFO', P_EMPID);
  --
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
DECLARE
  CURSOR C1
  IS
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  V_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE;
  EMP_CUR_DDL           INTEGER;
BEGIN
  EMP_CUR_DDL := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DDL, 'TRUNCATE TABLE EMPLOYEES_RETURN_TEST', DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
  FOR I IN C1
  LOOP
    SP_SINGLE_ROW_INSERT_RET(I.EMPLOYEE_ID, I.FIRST_NAME, I.LAST_NAME, I.EMAIL, I.PHONE_NUMBER, I.HIRE_DATE, I.JOB_ID, I.SALARY, I.COMMISSION_PCT, I.MANAGER_ID, I.DEPARTMENT_ID, V_EMPID);
    DBMS_OUTPUT.PUT_LINE('INSERTED EMPLOYEE DETAILS => EMPLOYEE_ID ->'||V_EMPID||' EMPLOYEE_NAME ->'||I.FIRST_NAME);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DDL) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
    END IF;
    RAISE;
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST;
MULTIPLE ROW INISERT:
CREATE OR REPLACE PROCEDURE SP_MULTIPLE_ROW_INSERT_RET
(P_DEPTID     EMPLOYEES.DEPARTMENT_ID%TYPE,
 P_EMPID  OUT DBMS_SQL.NUMBER_TABLE)
IS
  EMP_CUR_DDL           INTEGER;
  EMP_CUR_SRC           INTEGER;
  EMP_CUR_DEST          INTEGER;
  --
  V_MAX_COUNT           INTEGER;
  V_MIN_COUNT           INTEGER := 0;
  --
  V_EMPLOYEE_ID         DBMS_SQL.NUMBER_TABLE;    
  V_FIRST_NAME          DBMS_SQL.VARCHAR2_TABLE; 
  V_LAST_NAME           DBMS_SQL.VARCHAR2_TABLE; 
  V_EMAIL               DBMS_SQL.VARCHAR2_TABLE;  
  V_PHONE_NUMBER        DBMS_SQL.VARCHAR2_TABLE;  
  V_HIRE_DATE           DBMS_SQL.DATE_TABLE;          
  V_JOB_ID              DBMS_SQL.VARCHAR2_TABLE; 
  V_SALARY              DBMS_SQL.NUMBER_TABLE;   
  V_COMMISSION_PCT      DBMS_SQL.NUMBER_TABLE;   
  V_MANAGER_ID          DBMS_SQL.NUMBER_TABLE;     
  V_DEPARTMENT_ID       DBMS_SQL.NUMBER_TABLE; 
  --
  V_NO_OF_ROWS_SRC      INTEGER;
  V_NO_OF_ROWS_DEST     INTEGER;
  V_NAME                VARCHAR2(100);
BEGIN
  EMP_CUR_DDL := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DDL, 'TRUNCATE TABLE EMPLOYEES_RETURN_TEST', DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DDL);
  --
  SELECT COUNT(1)
  INTO V_MAX_COUNT
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = P_DEPTID;
  --
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = :DEPTID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_SRC, ':DEPTID', P_DEPTID);
  --
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 1, V_EMPLOYEE_ID, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 2, V_FIRST_NAME, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 3, V_LAST_NAME, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 4, V_EMAIL, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 5, V_PHONE_NUMBER, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 6, V_HIRE_DATE, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 7, V_JOB_ID, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 8, V_SALARY, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 9, V_COMMISSION_PCT, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 10, V_MANAGER_ID, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 11, V_DEPARTMENT_ID, V_MAX_COUNT, V_MIN_COUNT);
  --
  V_NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE(EMP_CUR_SRC);
  --
  LOOP
    V_NO_OF_ROWS_SRC := DBMS_SQL.FETCH_ROWS(EMP_CUR_SRC);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPLOYEE_ID);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_FIRST_NAME);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 3, V_LAST_NAME);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 4, V_EMAIL);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 5, V_PHONE_NUMBER);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 6, V_HIRE_DATE);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 7, V_JOB_ID);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 8, V_SALARY);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 9, V_COMMISSION_PCT);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 10, V_MANAGER_ID);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 11, V_DEPARTMENT_ID);
    --
    EXIT WHEN V_NO_OF_ROWS_SRC <> V_MAX_COUNT;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  --
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'INSERT INTO EMPLOYEES_RETURN_TEST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES('||
                               ':EMPLOYEE_ID, :FIRST_NAME, :LAST_NAME, :EMAIL, :PHONE_NUMBER, :HIRE_DATE, :JOB_ID, :SALARY, :COMMISSION_PCT, :MANAGER_ID, :DEPARTMENT_ID)'||
                               'RETURNING EMPLOYEE_ID INTO :V_EMP_RETURN_INFO', DBMS_SQL.NATIVE);
  --
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':EMPLOYEE_ID', V_EMPLOYEE_ID);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':FIRST_NAME', V_FIRST_NAME);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':LAST_NAME', V_LAST_NAME);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':EMAIL', V_EMAIL);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':PHONE_NUMBER', V_PHONE_NUMBER);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':HIRE_DATE', V_HIRE_DATE);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':JOB_ID', V_JOB_ID);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':SALARY', V_SALARY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':COMMISSION_PCT', V_COMMISSION_PCT);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':MANAGER_ID', V_MANAGER_ID);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':DEPARTMENT_ID', V_DEPARTMENT_ID);
  --
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':V_EMP_RETURN_INFO', P_EMPID);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  --
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':V_EMP_RETURN_INFO', P_EMPID);
  --
  FOR I IN P_EMPID.FIRST..P_EMPID.LAST
  LOOP
    SELECT FIRST_NAME||' '||LAST_NAME
    INTO V_NAME
    FROM EMPLOYEES_RETURN_TEST
    WHERE EMPLOYEE_ID = P_EMPID(I);
    DBMS_OUTPUT.PUT_LINE('INSERTED EMPLOYEE => '||'ID -> '||P_EMPID(I)||' NAME -> '||V_NAME);
  END LOOP;
  
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
SET SERVEROUTPUT ON;
DECLARE
  V_EMPID DBMS_SQL.NUMBER_TABLE;
BEGIN
  SP_MULTIPLE_ROW_INSERT_RET(30, V_EMPID);
END;
/
SINGLE ROW UPDATE:
CREATE OR REPLACE PROCEDURE SP_SINGLE_ROW_UPDATE_RET
(P_EMPID       EMPLOYEES.EMPLOYEE_ID%TYPE,
 P_NEW_COMM    EMPLOYEES.COMMISSION_PCT%TYPE,
 P_NEW_SAL OUT NUMBER)
IS
  EMP_CUR_SRC           INTEGER;
  EMP_CUR_DEST          INTEGER;
  --
  V_EMPLOYEE_ID         EMPLOYEES.EMPLOYEE_ID%TYPE;    
  V_SALARY              EMPLOYEES.SALARY%TYPE;   
  V_COMMISSION_PCT      EMPLOYEES.COMMISSION_PCT%TYPE; 
  V_NEW_COMMISSION_PCT  EMPLOYEES.COMMISSION_PCT%TYPE;
  --
  V_NO_OF_ROWS_SRC      INTEGER;
  V_NO_OF_ROWS_DEST     INTEGER;
  V_SAL_RETURN_INFO     NUMBER; 
BEGIN
  V_NEW_COMMISSION_PCT := P_NEW_COMM;
  EMP_CUR_SRC := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, SALARY, COMMISSION_PCT FROM EMPLOYEES WHERE EMPLOYEE_ID = :EMPID', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_SRC, ':EMPID', P_EMPID);
  --
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 1, V_EMPLOYEE_ID);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 2, V_SALARY);
  DBMS_SQL.DEFINE_COLUMN(EMP_CUR_SRC, 3, V_COMMISSION_PCT);
  --
  V_NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE_AND_FETCH(EMP_CUR_SRC);
  --
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'UPDATE EMPLOYEES_RETURN_TEST SET SALARY = :SALARY, COMMISSION_PCT = :COMMISSION_PCT WHERE EMPLOYEE_ID = :EMPLOYEE_ID '||
                               'RETURNING (SALARY*(1 + :COMMISSION_PCT)) INTO :V_SAL_RETURN_INFO', DBMS_SQL.NATIVE);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPLOYEE_ID);
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_SALARY);  
  DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 3, V_COMMISSION_PCT);
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE OLD SALARY = '||V_SALARY);
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE OLD COMMISSION_PCT = '||V_COMMISSION_PCT);
  --
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMPLOYEE_ID', V_EMPLOYEE_ID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':SALARY', V_SALARY*(1+V_NEW_COMMISSION_PCT));
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':COMMISSION_PCT', V_NEW_COMMISSION_PCT);
  --
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':V_SAL_RETURN_INFO', P_NEW_SAL);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  --
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':V_SAL_RETURN_INFO', P_NEW_SAL);
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE UPDATED SALARY = '||P_NEW_SAL);
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE UPDATED COMMISSION_PCT = '||P_NEW_COMM);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
    END IF;
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE EMPLOYEE_ID = 100;
DECLARE
  V_SAL  NUMBER := 0;
  V_COMM NUMBER := 0.2;
BEGIN
  SP_SINGLE_ROW_UPDATE_RET(100, V_COMM, V_SAL);
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE EMPLOYEE_ID = 100;
MULTIPLE ROW UPDATE:
CREATE OR REPLACE PROCEDURE SP_MULTIPLE_ROW_UPDATE_RET(
  P_DEPTNO       EMPLOYEES.DEPARTMENT_ID%TYPE,
  P_NEW_COMM     EMPLOYEES.COMMISSION_PCT%TYPE,
  P_NEW_SAL  OUT DBMS_SQL.NUMBER_TABLE)
IS
  EMP_CUR_SRC         INTEGER;
  EMP_CUR_DEST        INTEGER;
  --
  V_EMPLOYEE_ID        DBMS_SQL.NUMBER_TABLE;
  V_SALARY             DBMS_SQL.NUMBER_TABLE;
  V_NEW_COMMISSION_PCT DBMS_SQL.NUMBER_TABLE;
  --
  V_MAX_COUNT          INTEGER;
  V_MIN_COUNT          INTEGER := 0;
  --
  V_NO_OF_ROWS_SRC     INTEGER;
  V_NO_OF_ROWS_DEST    INTEGER;
  V_SAL_RETURN_INFO    NUMBER;
BEGIN
  SELECT COUNT(1)
  INTO V_MAX_COUNT
  FROM EMPLOYEES;
  --
  EMP_CUR_SRC          := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_SRC, 'SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = :P_DEPTNO', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_SRC, ':P_DEPTNO', P_DEPTNO);
  --
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 1, V_EMPLOYEE_ID, V_MAX_COUNT, V_MIN_COUNT);
  DBMS_SQL.DEFINE_ARRAY(EMP_CUR_SRC, 2, V_SALARY, V_MAX_COUNT, V_MIN_COUNT);
  --
  V_NO_OF_ROWS_SRC := DBMS_SQL.EXECUTE(EMP_CUR_SRC);
  --
  LOOP
    V_NO_OF_ROWS_SRC := DBMS_SQL.FETCH_ROWS(EMP_CUR_SRC);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 1, V_EMPLOYEE_ID);
    DBMS_SQL.COLUMN_VALUE(EMP_CUR_SRC, 2, V_SALARY);
    EXIT WHEN V_NO_OF_ROWS_SRC <> V_MAX_COUNT;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  --
  FOR I IN V_SALARY.FIRST..V_SALARY.LAST
  LOOP
    V_SALARY(I) := V_SALARY(I)*(1 + P_NEW_COMM);
    V_NEW_COMMISSION_PCT(I) := P_NEW_COMM;
  END LOOP;
  --
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'UPDATE EMPLOYEES_RETURN_TEST SET SALARY = :SALARY, COMMISSION_PCT = :COMMISSION_PCT WHERE EMPLOYEE_ID = :EMPLOYEE_ID '|| 'RETURNING SALARY INTO :V_SAL_RETURN_INFO', DBMS_SQL.NATIVE);
  --
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':EMPLOYEE_ID', V_EMPLOYEE_ID);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':SALARY', V_SALARY);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':COMMISSION_PCT', V_NEW_COMMISSION_PCT);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':V_SAL_RETURN_INFO', P_NEW_SAL);
  --
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':V_SAL_RETURN_INFO', P_NEW_SAL);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  --
  FOR I IN V_SALARY.FIRST..V_SALARY.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('UPDATED EMPLOYEE => '||'ID -> '||V_EMPLOYEE_ID(I)||' NEW COMMISSION -> '||V_NEW_COMMISSION_PCT(I)*100||'%'||' NEW SALARY -> '||V_SALARY(I));
  END LOOP;
  --
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  IF DBMS_SQL.IS_OPEN(EMP_CUR_SRC) THEN
    DBMS_SQL.CLOSE_CURSOR(EMP_CUR_SRC);
  END IF;
  IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
    DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
  END IF;
  RAISE;
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE DEPARTMENT_ID = 30;
DECLARE
  V_NEW_SAL     DBMS_SQL.NUMBER_TABLE;
  V_EMPLOYEE_ID DBMS_SQL.NUMBER_TABLE;
  V_SALARY      DBMS_SQL.NUMBER_TABLE;
BEGIN  
  SP_MULTIPLE_ROW_UPDATE_RET(30, .2, V_SALARY);
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE DEPARTMENT_ID = 30;
SINGLE ROW DELETE:
CREATE OR REPLACE PROCEDURE SP_SINGLE_ROW_DELETE_RET
(P_EMPID       EMPLOYEES.EMPLOYEE_ID%TYPE,
 P_EMPNO   OUT EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
  EMP_CUR_DEST          INTEGER;
  --
  V_NO_OF_ROWS_DEST     INTEGER;
BEGIN
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'DELETE EMPLOYEES_RETURN_TEST WHERE EMPLOYEE_ID = :EMPLOYEE_ID '||
                               'RETURNING EMPLOYEE_ID INTO :PEMPNO', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':EMPLOYEE_ID', P_EMPID);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':PEMPNO', P_EMPNO);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':PEMPNO', P_EMPNO);
  --
  DBMS_OUTPUT.PUT_LINE('DELETED EMPLOYEE = '||P_EMPNO);
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
      DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
    END IF;
    RAISE;
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE EMPLOYEE_ID = 207;
DECLARE
  V_EMPNO  EMPLOYEES.EMPLOYEE_ID%TYPE;
BEGIN
  SP_SINGLE_ROW_DELETE_RET(207, V_EMPNO);
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE EMPLOYEE_ID = 207;
MULTIPLE ROW DELETE:
CREATE OR REPLACE PROCEDURE SP_MULTIPLE_ROW_DELETE_RET(
  P_DEPTID          EMPLOYEES.DEPARTMENT_ID%TYPE,
  P_EMPID_ARRAY OUT DBMS_SQL.NUMBER_TABLE)
IS
  EMP_CUR_DEST      INTEGER;
  --
  V_NO_OF_ROWS_DEST INTEGER;
BEGIN
  EMP_CUR_DEST := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(EMP_CUR_DEST, 'DELETE EMPLOYEES_RETURN_TEST WHERE DEPARTMENT_ID = :P_DEPTID '|| 'RETURNING EMPLOYEE_ID INTO :P_EMPID_ARRAY', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(EMP_CUR_DEST, ':P_DEPTID', P_DEPTID);
  DBMS_SQL.BIND_ARRAY(EMP_CUR_DEST, ':P_EMPID_ARRAY', P_EMPID_ARRAY);
  --
  V_NO_OF_ROWS_DEST := DBMS_SQL.EXECUTE(EMP_CUR_DEST);
  DBMS_SQL.VARIABLE_VALUE(EMP_CUR_DEST, ':P_EMPID_ARRAY', P_EMPID_ARRAY);
  --
  FOR I IN P_EMPID_ARRAY.FIRST..P_EMPID_ARRAY.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('DELETED EMPLOYEE = '||P_EMPID_ARRAY(I));
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  IF DBMS_SQL.IS_OPEN(EMP_CUR_DEST) THEN
    DBMS_SQL.CLOSE_CURSOR(EMP_CUR_DEST);
  END IF;
  RAISE;
END;
/
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE DEPARTMENT_ID = 30;
DECLARE
  V_EMPLOYEE_ID DBMS_SQL.NUMBER_TABLE;
BEGIN  
  SP_MULTIPLE_ROW_DELETE_RET(30, V_EMPLOYEE_ID);
END;
/
DBMS_SQL Package: Multiple row delete
SELECT * FROM EMPLOYEES_RETURN_TEST
WHERE DEPARTMENT_ID = 30;

Author

Related Post
  1. The Ultimate Guide to PLSQL Compiler
  2. Working with Stored Procedure Parameters
  3. How to compile invalid trigger in Oracle?