Re: RE: Pl/sql question
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 28, 2002 14:05 Oki thanks for info can you please show me an example with autonoumus transactions? Please. perhaps u could just go to http://technet.oracle.com and do some research all by yourself? Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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).
RE: RE: Pl/sql question
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).
Ang: Re: RE: Pl/sql question
Ok sorry i found out this answer myself:-) Igor Neyman [EMAIL PROTECTED]@fatcity.com den 2002-01-28 08:25 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Roland, With this sort of questions, you are forcing listers to create a rule (in e-mail utility), which will forward your messages directly into trash bin. Try to restrain yourself. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 28, 2002 10:10 AM Man, do you not have manuals? Have you even attempted to look it up. This is not a teaching class. Go try something and if you are having issues come back and ask about that issue. But we are not here to write your code for you. There are folks you can hire for that. -Original Message- [EMAIL PROTECTED] Sent: Monday, January 28, 2002 4: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; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX:
Re: RE: Pl/sql question
Roland, With this sort of questions, you are forcing listers to create a rule (in e-mail utility), which will forward your messages directly into trash bin. Try to restrain yourself. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 28, 2002 10:10 AM Man, do you not have manuals? Have you even attempted to look it up. This is not a teaching class. Go try something and if you are having issues come back and ask about that issue. But we are not here to write your code for you. There are folks you can hire for that. -Original Message- [EMAIL PROTECTED] Sent: Monday, January 28, 2002 4: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; -Original Message- Sent: 28 January 2002 08:40 To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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