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
---------------------
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
-------------------
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
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
---------------------------
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
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
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
------------------------
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: -
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;
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 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.
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 (n NUMBER);
PROCEDUREprint_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDUREinit_pkg_state (n 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 (n NUMBER);
PROCEDUREprint_pkg_state;
END pkg1;
CREATE PACKAGE BODY pkg1
IS
PROCEDUREinit_pkg_state (n 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;
------------------------------------------
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