Archive

Archive for March, 2012

Create Global Temporary Table

March 29, 2012 Comments off

Today..i have used global temporary table to backup my system….

global temporary tables are very useful on session basis…

can have a session safe calculations using global temporary tables.

Two ways to create global temporary tables:

1.CREATE GLOBAL TEMPORARY TABLE TEMP_table( PRIMEID  NUMBER ) ON COMMIT PRESERVE ROWS;

2.CREATE GLOBAL TEMPORARY TABLE TEMP_table( PRIMEID  NUMBER ) ON COMMIT DELETE ROWS;

Advertisements
Categories: PL/SQL, SQL, Technical

PRAGMA Autonomous_transaction in Oracle

March 20, 2012 Comments off

Use of pragma autonomous_transaction in oracle PL/SQL is…the transactions performed in this procedure will be committed and does not bother about whether the calling procedures transactions are committed or rollback.

For Example: you are performing a banking transaction, whether the transaction is committed or rollback, you need to see the log of that transaction, So in your logging procedure, you can put PRAGMA Autonomous_transaction; statment to commit all the logs in log table.

Create or Replace procedure add_log( vlog_seq NUMBER,vmsg_type VARCHAR2,vmsg VARCHAR2)

is

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INERT INTO

Transaction_Log(log_seq,log_time,msg_type,msg)

VALUES (vlog_seq,systimestamp,vmsg_type,msg);

commit;

end add_log;

Categories: PL/SQL, Technical