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.
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.