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;