PRAGMA DIRECTIVES

PRAGMA DIRECTIVES:
Pragma compiler directives instructs compiler with certain information. It is Processed at compile time not at run time. The directive restricts member subprograms to query or modify database tables and packaged variables.


PRAGMA directive should be in declarative section of the PL-SQL block.
As of release 11.2 we have 5 PRAGMA directives:


1) PRAGMA AUTONOMOUS_TRANSACTION
2) PRAGMA SERIALLY_REUSABLE
3) PRAGMA RESTRICT_REFRENCES
4) PRAGMA EXCEPTION_INIT
5) PRAGMA INLINE


We will see fundamental use of each Pragma:

PRAGMA AUTONOMOUS_TRANSACTION :
This directive instructs the compiler to treat current PL-SQL block as independent from the mail transaction from which it is called. This means that any changes made to the database in the autonomous transaction are independent of the main transaction and are either committed or rolled back without affecting the main transaction.


Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.


Autonomous triggers (Trigger with Autonomous transaction directive) can contain commit or rollback as well as DDL statements.


If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.


Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.
In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.


If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

PRAGMA SERIALLY_REUSABLE:


sing this Pragma, State of any package will be lost after its call to server. So It will be helpful to reduce memory consumption in long running process.The state information for this package is not kept in Memory per user but It is kept in SGA for reuses.


The pool is kept in SGA memory so that the work area of a package can be reused across users who have requests for the same package. In this scheme, the maximum number of work areas needed for a package is only as many as there are concurrent users of the package, which is typically much fewer than the total number of logged on users. The user of "serially reusable" packages does increase the shared-pool requirements slightly, but this is more than offset by the decrease in the per-user memory requirements. 

Further, Oracle ages out work areas not in use when it needs to reclaim shared pool memory.


PRAGMA RESTRICT_REFERENCES:

This directive instructs compiler that Pl-SQL block should not read or write table or package variables.


If user defined functions are used to any extent it is advisable to encapsulate them within a package and designate this package as "pure", that is, the functions in the package do not modify the database. The "purity" of the package is denoted with the PL/SQL pragma: RESTRICT_REFERENCES. This pragma has one mandatory argument and three optional arguments. The optional arguments define the level of the "purity" of the function.


You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.


When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them.


If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor). You can still declare the pragma for individual functions. Such pragmas override the default pragma.


A RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.


RNDS  
Asserts that the subprogram reads no database state (does not query database tables).

WNDS
Asserts that the subprogram writes no database state (does not modify tables).

RNPS
Asserts that the subprogram reads no package state (does not reference the values of packaged variables)
You cannot specify RNPS if the subprogram invokes the SQLCODE or SQLERRM function.

WNPS
Asserts that the subprogram writes no package state (does not change the values of packaged variables).
You cannot specify WNPS if the subprogram invokes the SQLCODE or SQLERRM function.

TRUST
Asserts that the subprogram can be trusted not to violate one or more rules.
When you specify TRUST, the subprogram body is not checked for violations of the constraints listed in the pragma. The subprogram is trusted not to violate them. Skipping these checks can improve performance. TRUST is needed for functions written in C or Java that are invoked from PL/SQL, since PL/SQL cannot verify them at run time.

Usage Notes:
A RESTRICT_REFERENCES pragma can appear only in a package specification or object type specification. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures also.
To invoke a subprogram from parallel queries, you must specify all four constraints—RNDS, WNDS, RNPS, and WNPS. No constraint implies another.
Example:
CREATE PACKAGE test_sr AS
  FUNCTION test_function RETURN BOOLEAN;
  PRAGMA RESTRICT_REFERENCES (test_function, WNDS, RNPS);
END loans;
/

PRAGMA EXCEPTION_INIT:


This Pragma will associate used defined exception with Oracle Error number. You can catch user defined exception in exception handler.
PRAGMA EXCEPTION_INIT(exception_name, ORACLE_error_number);
where exception_name is the name of a previously declared exception.
DECLARE
insufficient_privileges EXCEPTION;
PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
---
----
BEGIN
…
EXCEPTION
WHEN insufficient_privileges THEN
— handle the error
…
END;

PRAGMA INLINE :

The INLINE pragma specifys that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.

To reiterate, with subprogram inlining, Oracle will re-write our code to replace a call or calls to a subprogram with the executable code of the subprogram itself.

when the INLINE pragma immediately precedes one of these statements, the pragma affects every call to the specified subprogram in that statement

• Assignment
• Call
• Conditional
• CASE
• CONTINUE WHEN
• EXECUTE IMMEDIATE
• EXIT WHEN
• LOOP
• RETURN

The INLINE pragma does not affect statements that are not in the preceding list.

ROWNUM, ROW_NUMBER , RANK AND  ROW_ID
ROWNUM and ROWID   are pseudocolumns where as row_number,rank and row_id are analytic functions.
We will take example of each one by one :


ROWNUM:
It will indicate number of the order in which oracle selects rows from table or any query. Using this we can limit number of rows returns from table or Query using query like:

Select * from emp where rownum <=11.

It will return exactly 11 rows although there are more rows in table.

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT * FROM
  (SELECT * FROM employees ORDER BY employee_id)
  WHERE ROWNUM < 11;

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
   WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

ROWID:

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

The data object number of the object

The data block in the datafile in which the row resides

The position of the row in the data block (first row is 0)

The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Rowid values have several important uses:
They are the fastest way to access a single row.
They can show you how the rows in a table are stored.
They are unique identifiers for rows in a table.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

Example This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, last_name  
  FROM employees
  WHERE department_id = 20;

ROW_NUMBER():

ROW_NUMBER() will  assign unique number to each row on which it is applied. as It is analytic function , function clause will be processed last in Query.

It is also useful in finding out TOP-N Analysis.But for It ROW_NUMBER() should be in the inner query.

Example:
SELECT department_id, first_name, last_name, salary
FROM
(
 SELECT
   department_id, first_name, last_name, salary,
   ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
 FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;

RANK():

Rank() function will give rank  of the row value within group of rows on which it is applied. Let’s take example:

SQL> select ename, sal,rank() over(partition by job order by sal desc) from emp
where job='CLERK';

ENAME             SAL RANK()OVER(PARTITIONBYJOBORDERBYSALDESC)
---------- ---------- ----------------------------------------
MILLER           1300                                        1
ADAMS            1100                                       2
JAMES             950                                          3
SMITH             800                                          4

Above, you can see Rank () is applied on rows which lies withing group of JOB=’CLERK’. And Rank () has given number according to order of salary of employee. Means Highest salary in CLERK group will have highest rank.

There is one thing which we should consider:

If the column having same value on which rank() is applied, It will return same value.  

SQL> select ename,sal,rank() over (order by sal desc) from emp ;

ENAME             SAL RANK()OVER(ORDERBYSALDESC)
---------- ---------- --------------------------
KING             5000                          1
FORD             3000                          2
SCOTT            3000                          2
JONES            2975                          4
BLAKE            2850                          5
CLARK            2450                          6
ALLEN            1600                          7
TURNER           1500                          8
MILLER           1300                          9
WARD             1250                         10
MARTIN           1250                         10
ADAMS            1100                         12
JAMES             950                         13
SMITH             800                         14



In Above example, you can see MARTIN and WARD are having same salary of 1250. So both have assigned same rank. And for adams rank () assign number 12 skipping of 11.

If we have used dense_rank() function , It will have given number 11 to adamas , as It is shown in below example.


SQL> SELECT job, ename, sal, rank() OVER (ORDER BY sal) AS rank  ,
 2                          dense_rank()   OVER (ORDER BY sal) AS drank,
 3                          row_number()   OVER (ORDER BY sal) AS row_num
 4    FROM emp;

JOB       ENAME                 SAL       RANK      DRANK    ROW_NUM
--------- ---------- ---------- ---------- ---------- ----------
CLERK     SMITH                      800          1          1          1
CLERK     JAMES                      950          2          2          2
CLERK     ADAMS                  1100          3          3          3
SALESMAN  WARD              1250          4          4          4
SALESMAN  MARTIN           1250          4          4          5
CLERK     MILLER                  1300          6          5          6
SALESMAN  TURNER           1500          7          6          7
SALESMAN  ALLEN               1600          8          7          8
MANAGER   CLARK              2450          9          8          9
MANAGER   BLAKE              2850         10          9         10
MANAGER   JONES              2975         11         10         11
ANALYST   SCOTT                 3000         12         11         12
ANALYST   FORD                  3000         12         11         13
PRESIDENT KING                 5000         14         12         14

14 rows selected.
NOTE:  We cannot use RANK(),ROW_ID() and ROW_NUMBER() in the where conditions predicates.
            We can not use RANK(),ROW_ID() and ROW_NUMBER() without using over or windowing clause.
    

Useful function to find Percentile in Oracle

CUME_DIST:
In Oracle documentation, Description of this function is somewhat difficult to understand. So I am putting description of this function in my words. Technically If I am wrong and any Oracle expert wants to rectify it please let me know ☺
This function can be used to find percentile of any row value within set of rows. It can take numeric or non-numeric input and returns numeric values.
This function can be used in two ways i.e As Aggregate function or Analytic function.
As a Aggregate function:
Function will take any hypothetical or any row value from a table and It will return hypothetical position of Input value within group of rows.
Example:
I am considering table emp which have records like:


Select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80	800		20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7566	JONES	MANAGER	7839	02-APR-81	2975		20
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-MAY-81	2850		30
7782	CLARK	MANAGER	7839	09-JUN-81	2450		10
7788	SCOTT	ANALYST	7566	19-APR-87	3000		20
7839	KING	PRESIDENT		17-NOV-81	5000		10
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	23-MAY-87	1100		20
7900	JAMES	CLERK	7698	03-DEC-81	950		30
7902	FORD	ANALYST	7566	03-DEC-81	3000		20
7934	MILLER	CLERK	7782	23-JAN-82	1300		10

Now If I want to find position for the percentile position for 1500 and 1400 commision salary. I can write query like :


SQL> SELECT CUME_DIST(1500, 1400) WITHIN GROUP
 2    (ORDER BY sal, comm) "Cume-Dist of 15500"
 3    FROM emp;

Cume-Dist of 15500
------------------
       .533333333

This Query will give hypothical percentile of salary-1500 and comm.-1400 within whole table emp according to order of sal and comm.
Here we can see that although we haven’t inserted value 1500 and 1400 values in table , It is giving percentile from table for these values.
Now , If I apply group by clause in above query , It will hypothetical percentile for each job group :


SQL> SELECT job, CUME_DIST(1500, 1400) WITHIN GROUP
 2    (ORDER BY sal, comm) "Cume-Dist of 15500"
 3    FROM emp
 4    group by job;

JOB       Cume-Dist of 15500
--------- ------------------
ANALYST           .333333333
CLERK                      1
MANAGER                  .25
PRESIDENT                 .5
SALESMAN                  .8

As a Analytic function:

As It is analytic function, It will be evaluated at last in Query execution after Order by clause in Main query.

Example :

It is finding relative percentile position for each salary value which are in CLERK job group. With Ordering of salary.

SQL> SELECT job, ename, sal, CUME_DIST()
 2    OVER (PARTITION BY job ORDER BY sal) AS cume_dist
 3    FROM emp
 4    WHERE job LIKE 'CLERK'
 5    ORDER BY job, ename, sal, cume_dist;

JOB       ENAME             SAL  CUME_DIST
--------- ---------- ---------- ----------
CLERK     ADAMS            1100        .75
CLERK     JAMES             950         .5
CLERK     MILLER           1300          1
CLERK     SMITH             800        .25

Pipelined Functions

Pipelined function..

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated.

Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. Notice the empty RETURN call, since there is no collection to return from the function.

PIPE ROW :
PIPE ROW statement causes a table function to pipe a row and continue processing.
The statement enables a PL/SQL table function to return rows as soon as they are produced. (For
performance, the PL/SQL runtime system provides the rows to the consumer in batches.


Example :

Non-Piplined function :

First, create a table object as the return value for the table function.
CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
/

Next, create the table function. This function accepts a comma-delimited string, which it splits and turns into rows in a table. Once the table is fully populated it is returned.

    CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
      RETURN t_in_list_tab
    AS
      l_tab   t_in_list_tab := t_in_list_tab();
      l_text  VARCHAR2(32767) := p_in_list || ',';
      l_idx   NUMBER;
    BEGIN
      LOOP
        l_idx := INSTR(l_text, ',');
        EXIT WHEN NVL(l_idx, 0) = 0;
        l_tab.extend;
        l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
        l_text := SUBSTR(l_text, l_idx + 1);
      END LOOP;

      RETURN l_tab;
    END;
    /

The following query shows the table function in action.

    SELECT *
    FROM   emp
    WHERE  job IN (SELECT * FROM TABLE(in_list('SALESMAN, MANAGER')))
    ORDER BY ename;

         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
          7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

    7 rows selected.

    SQL>

Piplined Function:

The following code uses a similar approach, but with a pipelined table function. The advantage here is that rows are piped to the query as they are produced, rather than building the entire table before the rows are returned.

    CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
      RETURN t_in_list_tab PIPELINED
    AS
      l_text  VARCHAR2(32767) := p_in_list || ',';
      l_idx   NUMBER;
    BEGIN
      LOOP
        l_idx := INSTR(l_text, ',');
        EXIT WHEN NVL(l_idx, 0) = 0;
        PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
        l_text := SUBSTR(l_text, l_idx + 1);
      END LOOP;

      RETURN;
    END;
    /

The following query shows the pipelined table function in action.

    SELECT *
    FROM   emp
    WHERE  job IN (SELECT * FROM TABLE(in_list('SALESMAN, MANAGER')))
    ORDER BY ename;

         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
          7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

    7 rows selected.

    SQL>

Advangtage Of Piplined functions :

1) It reduce Memory consumptions:
2) Improve Performance of ETL while there is large data to be processed.
3) Client gets first row before function generates last row.
4) Function doesn't need to build big honking array in memory.