Monday, January 30, 2017

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
AS
   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,
                     0,
                     11,
                     12,
                     30,
                     0,
                     20,
                     199,
                     2,
                     0,
                     9,
                     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;

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
   IS
      TABLE OF NUMBER
         INDEX BY BINARY_INTEGER;

   l_data   array;

   CURSOR c
   IS
      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)
   IS
   BEGIN
      NULL;
   END;

   PROCEDUREin_out_nocopy (p_tab IN OUT NOCOPY t_tab)
   IS
   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
IS
BEGIN
   INSERT INTO TEST_T
     VALUES   (1111, 'PHANI1');

   COMMIT;
END;

If I remove the Pragma Autonomous_transaction From the declaration sectionthen 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
AS
   FUNCTION F1
      RETURN NUMBER;

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

CREATE OR REPLACE PACKAGE BODY PKG12
AS
   FUNCTION F1
      RETURN NUMBER
   IS
      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
IS
   PRAGMA SERIALLY_REUSABLE;
   num   NUMBER := 0;

   PROCEDUREinit_pkg_state (NUMBER);

   PROCEDUREprint_pkg_state;
END pkg1;
/

CREATE PACKAGE BODY pkg1
IS
   PRAGMA SERIALLY_REUSABLE;

   PROCEDUREinit_pkg_state (NUMBER)
   IS
   BEGIN
      pkg1.num := n;
   END;

   PROCEDUREprint_pkg_state
   IS
   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
IS
   num   NUMBER := 0;

   PROCEDUREinit_pkg_state (NUMBER);

   PROCEDUREprint_pkg_state;
END pkg1;

CREATE PACKAGE BODY pkg1
IS
   PROCEDUREinit_pkg_state (NUMBER)
   IS
   BEGIN
      pkg1.num := n;
   END;

   PROCEDUREprint_pkg_state
   IS
   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;

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...