How to get a real signature using% ROWTYPE

Question

How to get a real signature for a PLSQL procedure that uses the% ROWTYPE arguments?

For instance:

clear screen; prompt > Table creation to support %ROWTYPE create table samples ( id number, code varchar2(15), lib varchar2(200) ); prompt > Package witch use %ROWTYPE create or replace package use_samples as procedure getSample(input_sample samples%ROWTYPE); end use_samples; / prompt > Package BODY witch use %ROWTYPE create or replace package body use_samples as procedure getSample(input_sample IN samples%ROWTYPE) is ex samples%ROWTYPE; begin select * into ex from samples where samples.code = input_sample.code; end getSample; end use_samples; / prompt > Proc arguments by ALL_ARGUMENTS set pagesize 50000 set linesize 2000 set verify off CLEAR COLUMNS; COLUMN object_name HEADING "PROC" FORMAT A30 JUSTIFY LEFT; COLUMN argument_name HEADING "ARGUMENT_NAME" FORMAT A30 JUSTIFY LEFT; select object_name, argument_name, in_out, data_level, position, data_type from all_arguments where owner = USER and package_name = 'USE_SAMPLES' and object_name = 'GETSAMPLE'; prompt >> Argument 'INPUT_SAMPLE' is shown as 'PL/SQL RECORD' without any link to 'samples%ROWTYPE' prompt > PLSQL types declared select * from DBA_PLSQL_TYPES where owner = USER and package_name = 'USE_SAMPLES'; prompt >> There is no declared type because we use directly a %ROWTYPE argument prompt > Clean up drop package use_samples; drop table samples; 

gives:

 > Table creation to support %ROWTYPE Table SAMPLES created. > Package witch use %ROWTYPE Package USE_SAMPLES compiled > Package BODY witch use %ROWTYPE Package body USE_SAMPLES compiled > Proc arguments by ALL_ARGUMENTS columns cleared PROC ARGUMENT_NAME IN_OUT DATA_LEVEL POSITION DATA_TYPE ------------------------------ ------------------------------ --------- ---------- ---------- ------------------------------ GETSAMPLE INPUT_SAMPLE IN 0 1 PL/SQL RECORD GETSAMPLE ID IN 1 1 NUMBER GETSAMPLE CODE IN 1 2 VARCHAR2 GETSAMPLE LIB IN 1 3 VARCHAR2 >> Argument 'INPUT_SAMPLE' is shown as 'PL/SQL RECORD' without any link to 'samples%ROWTYPE' > PLSQL types declared no rows selected >> There is no declared type because we use directly a %ROWTYPE argument > Clean up Package USE_SAMPLES dropped. Table SAMPLES dropped. 

So, with ALL_ARGUMENTS, "INPUT_SAMPLE" appears as "PL / SQL RECORD" without reference to "samples% ROWTYPE". And there is no such type in DBA_PLSQL_TYPES.

How can I get the declared type of this procedure in this form?

 GETSAMPLE INPUT_SAMPLE IN SAMPLES%ROWTYPE 

Answer 1

I searched in the dictionary and found nothing. If there is no information (I don't know), you can use something like:

 SELECT b.object_name,b.argument_name, REGEXP_SUBSTR (UPPER (text),'([^{ ,(;}]+)%ROWTYPE') tadaaa FROM user_source a JOIN (SELECT package_name, object_name, argument_name, in_out, data_level, position, data_type FROM user_arguments x WHERE data_type = 'PL/SQL RECORD' AND package_name = 'USE_SAMPLES' AND object_name = 'GETSAMPLE' ) b ON a.name = b.package_name WHERE a.TYPE = 'PACKAGE BODY' AND UPPER (text) LIKE '%\%ROWTYPE%' ESCAPE '\' AND UPPER (text) LIKE '%'||UPPER(ARGUMENT_NAME)||'%' 

:) Let some guru in the regular expression test the regular expression. Actually, the argument name should also be in regex.

Answer 2

I can suggest going the other way:

1) create an object type with all the attributes that you need in your table

2) create a table of objects of your TYPE

3) pass the argument to your procedure of your TYPE

4) you can get the type name from all_arguments

%rowtype and %type are pseudotypes dynamically resolved at compile time. So what would you expect to see in data_dictionary in this case? There is no name record / type / object related to sample% rowtype.