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 TABLE emp_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 TABLE emp_by_dept
AS
SELECT employee_id, department_id
FROM employees
WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF
departments.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 INTO
emp_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
/* Formatted on 2/4/2015 9:16:55 PM (QP5 v5.115.810.9015) */
------------------------
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
/* Formatted on 2/4/2015 9:16:55 PM (QP5 v5.115.810.9015) */
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;
PROCEDURE
in_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).
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;
PROCEDURE
init_pkg_state (n NUMBER);
PROCEDURE
print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE
init_pkg_state (n NUMBER)
IS
BEGIN
pkg1.num := n;
END;
PROCEDURE
print_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;
PROCEDURE
init_pkg_state (n NUMBER);
PROCEDURE
print_pkg_state;
END pkg1;
CREATE PACKAGE BODY pkg1
IS
PROCEDURE
init_pkg_state (n NUMBER)
IS
BEGIN
pkg1.num := n;
END;
PROCEDURE
print_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
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;
0 comments :
Post a Comment