roland Daane, February 14, 2002 - 5:42 am UTC
This is really interesting stuff.
I'm using this method of work by inderting the data for the exceptions into an Oracle table and from that creating the error package.
I'm really interested in the source for the who_called_me procedure!
February 22, 2002 - 9:00 am UTC
A reader, August 20, 2003 - 11:28 am UTC
Sami, January 26, 2004 - 11:23 am UTC
Who am i -- Packages
Marcio, January 31, 2004 - 10:01 am UTC
Tom, I still don't get how to work with packages and who_called_me, look this:
ops$t_mp00@MRP817> create or replace package pkg
3 procedure p;
4 procedure p1;
ops$t_mp00@MRP817> create or replace package body pkg
3 procedure p
8 procedure p1
10 l_owner varchar2(30);
11 l_name varchar2(30);
12 l_lineno number;
13 l_type varchar2(30);
15 who_called_me( l_owner, l_name, l_lineno, l_type );
16 dbms_output.put_line( l_owner || '.' || l_name);
Package body created.
ops$t_mp00@MRP817> exec pkg.p
How to log it into log_error, just package name? I mean the real name of procedure inside package can I see? Or any other suggestion for this kind of instrumentation am trying implement on?
January 31, 2004 - 10:13 am UTC
you get the schema object name (the package name) and the LINENUMBER.
there could be 500 procedure "p1's" in that package -- the package name and line number are what you get here. and that is all you can get. we do not track things down to the procedure/function name inside of a package.
Marcio, February 05, 2004 - 9:47 pm UTC
Suppose you have an entire batch system, setting up by parameters -- this system catch information from tables over dblink and populates some another tables and them write flat files as long as day range of parameter.
1) What would be a effective handling exception on this context?
2) Is it a good idea have a trigger before insert on log_error table to send up e-mails with body error caught to warning operators people that something is going wrong?
3) Is the exception_pkg code public?
February 06, 2004 - 8:37 am UTC
1) don't know, insufficient data, the programmers should "naturally know" as writing PLSQL code is no different then writing C, Java, VB -- just easier :)
This is a programming decision. I can only tell you the wrong ways to avoid -- such as "using when others" and returning error codes instead of propagating the exceptions out.
2) see #1. I would find it rather annoying myself but it is up to you.
3) everything on this site is for you to cut and paste, copy and use.
about question #2
Marcio, February 07, 2004 - 10:43 am UTC
2) But, how will the operator know if something was going wrong? I'm thinking mail him just where system goes down, not for any exception.
February 07, 2004 - 3:11 pm UTC
there are excellent system management tools on the market from hundreds of vendors (including Oracle with OEM) that will do that.
This was excellent!
Mike McAllister, January 06, 2006 - 5:39 pm UTC
This was an absolutely excellent and useful exception handling architecture. With a few changes I implemented it myself in the application I'm working on now. As another responder mentioned, I also modified the "throw" routine to also log the error to a table in the database using an autonomous transaction. I also modified the raise_application_error to set the third parameter to TRUE so that the application error is put on top of the error stack, rather than replacing it.
Exception INIT, Is it really required ?
Ravi Kumar, January 10, 2006 - 12:11 am UTC
I was looking for something in this website that "why do we need EXCEPTION INIT, While We have SQLCODE and we can use it in WHEN OTHERS".
And I found this and here you said ...
"What pragma exception_init is designed for is to convert an Oracle Error code into a named exception to make programing easier. Instead of having a big 'when
when others then
if ( sqlcode = some_value ) then
elsif ( sqlcode = some_other_value ) then
end if; "
But I think, If we use Pragma , We have to declare each exception , then associate each of it with oracle error code(using Pragma), and then handle each of it in exception section. I think in this way we are actualy making it bigger/difficult instead of shorter/easier.
Can I have your comments on this, Please ?
Thanks & Regards
January 10, 2006 - 8:03 am UTC
how many exceptions do you plan on handling there? Seems to me, it would be "a handful"
Which is more readable to you:
when no_data_found then ....
when too_many_rows then ....
when resource_busy then ...
when deadlock_detected then ....
if (sqlcode = -1403) then ...
elsif (sqlcode = -1422) then ...
elsif (sqlcode = -54) then ....
elsif (sqlcode = -60) then ...
I don't know about you, but I'd rather read the first - it is more efficient, it is definitely more readable, it is less error prone (tell me how many people will over look the need for the RAISE at the end?)
Given that most exceptions you want to trap are defined for you (no_data_found for example), and that you would rarely be having to define your own.....
I think that by using exceptions you are actually making it
o more maintainable
o more self documenting
Catch unique index error
Totu, April 20, 2006 - 2:43 am UTC
I can catch unique value error using DUP_VAL_ON_INDEX.
But if there are not one, some unique indexes, how can I catch which index and columns are the source of error?
Thanks in advance
April 20, 2006 - 8:03 am UTC
sqlerrm has the constraint name in it.
bug in code example
Dannie Kjeldgaard, March 15, 2007 - 9:39 am UTC
Notice that line 48 should read
if ( p_exception between -20999 and -20000 ) then
Minor change to who_called_me
Greg Anderson, March 20, 2007 - 12:12 pm UTC
many thanks for your excellent website.
My client is using Oracle9i 188.8.131.52.0. This appears to have a slightliy different call stack from that used in the original code.
This means the who_called_me procedure ought now to include:
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 21, 6 ));
line := substr( line, 29 );
Of course, this may require further modification for 10g.
I need help on control file structure in sql loader
anitha, April 16, 2010 - 4:53 am UTC
I wanted to reject row with all null fields while loading. i need option for the same in the control file structure.
Please give me the sample structure
April 16, 2010 - 10:50 am UTC
that option is:
<this space left intentionally blank>
it just happens already.
Matt, April 17, 2010 - 6:49 am UTC
Happy 10 Years Tom!!