Oracle SQL: Advance Pl/sql

FORALL

FORALL Concept:-
---------------------

FORALL concept help us reducing the Iterations between PL/SQL Block and SQL Engine.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.

If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.

Example:-

CREATE TABLEemp_by_dept

SELECT employee_id, department_id

FROM employees

WHERE 1 = 0;

DECLARE

TYPE dept_tab IS TABLE OFdepartments.department_id%TYPE;

deptnums dept_tab;

TYPE NumList IS TABLE OF NUMBER;

-- The zeros in this list will cause divide-by-zero errors.

num_tab NumList

:= NumList (10,

11,

12,

30,

20,

199,

1) ;

errors NUMBER;

dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT (dml_errors, -24381);

BEGIN

SELECT department_id

BULK COLLECT

INTO deptnums

FROM departments;

-- SAVE EXCEPTIONS means don't stop if some INSERT fail.

FORALL i IN 1 ..deptnums.COUNT

SAVE EXCEPTIONS

INSERT INTOemp_by_dept

SELECT employee_id, department_id

FROM employees

WHERE department_id = deptnums (i);

FOR i IN 1 ..deptnums.COUNT

LOOP

-- Count how many rows were inserted for each department; that is,

-- how many employees are in each department.

DBMS_OUTPUT.put_line( 'Dept '

|| deptnums (i)

|| ': inserted '

|| SQL%BULK_ROWCOUNT (i)

|| ' records');

END LOOP;

DBMS_OUTPUT.put_line ('Total records inserted =' || SQL%ROWCOUNT);

-- If any errors occurred during the FORALL SAVE EXCEPTIONS,

-- a single exception is raised when the statement completes.

EXCEPTION

WHEN dml_errors

THEN -- Now we figure out what failed and why.

errors := SQL%BULK_EXCEPTIONS.COUNT;

DBMS_OUTPUT.put_line (

'Number of INSERT statements that failed: ' || errors

);

FOR i IN 1 .. errors

LOOP

DBMS_OUTPUT.put_line( 'Error #'

|| i

|| ' occurred during '

|| 'iteration #'

|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

DBMS_OUTPUT.put_line('Error message is '

|| SQLERRM (-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

END LOOP;

END;

BULK COLLECT

BULK COLLECT:-
-------------------

Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.

If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.

Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.

Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.

In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.

Example:-
-----------

DECLARE

TYPE array

TABLE OF NUMBER

INDEX BY BINARY_INTEGER;

l_data array;

CURSOR c

SELECT empno FROM emp;

BEGIN

OPEN c;

LOOP

FETCH c BULK COLLECT INTO l_data LIMIT 10;

IF (c%NOTFOUND)

THEN

DBMS_OUTPUT.put_line( 'Cursor returned NOT FOUND but array has '

|| l_data.COUNT

|| ' left to process');

ELSE

DBMS_OUTPUT.put_line ('We have ' || l_data.COUNT || ' to process');

END IF;

EXIT WHEN c%NOTFOUND;

END LOOP;

CLOSE c;

END;
Returning clause with the Bulk collect with the DML opeartions:-
---------------------------------------------------------------------------

DELETE FROM emp

WHERE num = 30

RETURNING empno, ename BULK COLLECT INTO p_num, p_ename

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE:-
---------------------------

Note:- This type of Dynamic SQL would not work in the 10.7 version Database.

For the Select statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
----------

DECLARE

L_DEPTNO NUMBER DEFAULT 10 ;

L_SAL NUMBER;

BEGIN

EXECUTE IMMEDIATE 'select max(sal) from emp

where deptno = :l_deptno'

INTO L_SAL

USING L_DEPTNO;

DBMS_OUTPUT.PUT_LINE (L_SAL);

END;
For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
-----------

DECLARE

L_ENAME VARCHAR2 (20) DEFAULT 'PHANI' ;

L_EMPNO NUMBER DEFAULT 2 ;

L_DEPTNO NUMBER DEFAULT 10 ;

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO EMP(ENAME,EMPNO,DEPTNO) VALUES

(:L_ENAME,:L_EMPNO,:L_DEPTNO)'

USING L_ENAME, L_EMPNO,L_DEPTNO;

END;
For the Update Statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
-----------

DECLARE

L_ENAME VARCHAR2 (20) DEFAULT 'PHANI' ;

L_EMPNO NUMBER DEFAULT 2 ;

L_DEPTNO NUMBER DEFAULT 10 ;

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO EMP(ENAME,EMPNO,DEPTNO) VALUES

(:L_ENAME,:L_EMPNO,:L_DEPTNO)'

USING L_ENAME, L_EMPNO,L_DEPTNO;

END;

NOCOPY

NOCOPY Hint Demo:-
------------------------

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.

When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged. The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.

With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.

The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter.

nocopy.sql

SET SERVEROUTPUT ON

DECLARE

TYPE t_tab IS TABLE OF VARCHAR2 (32767);

l_tab t_tab := t_tab ();

l_start NUMBER;

PROCEDURE in_out (p_tab IN OUT t_tab)

BEGIN

NULL;

END;

PROCEDUREin_out_nocopy (p_tab IN OUT NOCOPY t_tab)

BEGIN

NULL;

END;

BEGIN

l_tab.EXTEND;

l_tab (1) := '1234567890123456789012345678901234567890';

l_tab.EXTEND (999999, 1); -- Copy element 1 into 2..1000000

-- Time normal IN OUT

l_start := DBMS_UTILITY.get_time;

in_out (l_tab);

DBMS_OUTPUT.put_line ('IN OUT : ' || (DBMS_UTILITY.get_time - l_start));

-- Time IN OUT NOCOPY

l_start := DBMS_UTILITY.get_time;

in_out_nocopy (l_tab); -- pass IN OUT NOCOPY parameter

DBMS_OUTPUT.put_line (

'IN OUT NOCOPY: ' || (DBMS_UTILITY.get_time - l_start)

);

END;

The output of the script clearly demonstrates the performance improvements possible when using the NOCOPY hint.

SQL> @nocopy.sql

IN OUT : 122
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.

Pragma

PRAGMA
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

1)Autonomous Transaction

Autonomous Transactions is the child transaction, which are Independent of Parent transactions. In Our Example, p1 is child transaction, which is used in the Parent transaction.

Example: -

CREATE or REPLACE Procedure p1 IS
Pragma Autonomous_transaction;

BEGIN

INSERT INTO TEST_T

VALUES (1111, 'PHANI1');

COMMIT;

END;

In the Declaration section, you will declare this Transaction as the Autonomous Transaction.

DECLARE

A NUMBER;

BEGIN

INSERT INTO TEST_T

VALUES (2222, 'JACK');

P1;

ROLLBACK;

END;NOW Table has (1111,’PHANI’) Record. COMMIT in the PROCEDURE P1 have not commit the Outside (p1) DML operations. It will just commit p1 transactions.

The ROLLBACK will not rollback PHANI record, it will just rollback the JACK record.

CREATE OR REPLACE PROCEDURE p1

BEGIN

INSERT INTO TEST_T

VALUES (1111, 'PHANI1');

COMMIT;

END;

If I remove the Pragma Autonomous_transaction From the declaration section, then this transaction will become the normal transaction. Now if you try to use the same parent transaction as given below.

>> delete from TEST_T;

DECLARE

A NUMBER;

BEGIN

INSERT INTO TEST_T

VALUES (2222, 'JACK');

P1; -- This transaction has ended with the COMMIT;

ROLLBACK;

END;

After executing the above transaction, you can see BOTH records got Inserted (PHANI and JACK records). Here COMMIT in P1 will commit both transactions (PHANI and JACK Records Insert) And then Rollback. Since, there are no transactions happening between COMMIT and ROLLBACK. Our ROLLBACK is not doing any ROLLBACK.

Note: - IF COMMIT is not given in P1 then, the ROLLBACK will do the ROLLBACK both the INSERT transaction (PHANI Record which is in p1 procedure and JACK Record).

2) Pragma Restrict_references

It gives the Purity Level of the Function in the package.

CREATE OR REPLACE PACKAGE PKG12

FUNCTION F1

RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES (F1, WNDS, RNDS, WNPS, RNPS);

END PKG12;

CREATE OR REPLACE PACKAGE BODY PKG12

FUNCTION F1

RETURN NUMBER

X NUMBER;

BEGIN

SELECT EMPNO

INTO X

FROM SCOTT.EMP

WHERE ENAME LIKE 'SCOTT';

DBMS_OUTPUT.PUT_LINE (X);

RETURN (X);

END F1;

END PKG12;You will get the Violate It’s Associated Pragma Error. This in Purity Level, we said

It cannot read from the database. RNDS (In Our Function F1, we have SELECT STATEMENT which is reading the data from the database).

3) Pragma SERIALLY_REUSABLE

In my 10 Years of Experience in the Oracle Applications, I have never found the requirement to use this feature :). But, I found this feature is used in some standard Oracle Packages. We may use this feature for improving the performance or to meet certain requirements.

This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.

You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

Examples

WITH PRAGMA SERIALLY_REUSABLE

The following example creates a serially reusable package:

CREATE PACKAGE pkg1

PRAGMA SERIALLY_REUSABLE;

num NUMBER := 0;

PROCEDUREinit_pkg_state (n NUMBER);

PROCEDUREprint_pkg_state;

END pkg1;

CREATE PACKAGE BODY pkg1

PRAGMA SERIALLY_REUSABLE;

PROCEDUREinit_pkg_state (n NUMBER)

BEGIN

pkg1.num := n;

END;

PROCEDUREprint_pkg_state

BEGIN

DBMS_OUTPUT.put_line ('Num: ' || pkg1.num);

END;

END pkg1;

BEGIN

pkg1.init_pkg_state (10);

pkg1.PRINT_PKG_STATE;

END;

Num: 10

begin

pkg1.PRINT_PKG_STATE;

end;

Num: 0

Note: - The first block is changing the value of the variable (num) to 10 and if I check the value in same block then it is showing the changed value that is 10. But, if I try to check the value of the (num) variable then it should the default value given to it (i.e.) “0”

WITHOUT PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE pkg1

num NUMBER := 0;

PROCEDUREinit_pkg_state (n NUMBER);

PROCEDUREprint_pkg_state;

END pkg1;

CREATE PACKAGE BODY pkg1

PROCEDUREinit_pkg_state (n NUMBER)

BEGIN

pkg1.num := n;

END;

PROCEDUREprint_pkg_state

BEGIN

DBMS_OUTPUT.put_line ('Num: ' || pkg1.num);

END;

END pkg1;

BEGIN

pkg1.init_pkg_state (10);

pkg1.PRINT_PKG_STATE;

>>Num: 10

begin

pkg1.PRINT_PKG_STATE;

end;

>>Num: 10

Note: - Now, you may noticed the difference. The second block is giving us the changed value.

DROP PACKAGE pkg1;

(There are many other pragma's like Pragma Exception_init etc. I have not convered these concepts in this article. I will cover them in Exception concept article).

Display the number value in Words:-
------------------------------------------

The following query can be used to display the number in the words.

select 'Your Number', (to_char(to_date('Your Number','j'), 'jsp')) from dual;

Example:-

select 211, (to_char(to_date(211,'j'), 'jsp')) from dual;