sagar singh

• Jun 11th, 2006

 

Pragmas are pre-compiler directives.

KHARTHY

• Jun 12th, 2006

 

ABOUT PRAGMApragma'S are private transactions , which does not affect anyother transaction. consider procedure1 is calling procedure2, after finishing a transaction in the second procedure if a error occurs in the procedure1 then all the transactions will be rolled back ( even tat happend in the procedure2) , if pragma_autonomous transaction is used in the second procedure ...tat transaction can be savedhttp://www.psoug.org/reference/autonomous_tx.htmlI HOPE TAT LINK WILL GIVE U A MUCH BETTER PICTUREREGARDSd.r. KHARTHY

Shivraj G. Gutte

• Jun 18th, 2006

 

Pragama is compile directive .

Example of Autonomous transactions

   Suppose you are updating value from table and you don't have update trigger on that table

but still you want to maintain a log entry for this update in seprate table.

  You can write a procedure and call that procedure to do this . But you can not use COMMIT in this called procedure  because it will save the entire transaction.

  To avoid this you can delclare this procedure as autonomous transaction procedure so that the execution of this procedure will be treated as totally diff. transaction and you can issue  commit in called procedure without affecting the main transaction.

 

  

  

pankaj_gauba

 Profile Answers by pankaj_gauba Questions by pankaj_gauba

• Jun 22nd, 2006

 

prgma is a compiler directive, it takes the oracle description error name from database for oracle error code.

Example of autonous transaction:

Autonomous Transaction is a feature of oracle 8i which  maintains  the state of its transactions and save  it ,  to affect with the commit or rollback  of the surrounding transactions.

 

Here is the simple example to understand this :-

 

ora816 SamSQL :> declare

  2   Procedure InsertInTest_Table_B

  3    is

  4    BEGIN

  5     INSERT into Test_Table_B(x) values (1);

  6     Commit;

  7    END ;

  8    BEGIN

  9      INSERT INTO Test_Table_A(x) values (123);

 10      InsertInTest_Table_B;

 11      Rollback;

 12    END;

 13  /

 

PL/SQL procedure successfully completed.

 

ora816 SamSQL :> Select * from Test_Table_A;

         X

----------

       123

ora816 SamSQL :> Select * from Test_Table_B;

         X

----------

         1

 

 Notice in above pl/sql  COMMIT at line no 6 , commits the transaction at  line-no 5 and  line-no 9. The Rollback at line-no 11 actually did nothing.  Commit/ROLLBACK at nested transactions will commit/rollback all  other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.

 

Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.

 

ora816 SamSQL :> declare

  2     Procedure InsertInTest_Table_B

  3     is

  4     PRAGMA AUTONOMOUS_TRANSACTION;

  5       BEGIN

  6         INSERT into Test_Table_B(x) values (1); 

  7         Commit;

  8       END ;

  9       BEGIN

 10        INSERT INTO Test_Table_A(x) values (123);

 11        InsertInTest_Table_B;

 12        Rollback;

 13       END;

 14     /

 

PL/SQL procedure successfully completed.

 

ora816 SamSQL :> Select * from Test_Table_A;

 

no rows selected

 

ora816 SamSQL :> Select * from Test_Table_B;

 

         X

----------

         1

 

With PRAGMA AUTONOMOUS_TRANSACTION , the transaction state maintained independently . Commit/Rollback of nested transaction will no effect the other transaction. It is advisable to increase the value of  TRANSACTIONS parameter in the INIT parameter file to allow for the extra concurrent transaction .

 

can we change the order of procedure parameter while calling procedure?
 Yes we can change the order , it is called Named Notation

 

g_sidhu

 Profile 

• Feb 1st, 2008

 

Using Named & Combination Parameter passing Methods you can change the order of parameter in a procedure
Named: List actual parameters in arbitrary order by associating each with its corresponding formal parameter.

• Combination: List some of the actual parameters as positional and some as named.

g_sidhu

 Profile 

• Feb 18th, 2008

 

A pragma is compiler directive. Pragmas are processed at compile time, not at run time. They pass information to the compiler.