Skip to Main Content
  • Questions
  • Dynamically Logging parameters of procedure or function

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ahmad.

Asked: October 23, 2016 - 9:47 am UTC

Last updated: October 24, 2016 - 1:33 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom.

Is there any way to log the parameters of functions and procedures inside package or as standalone objects without having to write every parameter name and its value.

I think of something generic where I can put into procedure/function and its automatically get parameters and their values and log them into table.

and Connor said...

Here's something that might help. I wrote this a while back because I was too lazy to add debugging :-) It just output's stuff you can cut-paste into your code:


SQL> CREATE OR REPLACE
  2  function param_debug(p_package_name varchar2, p_owner varchar2 default null) return clob is
  3    l_owner   varchar2(30);
  4    l_clob    clob;
  5    l_prev_obj varchar2(30) := '*';
  6
  7    procedure wrt(m varchar2) is
  8      x int := length(m);
  9    begin
 10      dbms_lob.writeappend( l_clob,x,m);
 11    end;
 12  begin
 13    dbms_lob.createtemporary(l_clob,true);
 14
 15    select owner into l_owner
 16    from  dba_objects
 17    where object_name = upper(p_package_name)
 18    and (
 19            ( p_owner is not null and owner = upper(p_owner) )
 20         or ( p_owner is null )
 21        )
 22    and   object_type in ('PROCEDURE','FUNCTION','PACKAGE');
 23
 24    for i in ( select rownum r, d.* , max(case when argument_name is not null             and
 25                                                    data_type not in ('REF','REF CURSOR') and
 26                                                    in_out in ('IN','IN/OUT')             and
 27                                                    data_level = 0 then
 28                                                          length(argument_name) else 0 end) over (partition by subprogram_id) +
 29                                      max(case when argument_name is not null  and
 30                                               data_type  in (  'PL/SQL TABLE'
 31                                                                  ,'TABLE'
 32                                                                  ,'VARRAY')    and
 33                                               in_out in ('IN','IN/OUT') and
 34                                               data_level = 0 then
 35                                               6 else 0 end) over (partition by subprogram_id) as arglen
 36               from dba_arguments d
 37               where package_name = upper(p_package_name)
 38               and   owner = l_owner
 39               order by subprogram_id, position
 40             ) loop
 41      if to_char(i.subprogram_id) != l_prev_obj then
 42         wrt(chr(10)||chr(10)||chr(10)||'  msg(''Start: '||i.object_name||''');'||chr(10));
 43         l_prev_obj := i.subprogram_id;
 44      end if;
 45
 46      if i.argument_name is not null             and
 47         i.data_type not in ('REF','REF CURSOR') and
 48         i.in_out in ('IN','IN/OUT')             and
 49         i.data_level = 0 then
 50
 51          if i.data_type in (  'BINARY_DOUBLE'
 52                              ,'BINARY_FLOAT'
 53                              ,'BINARY_INTEGER'
 54                              ,'PLS_INTEGER'
 55                              ,'CHAR'
 56                              ,'FLOAT'
 57                              ,'INTERVAL DAY TO SECOND'
 58                              ,'INTERVAL YEAR TO MONTH'
 59                              ,'NUMBER'
 60                              ,'RAW'
 61                              ,'ROWID'
 62                              ,'TIME'
 63                              ,'TIME WITH TIME ZONE'
 64                              ,'TIMESTAMP'
 65                              ,'TIMESTAMP WITH LOCAL TIME ZONE'
 66                              ,'TIMESTAMP WITH TIME ZONE'
 67                              ,'VARCHAR2'
 68                              ,'UROWID') then
 69            wrt('  msg('''||rpad(lower(i.argument_name),i.arglen)||'=>''||'||lower(i.argument_name)||');'||chr(10));
 70          elsif i.data_type = 'DATE' then
 71            wrt('  msg('''||rpad(lower(i.argument_name),i.arglen)||'=>''||to_char('||lower(i.argument_name)||',''yyyy-mm-dd hh24:mi:ss''));'||chr(10));
 72          elsif i.data_type = 'PL/SQL BOOLEAN' then
 73            wrt('  msg('''||rpad(lower(i.argument_name),i.arglen)||'=>''||case '||lower(i.argument_name)||' when false then ''FALSE'' when true then ''TRUE'' else ''NULL'' end);'||chr(10));
 74          elsif i.data_type in (  'PL/SQL TABLE'
 75                              ,'TABLE'
 76                              ,'VARRAY') then
 77            wrt('  msg('''||rpad(lower(i.argument_name||'.count'),i.arglen)||'=>''||'||lower(i.argument_name)||'.count);'||chr(10));
 78
 79          else
 80            wrt('  msg('''||rpad(lower(i.argument_name),i.arglen)||'=>***please fill in***);'||chr(10));
 81          end if;
 82      end if;
 83    end loop;
 84
 85    return l_clob;
 86  exception
 87    when too_many_rows then
 88       return 'More than one copy of package '||p_package_name||' found.  Please specify an owner as well '||chr(10)||
 89              'for example, PARAM_DEBUG(''MY_PKG'',''SCOTT'')';
 90  end;
 91  /

Function created.



So here is a package I've written

SQL>
SQL> create or replace
  2  package PKG is
  3    type t_assoc_array is table of int index by pls_integer;
  4
  5    procedure proc1(p_parm1 int, p_parm2 varchar2, p_parm3 date);
  6
  7    procedure proc2(p_parm1 int, p_parm2 t_assoc_array);
  8  end;
  9  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body PKG is
  3    procedure proc1(p_parm1 int, p_parm2 varchar2, p_parm3 date) is
  4    begin
  5      null;
  6    end;
  7
  8    procedure proc2(p_parm1 int, p_parm2 t_assoc_array) is
  9    begin
 10      null;
 11    end;
 12
 13  end;
 14  /

Package body created.


I call my "param_debug" procedure to give me some basic debugging code that I can cut/paste into the routine

SQL> select param_debug('PKG') from dual;

PARAM_DEBUG('PKG')
--------------------------------------------------------------------------------



  msg('Start: PROC1');
  msg('p_parm1=>'||p_parm1);
  msg('p_parm2=>'||p_parm2);
  msg('p_parm3=>'||to_char(p_parm3,'yyyy-mm-dd hh24:mi:ss'));



  msg('Start: PROC2');
  msg('p_parm1      =>'||p_parm1);
  msg('p_parm2.count=>'||p_parm2.count);


1 row selected.

SQL>


In my case, I had a generic "msg" routine that handled the debugging, but you can adapt this however you like obviously.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ahmad Refa'at, October 24, 2016 - 10:20 am UTC

Thank you Connor.

Good idea. Very Useful.

But I wish something like

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PROCEDURE proc1 (p_parm1 INT, p_parm2 VARCHAR2, p_parm3 DATE)
   IS
   BEGIN
      
      log_parameter('pkg.proc1');
        
      NULL;
   END;

   PROCEDURE proc2 (p_parm1 INT, p_parm2 r$emp)
   IS
   BEGIN
   
      log_parameter('pkg.proc2');
   
      NULL;
   END;
END;
/


I think it can be done if there is corresponding function for "name_in()" in Oracle Forms.

Is there function like name_in() in PL/SQL?
Chris Saxon
October 24, 2016 - 1:33 pm UTC

No. There isn't a function you can call to capture all the parameter to a procedure/function.

You might want to check out Logger, an open source logging API. It has functionality to log parameters. Though you need to pass them one by one:

https://github.com/OraOpenSource/Logger

Ahmad Refa'at, October 25, 2016 - 9:08 am UTC

Thank you.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.