RE: Pl/sql question

Yea more elegant if you happen to be running Oracle 8.<something> which unfortunately we're not!! ;o)

-----Original Message-----
Sent: 28 January 2002 11:30
To: Multiple recipients of list ORACLE-L

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; -----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: Roland.Skoldblom_at_ica.se

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, Kevin
  INET: Kevin.Thomas_at_calanais.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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: G.Plivna_at_itsystems.lv

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, Kevin
  INET: Kevin.Thomas_at_calanais.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).