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

G.Plivna_at_itsystems.lv_at_fatcity.com den 2002-01-28 03:30 PST

Sänd svar till ORACLE-L_at_fatcity.com

Sänt av: root_at_fatcity.com

Till: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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_at_cal       To:     Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
                    anais.com>              cc:
                    Sent by:                Subject:     RE: Pl/sql question
                    root_at_fatcity.com


                    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: kirti.deshpande_at_verizon.com

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: ListGuru_at_fatcity.com (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).