Oracle动态记录过程或函数的参数

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.

这是我写的一个包裹

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.

我调用“Param_debug”过程，给我一些基本的调试代码，我可以将这些代码剪切/粘贴到例程中

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>

在我的例子中，我有一个通用的“msg”例程来处理调试，但是显然，您可以根据自己的喜好对其进行调整。