Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Pl/sql question

RE: Pl/sql question

From: Thomas, Kevin <Kevin.Thomas_at_calanais.com>
Date: Mon, 28 Jan 2002 04:20:05 -0800
Message-ID: <F001.003FCD21.20020128041518@fatcity.com>

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.

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).
Received on Mon Jan 28 2002 - 06:20:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US