Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle Pragma

By Saurabh Gupta | on July 6, 2011 |
Oracle 11g Tutorials

The language compiler

In a programming language, compiler plays a crucial role in execution of a program. Compiler is a language component which converts a source code, written using one of the interactive programming languages, into machine code, which the server can interpret and act upon.

As a refresher, Oracle compilation technique took a wide turn in 11g release with the induction of real native compilation. The C compiler has been replaced with the native one, thus justifying the ‘real native compilation technique’.

Pragma: The HOW factor in compilation

Oracle has inducted few keywords in its glossary which behave as a clue to the compiler. These keywords are named as Pragma(s), which are read by the compiler to be decisive over the method used for the compilation of current program unit. Note that the method of compilation of a program unit manipulates its run-time behavior.

Being the part of source code, the pragma statement tips the compiler to differently compile the program unit, thus manipulating the behavior of the program unit, currently under compilation. Note that the pragma is interpreted only at the time of compilation and not at the time of execution.

Types of Pragmas

Until Oracle 11g, PL/SQL programming language could make use of five types of pragma(s) which are listed as below.

AUTONOMOUS_TRANSACTION – Compiler allows schema objects like subprograms, PL/SQL blocks, or triggers to commit their transaction within their scope.

RESTRICT_REFERENCES – Allows the compiler to scan the database purity during package compilation. It can be used in packages only.

EXCEPTION_INIT – Compiler associates a user defined exception name with an exception number.

SERIALLY_REUSABLE – Compiler deduces the package state from session level to just a server call. It can be used in Package spec only.

PRAGMA INLINE – New member of oracle pragma family. It is used to explicitly inline a program unit.

Oracle Pragma AUTONOMOUS_TRANSACTION

Oracle 8i release 1 saw the introduction of a new language feature, which allowed having simultaneous active transactions in the same database session.

The autonomous pragma allows a separate active transaction, which is independent of the main transaction in the session. The COMMIT/ROLLBACK done within the autonomous object does not affect the main transaction and vice versa. All in all, it is an individual transaction and not the part of main transaction.

Within a database schema, a standalone or nested subprogram, a trigger, an anonymous PL/SQL block, object type member method or packaged subprogram can be declared as autonomous.

Autonomous Transactions: Working

Often, users refer autonomous transaction as nested transaction, which is wrong. It would be completely mislead the philosophy behind the autonomous transactions. Vocally, there is nothing called nested transactions in database. There is only one main running transaction in a session, which contains all DML statements issued in the session. Single TCL command (commit/rollback) feeds the changes permanently into the database. Autonomous transactions are the voluntary ones which are activated within the main transaction, but share no relationship with it.

For better understanding purpose, autonomous transactions are executed in a separate temporary session created by the database server. The main transaction remains in passive or suspended state until the autonomous transaction is active. As soon as the autonomous transaction is completed, the main transaction resumes the action.

Pragma AUTONOMOUS_TRANSACTION Syntax

 [DECLARE | SUBPROGRAM HEADER]
   PRAGMA AUTONOMOUS_TRANSACTION
   [VARIABLE DECLARATION]
BEGIN
   [EXECUTABLE SECTION]
   [COMMIT | ROLLBACK]
END;

Pragma AUTONOMOUS_TRANSACTION Example Code 1

Suppose I have to implement error tracing mechanism in an application. The mechanism should trace all the exceptions raised in the application along with its host object details. The mechanism must handle the exception propagation situations and rolling back situations.

I create a procedure P_TRACK_ERROR as below. I capture and insert the required information into a table and mark this operation as autonomous transaction. In this way, I segregate the transaction performed in this procedure, from the main transaction. Therefore, even if the raised exception causes rollback to the current ongoing transaction, P_TRACK_ERROR would successfully capture the exception details.

CREATE OR REPLACE PROCEDURE P_TRACK_ERROR(P_CALLED_FROM VARCHAR2,
                                          P_OBJ_TYPE VARCHAR2,
                                          P_LINE VARCHAR2,
                                          P_SQLCODE VARCHAR2,
                                          P_SQLMSG VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SESSION_ERROR_TRACK (USERNAME,
                                 ERR_DATE,
                                 OBJ_NAME, 
                                 OBJ_TYPE,
                                 LINE,
                                 ERR_CODE,
                                 ERR_MSG)
VALUES (USER, SYSDATE, P_CALLED_FROM, P_OBJ_TYPE, P_LINE, P_SQLCODE, P_SQLMSG);
COMMIT;
END;

Let’s take a look at the above procedure to record the errors.

Pragma AUTONOMOUS_TRANSACTION Example Code 2

DECLARE
   L_ID VARCHAR2(10):='a';
   L_DESC VARCHAR2(1000):='TEST_DESC';
BEGIN
   INSERT INTO PRODUCT VALUES (L_ID, L_DESC);
   UPDATE PRODUCT
   SET PRODUCT_DESCRIPTION = 'Product '||PRODUCT_ID;
EXCEPTION
   WHEN OTHERS THEN
      P_TRACK_ERROR(COALESCE ($$plsql_unit, 'anonymous block'),$$PLSQL_TYPE,$$PLSQL_LINE,SQLCODE,SQLERRM);
END;

Query the error log table SESSION_ERROR_TRACK to check the entry from the above block

Pragma AUTONOMOUS_TRANSACTION Example Code 3

SQL>SELECT USERNAME, ERR_DATE, OBJ_NAME, ERR_CODE, ERR_MSG 
     FROM SESSION_ERROR_TRACK;
 
USERNAME   ERR_DATE  OBJ_NAME        ERR_CODE   ERR_MSG
---------- --------- --------------- ---------- -------------------------
SCOTT      21-JUN-11 anonymous block -1722      ORA-01722: invalid NUMBER

Oracle Pragma RESTRICT_REFERENCES

The pragma was introduced to control and preserve the database state. It worked as a watchdog for the packaged function so that they should not modify the package or database state. It restricted the creation of the package, if its member function violated the specified purity level.

The pragma checks purity level of the function in four states

WNDS – DML operations restricted on any table in the database

WNPS –Modification restricted on package variables

RNDS – SELECT query restricted on the database tables

RNPS – Restriction on the selection of package variables

Pragma RESTRICT_REFERENCES Syntax

 [FUNCTION SPECIFICATION]
PRAGMA RESTRICT_REFERENCES([WNDS | WNPS | RNDS | RNPS])

While the Pragma must use minimum one restricted state in its specification, it may contain all the four too.

Pragma RESTRICT_REFERENCES Example Code

Let’s take a look at the usage of pragma and impact of purity level in the below snapshot illustrations.

A package function F_CHANGE_LOC is created in the package PKS_REF_REF with WNPS level check. This implies that it cannot modify the package variable L_LOC.

Impact: Package PKS_RES_REF is created with compilation errors.

The function is created in RNDS purity check. The function would not allow the querying of database tables within its declare section or executable body section.

Impact: Package PKS_RES_REF is created with compilation errors.

The function F_UPD_SAL is created in RNDS purity level specification. The function would not allow the DML operations on the database table within its body.

Impact: Package PKS_RES_REF is created with compilation errors.

{mospagebreak title=Oracle Pragma EXCEPTION_INIT}

Oracle Pragma EXCEPTION_INIT

The pragma EXCEPTION INIT allows the association of a self chosen error number with a user defined exception. The error number can be any of the system recognized error number.

Pragma EXCEPTION_INIT Syntax

PRAGMA EXCEPTION_INIT([EX NAME],[ERROR NUMBER], [TRUE | FALSE])

In the PL/SQL block below, the LOCAL_EXCEPTION exception is linked with the error number -100 through the PRAGMA EXCEPTION_INIT. Note the usage of SQLCODE, which returns the same associated error number.

Pragma EXCEPTION_INIT Example Code

The procedure P_GET_ORDERS has been created to get the order code and order quantity for a given date. If no order booking has been made, procedure must display appropriate message.

CREATE OR REPLACE PROCEDURE P_GET_ORDERS (P_ORD_DATE DATE)
IS
   L_ORD_CODE VARCHAR2(100);
   L_ORD_QTY NUMBER;
   ORD_EXCEPTION EXCEPTION;
   PRAGMA EXCEPTION_INIT(ORD_EXCEPTION, -100);
CURSOR C IS
   SELECT ORD_CODE, ORD_QTY
   FROM ORDERS
   WHERE ORD_DATE= P_ORD_DATE;
BEGIN
   OPEN C;
   FETCH C INTO L_ORD_CODE, L_ORD_QTY;
   IF C%ROWCOUNT = 0 THEN
      RAISE ORD_EXCEPTION;
   END IF;
   CLOSE C;
EXCEPTION
   WHEN ORD_EXCEPTION THEN
     DBMS_OUTPUT.PUT_LINE ('No Orders booked on:'||TO_CHAR(P_ORD_DATE,'DD-MM-YYYY');
END;
/
 
SQL>EXEC P_GET_ORDERS(TRUNC(SYSDATE,'YYYY'));
 
No Orders booked ON 01-01-2011
 
PL/SQL PROCEDURE successfully completed.

Oracle Pragma SERIALLY_REUSABLE

The pragma SERIALLY_REUSABLE enables the package state initialization for each and every server call. As per the conventional behavior of the packages, their state is initialized only during the first time they are referenced in a session. This state remains persistent for the complete session and is flushed off only at the termination of the session.

With the use of the pragma SERIALLY_REUSABLE, the package state is persistent only for a sever call. It gets initialized with every call made to the server while invoking any of its member construct.

Pragma SERIALLY_REUSABLE Syntax

CREATE [OR REPLACE] PACKAGE [NAME] IS
PRAGMA SREIALLY_REUSABLE
   [MEMBER CONSTRUCT PROTOTYPES]
END;
 
CREATE [OR REPLACE] PACKAGE BODY [NAME] IS
PRAGMA SREIALLY_REUSABLE
   [MEMBER CONSTRUCT DEFINTIONS]
END;

Pragma SERIALLY_REUSABLE Example Code

The package PKG_TEST_PRAGMA demonstrates non persistent package state at every server call.

CREATE OR REPLACE PACKAGE PKG_TEST_PRAGMA
IS
PRAGMA SERIALLY_REUSABLE;
   L_PKG_STATE NUMBER := 1;
   PROCEDURE ASSIGN_SUM_VALUE(P_FACTOR NUMBER);
   FUNCTION DISPLAY_VALUE RETURN NUMBER;
END;
 
Package created.
 
CREATE OR REPLACE PACKAGE BODY PKG_TEST_PRAGMA
IS
PRAGMA SERIALLY_REUSABLE;
   PROCEDURE ASSIGN_SUM_VALUE (P_FACTOR NUMBER)
   IS
   BEGIN
      L_PKG_STATE:= L_PKG_STATE + P_FACTOR;
      DBMS_OUTPUT.PUT_LINE(‘CHANGE IN package state’|| L_PKG_STATE);
   END;
 
   FUNCTION DISPLAY_VALUE RETURN NUMBER
   IS 
   BEGIN
      RETURN L_PKG_STATE;
   END;
END;
 
Package body created.
 
SET SERVEROUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(‘Package state BEFORE CALL: ’||PKG_TEST_PRAGMA.DISPLAY_VALUE);
  PKG_TEST_PRAGMA.ASIGN_SUM_VALUE(5);
  DBMS_OUTPUT.PUT_LINE(‘Package state after CALL: ’||PKG_TEST_PRAGMA.DISPLAY_VALUE);
END;
/
 
Package state BEFORE CALL: 1
CHANGE IN package state: 6
Package state after CALL: 1
 
PL/SQL PROCEDURE successfully completed.

{mospagebreak title=Oracle Pragma Inline}

Oracle Pragma Inline

Inlining is an effective feature to gain performance benefits in a procedural and structural code. During compilation, oracle tends to replace the inline subprogram call with its definition itself.

The INLINE pragma is a latest induction in the family. It is used to manually inlining a subprogram when the optimizer level is set as 2. It has to be specified once, just before the subprogram call. The inlining effect would remain persistent for all the subsequent calls to the subprogram; unless and until the NO pragma is specified for the subprogram.

Oracle Pragma Inline Example Syntax

PRAGMA INLINE (subprogram name, [YES | NO]);

For quick reference, PLSQL_OPTIMIZE_LEVEL parameter specifies the optimization level of the subprogram.

• If it is 0, then the optimizer follows ‘NO inlining’ principle.

• For the value of 1, the optimizer follows intermediate inlining like working with iterative constants.

• For optimizer level 2, the optimizer takes the path of ‘Intelligent inlining’ i.e. it would inline the subprogram by virtue of its intelligence. This is the appropriate and default level for the optimizer and provides scalability to manually inline the subprograms using PRAGMA INLINE.

• At optimizer level 3, optimizer is forced to inline all possible subprograms at high priority, which is not commendable from developer’s perspective.

Oracle Pragma Inline Example Code

For demonstration purpose, the session has been altered to set PLSQL_OPTIMIZE_LEVEL at 2.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2
 
SESSION altered.

 

Oracle Pragma Inline Example Code

Now, for example, I have to calculate sum of factorial series in the format as 1! + 2! + 3! +…+N! I create a procedure P_CALC_SERIES and define a local function FACTORIAL to get the factorial of a number. I would inline the local function call using PRAGMA INLINE as shown below.

CREATE OR REPLACE PROCEDURE P_CALC_SERIES(P_LIMIT NUMBER)
IS
  L_SERIES NUMBER := 0;
  FUNCTION FACTORIAL (P_NUM NUMBER)    RETURN NUMBER IS
    L_FACT NUMBER := 1;
  BEGIN
    FOR I IN 1..P_NUM
    LOOP
       L_FACT := L_FACT*I;
    END LOOP;
    RETURN L_FACT;
  END FACTORIAL;
 
BEGIN
  FOR J IN 1 .. P_LIMIT 
  LOOP
    PRAGMA INLINE (FACTORIAL, 'YES');
    L_SERIES := L_SERIES + FACTORIAL(I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Sum of the series is '||TO_CHAR(L_SERIES));
END;
/
 
PROCEDURE created.
« « OOAD Analysis
OOAD Object Statics » »

Author Description

Avatar

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle 11g Read Only Tables

    June 16, 2011 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle 11g Subprogram Overloading

    June 17, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT