You are html tracking Visitor

Thursday, July 3, 2008

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 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 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 5 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;

end;

>>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).

13 comments:

Unknown said...

Thanks for sharing this interesting and useful blog..

Oracle Training

Melisa said...


The information you have given here is truly helpful to me. CCNA- It’s a certification program based on routing & switching for starting level network engineers that helps improve your investment in knowledge of networking & increase the value of employer’s network,
Regards,
ccna course in Chennai|ccna training in Chennai

Unknown said...

Pretty Post! It is really interesting to read from the beginning & I would like to share your blog to my circles for getting awesome knowledge, keep your blog as updated.
Regards,
SAP training in chennai|SAP courses in chennai|SAP Training in Chennai|sap course in Chennai

rmouniak said...

It's really interesting blog Thanks for sharing
Oracle SOA Online Training Hyderabad

Anonymous said...

Excellent Blog! I would like to thank for the efforts you have made in writing this post.
I am hoping the same best work from you in the future as well.


Big Data Hadoop Training In Chennai | Big Data Hadoop Training In anna nagar | Big Data Hadoop Training In omr | Big Data Hadoop Training In porur | Big Data Hadoop Training In tambaram | Big Data Hadoop Training In velachery

vivekvedha said...

Great blog lot of valuable information there.so good and useful.
acte reviews

acte velachery reviews

acte tambaram reviews

acte anna nagar reviews

acte porur reviews

acte omr reviews

acte chennai reviews

acte student reviews

ram said...

normally its great content thank u so much oracle training in chennai

anand said...

amazing blog
Software Testing Training in Chennai | Certification | Online
Courses

Software Testing Training in Chennai

Software Testing Online Training in Chennai

Software Testing Courses in Chennai

Software Testing Training in Bangalore

Software Testing Training in Hyderabad

Software Testing Training in Coimbatore

Software Testing Training

Software Testing Online Training

rocky said...

Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next
python training in bangalore

python training in hyderabad

python online training

python training

python flask training

python flask online training

python training in coimbatore
python training in chennai

python course in chennai

python online training in chennai


dhinesh said...

Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next.

Full Stack Training in Chennai

Full Stack Course Chennai
Full Stack Training in Bangalore

Full Stack Course in Bangalore

Full Stack Training in Hyderabad

Full Stack Course in Hyderabad

Full Stack Training

Full Stack Course

Full Stack Online Training

Full Stack Online Course


sakthi said...

Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next
Salesforce Training in Chennai

Salesforce Online Training in Chennai

Salesforce Training in Bangalore

Salesforce Training in Hyderabad

Salesforce training in ameerpet

Salesforce Training in Pune

Salesforce Online Training

Salesforce Training


Ramesh Sampangi said...

Amazing information. Informative and knowledgeable content. I really enjoyed while reading your article. Keep sharing more stuff like this. Thank you.
Online Data Science Training in Hyderabad

sri said...


Thanks for sharing this.,
certified scrum master certification
agile scrum master certification