Re: instrumentation

From: Billy <vslabs_at_onwe.co.za>
Date: 22 Aug 2005 23:20:55 -0700
Message-ID: <1124778055.105704.252180@g44g2000cwa.googlegroups.com>

prunoki wrote:

> Thanks a lot. How can you log the pl/sql stack?

Static state variable called FORMAT_CALL_STACK maintained in the DBMS_UTILITY package. It is a varchar2(4096) variable.

I use the following to access the call stack:

A call stack item desription:
==

create or replace type TPLSQLStackItem as object (

        handle                  varchar2(50),
        object_name             varchar2(200),
        line_number             number

);
/
==

The call stack dynamic array:
==

create or replace type TPLSQLStackTable as table of TPLSQLStackItem; /
==

Then I've coded the following object type as stack interface:
==

create or replace type TPLSQLStack as object (

   stack TPLSQLStackTable,
   constructor function TPLSQLStack return self as result );
/

create or replace type body TPLSQLStack as

constructor function TPLSQLStack return self as result is   stack$ varchar2(4096) default dbms_utility.format_call_stack;   i$ integer;
  line$ varchar2(255);

  handle$ varchar2(50);
  lineno$ integer;
  object$ varchar2(200);

begin
  self.stack := TPLSQLStackTable();

  loop

     i$ := INSTR( stack$, chr(10) );
     exit when ( i$ is NULL or i$ = 0 );

     line$  := SUBSTR( stack$, 1, i$-1 );
     stack$ := SUBSTR( stack$, i$+1 );

     if INSTR( line$, 'PL/SQL Call Stack' ) > 0 then
        line$ := '';
     end if;

     if (line$ LIKE '%object%line%object%')  then
        line$ := '';
     end if;

     if (line$ LIKE '%handle%number%name') then
        line$ := '';
     end if;

     if line$ is not NULL then
        i$ := INSTR( line$, ' ' );
        handle$ := SUBSTR( line$, 1, i$-1 );
        lineno$ := TO_NUMBER( TRIM( SUBSTR( line$, i$+1, 9 ) ) );
        object$ := TRIM( SUBSTR( line$, i$+10 ) );
        self.stack.Extend;
        self.stack( self.stack.Last ) := TPLSQLStackItem( handle$,
object$,  lineno$ );
     end if;

   end loop;

   return;
end;

end; -- of type body
/
==

To use. e.g.
==

SQL> declare
  2 call TPLSQLStack;
  3 begin

  4     call := NEW TPLSQLStack();
  5     for i in 1..call.Stack.Count
  6     loop
  7             W( 'level:'|| i ||' object:'||
call.stack(i).object_name ||' line:'|| call.stack(i).line_number );
  8     end loop;

  9* end;
 10 /
level:1 object:TYPE.TPLSQLSTACK line:4
level:2 object:anonymous block line:4

PL/SQL procedure successfully completed.
==

I define a column with type TPLSQLStackTable in my applog table (as a nested table column). Then I simply whack the results of the TPLSQLStack stack member into there via my applog API. Very useful for debugging after the fact.

--
Billy

Received on Tue Aug 23 2005 - 01:20:55 CDT