Tom thanks a lot for your reply. It was much help. One more thing though. Oracle manual (8.1.5) notes that Trace cannot be used when in MTS mode. Is there an alternative when running in this mode?

That is because your trace file is not your trace file (and this only applies in 815 -- maybe a better reason still to upgrade to supported software).

In 816 and up, since it writes to a table, your trace is your trace -- we can id it via the sequence -- there is no ambiguity.

When using MTS in 815, you will not be able to use this technique and there is no alternative short of coding it yourself.  You could use a procedure like my "who_called_me" 

and you would be able to write a small routine "mark" or something like that that asked "who called me" and gets the line number, owner, procedure name it its caller and saves them in a set of package variables.  You could then code:

ops$tkyte@ORA815.US.ORACLE.COM> create or replace package mark
  2  as
  3      g_owner    varchar2(30);
  4      g_name     varchar2(30);
  5      g_lineno   number;
  6      g_caller_t varchar2(255);
  7  
  8      procedure it;
  9  end;
 10  /

Package created.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> create or replace package body mark
  2  as
  3  
  4  procedure it
  5  as
  6  begin
  7      who_called_me( g_owner, g_name, g_lineno, g_caller_t );
  8  end;
  9  
 10  
 11  end;
 12  /

Package body created.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> create table t
  2  ( x int constraint check_x check (x>0),
  3    y int constraint check_y check (y>0)
  4  );

Table created.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> create or replace procedure p( p_x in int, p_y in int )
  2  as
  3  begin
  4      mark.it;
  5      insert into t values ( p_x, p_y );
  6      mark.it;
  7      update t set y = -p_y where x = p_x;
  8  exception
  9      when others then
 10          dbms_output.put_line( 'Error on line ' || mark.g_lineno ||
 11                                ' of ' || mark.g_caller_t || ' ' ||
 12                                mark.g_owner || '.' || mark.g_name );
 13  end;
 14  /

Procedure created.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> exec p(1,1)
Error on line 6 of PROCEDURE OPS$TKYTE.P

PL/SQL procedure successfully completed.

ops$tkyte@ORA815.US.ORACLE.COM> exec p(-1,-1)
Error on line 4 of PROCEDURE OPS$TKYTE.P

PL/SQL procedure successfully completed.
 

Tom,

I ran your procedure script, but it failed since the objects sys.plsql_trace_events, sys.plsql_trace_runnumber are not there in SYS schema.

Is there any script file to create these objects?...

Thanks.

Hi Tom !

I'm trying to set the trace ON from a PL/SQL procedure and it doesn't seem to work (no events in PLSQL_TRACE_EVENTS).

Here is my test case :

--==================================================
CREATE OR REPLACE PROCEDURE traceexc AS
l_runid NUMBER;
BEGIN
SELECT sys.plsql_trace_runnumber.CURRVAL
INTO l_runid
FROM DUAL;

FOR x IN (SELECT *
FROM sys.plsql_trace_events
WHERE runid = l_runid AND event_kind =
DBMS_TRACE.plsql_trace_excp_raised
ORDER BY event_seq DESC) LOOP
DBMS_OUTPUT.put_line (
'Exception '
|| x.excp
|| '('
|| SQLERRM (SQLCODE)
|| ') occured in '
|| x.event_unit_kind
|| ' '
|| x.event_unit_owner
|| '.'
|| x.event_unit
|| ' at line '
|| x.event_line
);
EXIT;
END LOOP;
END;
--==================================================
CREATE OR REPLACE PROCEDURE testexc AS
BEGIN
EXECUTE IMMEDIATE 'insert into tototata values (1,12)'; -- Will raise an
exception
EXCEPTION
WHEN OTHERS THEN
traceexc;
raise;
END testexc;
--==================================================
CREATE OR REPLACE PROCEDURE test AS
BEGIN
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
testexc;
END test;
--==================================================


Any idea on how to enable DBMS_TRACE from PL/SQL ?

Thanks in advance !

do it as i did it -- I did trace on AND THEN ran the code.  You are

"turning on trace, run the code"

It is my experience that you have to turn on trace and then it will affect all SUBSEQUENT runs of code, not the currently executing piece of code.


ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE PROCEDURE test AS
  2  BEGIN
  3     DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
  4     testexc;
  5  END test;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec test
BEGIN test; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "OPS$TKYTE.TESTEXC", line 7
ORA-06512: at "OPS$TKYTE.TEST", line 4
ORA-06512: at line 1


ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec   DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE PROCEDURE test AS
  2  BEGIN
  3     testexc;
  4  END test;
  5  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec test
Exception 942(ORA-00942: table or view does not exist) occured in PROCEDURE OPS$TKYTE.TESTEXC at line 3
BEGIN test; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "OPS$TKYTE.TESTEXC", line 7
ORA-06512: at "OPS$TKYTE.TEST", line 3
ORA-06512: at line 1 

I did some more tests... and if, in my test procedure I set the trace to the trace_all_calls level i.e.

DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);

then I get a full trace (including exceptions).

As my procedure is submitted as a JOB I think I need to set the trace in the procedure itself...

I also noticed that the trace contains a lot of

PL/SQL Virtual Machine started
PL/SQL Virtual Machine stopped

events... what's that ?

What about the fact that a set trace at trace_all_calls level i.e.

DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);

generates full trace (including exceptions) BUT a trace set (at the same place) at trace_all_exceptions level doesn't generate trace for raised exceptions ??

Tom,
TABLE sys.plsql_trace_events does not exits.
We are using 8.1.7

I have "select any table" privileges

SQL> desc sys.plsql_trace_events;
ERROR:
ORA-04043: object sys.plsql_trace_events does not exist
  

Hello TOM,
        As you mentioned in the script i ran the oracle (/admin folder)  scripts and your sample example.
        I got the following entries. But This file is not created in the udump folder.
        Is there any problem in My setting. 
        Platform : Oracle 9.0.1 on WIN98.



SQL>  select c.value || '\' || instance || '_ora_' ||
  2          ltrim(to_char(to_number(a.spid),'fm99999999999999')) || '.trc'
  3     from sys.v$process a, sys.v$session b, sys.v$parameter c, sys.v$thread c
  4    where a.addr = b.paddr
  5      and b.audsid = userenv('sessionid')
  6      and c.name = 'user_dump_dest'
  7  /

C.VALUE||'/'||INSTANCE||'_ORA_'||LTRIM(TO_CHAR(TO_NUMBER(A.SPID),'FM999999999999
--------------------------------------------------------------------------------
c:\oracle\admin\siva\udump\siva_ora_-658843.trc
c:\oracle\admin\siva\udump\siva_ora_-659555.trc
c:\oracle\admin\siva\udump\siva_ora_-667303.trc
c:\oracle\admin\siva\udump\siva_ora_-646979.trc
c:\oracle\admin\siva\udump\siva_ora_-649015.trc
c:\oracle\admin\siva\udump\siva_ora_-651179.trc
c:\oracle\admin\siva\udump\siva_ora_-734359.trc
c:\oracle\admin\siva\udump\siva_ora_-207687.trc



If i will get one entry and run the procedure i got the following error. I think this file
is not exists.


SQL> exec p(1,1);
BEGIN p(1,1); END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "SYS.P", line 22
ORA-02290: check constraint (SYS.CHECK_Y) violated
ORA-06512: at line 1


regards,
siva 

Hi Tom,

Please could you answer me how to find original call stuck when the exception occured?

Regards

Sasa

Hi Tom,

You answered about call stack :
"You can get the call stack using dbms_utility.format_call_stack -- BUT it'll be
the call stack from the perspective of the exception handler."

We know about dbms_utility.format_call_stack and in a some way it is not correct, because it shows only the path to the exception handler not to place where the real exception occurred .
We want to know how TOAD or ADO get the "real" call stack?

Thanks

Sasa

Thanks a lot Tom.

As usually you were brilliant.

Regards,

Sasa

Tom

I am able to get DBMS_TRACE to catch exceptions inside PL/SQL Block, here is how:
I used "dbms_trace.plsql_trace_excp_handled" instead of
"dbms_trace.trace_all_exceptions"

for example:
procedure test as
begin
dbms_trace.set_plsql_trace(dbms_trace.plsql_trace_excp_handled);
p(1,1);
dbms_trace.clear_plsql_trace;
end;

I do not know of any implications, but I would appreciate if someone at Oracle can confirm this method.

Thanks
Raghid Ajamoughli

It's most useful indeed,But I think I can't use it in my situation.I want to exec a huge *.sql file in SQL*PLUS,and want to trap the problematic statement,I use the 'WHENEVER SQLERROR EXIT' and 'spool some.log',but this log file have not the problematic statement and only the error code,so I can't catch which statement error by log.

And,The sql file is too huge,It contains statements such as:
'create table ...','insert into ...',eg.So I also can't use 'begin.. end.. exception.. ' like you said above.

Unless I write 'begin.. end.. exception..' by each statement such as 'insert',But It's too huge to act.

If can use 'WHENEVER SQLERROR EXIT pl/sql' is so good,but I know it's impossible,Could you give me directions?

Thanks a lot!

> Once you have an exception block in PLSQL and catch
> the exception -- the error goes away, it by very
> definition doesn't exist. You caught it, you are
> dealing with it, you are handling it.

I've often wondered if there is any way to force the error number into a trace file (e.g. ERROR #c:err=e tim=t) even though an exception handler is catching and handling the exception and not re-raising it. This would be good evidence to show an inexperienced developer the error that was thrown (and then ignored) in the developer's WHEN OTHERS THEN ... handler.

I am a little confused about using of debug.f package
of yours versus dbms_trace method that you explained above.
Are the complements to each other - and if so in what
way. I think that one could use debug.f in the following
ways:

1. To know where exactly you are in the procedure - to
see the info on what is happenning in sequence
2. To "time" the procedures. - Since time is
automatically generated - this may not be an issue -
You could also do it using dbms_utility.get_time
and debug.f the times it took
So debug.f - helps mainly in non-exception errors
and performance tracking/problems

You would use dbms_trace to trace exceptions - Not
all errors are exceptions -


hence you need both. Is this analysis correct?

Thanx!!

Actually, hate is a strong word, but I wish it were more like Java-type exceptions. I understand that the exception stack trace disappears when the code enters an exception handler, but I disagree that having the stack trace is no longer necessary. There are many times I have to catch an error in WHEN OTHERS, only to do some logging, closing cursors, and other cleanup-type activities. I understand (but don't agree) why I can't throw the same exception with an intact stack trace, but it still baffles me why I can at least have access to the entire stack trace via some system variable, like sqlerrm. For example:

create or replace procedure c

is

l_number number;

begin

--cause error here
l_number := 1/0;

end;
/

create or replace procedure b

is

begin

c;

end;
/

create or replace procedure a

is

begin

b;

exception
when others then
--mandatory cleanup, like closing cursors.
--I want to log the stack trace here! How?
--and optionally reraise to the client.
raise;

end;
/

Great discussion by the way....

Hi Tom,

Now we know we can use dbms_trace to track exception.  But How do I use it for other purpose?

I read the package guide, but it didn't give any exmpales to interpret the result.  I got some query  from other web site, but it is not very useful.  Here is what I tried.

The reason I am asking is that PL/SQL user's guide always mentions this package, but it seems that it is so difficult to interpret the result.


------------------------------------------------------------------------

SQL> exec DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);

PL/SQL procedure successfully completed.

-- Got this code from your "Expert" book
SQL> create or replace
  2  function fact_recursive( n int ) return number
  3  as
  4  begin
  5          if ( n = 1 )
  6          then
  7                  return 1;
  8          else
  9                  return n * fact_recursive(n-1);
 10          end if;
 11  end;
 12  /

Function created.

SQL> begin
  2   for i in 1 .. 50 loop
  3    dbms_output.put_line( fact_recursive(50) );
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.


SQL> exec DBMS_TRACE.clear_plsql_trace;

PL/SQL procedure successfully completed.



SQL> ed
Wrote file afiedt.buf

  1  SELECT r.runid,
  2         TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
  3         r.run_owner
  4  FROM   sys.plsql_trace_runs r
  5* ORDER BY r.runid
SQL> /

     RUNID RUN_DATE             RUN_OWNER
---------- -------------------- -------------------------------
         8 14-JUL-2004 12:07:17 SCOTT

1 rows selected.

-- I got this query from other site.  But useless in this case.
>SELECT e.runid,
       e.event_seq,
       TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
       e.event_unit_owner,
       e.event_unit,
       e.event_unit_kind,
       e.proc_line,
       e.event_comment
FROM   sys.plsql_trace_events e
WHERE  e.runid = 8
ORDER BY e.runid, e.event_seq;
 

Hi Tom,

The question is really how to use dbms_trace properly to get the useful information which can not be got from other tools. I tried, but don’t know how to interpret the result. I guess it may be the wrong way to use this package.

Or how do you use this package for your daily work besides tracking exception? If possible, give us an example, such as using trace_all_calls.

Thanks so much for your help.

Hi Tom,

This is "REALLY" what I want to hear.

Thanks so much for your help.

Sean

Oracle 9.2.0.5 on HP UX

Hi Tom,
I created this test scenerio..where I wanted to see the line number using your method of
tracing all the exceptions.
For some reason..it did not work for me when I tried to use it through a DBMS_JOB.RUN:

CREATE TABLE DBA_LOG
(
LOGID NUMBER NOT NULL,
LOGDS VARCHAR2(500) NOT NULL,
STARTDT DATE,
ENDDT DATE,
NOTES VARCHAR2(255)
)
TABLESPACE TS_SMALL;

ALTER TABLE DBA_LOG ADD (
CONSTRAINT PK_DBA_LOG PRIMARY KEY (LOGID)
USING INDEX
TABLESPACE TS_IDX_SMALL);

CREATE SEQUENCE DBA_LOGID INCREMENT BY 1
START WITH 1 NOCYCLE
NOCACHE ORDER;


CREATE OR REPLACE
PACKAGE DEPT_PROC
AS

PROCEDURE DEPT_INSERT (pDEPTNO NUMBER);

END DEPT_PROC;

/

CREATE OR REPLACE
PACKAGE BODY DEPT_PROC
AS

PROCEDURE DEPT_INSERT (pDEPTNO NUMBER)
AS
BEGIN

INSERT INTO DEPT (DEPTNO,DNAME)
VALUES (pDEPTNO, 'TEST');

END DEPT_INSERT;

END DEPT_PROC;

/

CREATE OR REPLACE
PROCEDURE INSERT_DEPT (pDEPTNO NUMBER)
AS
BEGIN

DEPT_PROC.DEPT_INSERT (pDEPTNO);

END INSERT_DEPT;

/


CREATE OR REPLACE
PROCEDURE LOG_EXCEPTION
AS
L_RUNID NUMBER;
ERRORDS_V DBA_LOG.LOGDS%TYPE;
NOTES_V DBA_LOG.NOTES%TYPE;

BEGIN
SELECT SYS.PLSQL_TRACE_RUNNUMBER.CURRVAL INTO L_RUNID
FROM DUAL;

FOR X IN ( SELECT *
FROM SYS.PLSQL_TRACE_EVENTS
WHERE RUNID = L_RUNID
AND EVENT_KIND = 52
ORDER BY EVENT_SEQ DESC )
LOOP
ERRORDS_V := 'Exception occured in source ' ||
X.EVENT_UNIT || ' ' || X.EVENT_UNIT_KIND ||
' on line ' || X.EVENT_LINE;
NOTES_V := SUBSTR(SQLERRM, 1, 200);

DBMS_OUTPUT.PUT_LINE( ERRORDS_V);

DBMS_OUTPUT.PUT_LINE( NOTES_V);
--DBMS_OUTPUT.PUT_LINE( 'Error Code ' || SQLCODE);

INSERT INTO DBA_LOG (LOGID, LOGDS, STARTDT, NOTES)
VALUES (DBA_LOGID.NEXTVAL, ERRORDS_V, SYSDATE, NOTES_V);

EXIT;
END LOOP;
END LOG_EXCEPTION;

/


CREATE OR REPLACE PROCEDURE TEST_EXCP_PROC
AS
BEGIN

INSERT_DEPT (10);

INSERT INTO DEPT (DEPTNO,DNAME)
VALUES (50, 'TEST');

END TEST_EXCP_PROC;
/


CREATE OR REPLACE PROCEDURE TEST_EXCP_PROC_RUN
AS

BEGIN

TEST_EXCP_PROC;

EXCEPTION
WHEN OTHERS THEN
LOG_EXCEPTION;

END TEST_EXCP_PROC_RUN;
/

set serveroutput on;

SELECT * FROM DBA_LOG;
execute dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions );
execute TEST_EXCP_PROC_RUN;
SELECT * FROM DBA_LOG;

/*Output from the above:

SQLWKS> set serveroutput on;
Server Output ON
SQLWKS>
SQLWKS> SELECT * FROM DBA_LOG;
LOGID LOGDS STARTDT ENDDT NOTES
---------- -------------------------------------------------------------------------------- -------------------- -------------------- --------------------------------------------------------------------------------
0 rows selected.
SQLWKS> execute dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions );
Statement processed.
SQLWKS> execute TEST_EXCP_PROC_RUN;
Statement processed.
Exception occured in source DEPT_PROC PACKAGE BODY on line 8
ORA-00001: unique constraint (MDBA.PK_DEPT) violated
SQLWKS> SELECT * FROM DBA_LOG;
LOGID LOGDS STARTDT ENDDT NOTES
---------- -------------------------------------------------------------------------------- -------------------- -------------------- --------------------------------------------------------------------------------
1 Exception occured in source DEPT_PROC PACKAGE BODY on line 8 19-AUG-04 ORA-00001: unique constraint (MDBA.PK_DEPT) violated
1 row selected.

*/


--Now let's try to run this through a job..in another session

TRUNCATE TABLE DBA_LOG;

CREATE OR REPLACE PROCEDURE TEST_EXCP_PROC_RUN
AS

BEGIN

dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions );
TEST_EXCP_PROC;

EXCEPTION
WHEN OTHERS THEN
LOG_EXCEPTION;

END TEST_EXCP_PROC_RUN;
/


DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'TEST_EXCP_PROC_RUN;'
,next_date => trunc(sysdate+1)+17/96
,interval => 'trunc(sysdate+1)+17/96'
,no_parse => TRUE
);
COMMIT;
END;
/

SELECT JOB,WHAT FROM USER_JOBS

/*

JOB WHAT
---------- -------------------------
63 TEST_EXCP_PROC_RUN;
1 row selected.

*/


set serveroutput on;
SELECT * FROM DBA_LOG;
EXECUTE DBMS_JOB.RUN(63);
SELECT * FROM DBA_LOG;

/*

SQLWKS> set serveroutput on;
Server Output ON
SQLWKS> SELECT * FROM DBA_LOG;
LOGID LOGDS STARTDT ENDDT NOTES
---------- -------------------------------------------------------------------------------- -------------------- -------------------- --------------------------------------------------------------------------------
0 rows selected.
SQLWKS> EXECUTE DBMS_JOB.RUN(63);
Statement processed.
SQLWKS> SELECT * FROM DBA_LOG;
LOGID LOGDS STARTDT ENDDT NOTES
---------- -------------------------------------------------------------------------------- -------------------- -------------------- --------------------------------------------------------------------------------
0 rows selected.

*/

So, Nothing gets logged in DBA_LOG table..
Is there a way around this..

Thanks.

and here i would like to quote the TOM's saying:

"i wish When Others exception doesn't exist"

This is in reference to procedure who_called_me at
</code> http://asktom.oracle.com/~tkyte/who_called_me/index.html <code>

In 10.1.0.3, this procedure gives an error because of
slight change in format call stack.
Only change is in the line
lineno := to_number(substr( line, 13, 6 ));
that changes to
lineno := to_number(substr( line, 13, 8 ));
Changed method follows (tested it quickly with 9i and 10gr1 - you may want to test it out more thoroughly:)):
---
create or replace procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 )
as
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
--
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
--
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
--
if ( NOT found_stack ) then
if ( line like '%handle%number%name%'
) then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 13, 8 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
elsif ( line like 'anonymous%' ) then
n := length( 'anonymous block ' );
else
n := null;
end if;
if ( n is not null ) then
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
else
caller_t := 'TRIGGER';
end if;

line := substr( line, nvl(n,1) );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end;
/

create or replace function who_am_i return varchar2
is
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_type varchar2(30);
begin
who_called_me( l_owner, l_name, l_lineno, l_type );
return l_owner || '.' || l_name;
end;
/
-----
Thanx.

I have two questions:
1. Are the different event kinds documented anywhere?
2. dbms_trace does nt seem to work for me in 9.2.0.1.0...
The same works for me in 10g r1. Is this a known bug?
---------
benchmark@ORA92I> create or replace procedure p3
2 is
3 l_x number := 0;
4 begin
5 dbms_output.put_line( 'p3' );
6 l_x := 1/ l_x; -- divide by zero
7 end;
8 /

Procedure created.

benchmark@ORA92I> show errors;
No errors.
benchmark@ORA92I> create or replace procedure p2
2 is
3 begin
4 dbms_output.put_line( 'p2' );
5 p3;
6 end;
7 /

Procedure created.

benchmark@ORA92I> show errors;
No errors.
benchmark@ORA92I> -- use dbms_trace
benchmark@ORA92I> -- procedure for printing dbms_trace info
benchmark@ORA92I> -- requires select on plsql_trace_events and plsql_trace_runnumber
benchmark@ORA92I> create or replace procedure print_dbms_trace
2 is
3 l_runid binary_integer;
4 begin
5 select sys.plsql_trace_runnumber.currval
6 into l_runid
7 from dual;
8 for x in ( select *
9 from sys.plsql_trace_events
10 where runid = l_runid
11 and event_kind = 52
12 order by event_seq DESC )
13 loop
14 dbms_output.put_line( 'Exception occured in source ' ||
15 x.event_unit ||
16 ' on line ' || x.event_line );
17 exit;
18 end loop;
19 end;
20 /

Procedure created.

benchmark@ORA92I> show errors;
No errors.
benchmark@ORA92I> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line( 'p1 (using dbms_trace - trace_all_exceptions)' );
5 dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions );
6 p2;
7 dbms_trace.clear_plsql_trace;
8 exception when others then
9 print_dbms_trace;
10 raise;
11 end;
12 /

Procedure created.

benchmark@ORA92I> show errors;
No errors.
benchmark@ORA92I> alter session set plsql_debug = true;

Session altered.

benchmark@ORA92I> alter procedure p1 compile debug;

Procedure altered.

benchmark@ORA92I> --exec dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions );
benchmark@ORA92I> exec p1
p1 (using dbms_trace - trace_all_exceptions)
p2
p3
BEGIN p1; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "BENCHMARK.P1", line 10
ORA-06512: at line 1

In the above run, I ran the dbms_trace.set* inside the
PL/SQL block. I tried running it outside as well before
posting the results - it had not worked.

Hi Tom,
Can you tell if there is any impact the dbms_trace.set_plsql_trace has on performance of the code.

Thanks,
-Raja

Hi Tom,
Your reply is highly appreciated. I will benchmark and see where we stand.

-Raja

I like this topic. I have written a package procedure to return the stack information using dbms_utility.format_call_stack whenever there is an error. It works fine when called from PLSQL block within proC, sqlplus or TOAD. But the stack is null when the package is called from a form. I am running Oracle 9.2.0.4, but forms 6i. Is it a problem with the PLSQL version of forms 6i not supporting that ? If not, can you think of any reason ?

Let me clarify, I call a database package that performs a select from a table. When no data found, the procedure calls another package function that gets the stack and return the stack information, which shows the name of the validation package. In the forms, I am not able to refer to DBMS_UTILITY.FORMAT_CALL_STACK directly, it will not compile.

In fact, I tried your procedure who_called_me and function who_am_i. I have the following in a form ON-NEW-FORM-INSTANCE trigger:
emessage('>>'||demof);

All I got is
>> . line

demof is similar to your demo procedure as below:
create or replace function demof return varchar2 as
begin
return who_am_i;
end;

I can get
FUNCTION bssrd05.DEMOF line 3
if call it from a PLSQL block in sqlplus or TOAD.

SQL> desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR                                              RAW(4)
 SID                                                NUMBER
 SERIAL#
blah blah blah                                            NUMBER
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 PREV_SQL_ADDR                                      RAW(4)
 PREV_HASH_VALUE
-^^^^^^^^^^^^^^^                                    NUMBER
 PREV_SQL_ID                                        -^^^^^^^^^^^                                    NUMBER
VARCHAR2(13)
 PREV_CHILD_NUMBER                                  NUMBER
-^^^^^^^^^^^^^^^^^                                    
...
 

Tom,

Hope you don't mind me stepping in. I'm sure you are aware that Mikito is referring to the inclusion of the SQL_ID, hash value etc of the previous SQL statement (cursor) executed in v$session. Seems to be mostly used as a default value for the SQL_ID parameter to DBMS_XPLAN.DISPLAY_CURSOR ( another 10g new feature ) to display actual execution plans ( unfortunately for you it replaces the DYNAMIC_PLAN_TABLE you had in your last book ! ). The various PREV_XXX values can also be used in lookups to V$SQL, V$SQL_PLAN etc.

Beware serveroutput though if you are in sqlplus, because if you have serveroutput on, then your PREV_XXX will be DBMS_OUTPUT.GET_LINES or some such like, and not the last SQL statement executed.

Regards
Adrian

Tom,
I'm using this "AFTER SERVERERROR" trigger in SYS schema
(original written by Nico Booyse in 2000) to capture the application errors (Perl/java w/ connection polls)

Works well, the only issue is that the SQL statement captured end up being not the error statement but the trigger's select:

CREATE TABLE LOG_ERRORS_TAB (
ERROR VARCHAR2(30),
TIMESTAMP DATE,
USERNAME VARCHAR2(30),
OSUSER VARCHAR2(30),
MACHINE VARCHAR2(64),
PROCESS VARCHAR2(8),
PROGRAM VARCHAR2(48),
OBJ_TYPE VARCHAR2(50),
OBJ_NAME VARCHAR2(50),
SQL_TEXT VARCHAR2(1000));


CREATE OR REPLACE TRIGGER LOG_ERRORS_TRIG
AFTER SERVERERROR ON DATABASE
DECLARE
v_sql_addr RAW(4);
v_hash number;
BEGIN

select PREV_SQL_ADDR,PREV_HASH_VALUE into v_sql_addr,v_hash
from v$session where audsid = userenv('sessionid');

INSERT INTO log_errors_tab (error,timestamp,username, osuser, machine, process, program,obj_type, obj_name,sql_text)
SELECT dbms_standard.server_error(1),SYSDATE,username, osuser, machine, process, program,sys.dictionary_obj_type,sys.dictionary_obj_name, sql_text
FROM v$session s ,v$sqlarea a
WHERE a.ADDRESS= v_sql_addr
AND a.HASH_VALUE=v_hash
AND audsid = userenv('sessionid');
END;
/
show errors

SELECT error,sql_text FROM log_errors_tab ORDER BY timestamp ;

ERROR SQL_TEXT
------------- ------
54 SELECT PREV_SQL_ADDR,PREV_HASH_VALUE FROM V$SESSION WHERE AUDSID = USERENV('sessionid')

I'm I using PREV_SQL_ADDR,PREV_HASH_VALUE properly?

Thank you for your time,
David

Nethermind,
usered your other posting to fix it :)
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1657308242210 <code>

CREATE OR REPLACE TRIGGER LOG_ERRORS_TRIG
AFTER SERVERERROR ON DATABASE
DECLARE
csql_text ora_name_list_t;
n NUMBER;
cSQL CLOB;
BEGIN
n:= ora_sql_txt(csql_text);
FOR I IN 1..N LOOP
cSQL:=cSQL||csql_text(i);
END LOOP;

INSERT INTO log_errors_tab (error,timestamp,username, osuser, machine, process, program,obj_type, obj_name,sql_text)
SELECT ora_server_error(1),SYSDATE,username, osuser, machine, process, program,ora_dict_obj_type,ora_dict_obj_name, cSQL
FROM v$session s
WHERE audsid = userenv('sessionid');
END;
/
show errors

Thanks!

Hello Tom,

Thanks for all your guidance. We use DBMS_TRACE, but currently I am finding few limitation for the same, please le us know if there is way out.

DBMS_TRACE shows output in SYS.PLSQL_TRACE_EVENTS table and it helps me to know how many triggers, procedures are invoked by my DML statements. As there are hundreds of Procedures, Triggers in our application it would be better if I get output as shown below...

Source Statement:
UPDATE TableName SET ColumnName = :1 WHERE ROWID = :2

Trigger: TrgRow_1

Procedure: Pro_1
SELECT ....
INSERT ....

Procedure: Pro_2
UPDATE
SELECT

This helps me to identify Statements related to corresponding Procedures or triggers. Currently We tried to club <sys.plsql_trace_events> and <user_source>, but still doesn't get output as shown above.

Please help.

Thanks & Regards,

Shailesh

On a 10.2 database, we are executing a procedure directly on sqlPlus and calling it through asp page.

Lately (probalbly since morning), we are getting ora-00600 errors when we execute it through a asp page (but runs good on sqlPlus prompt).

This is what the trace file has
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kcbgcur_9], [4600202], [1], [4294967250], [2], [], [], []
Current SQL statement for this session:
INSERT INTO SYS.PLSQL_TRACE_EVENTS( RUNID, EVENT_TIME, EVENT_SEQ, EVENT_KIND, EVENT_COMMENT, STACK_DEPTH, EVENT_UNIT_DBLINK, EVENT_UNIT_OWNER, EVENT_UNIT, EVENT_UNIT_KIND, EVENT_PROC_NAME, EVENT_LINE, MODULE, ACTION, CLIENT_INFO, CLIENT_ID, ECID_ID, ECID_SEQ) VALUES(:B1, SYSDATE, :B2, :B3, :B4, :B5, :B6, :B7, :B8, :B9, :B10, :B11, :B12, :B13, :B14, :B15, :B16, :B17)


We don't know why we are getting this error.

One more question: What is the difference between the parameters sql_trace and trace_enabled?

We tried to set both to true and later to false and still we got ora 600 error.

Thanks,

We will try contacting support.

Why would the process inserts into SYS.PLSQL_TRACE_EVENTS? What is this table for?

Hi Tom

Good Morning. I am having a problem while automating
PL/SQL Exception trace using DBMS_TRACE. We are working
on an web application which is having Oracle9iR2 as it's
DB. We are having some Stored Procedures for Back End
operations. Now, I want to track all the exceptions using
DBMS_TRACE. As per the way you've shown, I've written the
test code for that. But as it's a web application, I need
to do it in an automated way. Let me refresh in brief.
In our application, whenever any DB error(due to SPs etc.)
comes into piture, I need to catch that exception through
DBMS_TRACE package. But before making this package into
work, I need to run this package in the same session.
I've tried writing a DB startup trigger to do that in
the following way. But it's not supposed to work as this
has to run in the same session.

/*******************************************************/
CREATE OR REPLACE TRIGGER db_startup_dbms_trace_trigger
AFTER STARTUP ON DATABASE

BEGIN
DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_EXCEPTIONS);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error encountered -> ' || SQLCODE ||' , ' ||SQLERRM );
END;
/*******************************************************/

At the same time, I also can't do this in the body
of Stored Procedure.

Can you please give me a solution ? Please let me know
if I've failed to communicate the problem.

Thanks in advance for your time.

Thanks
Debashis
Bangalore

Hi Tom

Thanks a lot. Just one more query related to your
answer : while connecting to DB from web application(java),
the developer can set DBMS_TRACE after connecting to DB, right ?
Or you are telling any other possiblity ?

Thanks
Debashis
Bangalore

Hi Tom

Thanks a lot for your response. There is one more
possible solution coming into my mind. If we create
a logon trigger like this, it's working fine? Can I use
this ? Or it might have any problem in future ? Please
give your suggestions/thoughts on this.

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

CREATE OR REPLACE TRIGGER ON_LOGON_DBMS_TRACE_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
L_USER VARCHAR2(30);
BEGIN
SELECT USER
INTO L_USER
FROM DUAL ;
IF L_USER='DEV' THEN
DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_EXCEPTIONS);
END IF ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error encountered -> ' || SQLCODE ||' , ' ||SQLERRM );
END;

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

Thanks
Debashis
Bangalore

The object handle returned from format_call_stack is longer on Linux than Unix and so these lines will fail:

In 10.1.0.3, this procedure gives an error because of
slight change in format call stack.
Only change is in the line
lineno := to_number(substr( line, 13, 6 ));
that changes to
lineno := to_number(substr( line, 13, 8 ));
Changed method follows (tested it quickly with 9i and 10gr1 - you may want to test it out more
thoroughly:)):

I created a generic parser for the line that is not column dependent.

Hi Tom,

I would like to know if we can print the line numbers along with the source code. I tried for:

declare
var1 number := 0;
BEGIN
FOR cursor1 IN (SELECT line, text FROM user_source where NAME = 'MY_PACKAGE')
LOOP
var1 := var1+1;
if cursor1.text like '%PACKAGE BODY%'
THEN
DBMS_OUTPUT.PUT_LINE( var1 || ' ' || '/');
var1 := var1+1;
DBMS_OUTPUT.PUT_LINE( var1 || ' ' || cursor1.text);
ELSE
DBMS_OUTPUT.PUT_LINE( var1 || ' ' || cursor1.text);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE( var1 || ' ' || '/');
END;
/


it works almost fine for Packages and Functions with some modification. But I would like to print the same for all types of objects: Tables, Views, Functions, Packages ....

I will paramterize them.. So is there any Oracle supplied package that is readily available. Please help in case we have to write new PL SQL to achive this.

Hi Tom,

As usual, thanks for your great site.

I have been testing same task in different style of queires to know which is better as per performance is considered (as you do-- test it and proove it).

Let me share you all the queries which does almost the same work.


Method 1: (This example i took from your one of the example)

declare
l_time pls_integer:= dbms_utility.get_time;
l_elapsed pls_integer;
cursor c1 is select emp_object(empno,ename) from emp;
l_emp emp_type; --emp_type is table of object
begin
open c1;
fetch c1 bulk collect into l_emp;
for a in 1..l_emp.count loop
l_emp(a).empname := l_emp(a).empname||'B';
insert into emp2(empno,ename) values(l_emp(a).empno,l_emp(a).empname);
end loop;
commit;

dbms_output.put_line('Here we go ah');
dbms_output.put_line(' The time taken is '||round((dbms_utility.get_time-l_time)/100,2));

end;



Method 2:

declare
/* procedure with plsql table*/
type e_record is record(empno number, ename varchar2(20));
type e_type is table of e_record index by binary_integer;
cursor c1 is select empno,ename from scott.emp;
--type emp_type as object (empno number,ename varchar2(20));
--type emp_table is table of emp_type index by binary_integer;
l_emp e_type;
l_time pls_integer:= dbms_utility.get_time;
l_elapsed pls_integer;
begin
open c1;
fetch c1 bulk collect into l_emp;
for a in 1..l_emp.count loop
l_emp(a).ename := l_emp(a).ename||'B';
insert into emp2(empno,ename) values(l_emp(a).empno,l_emp(a).ename);
end loop;
commit;
dbms_output.put_line(' The time taken is '||round((dbms_utility.get_time-l_time)/100,2));
end;





Method 3:
declare
/*loop manually*/
cursor c1 is select empno,ename from emp;
l_empno emp.empno%type;
l_ename emp.ename%type;
l_time pls_integer:= dbms_utility.get_time;
l_elapsed pls_integer;
begin
open c1;
loop
fetch c1 into l_empno,l_ename;
l_ename := l_ename||'B';
insert into emp2 (empno, ename) values (l_empno,l_ename);
exit when c1%notfound;
end loop;
commit;
dbms_output.put_line(' The time taken is '||round((dbms_utility.get_time-l_time)/100,2));
end;




Method 4:
declare
/* for loop*/
l_time pls_integer:= dbms_utility.get_time;
l_elapsed pls_integer;

begin
for a in (select empno,ename from emp) loop
a.ename := a.ename || 'B';
insert into emp2 (empno,ename) values (a.empno,a.ename);
end loop;
commit;
dbms_output.put_line(' The time taken is '||round((dbms_utility.get_time-l_time)/100,2));

end;


What i would like to know is that, if i execute first block and if the same query is used in any of the followed blocks then the query plan is saved and reused, so i would like to know all the things which i should do before starting the new block to test the performance.

Can you please suggest me what are all the things that i need to do before going to test a new type of block which may use the similar query used in the earlier block. Do i need to delete query plan or anything
Note: the above queries are just for sample, my intention is to know how we can proceed with such scenarios.

I posted the same question, at some forum and got the answer saying that i need to run multiple times the same query to have the correct result.
However, I would request your suggestions on it. How can i proceed with such scenarios.

Regards,

Thanks Tom.

Hi Tom,
Great site.
I consider that your sustained work and effort give all of us a model to aim at and a proof that there is at least one place where knowledge is both priced and shared.

My problem is that I can't use BMS_UTLITY.FORMAT_CALL_STACK because of the Bug:5102847 (DBMS_UTLITY.FORMAT_CALL_STACK returns nothing when called via an RPC.)
Since its impossible to upgrade the database version at this time - high level decision - i must find another way to get that stack from plsql.
I'm pretty sure that this information is available during runtime in v$ tables or x$ "tables".
Can you please guide me where should I search next? (e.g. v$object_dependecy is close but not the same)
Thanks.