it is a very useful peace of code

thanks

Thanks for a wonderful piece of code

Hi,

I whole heartedly agree that knowing exactly which line of code errored and the error it got it of a huge help. But often I have found that you need the context of the error for investigation of the problem. So in your example while you know that you violated the PK on a table, you don't know why you violated it (in your example you tried to insert the value 1)

I've tried various models but it seems Oracle will never be able to give me both pieces of information, as I would have to trap the error to set context Info (perhaps parameters for an "insert into select" statement and then lose the line that actually caused me to jump into the exception.

Is there a way around this? The only idea I have had is to continually keep up to date some context information for the operation you are to perform next, so if you error, you know the value in this is context of the operation that failed. I was thinking of storeing this with application_info

However I'm worried about the overhead of this. I see some possible view points, I wondered if you subscribed to one of them or a seperate one.

1. I don't think you need that context info. the context info of the caller should be enough
2. Yeah, it's an overhead and tough. if it makes the application too slow maintaining the context, you have a bad design or either the application or your context model.
3. errr, that's it.

Thanks as always,

Mike.

Tom,
I tried executing your function whence independently as a PL/SQL Block. The return value (line) has the NULL value.

regards

>> function whence
>> 11 exit when ( cnt = 4 or n is NULL or n = 0 );

Tom , You provided a procedure variation of the above function (think you called it "who_called_me")
you had "exit when ( cnt = 3 or n is NULL or n = 0 ); "
and you commented:
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller

so what's cnt = 4 in the function whence then ? Is this as high as cnt will get ?
Thanks

>> what's cnt = 4 ?
never mind my original question...it's just one of the slice in the stack. Thanks

Tom,

I would like to offer a suggestion for getting context and I hope you or others might comment on it.

I would add 2 columns to the 'error_table' (e.g. CONTEXT_LOCATION and CONTEXT_ID) and add add the same 2 input parameters to the log_error procedure.
All you have to do to use it are these simple steps.
1) Define 2 local variables in your program corresponding to the 2 log_error parameters (l_context_location, l_context_id)
2) Before each sql statement, load L_CONTEXT_ID with enough info to be able to find the offending row (e.g. primary key info)
3) Additionally, you can also load L_CONTEXT_LOCATION with info to tell you where you are in your program, just in case the context_id info is not enough.
4) If there is an error, since these local variables are being passed to the log_error procedure, this info will be automatically get loaded into the error table.

create table error_table
( timestamp date,
whence varchar2(1024),
msg varchar2(1024),
code number,
context_location varchar2(1024),
context_id varchar2(1024)
);

create or replace procedure log_error ( p_context_location varchar2, p_context_id varchar2)
as
pragma autonomous_transaction;

l_whence varchar2(1024);
l_msg varchar2(1020) default sqlerrm;
l_code number default sqlcode;
begin
l_whence := whence;
insert into error_table
( timestamp, whence, msg, code, context_location, context_id )
values
( sysdate, whence, l_msg, l_code, p_context_location, p_context_id );
commit;
exception
when others then
rollback;
raise;
end;
/

Then your code would look like this, for example

declare
....
begin
....
L_CONTEXT_LOCATION := 'before main loop...';
for r1 in c1 loop
L_CONTEXT_ID := 'account_id:' || to_char(r1.account_id) || ':';
update t
set amount=r1.amount
where account_id = r1.account_id;
end loop
exception
when others then
log_error(L_CONTEXT_LOCATION, L_CONTEXT_ID);
raise;
end;

What do you think?

Thanks,

Robert.

declare
err_msg varchar2(1024);
begin
insert into t values ( 1 );
exception
when dup_val_on_index then
-- modified log_error procedure, in order to return the whence
log_error(err_msg);

raise;
dbms_output.put_line('err_msg'||err_msg);
when others then
log_error(err_msg);
dbms_output.put_line('err_msg'||err_msg);
raise;
end;

My requirement is, Instead of inserting error stack messages into a table in procedure log_error, I need to pass the Error Stack trace to Java application.
When I try to do so, the out string contains no values.

Please suggest me how can be done this.

Tom,
Can you comment on Log4j for PL/SQL. (it looks like you can lot of options with this).
How good is this (performance point of view and usability etc..).
thanks
Jack

Tom,
I have a requirement like below.
I have a main procedure say proc1. In this I'll call other procedures and functions. At the end I need to send all the errors as a out parameter( say err_desc out varchar2) occurred at the child procedures and functions along with the procedure/function name. The main Procedure Proc1 will be called from java application. At java end they will display the message as it is. Since, message is having name of the SP/Func and type of error, the debugging can be done easily.
Un fortunately, I should not go for any additional tables to resolve this issue.
Will this be solved with packaged variables.

My code looks like this.

create procedure Proc1(err_desc out varchar2)
begin

--

--- nested procedure calls.
proc2; -- is a child procedure.
select fun1('xx') into yy from dual; -- here is a function call.

---

end;
/

And, When I tried your example as it is, no entries were inserted for error_table.whence filed.

Hi Tom,
My actual code looks like this.
-- child program.
FUNCTION SP_Poc_UserRoles_FNC(in_nUserId IN NUMBER) RETURN VARCHAR2 IS

CURSOR v_cvRoleDesc(UserId VARCHAR2) IS SELECT ROLE_ID FROM T_Poc_USER_ROLE WHERE USER_ID = UserId ;
v_Description VARCHAR2(150);
v_TempDesc VARCHAR2(50);
BEGIN
FOR INDX IN v_cvRoleDesc(in_nUserId)
LOOP
SELECT INITCAP(ROLE_NAME) INTO v_TempDesc FROM T_Poc_ROLE WHERE ROLE_ID = INDX.ROLE_ID ;
v_Description := v_TempDesc||', '||v_Description;
END LOOP;
v_Description := SUBSTR(v_Description,0,(LENGTH(v_Description)-2));
RETURN v_Description;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;

END ;
The above function is called in the SP below at line no 12.

-- main program.
PROCEDURE SP_Poc_SysAdmin_UserDetails(in_nRoleId IN NUMBER,in_nOperationCenterId IN NUMBER,out_nStatusCode OUT NUMBER,out_nWipAccountNo OUT NUMBER,out_cvGenric OUT PKG_Poc_CommDefi.Gencurtyp,out_cErrorDesc OUT VARCHAR2) IS
v_cUserRole VARCHAR2(20) ;
v_cSqlString VARCHAR2(1000) ;
v_nSysAdmin NUMBER ;
v_nSuper NUMBER ;
BEGIN -- mian blcok begins
v_nSysAdmin := PKG_Poc_CommDefi.v_SysAdmin ;
v_nSuper := PKG_Poc_CommDefi.v_Super ;
out_nStatusCode := 0 ;
v_cSqlString := 'SELECT US.USER_NAME,
US.MODIFIED_BY ,
SP_Poc_UserRoles_FNC(US.USER_ID) User_Roles, ------**** here is a function call.
TO_CHAR(US.MODIFY_DATE ,''MM/DD/YYYY-HH24:MI'') ,
ROLE_NAME,
US.USER_ID
FROM T_Poc_USER US,
T_Poc_USER_ROLE USRROLE,
T_Poc_ROLE ROLE
WHERE USRROLE.USER_ID = US.USER_ID AND
USRROLE.ROLE_ID = ROLE.ROLE_ID AND
USRROLE.ROLE_ID != :SUPER AND
PRIMARY_ROLE_FLAG = ''Y'' AND
ACTIVE_FLAG = ''Y'' ;

-- Here v_nSysAdmin will be 2
IF in_nRoleId = v_nSysAdmin THEN

BEGIN

OPEN out_cvGenric FOR v_cSqlString ||'AND US.OPERATION_CENTER_ID = :OperationCenterId '
USING v_nSuper,in_nOperationCenterId ;

EXCEPTION WHEN OTHERS THEN
out_nStatusCode := SQLCODE; -- Send fail status back.
out_cErrorDesc := 'Error occurred while retrieving User Details. ' || SQLERRM;
END ;
-- Here v_nSysAdmin will be 1
ELSIF in_nRoleId = v_nSuper THEN
BEGIN
OPEN out_cvGenric FOR v_cSqlString ||' AND ROLE.ROLE_ID = :UserRole '
USING v_nSuper, v_nSysAdmin ;

EXCEPTION WHEN OTHERS THEN
out_nStatusCode := SQLCODE; -- Send fail status back.
out_cErrorDesc := 'Error occurred while retrieving User Details. ' || SQLERRM;
END ;
END IF;

EXCEPTION WHEN OTHERS THEN
out_nStatusCode := SQLCODE; -- Send fail status back.
out_cErrorDesc := 'Error occurred while retrieving User Details. ' || SQLERRM;
END SP_Poc_SysAdmin_UserDetails;
/
show err;

I(we) the child function will never raise errors. Because it is simple select statement.
But I still need to show the client, that, we are logging function name and error message in case of nested SP calling cases.

Tom:

If an insert did not work and it did 0 records would it raise any exception handler in pl/sql.

I have three tables and each insert is dependent on the previous one. Instead of creating a lot of IF SQL%rowcount > 0 after each insert I want to rollback if first one fails.

I was thinking of assigning
var1:=sql%rowcount after each insert and on the last statement do:

If (var1 > 0 and var2 > 0 And var3 > 0) then
commit;
else
rollback;
end if;

any suggestions?

I found the article most useful.

When testing the "whence" function I found that the error stack may vary in length, hence if the error stack did not contain 4 rows of data then a zero length (null) string would be inserted in to the "whence" column of the error_table.

I have rewritten the function to extract the last line of the call stack no matter the size.

FUNCTION whence RETURN VARCHAR2
AS
v_call_stack VARCHAR2(4096) DEFAULT DBMS_UTILITY.FORMAT_CALL_STACK; -- call stack buffer
v_len PLS_INTEGER DEFAULT 0; -- length of the call stack
v_pos PLS_INTEGER DEFAULT 0; -- position of line feed character
v_next_pos PLS_INTEGER DEFAULT 0; -- next position of line feed character
BEGIN
-- get the length of the call stack buffer
v_len := LENGTH(v_call_stack);

LOOP
-- find line feed
v_next_pos := INSTR(v_call_stack, CHR(10), v_pos+1);
-- find next line feed (look ahead - exit loop early as we required second to last line feed char.)
v_next_pos := INSTR(v_call_stack, CHR(10), v_next_pos+1);

-- exit when no more line feed characters found or length of buffer exceeded
EXIT WHEN (v_next_pos IS NULL OR v_next_pos = 0 OR v_next_pos >= v_len);

-- set the current position to the last one found plus one
v_pos := INSTR(v_call_stack, CHR(10), v_next_pos+1);
END LOOP;

-- return the last line in the call stack (+2 to allow for line feed character)
RETURN SUBSTR(v_call_stack, v_pos+2, v_len-(v_pos+2));
END;

Please disregard my submission as the code does not work correctly. I have gone back to using Tom's code for the "whence" function.

In page 473 of your book (almost half way done), you share one of your experiences where only WHEN OTHERS was used in the exceptions block of a deeply nested stored procedure. You mentioned that it was used in such a way that it "catches and ignores all exceptions" and that it "should never be used.." I didn't quite understand what you meant by "catches and ignores." To me, that seems contradictory. I'm sure you don't mean only using WHEN OTHERS in the exceptions block, as you did in your sample code above. Could you elaborate? Demonstrate what you mean please.

Ok I see. Makes sense now. Thanks

{
....
...

CallableStatement procnone = con.prepareCall (
"begin dbms_ouput.put_line('Hello Tom'); end;");

procnone.execute ();

....
}

as we can execute the java PL/SQL anonymous block 
from java stored proc., where is the output 
Hello Tom stored ?

if i m doing this on sql plus

SQL> begin dbms_output.put_line('Hello Tom'); end;
  2  /
Hello Tom

PL/SQL procedure successfully completed.

if I use the same anonymous block in 
what java object/property will I receive 

1.) Hello Tom
2.) PL/SQL procedure successfully completed.

outputs ? I need it.

Thank you very much...
 

Hi tom Thanks for your reply.

Still can you please simplify this for me !!

I just need only one statment in that block

"begin " +
" dbms_output.put_line('Hello Tom 1'); " +
" dbms_output.put_line('Hello Tom 2'); " +
"end;" ;

I tried to do that and it gave me lots of error
I am a newbee in java so please just
tellme on "WHAT VARIABLE " will I get the output of
dbms_output statment

do I have to bind that ?
also if you can show me that you ran that and output of that
it will be great ! ( I tried it since moring)

Thanks..

so Oracle don't have dbms_output " put_line " implemantation
for java/jdbc is that right ?

1.) plese show me how will you run that code
2.)if my user passes the pl/sql anynymos block/procedure with " dbms_output.put_line " , do i " have to " parese it and change it to get_line ? will you do that (if u konw, please give me solution.)
3.) let's say in the block there are more then one
dbms_output.put_line staments how to retirve them after execution?
4.) "PL/SQL procedure successfully completed."
" 39 rows selected." are those SQL*PLUS messages
and those are not returned by server ?
** so does sqlplus counts them and then displays ?
**for counting doesn't it retrive "all" the rows first/*+All_rows */ or looping or something ..?

/*************************************************/

import java.sql.*;

class DbmsOutput
{

private CallableStatement enable_stmt;
private CallableStatement disable_stmt;
private CallableStatement show_stmt;


public DbmsOutput( Connection conn ) throws SQLException
{
enable_stmt = conn.prepareCall( "begin dbms_output.enable(:1); end;" );
disable_stmt = conn.prepareCall( "begin dbms_output.disable; end;" );

String str = "declare msg varchar2(50); begin dbms_output.get_line('******Hello******',msg); end;";

// String str = "begin dbms_output.put_line('************Hello****'); end;";

/*
String str ="declare " +
" l_line varchar2(255); " +
" l_done number; numx number := 0;" +
" l_buffer long; myvar number := null;" +
"begin " +
" dbms_output.put_line( to_char(:maxbytes) ); numx := numx + 1; " +
// " dbms_output.get_line(:maxbytes,myvar); " +
" dbms_output.get_line( l_line, l_done ); " +
" dbms_output.put_line( ': **** Hello 1*****' ); " +
" dbms_output.put_line( ': **** Hello 2*****' ); " +
" l_buffer := l_buffer || l_line || chr(10); " +
" :done := l_done; " +
" :buffer := l_buffer; " +
"end;" ;
*/
show_stmt = conn.prepareCall(str );

System.out.println(str);

for(int xm=32000 ; ;xm++ )
{

System.out.println("enabling output size :" + xm);
enable(xm);

/* System.out.println("executing stmt");
show_stmt.execute ();
*/
System.out.println("showing output");
show();
/*
System.out.println("disabling output");
disable();
*/

}

}

public void enable( int size ) throws SQLException
{
enable_stmt.setInt( 1, size );
enable_stmt.executeUpdate();
}

public void disable() throws SQLException
{
disable_stmt.executeUpdate();
}


public void show() throws SQLException
{
int done = 0;
System.out.println("Trying to show output 1");

// show_stmt.registerOutParameter( 2, java.sql.Types.INTEGER );
// show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR );
show_stmt.registerOutParameter( 1, java.sql.Types.VARCHAR );
System.out.println("Trying to show output 2");

// show_stmt.setInt( 1, 32000 );
show_stmt.executeUpdate();
System.out.println("Trying to show output 3");

System.out.print(" Here is the output : " + show_stmt.getString(1) );

System.out.println("\n **Trying to show output 4");

// if ( (done = show_stmt.getInt(2)) == 1 ) ;
System.out.println(" \n **Trying to show output 5");

}

public void close() throws SQLException
{
enable_stmt.close();
disable_stmt.close();
show_stmt.close();
}
public static void main( String args[] )
throws SQLException
{

try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

System.out.println( "connection - " + "jdbc:oracle:thin:@"+ "myserver:1521" +":"+"myinstance" );
Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@"+ "myserver:1521" +":"+"myinstance","xyz","xki+=-ldkdi");


new DbmsOutput(con); // calling constructor

}catch(Exception e){
System.out.println( "Can't connect - " + e.getMessage());
}



}//end of main()

}
/*********************************************/

today I understood what you were saying..
I got the output !!

thanks,

I.

SQL> CREATE OR REPLACE PACKAGE TEST5
  2  AS
  3  PROCEDURE ERROR_TEST;
  4  END;
  5  /

Package created.


  1  CREATE OR REPLACE PACKAGE BODY TEST5
  2  AS
  3   PROCEDURE ERROR_TEST
  4      IS
  5      ET_VAR NUMBER(1);
  6      BEGIN
  7      ET_VAR_VAR := 'ABCDEF';
  8      END;
  9* END;
 10  /

Warning: Package Body created with compilation errors.

SQL> SHO ERR
Errors for PACKAGE BODY TEST5:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/5      PLS-00201: identifier 'ET_VAR_VAR' must be declared
7/5      PL/SQL: Statement ignored
SQL> 



*********************************************************************************************************************************
II.


SQL> ed
Wrote file afiedt.buf

  1  /* Formatted by PL/Formatter v2.0.8.0 on 2000/02/16 20:30  (08:30 PM) */
  2  CREATE OR REPLACE PACKAGE pk_genutilitypkg
  3  AS
  4     /*
  5     Generic String Parser: provide a delimiter and it returns an
  6     index-by table of the individual elements of the string that are
  7     separated by the specified delimiter.
  8     Author: "GAUTHAM CHANDRASEKARAN" <gautha@hotmail.com>
  9     */
 10     TYPE t_string IS TABLE OF VARCHAR2(2000)
 11        INDEX BY BINARY_INTEGER;
 12     m_ctr NUMBER(5);
 13     m_pos NUMBER(5);
 14     PROCEDURE sp_parsestring (
 15        p_string IN VARCHAR2,
 16        delimiter IN VARCHAR2,
 17        p_t_string OUT t_string);
 18  END pk_genutilitypkg;
 19  /
 20  CREATE OR REPLACE PACKAGE BODY pk_genutilitypkg
 21  AS
 22     PROCEDURE sp_parsestring (
 23        p_string IN VARCHAR2,
 24        delimiter IN VARCHAR2,
 25        p_t_string OUT t_string)
 26     IS
 27        m_string VARCHAR2(4000);
 28     BEGIN
 29        /* Raise a Error if the length of the delimiter is not 1 */
 30        IF LENGTH (delimiter) != 1
 31        THEN
 32           raise_application_error (-20001,
 33               'Delimiter should be of only one character');
 34           RETURN;
 35        END IF;
 36        m_string := p_string;
 37        m_ctr := 1;
 38        LOOP
 39           m_pos := INSTR (m_string, delimiter);
 40           IF m_pos > 1
 41           THEN
 42              p_t_string (m_ctr) := SUBSTR (m_string, 1, m_pos - 1);
 43              IF (m_pos < LENGTH (m_string))
 44              THEN
 45                 m_string := SUBSTR (
 46                                m_string,
 47                                m_pos + 1,
 48                                LENGTH (m_string) - m_pos
 49                             );
 50              ELSIF m_pos = LENGTH (m_string)
 51              THEN
 52                 m_ctr := m_ctr + 1;
 53                 p_t_string (m_ctr) := NULL;
 54                 EXIT;
 55              END IF;
 56           ELSIF m_pos = 1
 57           THEN
 58              p_t_string (m_ctr) := NULL;
 59              IF m_pos < LENGTH (m_string)
 60              THEN
 61                 m_string := SUBSTR (
 62                                m_string,
 63                                m_pos + 1,
 64                                LENGTH (m_string) - m_pos
 65                             );
 66              ELSIF m_pos = LENGTH (m_string)
 67              THEN
 68                 m_ctr := m_ctr + 1;
 69                 p_t_string (m_ctr) := NULL;
 70                 EXIT;
 71              END IF;
 72           ELSIF m_pos = 0
 73           THEN
 74              p_t_string (m_ctr) := m_string;
 75              EXIT;
 76           END IF;
 77           m_ctr := m_ctr + 1;
 78        END LOOP;
 79     END;
 80* END pk_genutilitypkg;
 81  /

Warning: Package created with compilation errors.

SQL> sho err
Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
   PACKAGE BODY | TRIGGER | VIEW
   | TYPE | TYPE BODY
   | JAVA SOURCE | JAVA CLASS } [schema.]name]
SQL> 

In the second case I have to query all_errors to find the details of the error, where as in the case I it immedietly returns the errors on show error command.

Please explain.
 

SQL> ED
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE "P"
  2  AS
  3  X NUMBER;
  4  BEGIN
  5  X =123;
  6* END;
SQL> /

Warning: Procedure created with compilation errors.

SQL> SHO ERR
No errors.

SQL> select * from user_errors;
P                              PROCEDURE             1          5          4
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   := . ( @ % ;
The symbol ":= was inserted before "=" to continue.


1 row selected.




SQL> CREATE OR REPLACE PROCEDURE P 
  2   AS
  3   X NUMBER;
  4   BEGIN
  5   X =123;
  6   END;
  7  /

Warning: Procedure created with compilation errors.

SQL> SHO ERR
Errors for PROCEDURE P:
5/4      PLS-00103: Encountered the symbol "=" when expecting one of the
         following:
         := . ( @ % ;
         The symbol ":= was inserted before "=" to continue.


Why is it that, when we use double quotes around procedure name, though the procedure has compilation errors, we cannot see them on show errors. This is in sqlplus.

If we go by the answer you gave for the above posting, then how come user_erros will contain the error information, in the first case. 

Hi

I tried executing the above code given by Tom but it does not show the name of the procedure in which the error occured .
I have put the code for raising the error in a stored proc but still it shows this entry in the error_table
:

21-JAN-04
66A1D550 1 anonymous block
ORA-00001: unique constraint (SCOTT.SYS_C003012) violated
-1

Ideally it should show the procedure name

Rgrds

BEGIN

for x in ( select ....) loop

begin
insert into t values ( ..... );
exception
when others then

dbms_output.putline(sqlcode||sqlerrm);
;
end;

END LOOP;

END;

Tom

in the above given illustration if a given iteration of the loop raises an error, will the dbms_output show it immedietly or after the entire loop completes ?

Thanks in advance

"dbms_output just writes to an array.
the array is retrieved by the client (sqlplus).
the array is printed by sqlplus.
"

What if the above block is part of a batch process. Will it print after whole batch completes?

Hi Tom,

It's wonderful to get the error in a table. My question is how to get the Function or Procedure's arguments name and values in the error.

Sometimes the procedure may be taking only few parameters and sometimes the number of parameters could be 100 or more, Is there any way to add those parameter name and values to the error description.

It's very Important to get those info, incase if you want to test the procedure or function stand alone, you need to know know what parameter values raised an error condition. I'll appreciate your input.

But every Developer doesn't have the privileges to go into production and do the tracing. We have the table that captures the errors and the user just send us the screen shot of the Form that causes an error and we can gointo database and find the error. In most of the cases it could be like No Data Found, rReturn many Rows, Dup Val on Index etc. Some times the errors are related to particular data and every field is not displayed on the form like Id's etc.

I'll appreciate if you can help me in finding the argument names and values of a calling procedure or function. This way we will alter the code and add these additional information (Parameters names and Values passed) in writing an error to the database.

Hi Tom,

I have a strange problem. i have a pacakge procedure it perform execute into a temporary table inside a loop.

after inserting 3-4 rows i get error

ORA-00001: unique constraint (vikas.GLIP_PK) violated

Now i would like to know the table name on which it is written. I have checked in user_constraints,all_constraints using

SELECT table_name from user_constraints where constrain_name like '%GL%';

but i could not find the table name for vikas.GLIP_PK constraint and also the table on which it is doing insert does not have any constraint.

Pl suggest.

Thanks

Vikas Sharma

Hi Tom,

Thanks for your help.

Actually it was my printing mistake i typed "vikas" in lwoer case but actually it was in upper case when i got the message. I checked the dba_constraints also but did not find. I was logged on by vikas user only.

Later i realized that this could be unique index and i checked it was a unique index on the table on which i was inserting the records.

In this case the message
ORA-00001: unique constraint (.) violated is bit mis-leading although it is a unique constraint violation but little more information would make life easy.

Hi tom
I notice that you use parameter names (e.g. :maxbytes etc)
instead of "?" in your program at many places (e.g. in Dbmsoutput class as shown below)

"declare " +
" l_line varchar2(255); " +
" l_done number; " +
" l_buffer long; " +
"begin " +
" loop " +
" exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
" dbms_output.get_line( l_line, l_done ); " +
" l_buffer := l_buffer || l_line || chr(10); " +
" end loop; " +
" :done := l_done; " +
" :buffer := l_buffer; " +
"end;" );

Although it is more readable, it seems that this is not
a supported method by Oracle drivers per the documentation...( from 10g JDBC doc) You can use bind by name using different proprietary methods from 10g onwards though..
See Binding by Name in the 10g JDBC doc...

Thanx!

--
"Binding by name is not supported when using the setXXX methods. Under certain
circumstances, previous versions of the Oracle JDBC drivers have allowed binding
statement variables by name when using the setXXX methods. In the following
statement, the named variable EmpId would be bound to the integer 314159.
PreparedStatement p = conn.prepareStatement
("SELECT name FROM emp WHERE id = :EmpId");
p.setInt(1, 314159);
This capability to bind by name using the setXXX methods is not part of the JDBC
specification, and Oracle does not support it. The JDBC drivers can throw a
SQLException or produce unexpected results. In 10g Release 1 (10.1) JDBC drivers,
bind by name is supported using the setXXXAtName methods. See "Interface
oracle.jdbc.OracleCallableStatement" on page 10-15 and "Interface
oracle.jdbc.OraclePreparedStatement" on page 10-14."

"i'm not a java programmer by any means."
trust me - even in jdbc you have clarified so many
doubts here!:) This was something I started using in
my code - before I happen to read up the doc and saw the caveat..

Thanx for a great site!

begin
dbms_mview.refresh('A');
dbms_mview.refresh('B');
dbms_mview.refresh('C');
end;

If there is an error after the execution of dbms_mview.refresh('A') (eg ORA- )
How can that be trapped ?

Thanks

bash-2.03$ sqlplus scott/andy |!grep -qiE 'ora|error|sp2'
bash: syntax error near unexpected token `|!'

pls explain the error here.

Thanks

Tom

one of the procedures is returning the below error message.
I've cut and past the exception block of the procedure.
This comes from a third party application.
Is this error a possible result from not having the RAISE;
at the end of the WHEN_OTHERS EXCEPTION block?

thanks

15:00:23> xxxxxxxx FAILURE: assigning xxxxxxx (3.6): 09/12/2004 03:00:23 pm
15:00:23> xxxxxxxx ERROR: ORA-06502: PL/SQL: numeric or value error
15:00:23> FAILURE: executing autonomous procedure for xxxxxx: 09/12/2004 03:00:23 pm
15:00:23> ERROR: User-Defined Exception



EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
COMMIT;
return_value := NULL;
return_mesg := NULL;
xxxxx_package.xxxxxxxx('XXXXXXXXX', 'XXXXX', NULL, 'N', return_value, return_mesg, TRUE);
IF (err_section IS NOT NULL)
THEN
xxx_logfile.m('FAILURE: '||err_section||': '||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS am'), i_logid);
END IF;
IF (return_value IS NOT NULL OR return_mesg IS NOT NULL)
THEN
xxx_logfile.m('RETURN INFO: '||return_value||' '||return_mesg, i_logid);
END IF;
xxx_logfile.m('ERROR: '||sqlerrm||CHR(10), i_logid);
END xxxxxxxxxxxxxxxxx;
/
show errors

Dear Sir,
Well and wish the same from you.I have a doubt and it is
"What is the difference between Validation handling and
Exception Handling?"
Please do reply.
Bye!

Dear Tom,
Validation means the thing we do in Front ends like Oracle
Forms using Validation Triggers like
i)When-validate-Item
ii)When-validate-Record
etc.
Validation in the sense we validate the value entered in the text field against our business requirement.
My Question is
"Do we Validate data to avoid exceptions being thrown
or Is Exception Handling a part of Data validation"
To be more simple
i) what is validation
ii)what is exception?Is it related to Validation?
Could you please clarify on this?
Please do reply.
Bye!

Hi Tom

All your procedure/function in book as well as on this site are great help and I am using it in every procedure, function, and package and it gives me heads up before developer could say any thing.

Some how I am not able to track procedure/function if its in a package. I only get the package name not the procedure/function name which is part of that package.

Is there any way to fetch package name along with its procedure/function name?

Thanks

Hi Tom

I have used your function 'whence' in every procedure and it’s a big help to trace source of error in big package which runs into over 1000 lines.

I get line number that refers to in exception block where error was was ‘raised’.

And if I don’t use exception block then it gives exact line number where error happened.

lkondal@OPTUNE> var v1 number
lkondal@OPTUNE> begin
2 select 1 into :v1
3 from dual
4 where 1=2;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 2



V1
----------

lkondal@OPTUNE> begin
2 select 1 into :v1
3 from dual
4 where 1=2;
5 exception
6 when no_data_found then
7 --some thing to capture line number here before raise
8 raise;
9 end;
10 /
begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8


V1
----------

lkondal@OPTUNE>

If I could capture this 'ORA-06512: at line 2' before 'raise' then it’s just the line where actual error happened.

We have most packages running over 1000 lines and call_stack gives almost the last line number.

dbms_utility.format_call_stack do not have that line number and the last line in call_stack is ‘2 anonymous block’.

Without exception block it is some where and I cant figure it out where to get it.

I do get procedure name from the package by looking next 'END proc_name;' after the error line number but if I could get the first error - where error actually occurred then it will be great help to trace back and fix it.

Thanks.

Hi Tom,

I have used when others then without raise and I did not have any problems so far. I have seen in this forums you mentioned that when others then without raise is a bug.
So I'm not clear about this, culd you please explain me with an example why is it necessary to use raise in when others then.

This is a really great site thanks for all your effort.

Sarya

Hi Tom,

Thanks for your reply.

We have used when others then with out raise to log oracle internal exceptions to a database table. We have used a stand alone procedure to log the error to the error_table. This application is kind of ETL tool which does loading and massaging data. We used pl/sql to develop this application and it is just running fine.

Would you please demonstrate this with an example of a procedure with Raise and with out Raise so that I can understand it better.

Thanks
Sarya

Hi Tom,

Still I'm confused. Here is the small example I did even then I'm not able to understand the point. Please let me know if I'm doing some thing wrong.

Here is a small function in a package.

FUNCTION chk_date(p_input_date IN VARCHAR2) RETURN DATE
IS
p_out_date DATE;
--date_type_mismatch exception;
--PRAGMA EXCEPTION_INIT (date_type_mismatch,-20000);

BEGIN
CASE SUBSTR(p_input_date,3,1)
WHEN '/' THEN
p_out_date := TO_DATE(p_input_date, 'MM/DD/RRRR');

WHEN '-' THEN
IF SUBSTR(UPPER(p_input_date),4,3) IN ('JAN','FEB','MAR','APR','MAY','JUN',
'JUL','AUG','SEP','OCT','NOV','DEC') THEN
p_out_date := TO_DATE(p_input_date, 'DD-MON-RRRR');

ELSE
p_out_date := TO_DATE(p_input_date,'MM-DD-RRRR');

END IF;
ELSE
p_out_date := TO_DATE(p_input_date,'MMDDRRRR');

END CASE;
RETURN p_out_date;
EXCEPTION
--WHEN date_type_mismatch THEN
--RAISE_APPLICATION_ERROR (-20001, 'Date type mismatch');

WHEN OTHERS THEN
RETURN p_out_date;
log_msg(P_MODULE => 'chk_dt function'
,P_MSG => 'Error in Date Validation'||sqlerrm);
--raise;
END;

I'm invoking this function by following anonymous block.

DECLARE
"Return Value" DATE;
BEGIN

"Return Value" := elease_util.chk_date('01-xeb-02');

dbms_output.put_line(SubStr('"Return Value" = '||TO_CHAR("Return Value"), 1, 255));

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
--RAISE;
END;

I have seen the output with Raise and with out Raise statement and there is no difference. I'm alwasy able to see the error in my log table.

Here's the output.

ID MODULE MSG CREATE_DT

111226 chk_dt function Error in Date ValidationORA-01858: a non-numeric character was found where a numeric was expected 1/18/2005 12:16:26 PM


PROCEDURE LOG_MSG (p_module IN VARCHAR2, p_msg IN VARCHAR2)
IS
BEGIN
INSERT INTO LOG
VALUES (log_seq.NEXTVAL, p_module, p_msg, SYSDATE);

COMMIT;
END log_msg;

Only thing I'm assuming is if suppose I get an error while trying to insert into the log table for some reason in that case If I have raise statement in when others then at my function level would that help in propogating that error?

Please let me know what where i'm doing wrong or demonstrate me with some example.

As alwasy thanks for all your help.

Sarya

so, say I call that procedure.

Me, Tom.  I call that procedure.  


From my code.  Code that won't exist until tomorrow.


How do I -- Tom -- know that the date check failed?


that is some messed up error handling there

EXCEPTION
    --WHEN date_type_mismatch THEN
    --RAISE_APPLICATION_ERROR (-20001, 'Date type mismatch');

    WHEN OTHERS THEN
    RETURN p_out_date;
    log_msg(P_MODULE    => 'chk_dt function'
                          ,P_MSG => 'Error in Date Validation'||sqlerrm);
    --raise;
END;


when other then return NULL;

is functionally equivalent.  the invoker, your anonymous block -- it has NO CLUE whatsoever that "it failed"

when I run your code, as is, i get:

ops$tkyte@ORA9IR2> decLARE
  2  "Return Value" DATE;
  3  BEGIN
  4
  5    "Return Value" := chk_date('01-xeb-02');
  6
  7  dbms_output.put_line(SubStr('"Return Value" = '||TO_CHAR("Return Value"), 1, 255));
  8
  9  EXCEPTION
 10  WHEN OTHERS THEN
 11    dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
 12  --RAISE;
 13  END;
 14  /
"Return Value" =
 
PL/SQL procedure successfully completed.


that is, I get no clues that "it failed" 

Hi Tom,

Even I turned on raise statement in my invoker block still i'm getting the same thing even though the function raised an exception. I understood the point but not able to demonstrate with this code.

Thanks
Sarya

Hi Tom,

I got it, but this is only useful when I execute any procedure from sqlplus or some other tool right? B'coz as I'm logging my error in a table so If I check in my log table for successful execution I would know immediately.

Thanks for your effort in clarifying this to me.

sarya

Hi Tom,

Is it possible to get sqlcode using SQLERRM?
LIKE
SQL > select sqlcode from ... where sqlerrm like 'cursor %'

Please do reply.

Tom,
In your original reply to this post, you have use Autonomous transactions to Log error. I understand it is to 'detach' transaction control from the program causing the error (so that it can either rollback or commit). This should leave the error to be committed or rolled back using procedure error_log.

But

1) Is that really the primary and only reason?

2) Assuming there could be a 1000 programs calling error_log at the SAME TIME (any given time) and creating a 1000 'extra' transactions,? Is there a performance impact due to these 'mutilated' transactions? (And thus a disadvantage of Autonomous transactions).

3) How does Auto transactions compare with DBMS_PIPE to do the same thing. Assume that error_log is coded merely to compose and send a piped message which is picked by a deamon process which commits to the error_log table?

Who wins the DBMS_PIPE vs AUTO TRANSACTION battle for error logging and WHY?

Thanks

Kumar

Tom,

1)Are you suggesting that if the SAME pipe were to be used by several processes at the same time, it is SERIALIZED in some way, ie, each transaction awaits its turn to FILL the pipe with info?

2) But if several processes were to write data using AUTO transacs can they table structure be cleverly built up so that users don't have to wait (not SERIALIZED)?

Say can we set something like INITRANS or MAXTRANS or some table structure combination (PCTFREE/PCTUSED) so that Data Blocks are always available for several users for write (Insert) at the same time?

b) Answers for points 1) and 2) would again favour AUTO transacts over DBMS_PIPE?

3) The other key question is I'd been told if the Database goes down, it takes with it all info a PIPE has with it forever. But with AUTO transacts, because the call to insert is 'immediate' does that compare better with the Pipe. Theoretically more info could have been written using AUTO transacts (vs DBMS_PIPE)?

The whence function gives the line number of the current exception. 
I am facing a problem with a procedure where a single exception block has been written for the entire code instead of encapsulating each DML statement in a begin..end block.
Is it possible to get the line number of the statement that raised an exception. To state an anology, suppose we have a pl/sql block which doesnt have an exception handler. If an exception is raised, we get a synopsis of the error, like which line raised it, sqlcode and sqlerrm. basically, i want the text after the ora-6512 heading...

an example :- 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> declare
  2    i integer ;
  3  begin
  4     i := 1 ;
  5     i := 2 ;
  6     i := 'A' ;
  7     i := 3 ;
  8     i := 4 ;      
  9  end ;
 10  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6

I dont have rights to modify the existing code and hence this simple instrumentation will make my day. 
 

Hi Tom,
I have created a table
err_msg(errnum number,err_descr varchar2(256))
When I insert into it
using

begin
for i in 1..20000 loop
insert into err_msg values(i,sqlerrm(-i));
end loop;
end;

I am getting error during insert.
Is there any way to correct this so that
error numbers and error descriptions are inserted??
Please do reply.
Bye!

Hello Tom,
Oracle error messages do not give out table name when exceptions raised in the PL/SQL code. So in order to print out the table name, i have to wrap BEGIN EXCEPTION END around each SQL statement, but this makes code look so messy. Is there a better way to get table name in the exception handler? How about multiple tables are involved in one SQL statement?

i.e.

BEGIN
UPDATE tab1 ...;

INSERT tab2 ...;

TRUNCATE tab3 ...;

EXCEPTION
WHEN OTHERS THEN
-- how to get table_name ???
raise_application_error(-20999, table_name||':'||SQLERRM)
END;

Hi Tom,

Currently we are using Oracle 9i Database.
Now, we are in testing phase before migrating to 10g Database.
Version 10.1.0.4.0. We are using daemon procedure written in "C" procedure.
and accessing thro' DBMS_PIPE. This is working fine for sending and receiving messages from C programs.
Code is passing unix command thro' dbms_pipe. (i.e. dbms_pipe.send_message).
and it returns ouput from C by dbms_pipe.receive_message.

We have created similar environment for 10g same as what we are having in production database. But dbms_pipe.receive_message it fails and returns the output as "1". i.e. status as 1 instead of 0 what it return in production database.

This was working fine even for 10.0.3.0 but fails in 10.1.0.4.0.
I wanted to know what could be the reason for above stated problem?
Is that DBMS_PIPE is not holding the values and when trying to create a PIPE it fails and returning the status 1?
or DBA's has done something wrong with database?
Is that possible or steps to attack this problem?

Hope this question clarify everything if you require any information you can mail me on biping@hexaware.com or contact on Cell +919820488700.

And also this Question is not like "my car was running till yestederday and suddenly stops?"


Warm Regards,
Bipin G.

How do you return this:
-----------------------------------------------------------
ERROR at line 6:
ORA-06550: line 6, column 33:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored
-----------------------------------------------------------

instead of just this:
-----------------------------------------------------------
ORA-00942: table or view does not exist
-----------------------------------------------------------

from a program like this:
-----------------------------------------------------------
declare
g_error_message long:= NULL;
l_val varchar2(100);
begin
execute immediate('select ''hello'' into l_val from nodual');
--select 'hello' into l_val from nodual;
exception
when others
then
g_error_message := sqlerrm;
dbms_output.put_line(sqlerrm);
end;
/
-----------------------------------------------------------

PS: I used this:
-----------------------------------------------------------
declare
g_error_message long:= NULL;
l_val varchar2(100);
begin
--execute immediate('select ''hello'' into l_val from nodual');
select 'hello' into l_val from nodual;
--exception
--when others
--then
-- g_error_message := sqlerrm;
-- dbms_output.put_line(sqlerrm);
end;
/
-----------------------------------------------------------
to generate the second error stack.

Thanks a bunch.

you do not in 9i and before.

In 10g and above, you can.

ops$tkyte@ORA10GR2> create or replace procedure x
  2  as
  3          n number;
  4  begin
  5          execute immediate 'select count(*) from abc' into n;
  6  end;
  7  /

Procedure created.

ops$tkyte@ORA10GR2> declare
  2          n       number;
  3  begin
  4          x;
  5  exception
  6          when others
  7                  then
  8                     dbms_output.put_line( dbms_utility.FORMAT_ERROR_BACKTRACE );
  9                     dbms_output.put_line( sqlerrm );
 10  end;
 11  /
ORA-06512: at "OPS$TKYTE.X", line 5
ORA-06512: at line 4

ORA-00942: table or view does not exist

PL/SQL procedure successfully completed.


<b>but remember, a when others that is not followed by RAISE is almost always a bug in your developed code.  make sure to RE-RAISE THE EXCEPTION</b>

 

I am just trying to understand why I can't get sqlerrm to return the line number of the failure.

I read that is part of the return information.
Is that true?

I must be misunderstanding something.

Thank you for your help in advance.

( sysdate, whence, l_msg, l_code );

should probably be:

( sysdate, l_whence, l_msg, l_code );

The way you have it does work, but you are needlessly calling the whence proc twice.

TEST @ adm1 SQL>create table t_table (col1 number);

Tabelle wurde erstellt.

Abgelaufen: 00:00:00.02
TEST @ adm1 SQL>create procedure p_table_sub as
  2  begin
  3    insert into t_table (col1) values (1/0);
  4  end;
  5  /

Prozedur wurde erstellt.

Abgelaufen: 00:00:00.04
TEST @ adm1 SQL>create procedure p_table_main as
  2  begin
  3    insert into t_table (col1) values (1);
  4    p_table_sub;
  5  end;
  6  /

Prozedur wurde erstellt.

Abgelaufen: 00:00:00.04
TEST @ adm1 SQL>select * from v$session, v$transaction where taddr = addr;

Es wurden keine Zeilen ausgewählt

Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>exec p_table_main;
BEGIN p_table_main; END;

*
FEHLER in Zeile 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P_TABLE_SUB", line 3
ORA-06512: at "TEST.P_TABLE_MAIN", line 4
ORA-06512: at line 1


Abgelaufen: 00:00:00.03
TEST @ adm1 SQL>select * from v$session, v$transaction where taddr = addr;

Es wurden keine Zeilen ausgewählt

Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>select * from t_table;

Es wurden keine Zeilen ausgewählt

Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>commit;

Transaktion mit COMMIT abgeschlossen.

Abgelaufen: 00:00:00.00
TEST @ adm1 SQL>select * from t_table;

Es wurden keine Zeilen ausgewählt

Abgelaufen: 00:00:00.00



it seems that the server does an implicit rollback of the whole transaction in case of an exception. BUT...



TEST @ adm1 SQL>select * from t_table;

Es wurden keine Zeilen ausgewählt

Abgelaufen: 00:00:00.00
TEST @ adm1 SQL>select count(*) from v$session, v$transaction where taddr = addr;

  COUNT(*)
----------
         0

Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>insert into t_table (col1) values (0);

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.00
TEST @ adm1 SQL>select count(*) from v$session, v$transaction where taddr = addr;

  COUNT(*)
----------
         1

Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>exec p_table_main;
BEGIN p_table_main; END;

*
FEHLER in Zeile 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P_TABLE_SUB", line 3
ORA-06512: at "TEST.P_TABLE_MAIN", line 4
ORA-06512: at line 1


Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>select count(*) from v$session, v$transaction where taddr = addr;

  COUNT(*)
----------
         1

Abgelaufen: 00:00:00.01
TEST @ adm1 SQL>select * from t_table;

      COL1
----------
         0

Abgelaufen: 00:00:00.00


shows that only the PL/SQL part is undone, not the whole transaction! how is this magically "border" called? are pl/sql procedures handled as atomically statements? 

Respected Sir,

I have one query it seems very silly but could not answer myself. Every Oracle error has a negative number. Only “NO_DATA_FOUND” has different value for Oracle Error (ORA-01403) and SQLCODE (+100) and other errors, obviously predefined, have same value (absolute) for Oracle Error and SQLCODE value.
So my query is - what’s the objective of SQLCODE value in real life application or why does Oracle maintain this SQLCODE value when we never use this SQLCODE value?

Sir,

I do believe I could not get my message across to you properly. So I request you earnestly please have a look at the chapter 7 (Handling PL/SQL Errors) of Oracle 9i PL/SQL Guide. Under the topic “Predefined PL/SQL Exceptions” you will find one tabular form on “Exception”, “Oracle Error” and “SQLCODE Value”. My Question was why SQLCODE Value is required when Oracle Error gives us the SAME code value except “NO_DATA_FOUND”. Now am I able to clear you Sir?
You please take a look at the following code and its output.

declare

V_EMPID ARIN_ANALYTIC.EMPID%TYPE;

begin

SELECT EMPID
INTO V_EMPID
FROM ARIN_ANALYTIC
WHERE EMPID = 20;

exception

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO DATA - > SQLCODE = '||SQLCODE);
DBMS_OUTPUT.PUT_LINE ('ERR-MSG = '||SQLERRM);

BEGIN

SELECT EMPID
INTO V_EMPID
FROM ARIN_ANALYTIC
WHERE EMPID BETWEEN 10 AND 15;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('MANY ROWS - > SQLCODE = '||SQLCODE) ;
DBMS_OUTPUT.PUT_LINE ('ERR-MSG = '||SQLERRM);
END;

end;
/

Output
----------

NO DATA - > SQLCODE = 100
ERR-MSG = ORA-01403: no data found

MANY ROWS - > SQLCODE = -1422
ERR-MSG = ORA-01422: exact fetch returns more than requested number of rows

ORA-01403: no data found

PL/SQL procedure successfully completed.

Sir, could you kindly tell me why I am getting twice ORA-01403 when I have caught it beforehand.

Respected Sir,

You please don’t get me wrong. I wanted to tell you that “SQLCODE” should be same as “Oracle Error”. Suppose we get one “Oracle Error” like “ORA-06533”. That means “SQLCODE” should be “-6533” but in “NO_DATA_FOUND” we get DIFFERENT value – Oracle Error = ORA-01403 and SQLCODE = +100. So I asked you that question.
Now is it clear?
I know if the error has no predefined name, we can catch them with sqlcode or put their name with “pragma exception_init” accompanied by sqlcode. Anyway I am learner, so I don’t wish to go against the current.

Now you please tell me from my code pasted to my earlier review, why I am getting twice ORA-01403 when I have caught it beforehand.

Tom,

Arindam just wants to know why his code example is returning NO_DATA_FOUND twice.... very puzzling?

Thanks,

Robert.

well, if that is all they wanted to know, they certainly obscured that with lots of other stuff.

in this case, it is the nested exception in his release of the database:

ops$tkyte%ORA10GR2> declare
  2    V_EMPID    ARIN_ANALYTIC.EMPID%TYPE;
  3  begin
  4      SELECT EMPID INTO    V_EMPID
  5        FROM ARIN_ANALYTIC
  6       WHERE EMPID BETWEEN 10 AND 15;
  7  EXCEPTION
  8      WHEN TOO_MANY_ROWS THEN
  9          DBMS_OUTPUT.PUT_LINE ('MANY ROWS - > SQLCODE = '||SQLCODE) ;
 10          DBMS_OUTPUT.PUT_LINE ('ERR-MSG = "'||SQLERRM || '"' );
 11          begin
 12              SELECT EMPID INTO    V_EMPID
 13                FROM ARIN_ANALYTIC
 14               WHERE EMPID BETWEEN 10 AND 15;
 15          EXCEPTION
 16              WHEN TOO_MANY_ROWS THEN
 17                  DBMS_OUTPUT.PUT_LINE ('MANY ROWS - > SQLCODE = '||SQLCODE) ;
 18                  DBMS_OUTPUT.PUT_LINE ('ERR-MSG = "'||SQLERRM || '"' );
 19          end;
 20  end;
 21  /
MANY ROWS - > SQLCODE = -1422
ERR-MSG = "ORA-01422: exact fetch returns more than requested number of rows"
MANY ROWS - > SQLCODE = -1422
ERR-MSG = "ORA-01422: exact fetch returns more than requested number of
rows
ORA-01422: exact fetch returns more than requested number of rows"

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> spool of

it is not that no data found was raised twice, it was just concatenated on (see the "" I used to delineate...) 

Hi Tom
whenever I try to access the "sql query" topic page on your web site my mozilla browser quits...there is some kind of problem with that oage of your's I think. It is happening on two machines...Please tell me the sequence in which oracle runs the submitted query..like it first runs the FROM CLAUSE AND so on..

Thanks

Hi Arindam,
Please be specific and to the point. Do NOT write an essay where a Short hand would suffice.
Tom actually got lost in all those words and missed out on your question.
Let us be Short and Sweet.
I hope everyone agrees :)

"what is the "sql query" topic page?"
Personally, I wish that anyone who posts a question to AskTom with a subject like 'SQL query' would get instantly banned for life. They must be able to give a more precise subject than that.

Hello Tom,
I am referring the error_table you have created at the start of this Link.

-->(First Reply you gave at the start of this link)
ops$tkyte@ORA8I.WORLD> create table error_table
2 ( timestamp date,
3 whence varchar2(1024),
4 msg varchar2(1024),
5 code number
6 )
7 /
--<--

I am also creating a similar table in my application.

Can you please adivce as whether I use the following size and datatypes of the columns that would store the SQLCODE and SQLERRM values:-

Column that stores SQLCODE : NUMBER
Column that stores SQLERRM : VARCHAR2(1024)

Will any truncation or datatype mismatch error would occur when I am inserting the SQLCODE and SQLERRM into the error_table.

Please advice
Thanks and Best Regards
-Hemal Deshmukh

Thanks a lot, Tom.
Best Regards
-Hemal

Hello Tom,
I have writtern the following procedure to log the error (Shown Below).
I will be calling it in the "WHEN OTHERS THEN exception handler" of the packaged procedures.

It is a Java Based Multiuser Application.
I am worried about the concurrent users scenarion.

If concurrent users call this procedure at the same time then will the value of the SQLCODE and SQLERRM get properly stored for each user ?

My friends are suggesting me to pass "SQLCODE" and "SQLERRM" as parameters to this procedure (log_error procedure) instead of writing SQLCODE and SQLERRM in the procedure.Because of this we are sure that a proper error information will get inserted into the error_log table for each user concurrently calling this procedure.

Please advice me shall I change the coding and pass SQLCODE and SQLERRM as parameters ?

--->

CREATE OR REPLACE PACKAGE ERROR_LOG_PKG
AUTHID CURRENT_USER
IS
PROCEDURE log_error (p_i_vr_package_name IN error_log.package_name%TYPE,
p_i_vr_user_name IN error_log.user_name%TYPE,
p_i_vr_user_id IN error_log.user_id%TYPE);

END ERROR_LOG_PKG;
/

PROCEDURE log_error (p_i_vr_package_name IN error_log.package_name%TYPE,
p_i_vr_user_name IN error_log.user_name%TYPE,
p_i_vr_user_id IN error_log.user_id%TYPE) AS

PRAGMA AUTONOMOUS_TRANSACTION;

lv_nm_error_code_info error_log.error_code_info%TYPE;
lv_vr_error_message_info error_log.error_message_info%TYPE;

BEGIN
lv_nm_error_code_info := SQLCODE;
lv_vr_error_message_info := SUBSTR(SQLERRM,1,2000);

INSERT INTO error_log( package_name,
user_name,
user_id,
run_date,
error_code_info,
error_message_info)
VALUES( p_i_vr_package_name,
p_i_vr_user_name,
p_i_vr_user_id,
SYSDATE,
lv_nm_error_code_info,
lv_vr_error_message_info);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;

END log_error;
-----------------------------------------------------------

END ERROR_LOG_PKG;
/
---<--
Please help me tom.
Thanks and Best Regards
-Hemal

Hi Tom,

when I see it right the last block of code

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;

isn't useless at all. The procedure has the pragma
AUTONOMOUS_TRANSACTION in it and I have read in the
oracle documentation that in such a procedure a
transaction must allways be closed, either with COMMIT
or with ROLLBACK. And if the INSERT fails the transaction
remains open, if you don't specify ROLLBACK in the exception
handler.

Please correct me, if I'm wrong.

Greetings,

Niels

Mr Kyte,

I am looking for information regarding the use of DBMS_TRACE (to assist my PL/SQL instrumentation).

I have tried to search your site for this, but all I can seem to find so far are your replies saying "search this site for DBMS_TRACE".

Do you have a link to the article you were originally referring to?

Thanks in advance,
Paul

Tom,

This was my first link when searching today..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1932043078402

and does cover the topic well. I must have been having an "off" day yesterday.

Thanks for your follow-up.

I'm glad you have the patience to respond to all of our questions without wanting to scream at us.

You make the Oracle world a better place.

After seeing the stack stuff you have mentioned in oracle got existed and tried to achive some thing like this.

create or replace package p
is
 procedure p1;
 procedure p2;
 procedure p3;
 procedure p4;
 procedure p5;
end p;
/

create or replace package body p
is
 procedure p1
 is
 begin
  raise_application_error(-20001, 'My Error');
 end;
 
 procedure p2
 is
 begin
  p1;
 end;

 procedure p3
 is
 begin
  p2;
 end;

 procedure p4
 is
 begin
   p3;
 end;
 
 procedure p5
 is
 begin
  p4;
 exception
  when others then
   dbms_output.put_line(dbms_utility.format_call_stack);
   raise;
 end;
end p;
/

and i executed it 

SQL>  begin
  2   p.p5;
  3  end;
  4  /
----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
41DF6A68        33  package body SYSADM.P
3B94B00C         2  anonymous
block

 begin
*
ERROR at line 1:
ORA-20001: My Error
ORA-06512: at "SYSADM.P", line 34
ORA-06512: at line 2
Can this package can be modified to get output like this when an error occurs.

but iam looking for some output like this.

Line 16: Error on PROCEDURE P1 of PACKAGE P 
Line 22: Error on PROCEDURE P2 of PACKAGE P Calling PROCEDURE P1
Line 28: Error on PROCEDURE P3 of PACKAGE P Calling PROCEDURE P2
Line 34: Error on PROCEDURE P4 of PACKAGE P Calling PROCEDURE P3
Line 40: Error on PROCEDURE P5 of PACKAGE P Calling PROCEDURE P4
Line 2 : Error on Anonymous Block Call

basically what i want is to get the procedure information within the package.

is it possible.

 27      procedure p5
 28      is
 29      begin
 30          p4;
 31      exception
 32          when others then
 33              dbms_output.put_line(dbms_utility.format_error_backtrace);
 34              raise;
 35      end;
 36  end p;
 37  /

Package body created.

ops$tkyte@ASKTOMDB>
ops$tkyte@ASKTOMDB> exec p.p5
ORA-06512: at "OPS$TKYTE.P", line 6
ORA-06512: at "OPS$TKYTE.P", line 12
ORA-06512: at "OPS$TKYTE.P", line 18
ORA-06512: at "OPS$TKYTE.P", line 24
ORA-06512: at "OPS$TKYTE.P", line 30

BEGIN p.p5; END;

*
ERROR at line 1:
ORA-20001: My Error
ORA-06512: at "OPS$TKYTE.P", line 34
ORA-06512: at line 1

Hi Tom,

When I explicitly handle the exception, why I am also getting oracle error?
DECLARE
V_first_name varchar2(10) :='XXXX';
BEGIN
SELECT first_name INTO v_first_name
FROM name
WHERE first_name ='XXXX';
EXCEPTION
WHEN TOO_MANY_ROWS
--WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20001, 'there is a duplicate value first_name ="XXXX" in the name');
end;
/
DECLARE
*
ERROR at line 1:
ORA-20001: there is a duplicate value first_name ="XXXX" in the name
ORA-06512: at line 11

is there a best way of catching and handling exceptions.
for example i have different select statements from 2..n tables and i want to handle 2..n cases like below.

I usually do this. Is this ok or is it better to write the second option. this applies to programs with 5 different selects sometimes.

I want hte program to stop raise the exception and stop execution if a record does not exist.

Option 1

select count(*) from table1 where cust_id=p_cust and rownum=1;
If (l_cnt = 0) then raise exc_no_cust;

select count(*) from table2 where order_id=p_order and rownum=1;
If (l_cnt = 0) then raise exc_no_order;

exception
when exc_no_cust then
dbms_ouput.put_line('no customer');

whenexc_no_order then
dbms_output.put_line('no order');


Option 2

begin
my_var := 'table 1 has no data';
select * from table 1 where cust_id = p_cust;

my_var := 'table 2 has no data';
select * from table2 where order_id=p_order;

expcetion
when no_data_found
dbms_output.put_line(my_var

begin
    ...
    begin
         select * into some_record from table1 where cust_id = p_cust;
    exception
         when no_data_found
         then
              log_error( 'table 1, no data for ' || p_cust );<b>
              RAISE;</b>
   end;
   ......
    begin
         select * into some_other_record from table1 where cust_id = p_cust;
    exception
         when no_data_found
         then
              log_error( 'table 2, no data for ' || p_cust );<b>
              RAISE;</b>
   end;
   ....
end;

Dear Tom,

Firstly, let me wish you,albeit belated, a very Happy and Prosperous New Year 2009. I hope closing this priceless blog is not on your resolution list for 2009 :-) Please continue with this great job.

I have one query about using ROLLBACK TCL command within a PL/SQL exception block, especially when we're re-raising the exception. Is this mandatory? or just a good programming practice to better assure the things.

Let me give an example to elucidate my point:-

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

SQL> 
SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------------
ORCL

SQL> create table t(
  2   x int primary key
  3  )
  4  /

Table created.

SQL> create table s(
  2   x int
  3  );

Table created.

SQL> 
SQL> create or replace procedure test_proc
  2  as
  3  begin
  4       
  5       insert into s values ( 1 );
  6       insert into t values ( 1 );
  7       commit;
  8       exception when others then 
  9        -- place-holder for exception handling "autonomous-transaction" code
 10       raise; -- no explicit "rollback" specified as "raise" would do the job on it's behalf.
 11  
 12  end;
 13  /

Procedure created.

SQL> exec test_proc;

PL/SQL procedure successfully completed.

SQL> select * from t;

         X
----------
         1

SQL> select * from s;

         X
----------
         1
SQL> exec test_proc;
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C007705) violated
ORA-06512: at "SCOTT.TEST_PROC", line 10
ORA-06512: at line 1


SQL> select * from t;

         X
----------
         1

SQL> select * from s;

         X
----------
         1


SQL> commit;

Commit complete.

SQL> select * from s;

         X
----------
         1

SQL>

Thus, even an explicit "commit" would be no help here which confirms that "raise" (line#10 above) had done it's job as expected.

Now, let me try the same example by preceding "raise" with an explicit "rollback" statement.

SQL> create or replace procedure test_proc
  2  as
  3  begin
  4       
  5       insert into s values ( 1 );
  6       insert into t values ( 1 );
  7       commit;
  8       exception when others then 
  9        -- place-holder for exception handling "autonomous transaction" code
 10       rollback; -- to explicitly "rollback" the stuff if fails
 11       raise; 
 12  
 13  end;
 14  /

Procedure created.

SQL> truncate table s;

Table truncated.

SQL> truncate table t;

Table truncated.

SQL> exec test_proc;

PL/SQL procedure successfully completed.

SQL> select * from s;

         X
----------
         1

SQL> select * from t;

         X
----------
         1

SQL> exec test_proc;
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C007705) violated
ORA-06512: at "SCOTT.TEST_PROC", line 11
ORA-06512: at line 1

SQL> commit;

Commit complete.

SQL> select * from s;

         X
----------
         1

Eagerly looking forward to your valuable perspective on this.

Best Regards,
Vijay

procedure_1;
procedure_2; -- your code is procedure_2

procedure_1;
if (we have any error)
   die();

procedure_2;
if ( we got a deadlock )
{
      procedure_2; -- retry!
      if (we have any error )
            die();
}

ops$tkyte@ORA10G> create table t2 ( cnt int );
Table created.
 
ops$tkyte@ORA10G> insert into t2 values ( 0 );
1 row created.

ops$tkyte@ORA10G> create table t ( x int check ( x>0 ) );
Table created.

ops$tkyte@ORA10G> create trigger t_trigger
  2  before insert or delete on t for each row
  3  begin
  4     if ( inserting ) then
  5          update t2 set cnt = cnt +1;
  6     else
  7          update t2 set cnt = cnt -1;
  8     end if;
  9     dbms_output.put_line( 'I fired and updated '  ||
 10                                     sql%rowcount || ' rows' );
 11  end;
 12  /
Trigger created.


ops$tkyte@ORA10G> create or replace procedure p
  2  as
  3  begin
  4          insert into t values ( 1 );
  5          insert into t values (-1 );
  6  end;
  7  /
Procedure created.
 
ops$tkyte@ORA10G> select * from t;
no rows selected
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         0

ops$tkyte@ORA10G> begin
  2          p;
  3  end;
  4  /
I fired and updated 1 rows
I fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C009598) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 2
  
ops$tkyte@ORA10G> select * from t;
no rows selected
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         0

ops$tkyte@ORA10G> begin
  2      p;
  3  exception
  4      when others then null;
  5  end;
  6  /
I fired and updated 1 rows
I fired and updated 1 rows
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> select * from t;
 
         X
----------
         1
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         1

ops$tkyte@ORA10G> begin
  2      savepoint sp;
  3      p;
  4  exception
  5      when others then
  6          rollback to sp;
  7  end;
  8  /
I fired and updated 1 rows
I fired and updated 1 rows
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> select * from t;
 
no rows selected
 
ops$tkyte@ORA10G> select * from t2;
 
       CNT
----------
         0

Tom:

The client is a browser. The whole application is PL/SQL stored procedures.

Based on your code below:
- the program will raise an oracle error if cusotmer id is not valid and then continue to the next begin.

I simply want to stop program execution and print to the user a friendly message saying that "Customer ID he provided is not valid code".

I do not need to log it in. If the customer id is valid then i want to go to the next validation subroutine and do same thing.




begin
...
begin
select * into some_record from table1 where cust_id = p_cust;
exception
when no_data_found
then
log_error( 'table 1, no data for ' || p_cust );
RAISE;
end;
......
begin
select * into some_other_record from table1 where cust_id = p_cust;
exception
when no_data_found
then
log_error( 'table 2, no data for ' || p_cust );
RAISE;
end;
....
end;

begin
    ...
    begin
         select * into some_record from table1 where cust_id = p_cust;
    exception
         when no_data_found
         then
              htp.p( 'table 1, no data for ' || p_cust );
              RAISE;
   end;
   ......
    begin
         select * into some_other_record from table1 where cust_id = p_cust;
    exception
         when no_data_found
         then
              htp.p( 'table 2, no data for ' || p_cust );
              RAISE;
   end;
   ....

exception
when no_data_found 
then
   htp.p( 'sorry about that, try again' );
end;

Tom:

That is excellent. No COUNT statements and IF conditions required. this is efficient programming.

I think the RAISE in the first exception handler will send control
to the main NO_DATA_FOUND exception handler.

If table 1 does not have customer code, the program will print this


table 1, no data for Customer 1000
sorry about that, try again


If table 2 does not an order number, the program will print this


table 2, no data for Order 500
sorry about that, try again

Hello Tom,

"In page 473 of your book (almost half way done), you share one of your experiences where only WHEN
OTHERS was used in the exceptions block of a deeply nested stored procedure. You mentioned that it
was used in such a way that it "catches and ignores all exceptions" and that it "should never be
used.."

I have your books:
1. Effective Oracle by design
2. Expert Oracle 10g

But I could not find that information. Please tell me which book he is talking about?

Your books has given me a real insight of Oracle. I don't have proper words to say thanks for sharing your knowledge.

Thanks,
Arvind

tkyte@TKYTE816> create table t ( x int primary key );
Table created.

tkyte@TKYTE816> alter session set sql_trace=true;
Session altered.

tkyte@TKYTE816> select * from;
select * from
            *
ERROR at line 1:
ORA-00903: invalid table name


tkyte@TKYTE816> insert into t values ( 1 );
1 row created.

tkyte@TKYTE816> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.SYS_C002207) violated

tkyte@TKYTE816> exit

=====================
PARSE ERROR #3:len=15 dep=0 uid=69 oct=3 lid=69 tim=7160573 err=903
select * from
=====================
PARSING IN CURSOR #3 len=27 dep=0 uid=69 oct=2 lid=69 tim=7161010 hv=1601248092
  ad='32306c0'
insert into t values ( 1 )
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7161010
=====================
¿
EXEC #3:c=1,e=9,p=0,cr=9,cu=7,mis=0,r=0,dep=0,og=4,tim=7161019
ERROR #3:err=1 tim=7161019

Hello Tom,

Now I understand "when others" and use of trace file in debugging . Thanks a Lot.

As far as my understanding,Expert Oracle database architecture has replaced Expert one on one. Have you relocated contents related to PL/SQL to some other book? If so please tell me the name of that book. I am sure it will redefine my pl/sql concepts.

Thanks,

Arvind

Any new books in the near future?

Hi Tom,
thanks for wonderful insights..... If when others should never be used then why oracle has provided this exception ?

I agree it gets misused by programmers but then in first place if its not there in oracle then no one can misuse it ?

Where when others exception has real use ?

atul

Hi
This code and comments are very useful. But i have one question in this. if concurrent users are accessing the application, which is inserting the Error-throwing procedure thru format_call_stack, then how to get the procedure_name from format_call_stack.
I mean , when concurrent users throwing different execptions in an application, can we track the error-throwing procedure using DBMS_Utility.format_call_stack?

In your example, log_error procedure, is the exception block with when others necessary? The PL/SQL user guide says you must either commit or rollback in an autonomous procedure. If you are unable to insert a record for error logging because of no more space in a tablespace, wouldn't Oracle automatically rollback and report the error back to the client calling the log_error procedure?

Thanks in advance for your reply.

ops$tkyte@ORA8I.WORLD> create or replace procedure log_error
2 as
3 pragma autonomous_transaction;
4
5 l_whence varchar2(1024);
6 l_msg varchar2(1020) default sqlerrm;
7 l_code number default sqlcode;
8 begin
9 l_whence := whence;
10 insert into error_table
11 ( timestamp, whence, msg, code )
12 values
13 ( sysdate, whence, l_msg, l_code );
14
15 commit;
16 exception
17 when others then
18 rollback;
19 raise;
20 end;
21 /

Is there a way in 10g , oralce dataprovider for .net/c#, to find out what table or view does not exist ?

I am getting Ora-00942 through spring,
1.) Don't know c# line#
2.) which table or view it is NOT finding...

clearly oracle "knows" the name it didn't find, how do I get it ?

Ping

Hi Tom,

we have a product which is used by number of customers. Recently one of the client reported a data issue. The source of the problem is narrowed down to one particular procedure which executes but does nothing, just comes out without performing any of the logic inside the procedure. Here is how the procedure looks like.

BEGIN
FOR i in (select <column_list>
from table1, table2
where <join_conditions>) LOOP

<procedural logic>
END LOOP;
END;

1) The SQL in the for loop returns rows when
executed stand-alone
2) removed procedural logic and just put dbms_output and
executed the procedure. The debug statement
is not printed
3) The bizarre thing is if i move "close bracket and LOOP"
to next line, it works fine.

This is working:

BEGIN
FOR i in (select <column_list>
from table1, table2
where <join_conditions>
) LOOP

<procedural logic>
END LOOP;
END;

Not sure how to debug this issue? Any ideas would be greatly appreciated. Please let me know if you need more info.

Hi Tom,

Close bracket i mean close parenthesis before LOOP.

this doesn't work:

for i in (select statement) LOOP
---
end loop;

this works:

for i in (select statement
) LOOP
---
end loop;

There was "when others then dbms_output" (not my code) which i got rid of. But it doesn't throw an error.

Todays update: if I create an index on one of the columns that is refereed in the join, the query works. Also if comment the column (dont use it) in the query, it works again. I have never seen this kind of problem before. very puzzling.