Home » SQL & PL/SQL » SQL & PL/SQL » Exception handling (Oracle 9.2.0.6)
Exception handling [message #418406] Fri, 14 August 2009 19:31 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
Is it possible to insert a table in case of exceptions. For example, i have a function like this:

Create or replace Function test_function(p_Parameter_ID number)
Return number is
l_Variable number;
Begin
 Select output into l_Variable from test_table where  id=p_Parameter_ID;
 Exception when others then
  Insert into exception_Table values('No Data Found',    p_Parameter_ID);		  
  return l_Variable;	   	     
End test_function;	


In this function, if you notice, i wanted to populate an exception table for further analysis in case data is not found. The function gets compiled without any error in this case. However when i execute this and the Select query does not return any output for a particular parameter_ID, then the exception part fails. It gives me an error "cannot perform a DML operation inside a query".

Is there a way to achieve this? Can't i write insert statements inside the exception section?

Thanks in advance.
SA
Re: Exception handling [message #418407 is a reply to message #418406] Fri, 14 August 2009 19:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you would have followed posting Guidelines you would have posted the whole error code & message

14551, 00000, "cannot perform a DML operation inside a query "
// *Cause:  DML operation like insert, update, delete or select-for-update
//          cannot be performed inside a query or under a PDML slave.
// *Action: Ensure that the offending DML operation is not performed or
//          use an autonomous transaction to perform the DML operation within
//          the query or PDML slave.


If you would have followed the Posting Guidelines you would have SEARCH this forum & found the following:

http://www.orafaq.com/forum/?SQ=0278eef19ba16ff7f7f0d7038e3cd45b&t=search&srch=cannot+perform+a+DML+operation+inside+a+query+ &btn_submit=Search&field=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=

>Can't i write insert statements inside the exception section?
The problem is not the exception handler.
The problem is Oracle does not allow DML inside functions!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Fri, 14 August 2009 19:58]

Report message to a moderator

Re: Exception handling [message #418416 is a reply to message #418406] Fri, 14 August 2009 22:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Oracle does indeed allow dml in functions.

SQL> create table tab1 (a number);

Table created.

SQL> 
SQL> create or replace function fun1 return number is
  2  begin
  3     insert into tab1 values(1);
  4     return (1);
  5  end;
  6  /

Function created.

SQL> 
SQL> select * from tab1;

no rows selected

SQL> 
SQL> declare
  2     v1 number;
  3  begin
  4     v1 := fun1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from tab1;

         A
----------
         1


There is no such restriction.

On the other hand, there are things to be said about this:

First, what is the difference between a procedure and a function? Here is my short answer: a procedure is intended to alter the state of a system, whereas a function is inteded to provide information about the state of a aystem. This Think on that for a while. It is a well accept idea that functions should not be used to make changes to data. It often yields side affects. Do some googling on functions and side affects to read more.

Second I think BlankSwam may have been hinting that functions can be used in places where procedures cannot (another difference). Many of the places where functions are allow and procedure are not (say SELECT LIST OF A QUERY) do not normally allow dml.

Third many people when faced with this issue look for ways around the limitation. They go for the AUTONOMOUS_TRANSACTION. Unfortunately this feature of oracle is a misunderstood feature and subject to abuses that lead to data corruption. I think this may be what the OP had in mind. If so, please consult this:

Autonomous Transactions: a Poor Mis-Understood Feature

All this said, use of an autonomous_transaction in an exception handler to record the occurrance of an error is a legitimate use of the feature so bang away. Just remember to re-raise the error.

Good luck, Kevin

[Updated on: Fri, 14 August 2009 22:24]

Report message to a moderator

Re: Exception handling [message #418426 is a reply to message #418416] Sat, 15 August 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
There is no such restriction.

To add to Kevin's answer on this point: however you cannot call this function from a query and this is likely what you did:
SQL> select fun1 from dual;
select fun1 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "MICHEL.FUN1", line 3


Also, if you just want to trap the case the query do not return a row then replace "when others" by "when no_data_found" and NEVER use "when others" without reraising the exception, but better do not use it.

Regards
Michel
Re: Exception handling [message #418434 is a reply to message #418406] Sat, 15 August 2009 04:39 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Refer Restriction on user defined function portion on this page.

regards,
Delna
Re: Exception handling [message #418452 is a reply to message #418406] Sat, 15 August 2009 11:17 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
1- Function can perform DML but in that case it cant be called from SQL. It can only be used in plsql block.

2- regarding inserting data into a table from exception section,
we can do this by creating a log table and inserting the values of sqlcode and sqlerrm (by taking them in a string variable).
Re: Exception handling [message #419638 is a reply to message #418406] Mon, 24 August 2009 11:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
of course in the interest of total disclosure:

Functions doing DML can indeed be called from sql if they are autonomous.

SQL> create table tab1 (a number);

Table created.

SQL> 
SQL> select * from tab1;

no rows selected

SQL> 
SQL> create or replace function fun1 return number is
  2     pragma autonomous_transaction;
  3  begin
  4     insert into tab1 values(1);
  5     commit;
  6     return (1);
  7  end;
  8  /

Function created.

SQL> 
SQL> select fun1 from dual;

      FUN1
----------
         1

1 row selected.

SQL> 
SQL> select * from tab1;

         A
----------
         1

1 row selected.

SQL> 

That said, this is generally a very stupid thing to do and not the intent of feature.

I am sure people will have plenty to say about this.

Remember, I said this specific example was STUPID STUPID STUPID, and that only the most advanced of Oracle experts should employ such code because we assume they actually know what they are doing and why it would thus be OK.

Consider for a moment how unintuitive it is that using a function in a select list causes that function to create rows in a table for each row selected by the query? WOW, that is pretty out there. I have seen people do this, and they did have a reason why, but it is very rare and very special case and no newbie should be writing this kind of code. Indeed I have never written this kind of code myself and I have been working with the oracle database since 1985.

Indeed, the issue is made more complicated by new features of 10g and 11g wherein this function may be called more than once per row, or not at all for a row or even never for the entire query, and it mighte be called for rows that are not actually returned by the query, not to mention what would happen if this were used in an existential subquery. And then there are the optimization strategies that the oracle optimizer might use to re-write your query in which case who knows how and/or when and/or if your function will get called.

I have only included this example for completeness.

Good luck, Kevin
Re: Exception handling [message #419682 is a reply to message #418452] Tue, 25 August 2009 02:34 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
Hi Kevin, yes it can be done..but it should not be encouraged..pragma Autonomous_transaction should only be used of you want any procedure to be used for auditing purpose.
Re: Exception handling [message #419701 is a reply to message #418406] Tue, 25 August 2009 04:32 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Let me answer that question since I was doing some stuff like this lately:

CREATE PROCEDURE p IS
   x INTEGER;
BEGIN
   NULL;
EXCEPTION
   WHEN OTHER THEN
      "log_package"."error"
      (msg=>'error happened'
      ,details=>'dynamic content '||x
      );
      RAISE; -- always re-raise exceptions
      -- but since you log exception you can catch it and forget about re-raising
END;

CREATE PACKAGE "log_package" IS
BEGIN
   PROCEDURE "error"(msg VARCHAR2, details CLOB);
END "log_package";

CREATE PACKAGE BODY "log_package" IS
BEGIN
   PROCEDURE INSERT_LOG IS
      PRAGMA AUTONOMOUS TRANSACTIONS
   BEGIN
      INSERT INTO my_table VALUES (...); -- would be the best to use a cursor for this... do not specify time - instead skip such column here and create it in the table with default set to sysdate or current_timestamp or ...
      -- you might also log traceback...
   END INSERT_LOG;
   PROCEDURE "error"(msg VARCHAR2, details CLOB) IS
   BEGIN
      INSERT_LOG(msg,details,sqlcode,sqlerrm,'error');
   EXCEPTION
      WHEN OTHERS THEN
         RAISE;
         -- either re-raise or handle it... but that procedure should never fail or you are in trouble
   END "error";
END "log_package";


I have much more complex logging mechanism which is written in pure PL/SQL - however it is a proprietary code.
This might be better to use LOG4SQL, LOG4PLSQL or similar things - I could not use them so I did it in PL/SQL.
Re: Exception handling [message #419702 is a reply to message #418406] Tue, 25 August 2009 04:42 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Some more tips:
- I hope that I have answered your question - although it was probably not clear enough.
- You can insert into a table in exception block - but you should avoid that.
- You should never handle exceptions (at least "WHEN OTHERS") without re-raising them. The only exception from that rule that I know is when you want to log them and continue processing. This is what Michel Cadot wrote (hi Michel Smile ).
- You should never use DDL (ex. "create table") inside PL/SQL - since it is not PL/SQL... And I must admit that I am using DDL from my PL/SQL but I know about the risks and problems that are related to it (at least I think so). This is what Kevin Meade wrote.
- Pragmas are described in the page provided by delna.sexy.
- Autonomous transactions are extremely dangerous... Think twice... and one more time... and one more... before you use them. And the same for triggers. And a lot of other things in Oracle.

Good luck Wink
Re: Exception handling [message #419741 is a reply to message #419702] Tue, 25 August 2009 07:36 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You should not catch exceptions unless you plan to do something with that info. The WHEN OTHERS THEN RAISE is a perfect example of bad code; it only obscures the line where the actual exception occurred.
Re: Exception handling [message #419746 is a reply to message #419702] Tue, 25 August 2009 07:47 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
wakula

   PROCEDURE INSERT_LOG IS
      PRAGMA AUTONOMOUS TRANSACTIONS
   BEGIN
      INSERT INTO my_table VALUES (...); -- would be the best to use a cursor for this... do not specify time - instead skip such column here and create it in the table with default set to sysdate or current_timestamp or ...
      -- you might also log traceback...
   END INSERT_LOG;x



you missed to mention COMMIT/ROLLBACK in your code Wink

[Updated on: Tue, 25 August 2009 07:50]

Report message to a moderator

Re: Exception handling [message #419747 is a reply to message #419741] Tue, 25 August 2009 07:53 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Frank wrote on Tue, 25 August 2009 14:36
You should not catch exceptions unless you plan to do something with that info. The WHEN OTHERS THEN RAISE is a perfect example of bad code; it only obscures the line where the actual exception occurred.


Thank's god that there is DBMS_UTILITY Wink
Re: Exception handling [message #419749 is a reply to message #419701] Tue, 25 August 2009 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
wakula wrote on Tue, 25 August 2009 05:32

CREATE PACKAGE "log_package" IS

CREATE PACKAGE BODY "log_package" IS
...
   PROCEDURE "error"(msg VARCHAR2, details CLOB) IS
...
   END "error";
END "log_package";




Why would put a burden on someone by doing something as stupid as this? Now every time this package and it's procedure is used, you must call it with double quotes and make it case sensitive.
Re: Exception handling [message #419750 is a reply to message #419746] Tue, 25 August 2009 08:01 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
ayush_anand wrote on Tue, 25 August 2009 14:47

you missed to mention COMMIT/ROLLBACK in your code Wink

Yeap, I forgot about it... wrote it just from my head Smile

[Updated on: Tue, 25 August 2009 08:01]

Report message to a moderator

Re: Exception handling [message #419761 is a reply to message #419747] Tue, 25 August 2009 08:40 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
wakula wrote on Tue, 25 August 2009 14:53
Frank wrote on Tue, 25 August 2009 14:36
You should not catch exceptions unless you plan to do something with that info. The WHEN OTHERS THEN RAISE is a perfect example of bad code; it only obscures the line where the actual exception occurred.


Thank's god that there is DBMS_UTILITY Wink

Huh?

Would it not be better to remove redundant code than to leave it in and (try to) work your way around it?
I am quite sure that you can dbms_whatever you want, but you will NOT be able to trace back the original line of code, once I caught it in a WHEN OTHERS and reRAISE it.
Re: Exception handling [message #419779 is a reply to message #419761] Tue, 25 August 2009 10:03 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Frank wrote on Tue, 25 August 2009 15:40
I am quite sure that you can dbms_whatever you want, but you will NOT be able to trace back the original line of code, once I caught it in a WHEN OTHERS and reRAISE it.

Using DBMS_UTILITY you can get/save the line in the code there exception happened. If you have ever worked with object oriented programming (ex. java or c#) then you should know what is an exception dump... If you can dump an exception with its number, description, location in the code, date and time.... what more would you need? You can use that information to log it and then re-raise. From the log you "trace back the original line of code"...
So once again - please explain me what is wrong with that:
PROCEDURE p ... IS BEGIN
EXCEPTION WHEN OTHERS THEN
my_log_procedure;RAISE;
END p;


I guess that you might not believe me so here is an example log from such working system (I have modified it a little bit to mask probably-proprietary data):

Error details: "Higher partition already exists (MY_TABLE/MY_TABLE_2009)"

Callstack:
----- PL/SQL Call Stack -----
object line object
handle number name
46fc2ef48 402 package body LOG
46fc2ef48 581 package body LOG
441ae0fe8 159 procedure ADD_PARTITION
441ae0fe8 244 procedure ADD_PARTITION
441ae0fe8 282 procedure ADD_PARTITION
441ae0fe8 316 procedure ADD_PARTITION
46f5b24c8 3 anonymous block

Error stack:
ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

Error backtrace:
ORA-06512: at "ADD_PARTITION", line 181

[Updated on: Tue, 25 August 2009 10:07]

Report message to a moderator

Re: Exception handling [message #419892 is a reply to message #419779] Wed, 26 August 2009 01:06 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I put together a testcase, and either there is another method in dbms_utility that I overlooked, or handled exceptions are indeed NOT retracable:
SQL> conn scott/tiger
Connected.
SQL> set echo on
SQL> @faq
SQL> create or replace package faq_tst
  2  as
  3    procedure entry_point;
  4  end faq_tst;
  5  /

Package created.

SQL> create or replace package body faq_tst
  2  as
  3    procedure inner_proc_2
  4    is
  5    begin
  6      raise no_data_found;
  7    exception
  8      when others
  9      then
 10        raise;
 11    end;
 12
 13    procedure inner_proc_1
 14    is
 15    begin
 16      inner_proc_2;
 17    exception
 18      when others
 19      then
 20        raise;
 21    end;
 22
 23    procedure entry_point
 24    is
 25    begin
 26      inner_proc_1;
 27    end;
 28  end faq_tst;
 29  /

Package body created.

SQL> declare
  2    l_text varchar2(32000);
  3  begin
  4    faq_tst.entry_point;
  5  exception
  6    when others
  7    then
  8      dbms_output.put_line(substr(sqlerrm, 1, 256));
  9      dbms_output.put_line('Error stack:');
 10      l_text := dbms_utility.format_error_stack;
 11      while length(l_text) > 0
 12      loop
 13        dbms_output.put_line(substr(l_text, 1, 256));
 14        l_text := substr(l_text, 256);
 15      end loop;
 16      dbms_output.put_line('Call stack:');
 17      l_text := dbms_utility.format_call_stack;
 18      while length(l_text) > 0
 19      loop
 20        dbms_output.put_line(substr(l_text, 1, 256));
 21        l_text := substr(l_text, 256);
 22      end loop;
 23  end;
 24  /
ORA-01403: no data found
Error stack:
ORA-01403: no data found

Call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
6860DCA0        17  anonymous block


PL/SQL procedure successfully completed.
Re: Exception handling [message #419900 is a reply to message #419892] Wed, 26 August 2009 02:26 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Frank wrote on Wed, 26 August 2009 08:06
I put together a testcase, and either there is another method in dbms_utility that I overlooked, or handled exceptions are indeed NOT retracable:


You cannot re-throw all the call stack - and this is why you log it.
If your system is a high reliability system then you cannot say "sorry but google.com is down since we had an unhandled exception - now we are reading the exception's message to check what is wrong". Instead you capture the error, log it, handle it... and if you cannot handle it then you might re-throw it (because upper layer might handle it). You don't want to make your system totally down by not-capturing the exception and you just need to know where the exception occured to fix the issue later.
Check google for exception handling theory. Some example is:
try
{
   nothing;
}
catch (Exception e)
{
   if(!can_handle(e))
   {
      throw(e);
   }
}


Just to point it out:
Frank wrote on Tue, 25 August 2009 15:40

I am quite sure that you can dbms_whatever you want, but you will NOT be able to trace back the original line of code, once I caught it in a WHEN OTHERS and reRAISE it.

Since you can log the exact location in the code you might trace back the original line of code and reRAISE it.

[Updated on: Wed, 26 August 2009 02:28]

Report message to a moderator

Re: Exception handling [message #419909 is a reply to message #419900] Wed, 26 August 2009 02:38 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
My point was that you should not catch exceptions if you don't plan to do anything but raise them. Your original code example (the one I initially replied to) had
  WHEN OTHERS
  THEN
    RAISE;

I said this is bad code. I still say so.
There is no point in catching when others other then in the "outer layer" of your code and my original point still stands: if you don't plan to do anything, you should not catch an exception. (Unless you expect it and want to ignore it)
Re: Exception handling [message #420628 is a reply to message #419909] Tue, 01 September 2009 01:17 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Hi Frank

I just came across a piece of code that might be useful
 CREATE OR REPLACE PROCEDURE proc1 IS
    BEGIN
       DBMS_OUTPUT.put_line ('running proc1');
       RAISE NO_DATA_FOUND;
    END;
    /

    CREATE OR REPLACE PROCEDURE proc2 IS
       l_str VARCHAR2 (30) := 'calling proc1';
    BEGIN
       DBMS_OUTPUT.put_line (l_str);
       proc1;
    END;
    /
    CREATE OR REPLACE PROCEDURE proc3 IS
    BEGIN
       DBMS_OUTPUT.put_line ('calling proc2');
       proc2;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line ('Error stack at top level:');
          DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
    END;
    /

The only program with an exception handler is the outermost program, proc3. I have placed a call to the backtrace function in proc3's WHEN OTHERS handler. When I run this procedure I see the following results:

    SQL> SET SERVEROUTPUT ON
    SQL> BEGIN
      2     DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace');
      3     proc3;
      4  END;
      5  /
    Proc3 -> Proc2 -> Proc1 backtrace
    calling proc2
    calling proc1
    running proc1
    Error stack at top level:
    ORA-06512: at "SCOTT.PROC1", line 4
    ORA-06512: at "SCOTT.PROC2", line 5
    ORA-06512: at "SCOTT.PROC3", line 4

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

[Updated on: Tue, 01 September 2009 01:20]

Report message to a moderator

Re: Exception handling [message #420631 is a reply to message #419892] Tue, 01 September 2009 01:29 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
@ Frank In your words
SQL> set serverout on
SQL>  declare
  2     l_text varchar2(32000);
  3   begin
  4     faq_tst.entry_point;
  5   exception
  6     when others
  7     then
  8       dbms_output.put_line(substr(sqlerrm, 1, 256));
  9       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
 10       dbms_output.put_line('Error stack:');
 11       l_text := dbms_utility.format_error_stack;
 12       while length(l_text) > 0
 13       loop
 14         dbms_output.put_line(substr(l_text, 1, 256));
 15         l_text := substr(l_text, 256);
 16       end loop;
 17       dbms_output.put_line('Call stack:');
 18       l_text := dbms_utility.format_call_stack;
 19       while length(l_text) > 0
 20       loop
 21         dbms_output.put_line(substr(l_text, 1, 256));
 22         l_text := substr(l_text, 256);
 23       end loop;
 24   end;
 25   /
ORA-01403: no data found
ORA-06512: at "AYUSH.FAQ_TST", line 20
ORA-06512: at "AYUSH.FAQ_TST", line 26
ORA-06512: at line 4

Error stack:
ORA-01403: no data found

Call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
254C7BB4        18  anonymous block


PL/SQL procedure successfully completed.

[Updated on: Tue, 01 September 2009 01:30]

Report message to a moderator

Re: Exception handling [message #420634 is a reply to message #420631] Tue, 01 September 2009 01:41 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Thanks for the examples, but this only proves my point: do NOT catch exceptions at subordinate levels, or you will lose your stack-info.
Re: Exception handling [message #420648 is a reply to message #420634] Tue, 01 September 2009 04:25 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Am I getting it wrong ?
ORA-01403: no data found
ORA-06512: at "AYUSH.FAQ_TST", line 20
ORA-06512: at "AYUSH.FAQ_TST", line 26
ORA-06512: at line 4


This DBMS_UTILITY.format_error_backtrace actually traces to the starting point of the exception.
Re: Exception handling [message #420666 is a reply to message #420648] Tue, 01 September 2009 06:38 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
That is because you did not catch it anywhere but in the outermost layer.
The point was that you should not catch (and reraise) the exception in proc1, proc2, etc. because then you would no longer be able to trace the origin of the exception.
Re: Exception handling [message #420673 is a reply to message #420666] Tue, 01 September 2009 07:19 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> set echo on
SQL> @faq
SQL> create or replace package faq_tst
  2  as
  3    procedure entry_point;
  4  end faq_tst;
  5  /

Package created.

SQL> create or replace package body faq_tst
  2  as
  3    procedure inner_proc_2
  4    is
  5    begin
  6      raise no_data_found;
  7    exception
  8      when others
  9      then
 10        raise;
 11    end;
 12
 13    procedure inner_proc_1
 14    is
 15    begin
 16      inner_proc_2;
 17    exception
 18      when others
 19      then
 20        raise;
 21    end;
 22
 23    procedure entry_point
 24    is
 25    begin
 26      inner_proc_1;
 27    end;
 28  end faq_tst;
 29  /

Package body created.
SQL> set serverout on
SQL>  declare
  2     l_text varchar2(32000);
  3   begin
  4     faq_tst.entry_point;
  5   exception
  6     when others
  7     then
  8       dbms_output.put_line(substr(sqlerrm, 1, 256));
  9       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
 10       dbms_output.put_line('Error stack:');
 11       l_text := dbms_utility.format_error_stack;
 12       while length(l_text) > 0
 13       loop
 14         dbms_output.put_line(substr(l_text, 1, 256));
 15         l_text := substr(l_text, 256);
 16       end loop;
 17       dbms_output.put_line('Call stack:');
 18       l_text := dbms_utility.format_call_stack;
 19       while length(l_text) > 0
 20       loop
 21         dbms_output.put_line(substr(l_text, 1, 256));
 22         l_text := substr(l_text, 256);
 23       end loop;
 24   end;
 25   /
ORA-01403: no data found
ORA-06512: at "AYUSH.FAQ_TST", line 20
ORA-06512: at "AYUSH.FAQ_TST", line 26
ORA-06512: at line 4

Error stack:
ORA-01403: no data found

Call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
254C7BB4        18  anonymous block


PL/SQL procedure successfully completed.

Please look into this

[Updated on: Tue, 01 September 2009 07:21]

Report message to a moderator

Re: Exception handling [message #420674 is a reply to message #420666] Tue, 01 September 2009 07:21 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
sorry for reposting

[Updated on: Tue, 01 September 2009 07:22]

Report message to a moderator

Re: Exception handling [message #420676 is a reply to message #420673] Tue, 01 September 2009 07:49 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Your error-stack does not show that the actual error occurred at line 6.
Re: Exception handling [message #420677 is a reply to message #420676] Tue, 01 September 2009 08:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
How about this Wink
SQL> CREATE OR REPLACE PROCEDURE proc1 IS
  2  BEGIN
  3     DBMS_OUTPUT.put_line ('running proc1');
  4     RAISE NO_DATA_FOUND;
  5  EXCEPTION
  6     WHEN OTHERS THEN
  7        DBMS_OUTPUT.put_line (
  8           'Error stack in block where raised:');
  9        DBMS_OUTPUT.put_line  (
 10           DBMS_UTILITY.format_error_backtrace);
 11        RAISE;
 12  END;
 13  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE proc2
  2  IS
  3     l_str VARCHAR2 (30) := 'calling proc1';
  4  BEGIN
  5     DBMS_OUTPUT.put_line (l_str);
  6     proc1;
  7  END;
  8  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE proc3 IS
  2  BEGIN
  3     DBMS_OUTPUT.put_line ('calling proc2');
  4     proc2;
  5  EXCEPTION
  6     WHEN OTHERS
  7     THEN
  8        DBMS_OUTPUT.put_line ('Error stack at top level:');
  9        DBMS_OUTPUT.put_line  (DBMS_UTILITY.format_error_backtrace);
 10        DBMS_OUTPUT.put_line  (DBMS_UTILITY.format_error_backtrace);
 11  END;
 12  /

Procedure created.

SQL>  exec proc3
calling proc2
calling proc1
running proc1
Error stack in block where raised:
ORA-06512: at "AYUSH.PROC1", line 4

Error stack at top level:
ORA-06512: at "AYUSH.PROC1", line 11
ORA-06512: at "AYUSH.PROC2", line
6
ORA-06512: at "AYUSH.PROC3", line 4

ORA-06512: at "AYUSH.PROC1", line 11
ORA-06512: at "AYUSH.PROC2", line
6
ORA-06512: at "AYUSH.PROC3", line 4


PL/SQL procedure successfully completed.


Ok I got now what you trying to say
Sorry..and Thanks

[Updated on: Tue, 01 September 2009 08:07]

Report message to a moderator

Re: Exception handling [message #420679 is a reply to message #420677] Tue, 01 September 2009 08:08 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
This is going absolutely nowhere. Of course the error-line is available from the procedure itself.
You are STILL unable to trace the original error-line from beyond a caught exception. The discussion was about why not to catch an exception.
Bottom line: if you catch an exception, it is IMPOSSIBLE to retrieve the original error-line from any point onward.
Re: Exception handling [message #420680 is a reply to message #420679] Tue, 01 September 2009 08:10 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
got that ..Lots of thanks ..

this is what I found from Oracle Docs might be useful for future users

When I call the backtrace function within the lowest-level program, it correctly identifies line 4 of proc1 as the line in which the error is first raised. I then re-raise the same exception using the RAISE statement. When the exception propagates to the outermost block, I call the backtrace function again, and this time it shows that the error was raised on line 11 of proc1.

From this behavior, we can conclude that DBMS_UTILITY.FORMAT_ERROR_BACKTRACE shows the trace of execution back to the last RAISE in one's session. As soon as you issue a RAISE of a particular exception or re-raise the current exception, you restart the stack that the backtrace function produces. This means that if you want to take advantage of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, take one of the following two approaches:


Call the backtrace function in the exception section of the block in which the error was raised. This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack.
Avoid exception handlers in intermediate programs in your stack, and call the backtrace function in the exception section of the outermost program in your stack.

[Updated on: Tue, 01 September 2009 08:15]

Report message to a moderator

Re: Exception handling [message #420683 is a reply to message #420680] Tue, 01 September 2009 08:30 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ayush_anand wrote on Tue, 01 September 2009 15:10
From this behavior, we can conclude that DBMS_UTILITY.FORMAT_ERROR_BACKTRACE shows the trace of execution back to the last RAISE in one's session. As soon as you issue a RAISE of a particular exception or re-raise the current exception, you restart the stack that the backtrace function produces.

This is not entirely true. The RAISE does not reset the stacktrace, but the fact that the exception is caught. The moment an exception is caught, it is considered to be handled. Therefore, you cannot trace back to it anymore.
Re: Exception handling [message #420684 is a reply to message #420683] Tue, 01 September 2009 08:32 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
this time I have copied from this link Wink

http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html
Re: Exception handling [message #420685 is a reply to message #420684] Tue, 01 September 2009 08:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Sorry, you are correct. It is indeed the RAISE that reinitializes the stacktrace.
Re: Exception handling [message #474166 is a reply to message #419638] Fri, 03 September 2010 05:38 Go to previous messageGo to next message
danilopiazza
Messages: 5
Registered: September 2010
Location: Italy
Junior Member
Kevin Meade wrote on Mon, 24 August 2009 18:40
Functions doing DML can indeed be called from sql if they are autonomous.
[...]
That said, this is generally a very stupid thing to do and not the intent of feature.


Is using autonomous transactions a stupid thing if I only insert data into a temporary table?

create global temporary table tmp_table_dummy (val number);

create or replace function dummy_fun return number is
  pragma autonomous_transaction;
  s number;
begin
  -- A complex query, requiring a long time to execute
  insert into tmp_table_dummy
  select 4 from dual
  union all
  select 3 from dual;
  
  -- Further processing, where the saved data is read more than once
  select sum(val) into s from tmp_table_dummy;
  commit;
  return s;
end dummy_fun;

select * from (select dummy_fun from dual);
Re: Exception handling [message #474174 is a reply to message #474166] Fri, 03 September 2010 06:54 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why bother using an autonomous transaction in this case?
Re: Exception handling [message #474181 is a reply to message #474174] Fri, 03 September 2010 07:25 Go to previous messageGo to next message
danilopiazza
Messages: 5
Registered: September 2010
Location: Italy
Junior Member
Because of error ORA-14551: 14551, 00000, "cannot perform a DML operation inside a query ".

I need to save intermediate data (from an expensive computation) in a temporary table, in order to process it further (multiple times in multiple, different ways) in another query.
Re: Exception handling [message #474184 is a reply to message #474181] Fri, 03 September 2010 07:40 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
It might be valid in this case.
Though if you do it make sure you comment the function to indicate why it's acceptable in this case so other people don't try and copy it for non temp tables.

However I would look long and hard at your code to see if you can't rewrite all this as a single query, or just store the result in a variable.
It'll probably be more effiecient if you do.
Re: Exception handling [message #474196 is a reply to message #474184] Fri, 03 September 2010 08:35 Go to previous messageGo to next message
danilopiazza
Messages: 5
Registered: September 2010
Location: Italy
Junior Member
I tried to "store" intermediate results in an inline view using WITH, but I hit "ORA-32036: unsupported case for inlining of query name in WITH clause" (using the result of a WITH subquery more than once appears to be unsupported when using XA transactions).

Then I thought I could store intermediate results in a locally-defined collection and reuse them in a subsequent query, but "PLS-00642: local collection types not allowed in SQL statements".

The only remaining options are:
* Use a global temporary table.
* Call the expensive query more than one time, increasing my function's execution time by several minutes.
* Store intermediate results in a collection anyway, and rewrite in tedious procedural code what I would have accomplished with a query.
Re: Exception handling [message #474216 is a reply to message #474196] Fri, 03 September 2010 10:37 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have run into some of these cases, too.

For me using a global temporary table has worked well there.
It's one of the few cases where a GTT is actual the best solution my my eyes. (When you have one intermediate result that you have to process further in multiple different steps)

One further thing:

It's also easier to debug when you have a way to get at the intermediate result multiple times. In some cases I even built a view/edit function for the GTTs into the user interface, so that someone with more common sense than a computer can check and adjust the intermediate results.

[Updated on: Fri, 03 September 2010 10:40]

Report message to a moderator

Previous Topic: ORA-22813: operand value exceeds system limits
Next Topic: getting the rows' and columns' values (merged)
Goto Forum:
  


Current Time: Sun Nov 10 06:49:19 CST 2024