Here is a link to a fine article about autonomous transactions by Tom Kyte : http://osi.oracle.com/~tkyte/autonomous/
- Kirti -----Original Message----- Sent: Monday, January 28, 2002 6:05 AM To: Multiple recipients of list ORACLE-L Oki thanks for info can you please show me an example with autonoumus transactions? Please. Thanks in advance Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-28 03:30 PST S�nd svar till [EMAIL PROTECTED] S�nt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Just a note Inserting in an error table in the excpetion clause is a common problem because if something goes wrong and exception was thrown then You usually don't want commit But You cannot insert ereror record without commit So in ancient times there was dbms_pipe etc. Since 8.<something> You can use autonomous transactions instead, I think it is more elegant solution Gints Plivna IT Sist�mas, Mer�e�a 13, LV1050 R�ga http://www.itsystems.lv/gints/ "Thomas, Kevin" <Kevin.Thomas@cal To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> anais.com> cc: Sent by: Subject: RE: Pl/sql question [EMAIL PROTECTED] 2002.01.28 11:20 Please respond to ORACLE-L Hi Roland, Best way to do this is just set some variables at the start of your code: l_proc_start := sysdate; l_proc_name := 'proc_name'; begin ...commands... exception when <exception> then l_proc_end := sysdate; SELECT count(*) INTO l_ins_count FROM <table being inserted to>; INSERT INTO error_table ( proc_name ,proc_start ,proc_end ,proc_count ,proc_err ,proc_err_msg ) VALUES ( l_proc_name ,l_proc_start ,l_proc_end ,l_ins_count ,sqlcode ,sqlerrm ); COMMIT; END; -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
