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,’BERNA1’);
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,’PRABHU’);
P1;
ROLLBACK;
END;

NOW Table has (1111,’BERNA’) 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 BERNA record, it will just rollback the PRABHU record.

CREATE or REPLACE Procedure p1 IS
BEGIN
INSERT INTO TEST_T VALUES (1111,’BERNA1’);
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,’PRABHU’);
P1; -- This transaction has ended with the COMMIT;
ROLLBACK;
END;

After executing the above transaction, you can see BOTH records got Inserted (BERNA and PRABHU records). Here COMMIT in P1 will commit both transactions (BERNA and PRABHU 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 (BERNA Record which is in p1 procedure and PRABHU 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.EMPWHERE 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
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.

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

To get the digits from a string

CREATE OR REPLACE FUNCTION get_digits (i_str IN varchar2)
RETURN varchar2
IS
temp_str varchar2 (100);
retval varchar2 (100) := '';
temp_chr varchar2 (1);
BEGIN
temp_str := i_str;
WHILE LENGTH (temp_str) > 0
LOOP
temp_chr := SUBSTR (temp_str, 1, 1);
IF ASCII (temp_chr) BETWEEN 48 AND 57
THEN
retval := retval temp_chr;
END IF;
temp_str := SUBSTR (temp_str, 2);
END LOOP;
RETURN retval;
END;

To test a string for numeric characters

SELECT LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) FROM DUAL;

OUTPUT

----------

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')));	would return 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' ')));	would return 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' ')));	would return null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));	would return null

Truncate Vs Delete

Importing Dump

C:\Documents and Settings\Welcome>SQLPLUS /NOLOG
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 17:42:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONN SYS AS SYSDBA

Enter password:Connected.

SQL> SHOW USER;

USER is "SYS"

SQL> CREATE TABLESPACE TBS01 DATAFILE 'E:\oracle\DBF\TBS01.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M;
Tablespace created.
SQL> CREATE USER DICGC IDENTIFIED BY DICGC DEFAULT TABLESPACE TBS01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON TBS01;
User created.
SQL> ALTER USER DICGC QUOTA 0 ON SYSTEM;
User altered.
SQL> GRANT CREATE SESSION, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA TO DICGC;
Grant succeeded.
D:\DICGC\Mon>IMP DICGC/DICGC@ORCL FILE=MON.DMP LOG=EXPMON.LOG IGNORE=Y COMMIT=Y FULL=Y
Import: Release 10.2.0.1.0 - Production on Tue Oct 7 17:52:38 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ORION, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set. importing ORION's objects into DICGC. . importing table "AUTO_TEMP" 0 rows imported. . importing table "DI01_DICGC" 4507 rows imported

Ref Cursor

A ref cursor variable is not a cursor, but a variable that points to a cursor. A ref cursor allows you to store the results of a 'bulk collect' from a table into PL/SQL table and then reference the array with the ref cursor as a pointer.

Cursors

Cursors:
======
Cursor is a pointer which points to the memory location where the processed information is stored.PL/SQL uses cursor to retrieve the processed information stored in the memory for manipulation - one row at a time.

Types of Cursors:
============
Simple Cursors
==========

Implicit Cursors
==========

Implicit Cursors are those which implicitly opens,fetches and closes.All DML statements and SELECT statements are implicit cursors. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. its transparent to your application code - no cursor declaration is required. SQL statements with implicit cursors are used for COMMIT, ROLLBACK, INSERT, UPDATE, DELETE and SELECT (single row and multi-row) queries.

Implicit Cursor Attributes
================
sql%found
sql%notfound
sql%isopen

Explicit Cursors
==========
Explicit cursors are those which has to be explicitly opened, fetched and closed.If we use cursor for loops then it implicitly opens,fetches and closes. Requires a PL/SQL Program cursor declaration. Used for multi-row SELECT queries.

Explicit Cursor Attributes
================
%isopen
%isfound
%isnotfound

Ref Cursors
========
Ref cursors are those, in which, select statements are dynamically associated at run time.

Week Cursors
=========
Syntax:
DECLARE
TYPE C1 AS REF CURSOR
TYPE1 C1;
BEGIN
IF A THEN
OPEN TYPE1 FOR SELECT * FROM EMPLOYEE;
IF B THEN
OPEN TYPE1 FOR SELECT * FROM DEPARTMENT;
END;

Strong Cursors
==========
Syntax:
DECLARE
TYPE C1 AS REF CURSOR RETURN EMPLOYEE%ROWTYPE --strong Cursors
TYPE1 C1;
BEGIN
IF A THEN
OPEN TYPE1 FOR SELECT * FROM EMPLOYEE;
IF B THEN
OPEN TYPE1 FOR SELECT * FROM DEPARTMENT;
END;

Parametrised Cursors
==============
In parametrised cursors we can define parameters to the cursor at design time. And while opening the cursor we have to pass the cursors parameters.

Note:

===

There are no explicit cursors for INSERT, UPDATE and DELETE statements. however there is always an implicit cursor for any INSERT, UPDATE or DELETE.

-------------------------------------------------------------------------------------------------