Wednesday, June 17, 2015

7 Ways to Log Data in the Oracle Database

Updated 14-Dec-2015: Corrected image for 6. Pipelined Function

Overview

  1. Insert into Table
  2. AUTONOMOUS_TRANSACTION
  3. DBMS_APPLICATION_INFO
  4. SET_SESSION_LONGOPS
  5. DBMS_OUTPUT
  6. Pipelined Function
  7. DBMS_PIPE
Each section contains:
  • Executable code
  • An example output of the code
  • "The Good"
  • "The Bad"
All code has been tested in SQL*Plus on Oracle11gXE. The following setup must be run as "SYS as SYSDBA". This setup must be complete before running code in any section.

create user demo identified by demo
   default tablespace users;

grant connect, resource to demo;
grant select on SYS.V_$SESSION to demo;
grant execute on DBMS_LOCK to demo;
grant execute on DBMS_PIPE to demo;
grant create job to demo;

connect demo/demo

create table demo_tab
   (dtm  timestamp(6)
   ,usr  varchar2(30)
   ,sid  number
   ,txt  varchar2(4000)
   ,loc  varchar2(4000)
   );

create trigger demo_tab_bi before insert
   on demo_tab for each row
begin
   if :new.dtm is null then
      :new.dtm := systimestamp;
   end if;
   if :new.usr is null then
      :new.usr := USER;
   end if;
   if :new.sid is null then
      :new.sid := sys_context('USERENV','SID');
   end if;
   if :new.loc is null then
      :new.loc := DBMS_UTILITY.FORMAT_CALL_STACK;
   end if;
end demo_tab_bi;
/

create procedure log_demo (in_text in varchar2)
is PRAGMA AUTONOMOUS_TRANSACTION;
begin
   insert into demo_tab (txt) values (in_text);
   commit;
end log_demo;
/

create or replace type pipeline_demo_tab
  as table of varchar2(40);
/

create or replace function pipeline_demo
 return pipeline_demo_tab pipelined as
begin
  pipe row('Logging Demo');
  for i in 1 .. 5 loop
    dbms_lock.sleep(1);    -- delay between iterations
    pipe row('  Test: ' || i);
  end loop;
end;
/

create package pipe_demo as
   PIPE_NAME    CONSTANT varchar2(15) := 'Logging Demo';
   status       number;
   procedure log(in_txt in demo_tab.txt%TYPE);
   procedure run_server;
   procedure stop_server;
end pipe_demo;
/

create package body pipe_demo as
procedure log(in_txt in demo_tab.txt%TYPE) is begin
   dbms_pipe.reset_buffer;
   dbms_pipe.pack_message('LOG');
   dbms_pipe.pack_message(to_char(systimestamp,'YYYYMMDDHH24MISS.FF6'));
   dbms_pipe.pack_message(USER);
   dbms_pipe.pack_message(to_number(sys_context('USERENV', 'SID')));
   dbms_pipe.pack_message(DBMS_UTILITY.FORMAT_CALL_STACK);
   dbms_pipe.pack_message(in_txt);
   status := dbms_pipe.send_message(PIPE_NAME);
end log;
procedure run_server is
   cmd_buff     varchar2(30);
   dtm_buff     varchar2(25);
   usr_buff     demo_tab.usr%TYPE;
   sid_buff     demo_tab.sid%TYPE;
   loc_buff     demo_tab.loc%TYPE;
   txt_buff     demo_tab.txt%TYPE;
begin
   insert into demo_tab (txt) values ('Server started');
   commit;
   loop
      loop -- Allow the PIPE Read timeout to prevent "freezing"
         status := dbms_pipe.receive_message (PIPE_NAME, 10);  -- seconds
         exit when status != 1;   -- Status 1 is a timeout error
      end loop;
      if status != 0 then
         insert into demo_tab (txt) values ('Received status ' || status);
         commit; exit;
      end if;
      dbms_pipe.unpack_message(cmd_buff);
      if upper(cmd_buff) = 'LOG' then
         dbms_pipe.unpack_message(dtm_buff);
         dbms_pipe.unpack_message(usr_buff);
         dbms_pipe.unpack_message(sid_buff);
         dbms_pipe.unpack_message(loc_buff);
         dbms_pipe.unpack_message(txt_buff);
         insert into demo_tab (dtm, usr, sid, loc, txt)
            values (to_timestamp(dtm_buff,'YYYYMMDDHH24MISS.FF6'),
               usr_buff, sid_buff, loc_buff, txt_buff);
         commit;
      else
         insert into demo_tab (txt) values ('Received ' || cmd_buff);
         commit; exit;
      end if;
   end loop;
end run_server;
procedure stop_server is begin
   dbms_pipe.reset_buffer;
   dbms_pipe.pack_message('STOP');
   status := dbms_pipe.send_message(PIPE_NAME);
end stop_server;
end pipe_demo;
/

1. Insert into Table

connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap

insert into demo_tab (txt) values ('I want to log this text');

select dtm, usr, sid, txt from demo_tab order by dtm desc;

rollback;

select dtm, usr, sid, txt from demo_tab order by dtm desc;


The Good:

  • Simple.
  • Reasonably fast.

The Bad:

  • Not necessarily persistent - a ROLLBACK can remove uncommitted records from the DEMO_TAB table.
  • Unavailable to "other" sessions until COMMIT.

2. Autonomous_Transaction

connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap

BEGIN
   log_demo('No rollback on this record');
   rollback;
END;
/

select dtm, usr, sid, txt from demo_tab order by dtm desc;


The Good:

  • Persistent - a ROLLBACK will not remove records from the DEMO_TAB table.
  • Immediately available to "other" sessions.

The Bad:

  • Requires a procedure or a package.
  • Performance penalty - Redolog Buffer Flush during COMMIT.

3. DBMS_APPLICATION_INFO

connect demo/demo
column sid format 99999
column client_info format a20
column module format a20
column action format a30

BEGIN
   DBMS_APPLICATION_INFO.set_client_info
      (client_info => 'Logging Demo');
   DBMS_APPLICATION_INFO.set_module
      (module_name => 'Demo3' 
      ,action_name => 'Temporary Entry'); 
END;
/

select sid, client_info, module, action from V$SESSION
 where client_info = 'Logging Demo';


The Good:

  • Simple.
  • Very fast.
  • Immediately available to "other" sessions.

The Bad:

  • Security - Need access to V$SESSION
  • Not persistent - V$SESSION data is automatically removed from memory.
  • Not really a log - Only 1 V$SESSION entry per session.

4. SET_SESSION_LONGOPS

connect demo/demo
column sid format 99999
column opname format a30
column sofar format 9999
column units format a5

DECLARE
   ri  binary_integer := dbms_application_info.set_session_longops_nohint;
   sn  binary_integer;   -- slno
BEGIN
   DBMS_APPLICATION_INFO.set_session_longops
      (rindex => ri, slno => sn, units => 'un',sofar => 0,
       op_name => 'Logging Demo', totalwork => 1);
   DBMS_APPLICATION_INFO.set_session_longops
      (rindex => ri, slno => sn , sofar => 1,
      totalwork => 1);
END;
/

select sid, opname, sofar, totalwork, units from v$session_longops
 where opname = 'Logging Demo' order by start_time desc;


The Good:

  • Very Fast.
  • Automatically calculates TIME_REMAINING.
  • Immediately available to "other" sessions.

The Bad:

  • Security - Need access to V$SESSION_LONGOPS.
  • Can require some thought to properly setup.
  • Not persistent - V$SESSION_LONGOPS data is automatically removed from memory.

5. DBMS_OUTPUT

connect demo/demo
set serveroutput on

BEGIN
   DBMS_OUTPUT.put_line('Logging Demo');
   for i in 1 .. 5 loop
      dbms_lock.sleep(1);    -- delay between iterations
      DBMS_OUTPUT.put_line('  Test: ' || i);
   end loop;
END;
/


The Good:

  • Simple.
  • Very Fast.
  • Can read DBMS_OUTPUT buffer within session PL/SQL.

The Bad:

  • Not persistent - Stored in DBMS_OUTPUT buffers.
  • Client must wait for call to complete before reading buffer.
  • Never available to "other" sessions.

6. Pipelined Function

connect demo/demo
set arraysize 1

select * from table(pipeline_demo);


The Good:

  • Fast.
  • Results returned while PL/SQL is executing.

The Bad:

  • Not persistent - Results returned via SQL SELECT.
  • Must hide DML behind autonomous_transaction
  • Strange way to run PL/SQL, using an SQL SELECT.
  • Never available to "other" sessions.

7. DBMS_PIPE

connect demo/demo
alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF6';
column dtm format a27
column usr format a4
column sid format 99999
column txt format a40 wrap

BEGIN
   DBMS_SCHEDULER.create_job
      (job_name   => 'pipe_demo_server'
      ,job_type   => 'PLSQL_BLOCK'
      ,job_action => 'BEGIN pipe_demo.run_server; END;'
      ,enabled    => TRUE);END;
/

--  PAUSE HERE to allow the job to start
select job_name, session_id from user_scheduler_running_jobs;

BEGIN
   log_demo('Sending message on pipe.');
   pipe_demo.log('This is the pipe message');
   log_demo('Sent message on pipe.');
   pipe_demo.stop_server;
END;
/

--  PAUSE HERE to allow the job to complete
select job_name, session_id from user_scheduler_running_jobs;

select dtm, usr, sid, txt from demo_tab order by dtm desc;


The Good:

  • Very Fast.
  • If configured, can be available to multiple "other" sessions.

The Bad:

  • Complicated to Setup.
  • Not persistent while in the pipe.
  • Reading a pipe can hang, causing the database to "freeze'.

Cleanup

Cleanup code must be run as "SYS" or "SYSTEM".

BEGIN
   demo.pipe_demo.stop_server;
END;
/

drop user demo cascade;


Posted by at No comments:
Labels: , , , , , , ,

Wednesday, June 3, 2015

PL/SQL Call Graphs from DBA_IDENTIFIERS Data

Note: This article was formerly called "PL/SQL Call Trees from DBA_IDENTIFIERS Data".  For the purpose of this article, Call Tree, Call Graph, and Call Hierarchy are all the same.  (http://en.wikipedia.org/wiki/Call_graph)

Steven Feuerstein has an excellent article on "Use PL/Scope to analyze your PL/SQL". The PL/Scope functionality became available in Oracle11g. Its basic function is to populate the DBA_IDENTIFIERS table with all PL/SQL identifiers found while compiling database objects. Steven's post goes on to show an "Identifier Hierarchy" of variables, parameters, and other components of a particular package body.

Many years after Steven's post, I began supporting some rather complex PL/SQL in Oracle11g. I found a great tool called ClearDB Documenter 4 that would (among other things) display a "PL/SQL Call Tree". This was extremely handy for understanding the relationships between the many packages on the system.  However, I wanted a way to get the PL/SQL Call Tree in an SQL report that I could further manipulate.  I was able to coax the needed information from DBA_IDENTIFIERS, but it wasn't easy.

At the heart of the problem is a lack of documentation on the contents of DBA_IDENTIFIERS. After some reverse-engineering, I developed these guidelines for the contents of DBA_IDENTIFIERS.
  1. OWNER, OBJECT_NAME, and OBJECT_TYPE are unique database objects.
  2. USAGE_ID is a unique location and usage within a unique database object.
  3. USAGE_CONTEXT_ID points to a USAGE_ID within a unique database object.
  4. USAGE_CONTEXT_ID = 0 for top level database object "DECLARATION".
  5. USAGE_ID with "CALL" identifies the calling program location.
  6. SIGNATURE with "CALL" identifies the called program name and type.
  7. SIGNATURE with "DECLARATION" is globally unique.
  8. SIGNATURE with "DEFINITION" can be redefined and may not exist for all objects.
  9. SIGNATURE is the same for package specification and body.
  10. DBA_IDENTIFIERS is indexed on OBJ# and SIGNATURE.
  11. OBJECT_NAME matches NAME and OBJECT_TYPE matches TYPE for functions and procedures that are not in packages.
  12. "DECLARATION" and "DEFINITION" for synonyms and triggers match OBJECT_NAME with NAME and OBJECT_TYPE with TYPE.
  13. "DECLARATION" for packages match OBJECT_NAME with NAME and OBJECT_TYPE with TYPE
  14. "DEFINITION" for packages match OBJECT_NAME with NAME and OBJECT_TYPE = "PACKAGE BODY" and TYPE = "PACKAGE"
With these guidelines, I was able to construct some queries for the PL/SQL call tree.  However, the queries took too long to run.  So, I developed these actions to enable faster access to the data in DBA_IDENTIFIERS...


UPDATE (28-Mar-2016): Added this section to recompile all source.

Recompile All Source

(Run this as system)

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
begin
   for buff in (
      select username from dba_users
    --  where nvl(oracle_maintained,'N' = 'N') -- for 12c
         and  (   owner not in ('ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'DIP',
                                'FLOWS_FILES', 'HR', 'MDSYS', 'ORACLE_OCM',
                                'ORDS_METADATA', 'ORDS_PUBLIC_USER', 'OUTLN', 
                                'SCOTT', 'SYS', 'SYSTEM', 'XDB', 'XS$NULL')
               or owner not like 'APEX\_' escape '\');  -- for 11gXE
   ) loop
      DBMS_UTILITY.compile_schema (
         schema          => buff.username,
         compile_all     => TRUE,
         reuse_settings  => FALSE);
      for trig in (
         select object_name from dba_objects
          where object_type = 'TRIGGER'
           and  owner = buff.username           )
      loop
         begin
            execute immediate 'ALTER TRIGGER ' || buff.username ||
                         '.' || trig.object_name || ' COMPILE';
         exception when others then null;
         end;
      end loop;
   end loop;
end;
/



UPDATE (28-Mar-2016): Added this function to compute un-named PL/SQL block start line in trigger source.

Create Trigger Offset Function

UPDATE (29-Dec-2016): Added this grant for the function below.
(Run this as sys): grant select on dba_source to system with grant option;

(Run this as system)

create FUNCTION temp_trigger_offset
      (dout_name_in   in  varchar2
      ,dout_type_in   in  varchar2
      ,dout_owner_in  in  varchar2)
   return number
   --AUTHID DEFINER
   AUTHID CURRENT_USER
IS
BEGIN
   if nvl(dout_type_in,'BOGUS') <> 'TRIGGER' then
      --dbms_output.put_line('TEMP_TRIGGER_OFFSET: Not a TRIGGER: ' || dout_type_in);
      return 0;
   end if;
   for buff in (
      select line, text from dba_source
       where name  = dout_name_in
        and  type  = dout_type_in
        and  owner = dout_owner_in
      order by line )
   loop
      if regexp_instr(buff.text,
                      '(^declare$' ||
                      '|^declare[[:space:]]' ||
                      '|[[:space:]]declare$' ||
                      '|[[:space:]]declare[[:space:]])', 1, 1, 0, 'i') <> 0
         OR
         regexp_instr(buff.text,
                      '(^begin$' ||
                      '|^begin[[:space:]]' ||
                      '|[[:space:]]begin$' ||
                      '|[[:space:]]begin[[:space:]])', 1, 1, 0, 'i') <> 0 
      then
         --dbms_output.put_line('TEMP_TRIGGER_OFFSET: ' || buff.line - 1');
         return buff.line - 1;
      end if;
   end loop;
   --dbms_output.put_line('TEMP_TRIGGER_OFFSET: Did not find DECLARE or BEGIN');
   return 0;
END temp_trigger_offset;
/


UPDATE (13-Jan-2016): In order to capture all cross-schema references, run these statements as SYSTEM or another DBA user. Tables are created with the prefix "TEMP_" in the name. There is a CLEANUP at the bottom.

UPDATE (28-Mar-2016): Added SYSAUX tablespaces, TEMP_DBA_SOURCE table, and table/column comments.

Copy Some Tables

Make a copy of DBA_IDENTIFIERS and index it for speed. (Run this as system)

create table temp_dba_identifiers
 tablespace SYSAUX
 as
 select * from dba_identifiers;
create unique index temp_dba_identifiers$uk1
 on temp_dba_identifiers (owner, object_name, object_type, usage_id)
 tablespace SYSAUX;
create index temp_dba_identifiers$ix1
 on temp_dba_identifiers (signature)
 tablespace SYSAUX;
comment on table temp_dba_identifiers is
 'Static, indexed version of information about the identifiers in all stored objects in the database';
comment on column temp_dba_identifiers.OWNER is
 'Required owner of the identifier';
comment on column temp_dba_identifiers.NAME is
 'Name of the identifier';
comment on column temp_dba_identifiers.SIGNATURE is
 'Signature of the identifier';
comment on column temp_dba_identifiers.TYPE is
 'Type of the identifier';
comment on column temp_dba_identifiers.OBJECT_NAME is
 'Required name of the object where the identifier action occurred';
comment on column temp_dba_identifiers.OBJECT_TYPE is
 'Type of the object where the identifier action occurred';
comment on column temp_dba_identifiers.USAGE is
 'Type of the identifier usage: DECLARATION, DEFINITION, CALL, REFERENCE, ASSIGNMENT';
comment on column temp_dba_identifiers.USAGE_ID is
 'Unique key for the identifier usage within the object';
comment on column temp_dba_identifiers.LINE is
 'Line number of the identifier action';
comment on column temp_dba_identifiers.COL is
 'Column number of the identifier action';
comment on column temp_dba_identifiers.USAGE_CONTEXT_ID is
 'Context USAGE_ID of the identifier usage';
--comment on column temp_dba_identifiers.ORIGIN_CON_ID is
-- 'The ID of the container where the data originates. Possible values include: 0: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)';  -- Oracle 12c

Make a copy of DBA_SOURCE and index it for speed.

create table temp_dba_source
 tablespace SYSAUX
 as
 select * from dba_source;
create index temp_dba_source$ix1
 on temp_dba_source (owner, name, type, line)
 tablespace SYSAUX;
comment on table temp_dba_source is
 'Static, indexed version of text source descriptions of all stored objects in the database';
comment on column temp_dba_source.OWNER is
 'Required owner of the object';
comment on column temp_dba_source.NAME is
 'Required name of the object';
comment on column temp_dba_source.TYPE is
 'Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY';
comment on column temp_dba_source.LINE is
 'Required line number of this line of source';
comment on column temp_dba_source.TEXT is
 'Text source of the stored object';
--comment on column temp_dba_source.ORIGIN_CON_ID is
-- 'The ID of the container where the data originates. Possible values include: 0: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)';  -- Oracle 12c

Make a copy of DBA_DEPENDENCIES and index it for speed.

create table temp_dba_dependencies
 tablespace SYSAUX
 as
 select * from dba_dependencies
 where referenced_type in ('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW')
 -- and  owner not in (select username from dba_users
 --                    where oracle_maintained = 'Y' );  -- for 12c
  and  (   owner not in ('ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'DIP',
                         'FLOWS_FILES', 'HR', 'MDSYS', 'ORACLE_OCM',
                         'ORDS_METADATA', 'ORDS_PUBLIC_USER', 'OUTLN', 
                         'SCOTT', 'SYS', 'SYSTEM', 'XDB', 'XS$NULL')
        or owner not like 'APEX\_' escape '\');  -- for 11gXE
create index temp_dba_dependencies$ix1
 on temp_dba_dependencies (owner, name, type)
 tablespace SYSAUX;
create index temp_dba_dependencies$ix2
 on temp_dba_dependencies (referenced_owner, referenced_name, referenced_type)
 tablespace SYSAUX;
comment on table temp_dba_dependencies is
 'Static, indexed version of all dependencies between selected objects (TABLE,VIEW,SYNONYM,MATERIALIZED VIEW) in the database. This view does not display the SCHEMAID column';
comment on column temp_dba_dependencies.OWNER is
 'Required owner of the object';
comment on column temp_dba_dependencies.NAME is
 'Name of the object';
comment on column temp_dba_dependencies.TYPE is
 'Type of the object';
comment on column temp_dba_dependencies.REFERENCED_OWNER is
 'Owner of the referenced object (remote owner if remote object)';
comment on column temp_dba_dependencies.REFERENCED_NAME is
 'Name of the referenced object';
comment on column temp_dba_dependencies.REFERENCED_TYPE is
 'Type of the referenced object';
comment on column temp_dba_dependencies.REFERENCED_LINK_NAME is
 'Name of the link to the parent object (if remote)';
comment on column temp_dba_dependencies.DEPENDENCY_TYPE is
 'Indicates whether the dependency is a REF dependency (REF) or not (HARD)';

Main Call Tree Table

Create the main call tree table
  • SRC - identifiers are all about this, the source code.
  • TGT - The target of the SRC usage, like a reference or a call
  • CTX - The current context of the source code, what SRC this SRC is under
  • PAR - The parent context is a function, procedure, or package this CTX is under
create table temp_ident_assocs
 tablespace SYSAUX
 as
select tgt.owner                  tgt_object_owner
      ,tgt.object_type            tgt_object_type
      ,tgt.object_name            tgt_object_name
      ,tgt.name                   tgt_name
      ,tgt.type                   tgt_type
      ,src.owner                  src_object_owner
      ,src.object_type            src_object_type
      ,src.object_name            src_object_name
      ,src.usage                  par_usage        -- BOGUS placed holder data
      ,src.name                   par_name         -- BOGUS placed holder data
      ,src.type                   par_type         -- BOGUS placed holder data
      ,ctx.usage                  ctx_usage
      ,ctx.name                   ctx_name
      ,ctx.type                   ctx_type
      ,src.usage                  src_usage
      ,src.name                   src_name
      ,src.type                   src_type
      ,case when src.object_type = 'TRIGGER'
            then src.line + temp_trigger_offset
                                 (src.object_name
                                 ,src.object_type
                                 ,src.owner      )
            else src.line end     src_line
      ,src.col                    src_col
      ,dbas.text                  src_text
      ,tgt.signature              tgt_signature
      ,ctx.signature              par_signature
      ,ctx.signature              ctx_signature
      ,src.signature              src_signature
      ,src.usage_id               src_usage_id
 from  temp_dba_identifiers src
  left join temp_dba_identifiers tgt on (tgt.signature = src.signature)
                                     and tgt.usage = 'DECLARATION'
  left join temp_dba_identifiers ctx on (ctx.owner        = src.owner
                                     and ctx.object_name  = src.object_name
                                     and ctx.object_type  = src.object_type
                                     and ctx.usage_id     = src.usage_context_id)
  left join temp_dba_source dbas on (dbas.owner  = src.owner
                                 and dbas.type   = src.object_type
                                 and dbas.name   = src.object_name
                                 and dbas.line   = src.line);
create index temp_ident_assocs$ix1
 on temp_ident_assocs (tgt_object_name, tgt_object_type, tgt_object_owner)
 tablespace SYSAUX;
create index temp_ident_assocs$ix2
 on temp_ident_assocs (src_object_owner, src_object_name, src_object_type, src_line)
 tablespace SYSAUX;
create index temp_ident_assocs$ix3
 on temp_ident_assocs (src_object_name, src_object_type, src_object_owner, src_usage_id)
 tablespace SYSAUX;
create index temp_ident_assocs$ix4
 on temp_ident_assocs (tgt_signature, src_usage)
 tablespace SYSAUX;
create index temp_ident_assocs$ix5
 on temp_ident_assocs (src_signature)
 tablespace SYSAUX;
create index temp_ident_assocs$ix6
 on temp_ident_assocs (ctx_signature)
 tablespace SYSAUX;
comment on column temp_ident_assocs.tgt_object_owner is
  'Target database object owner that is being called or referenced';
comment on column temp_ident_assocs.tgt_object_type is
  'Target database object type that is being called or referenced';
comment on column temp_ident_assocs.tgt_object_name is
  'Target database object name that is being called or referenced';
comment on column temp_ident_assocs.tgt_name is
  'Target element name (procedure, function, etc.) being called or referenced';
comment on column temp_ident_assocs.tgt_type is
  'Target element type (procedure, function, etc.) being called or referenced';
comment on column temp_ident_assocs.src_object_owner is
  'Database object owner that is the source of the call or reference';
comment on column temp_ident_assocs.src_object_type is
  'Database object type that is the source of the call or reference';
comment on column temp_ident_assocs.src_object_name is
  'Database object name that is the source of the call or reference';
comment on column temp_ident_assocs.par_usage is
  'Parent context usage (call, reference, etc) of the source of the call or reference';
comment on column temp_ident_assocs.par_name is
  'Parent context name of the source of the call or reference';
comment on column temp_ident_assocs.par_type is
  'Parent context type of the source of the call or reference';
comment on column temp_ident_assocs.ctx_usage is
  'Context usage (call, reference, etc) of the source of the call or reference';
comment on column temp_ident_assocs.ctx_name is
  'Context name of the source of the call or reference';
comment on column temp_ident_assocs.ctx_type is
  'Context type of source of the call or reference';
comment on column temp_ident_assocs.src_usage is
  'Usage (call, reference, etc) of the source of the call or reference';
comment on column temp_ident_assocs.src_name is
  'Name of the source of the call or reference';
comment on column temp_ident_assocs.src_type is
  'Type of the source of the call or reference';
comment on column temp_ident_assocs.src_line is
  'Database object line number of the source of the call or reference';
comment on column temp_ident_assocs.src_col is
  'Database object column number of the source of the call or reference';
comment on column temp_ident_assocs.src_text is
  'Text of the source  of the call or reference';
comment on column temp_ident_assocs.tgt_signature is
  'Target signature that is being called or referenced';
comment on column temp_ident_assocs.par_signature is
  'Parent context signature of the source of the call or reference';
comment on column temp_ident_assocs.ctx_signature is
  'Context signature of the source of the call or reference';
comment on column temp_ident_assocs.src_signature is
  'Signature of the source of the call or reference';
comment on column temp_ident_assocs.src_usage_id is
  'Unique location and usage within a database object';

Populate as much parent context as possible from the same source line (fast)

update temp_ident_assocs dia
  set  (par_usage, par_name, par_type, par_signature) = (
       select ctx.ctx_usage, ctx.ctx_name, ctx.ctx_type, ctx.ctx_signature
        from  temp_ident_assocs ctx
        where ctx.ctx_type         in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
         and  ctx.ctx_usage        in ('DECLARATION','DEFINITION')
         and  ctx.src_object_owner = dia.src_object_owner
         and  ctx.src_object_type  = dia.src_object_type
         and  ctx.src_object_name  = dia.src_object_name
         and  ctx.src_line         = dia.src_line
         and  ctx.src_usage_id     = (
              select max(mc.src_usage_id) from temp_ident_assocs mc
               where mc.ctx_type         in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
                and  mc.ctx_usage        in ('DECLARATION','DEFINITION')
                and  mc.src_object_owner = dia.src_object_owner
                and  mc.src_object_type  = dia.src_object_type
                and  mc.src_object_name  = dia.src_object_name
                and  mc.src_line         = dia.src_line
                and  mc.src_usage_id    <= dia.src_usage_id ) );


Populate all other parent context from previous source lines (slow)

update temp_ident_assocs dia
  set  (par_usage, par_name, par_type, par_signature) = (
       select ctx.ctx_usage, ctx.ctx_name, ctx.ctx_type, ctx.ctx_signature
        from  temp_ident_assocs ctx
        where ctx.ctx_type         in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
         and  ctx.ctx_usage        in ('DECLARATION','DEFINITION')
         and  ctx.src_object_owner = dia.src_object_owner
         and  ctx.src_object_type  = dia.src_object_type
         and  ctx.src_object_name  = dia.src_object_name
         and  ctx.src_usage_id     = (
              select max(mc.src_usage_id) from temp_ident_assocs mc
               where mc.ctx_type         in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
                and  mc.ctx_usage        in ('DECLARATION','DEFINITION')
                and  mc.src_object_owner = dia.src_object_owner
                and  mc.src_object_type  = dia.src_object_type
                and  mc.src_object_name  = dia.src_object_name
                and  mc.src_usage_id    <= dia.src_usage_id ) )
 where par_signature is null;


Add the missing context for declarations and definitions

update temp_ident_assocs
  set  ctx_usage     = src_usage
      ,ctx_name      = src_name
      ,ctx_type      = src_type
      ,ctx_signature = src_signature
      ,par_usage     = src_usage
      ,par_name      = src_name
      ,par_type      = src_type
      ,par_signature = src_signature
 where src_usage     in ('DECLARATION','DEFINITION')
  and  ctx_signature is null
  and  tgt_signature = src_signature;


This appears to be a bug in PLSCOPE because some context_usage_id's don't exist.  So, we will fix that.

update temp_ident_assocs dia
  set  ctx_usage     = par_usage
      ,ctx_name      = par_name
      ,ctx_type      = par_type
      ,ctx_signature = par_signature
 where ctx_signature is null
  and  par_signature is not null;


Create the last index on temp_ident_assocs

create index temp_ident_assocs$ix7
 on temp_ident_assocs (par_signature, src_usage);

Call Tree Query

Done!  Now to run a PL/SQL Call Tree Query.  Pick a function or procedure in a package that calls other functions and/or procedures.  Run the following SQL with ":OWNER" as the package owner, ":PKG_NAME" as the package name, and ":PROC_NAME" as the function or procedure name.  (Updated SQL 04-Jun-2015).

with par as (
select * from temp_ident_assocs
 where src_object_owner = upper(:OWNER)
  and  src_object_name  = upper(:PKG_NAME)
  and  src_name         = upper(:PROC_NAME)
  and  src_usage        = 'DECLARATION'
), nodes as (
select tgt_signature
      ,par_signature
      ,src_object_owner calling_owner
      ,src_object_name  || '.' ||
       par_name         calling_name
      ,par_type         calling_type
      ,tgt_object_owner called_owner
      ,tgt_object_name  || '.' ||
       tgt_name         called_name
      ,tgt_type         called_type
      ,src_line
      ,src_text
 from  temp_ident_assocs
 where src_usage = 'CALL'
  and  tgt_type != 'CURSOR'
  and  tgt_object_owner != 'SYS'
)
--select * from par;
--select * from nodes;
select calling_owner
      ,LPAD('-| ', (Level-1)*3, '-|-') || calling_name    calling_name
      ,src_line
      ,called_type
      ,called_name
      ,called_owner
 from  nodes
 START WITH par_signature in (select src_signature from par)
 CONNECT BY NOCYCLE par_signature = PRIOR tgt_signature
 order siblings by src_line;


Reverse Call Tree Query

We can also run a reverse call tree query. Pick a function or procedure in a package that is called by other functions and/or procedures.  Again, set the ":OWNER" as the package owner, ":PKG_NAME" as the package name, and ":PROC_NAME" as the function or procedure name.  (Updated SQL 04-Jun-2015)

with tgt as (
select * from temp_ident_assocs
 where src_object_owner = upper(:OWNER)
  and  src_object_name  = upper(:PKG_NAME)
  and  src_name         = upper(:PROC_NAME)
  and  src_usage        = 'DECLARATION'
), nodes as (
select tgt_signature
      ,par_signature
      ,src_object_owner calling_owner
      ,src_object_name  || '.' ||
       par_name         calling_name
      ,par_type         calling_type
      ,tgt_object_owner called_owner
      ,tgt_object_name  || '.' ||
       tgt_name         called_name
      ,tgt_type         called_type
      ,src_line
      ,src_text
 from  temp_ident_assocs
 where src_usage = 'CALL'
  and  tgt_type != 'CURSOR'
  and  tgt_object_owner != 'SYS'
)
--select * from tgt;
--select * from nodes;
select rownum
      ,called_owner
      ,substr(SYS_CONNECT_BY_PATH(called_name, ' <- '),4) || ' <-'
                             called_path
      ,calling_name
      ,calling_type
      ,calling_owner
      ,src_line
      ,src_text
 from  nodes
 START WITH tgt_signature in (select src_signature from tgt)
 CONNECT BY NOCYCLE tgt_signature = PRIOR par_signature
 order siblings by src_line;


BONUS: Procedure/Function Lines in a Package

UPDATE (28-Mar-2016): Modified and renamed this table to use line/column position instead of line.

Ever wondered how to determine which DBA_SOURCE lines belong to a procedure/function in a package?  This table does that...

create table temp_proc_pos
 tablespace SYSAUX
 as
select src_object_owner    object_owner
      ,src_object_name     object_name
      ,src_object_type     object_type
      ,par_name            proc_name
      ,par_type            proc_type
      ,src_signature       proc_signature
      ,src_line            beg_line
      ,src_line            end_line
      ,src_line            beg_pos
      ,src_line            end_pos
 from  temp_ident_assocs
 where 0 = 1;
DECLARE
   cursor main is
      select src_object_owner, src_object_name, src_object_type,
             par_name, par_type, par_signature, src_line, src_col
       from  temp_ident_assocs
       where src_usage in ('DECLARATION','DEFINITION')
        and  src_object_type not in ('PACKAGE','TYPE','SYNONYM')
      order by src_object_owner, src_object_name, src_object_type,
             src_line, src_col;
   type  main_nt_type is table of main%ROWTYPE;
   prev       main%ROWTYPE;
   curr       main%ROWTYPE;
   cnt        number;
   recs       number;
   function get_last_oline return number is
      oline  number;
   begin
      select max(line) into oline from temp_dba_source
       where owner = prev.src_object_owner
        and  name  = prev.src_object_name
        and  type  = prev.src_object_type;
      return oline;
   end get_last_oline;
   function get_last_ocol (in_last_oline in number) return number is
      ocol   number;
   begin
      select length(text) into ocol from temp_dba_source
       where owner = prev.src_object_owner
        and  name  = prev.src_object_name
        and  type  = prev.src_object_type
        and  line  = in_last_oline;
      return ocol;
   end get_last_ocol;
   procedure ins_rec (in_end_line in number, in_end_col in number) is
      lv_beg_pos   number;
      lv_end_pos   number;
      lv_end_line  number;
      lv_end_col   number;
   begin
      IF in_end_col = 1 THEN
         lv_end_line := in_end_line - 1;
         lv_end_col  := get_last_ocol(lv_end_line);
      ELSE
         lv_end_line := in_end_line;
         lv_end_col  := in_end_col - 1;
      END IF;
      lv_beg_pos := prev.src_line + prev.src_col/1000000;
      lv_end_pos := lv_end_line + lv_end_col/1000000;
      if lv_beg_pos < lv_end_pos then
         insert into temp_proc_pos
               (object_owner
               ,object_name
               ,object_type
               ,proc_name
               ,proc_type
               ,proc_signature
               ,beg_line
               ,end_line
               ,beg_pos
               ,end_pos)
          values
               (prev.src_object_owner
               ,prev.src_object_name
               ,prev.src_object_type
               ,prev.par_name
               ,prev.par_type
               ,prev.par_signature
               ,prev.src_line
               ,lv_end_line
               ,lv_beg_pos
               ,lv_end_pos);
         recs := recs + 1;
      end if;
   end ins_rec;
BEGIN
   recs := 0;
   cnt  := 0;
   open main;
   fetch main into curr;
   prev := curr;
   WHILE main%FOUND LOOP
      IF prev.src_object_owner != curr.src_object_owner OR
         prev.src_object_name  != curr.src_object_name  OR
         prev.src_object_type  != curr.src_object_type
      THEN
         ins_rec(get_last_oline, get_last_ocol(get_last_oline)+1);
         prev := curr;
      ELSIF prev.par_signature != curr.par_signature
      THEN
         ins_rec(curr.src_line, curr.src_col);
         prev := curr;
      END IF;
      cnt := cnt + 1;
      fetch main into curr;
   END LOOP;
   if prev.src_object_name is not null then
      ins_rec(get_last_oline, get_last_ocol(get_last_oline)+1);
   end if;
   close main;
   dbms_output.put_line('cnt = '||cnt||', recs = '||recs);
END;
/
create index temp_proc_pos$ix1
 on temp_proc_pos(object_owner, object_name, object_type, beg_pos)
 tablespace SYSAUX;
comment on table temp_proc_pos is
 'Static, indexed version of procedure and function locations within source code in the database.';
comment on column temp_proc_pos.object_owner is
  'Database source object owner';
comment on column temp_proc_pos.object_type is
  'Database source object type';
comment on column temp_proc_pos.object_name is
  'Database source object name';
comment on column temp_proc_pos.proc_name is
  'Procedure or function name';
comment on column temp_proc_pos.proc_type is
  'Procedure or function type';
comment on column temp_proc_pos.proc_signature is
  'Procedure or function signature from ALL_IDENTIFIERS';
comment on column temp_proc_pos.beg_line is
  'Beginning line number in the source';
comment on column temp_proc_pos.end_line is
  'Ending line number in the source';
comment on column temp_proc_pos.beg_pos is
  'Beginning position in the source.  Position is line number + (column position/1000000)';
comment on column temp_proc_pos.end_pos is
  'Ending position in the source.  Position is line number + (column position/1000000)';

DOUBLE BONUS: Tables used by functions and procedures

UPDATE (31-Mar-2016): Modified data in this table to use line/column position instead of line.

How about matching database tables with functions and procedures?   The following script creates a table that does, almost.  It uses a simple string match, so it may return extra source lines that don't reference a database table.  The source text is included to determine if it is not a table.

create table temp_referenced_tabs
 tablespace SYSAUX
 as
select proc.object_owner    object_owner
      ,proc.object_name     object_name
      ,proc.object_type     object_type
      ,proc.proc_name       proc_name
      ,proc.proc_type       proc_type
      ,proc.proc_signature  proc_signature
      ,proc.object_owner    referenced_owner
      ,proc.object_name     referenced_name
      ,proc.object_type     referenced_type
      ,src.line             line
      ,src.line             col
      ,src.text             text
 from  temp_proc_pos proc cross join temp_dba_source src
 where 1 = 0;
DECLARE
   fnd_col  number;
BEGIN
   for procs in (
      select * from temp_proc_pos
       order by object_owner, object_name, object_type, beg_pos)
   loop
      for deps in (
         select * from temp_dba_dependencies  dep
          where dep.owner = procs.object_owner
           and  dep.name  = procs.object_name
           and  dep.type  = procs.object_type )
      loop
         for srcs in (
            select * from temp_dba_source  src
             where src.owner      = procs.object_owner
              and  src.name       = procs.object_name
              and  src.type       = procs.object_type
              and  src.text      is not null
              and  src.line between trunc(procs.beg_pos)
                                and trunc(procs.end_pos) )
          loop
             fnd_col :=
                regexp_instr
                  (srcs.text
                  ,'[[:space:],]'||deps.referenced_name||'[[:space:],]' || '|' ||
                   '[[:space:],]'||deps.referenced_name||'$'            || '|' ||
                              '^'||deps.referenced_name||'[[:space:],]' || '|' ||
                              '^'||deps.referenced_name||'$'
                  ,1,1,0,'i');
             if fnd_col > 0    AND
                (srcs.line + fnd_col/1000000) between procs.beg_pos
                                                  and procs.end_pos
             then
               insert into temp_referenced_tabs
                     (object_owner
                     ,object_name
                     ,object_type
                     ,proc_name
                     ,proc_type
                     ,proc_signature
                     ,referenced_owner
                     ,referenced_name
                     ,referenced_type
                     ,line
                     ,col
                     ,text)
                   values
                     (procs.object_owner
                     ,procs.object_name
                     ,procs.object_type
                     ,procs.proc_name
                     ,procs.proc_type
                     ,procs.proc_signature
                     ,deps.referenced_owner
                     ,deps.referenced_name
                     ,deps.referenced_type
                     ,srcs.line
                     ,fnd_col
                     ,srcs.text);
             end if;
          end loop;
      end loop;
      commit;
   end loop;
END;
/
create index temp_referenced_tabs$ix1
 on temp_referenced_tabs (object_name, object_type, object_owner)
 tablespace SYSAUX;
create index temp_referenced_tabs$ix2
 on temp_referenced_tabs (referenced_name, referenced_type, referenced_owner)
 tablespace SYSAUX;
comment on table temp_referenced_tabs is
 'Static, indexed version of procedure and function references to table-like objects in the database.';
comment on column temp_referenced_tabs.object_owner is
  'Database source object owner';
comment on column temp_referenced_tabs.object_type is
  'Database source object type';
comment on column temp_referenced_tabs.object_name is
  'Database source object name';
comment on column temp_referenced_tabs.proc_name is
  'Procedure or function name';
comment on column temp_referenced_tabs.proc_type is
  'Procedure or function type';
comment on column temp_referenced_tabs.proc_signature is
  'Procedure or function signature from ALL_IDENTIFIERS';
comment on column temp_referenced_tabs.referenced_owner is
  'Database source object owner of the object being directly referenced';
comment on column temp_referenced_tabs.referenced_type is
  'Database source object type of the object being directly referenced';
comment on column temp_referenced_tabs.referenced_name is
  'Database source object name of the object being directly referenced';
comment on column temp_referenced_tabs.line is
  'Line number in the source';
comment on column temp_referenced_tabs.col is
  'Column number in the source';
comment on column temp_referenced_tabs.text is
  'Text of the source';

Cleanup

drop table temp_referenced_tabs;
drop table temp_proc_pos;
drop table temp_ident_assocs;
drop table temp_dba_dependencies;
drop table temp_dba_source;
drop table temp_dba_identifiers;
drop function temp_trigger_offset;

Posted by at 4 comments:
Labels: , , , , , , ,
Subscribe to: Posts (Atom)