Exception handling [message #418406] |
Fri, 14 August 2009 19:31 |
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 #418416 is a reply to message #418406] |
Fri, 14 August 2009 22:21 |
|
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 #418452 is a reply to message #418406] |
Sat, 15 August 2009 11:17 |
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 |
|
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 |
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 |
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 #419741 is a reply to message #419702] |
Tue, 25 August 2009 07:36 |
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 |
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
[Updated on: Tue, 25 August 2009 07:50] Report message to a moderator
|
|
|
|
Re: Exception handling [message #419749 is a reply to message #419701] |
Tue, 25 August 2009 07:58 |
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 #419779 is a reply to message #419761] |
Tue, 25 August 2009 10:03 |
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 |
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 |
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 |
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
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 |
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 |
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 |
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 |
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 |
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 |
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 #420677 is a reply to message #420676] |
Tue, 01 September 2009 08:03 |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
How about this
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 |
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 |
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 |
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 #474166 is a reply to message #419638] |
Fri, 03 September 2010 05:38 |
danilopiazza
Messages: 5 Registered: September 2010 Location: Italy
|
Junior Member |
|
|
Kevin Meade wrote on Mon, 24 August 2009 18:40Functions 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 #474181 is a reply to message #474174] |
Fri, 03 September 2010 07:25 |
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 |
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 |
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 |
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
|
|
|