Re: print out structure of pl/sql record

spliffy wrote:

> Hi Daniel,
>
> First thanks for answering.
>
> Ok I'll give you more details...
>
> We use a oracle package as an interface into a database application
> system.
> The package defines some record types, and the package procedures take
> these record types as IN parameters, and then uses them to populate
> tables in the application database.
>
> The problem is the interface often changes each release and fields are
> added and removed from the record types. This has an impact on our
> test scripts/files for the interface, which need constant maintaining
> by manually editting them.
>
> What I'd like to do is use a JDBC, or PL/SQL program to query the
> database about the package and automatically generate the test
> scripts, and/or change the existing testfiles. Which ever is easiest,
> however I can't even start as I can't find anyway to extract details
> about pl/sql record types!
>
> Can you help?
>
> I give an example of the type of package below
> (I have not compiled it - so it might not be syntactically correct!)
>
>
> --
>
> CREATE OR REPLACE my_pkg
> IS
> BEGIN
>
> TYPE my_record IS RECORD
> (
> ID NUMBER,
> NAME VARCHAR2(10)
> );
>
> PROCEDURE my_proc( in_record my_record IN,
> out_error_code NUMBER OUT );
>
> END;
>
> --
>
> CLIENT SYSTEM would then do the following
>
> rec my_pkg.my_record;
> error_code number;
>
> rec.id := 1;
> rec.name := 'DAVID';
> my_pkg.my_proc(rec, error_code);
>
> ---
>
>
>
>
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1085094937.723200_at_yasure>...
>

>>spliffy wrote:
>>
>>
>>>Hi,
>>>
>>>I'd like to query the database for the definition of a user defined
>>>pl/sql record (i.e. print out the record's fields, and their type).
>>>You'd think describe would do it, but it's doesn't work with pl/sql
>>>records.
>>>So does anyone know how to do this from sqlplus? 
>>>Note, it must be done by querying the database, and not parsing text
>>>files.
>>>
>>>Kind regards
>>
>>If I had a clue what you mean by "a user defined pl/sql record" because
>>users don't, and can't, define PL/SQL records.
>>
>>Can you better explain what you mean?

SELECT overload, argument_name, in_out, data_type, default_value, data_length, data_precision
FROM all_arguments
WHERE package_name = <package_name>
AND object_name = <procedure_or_function_name> ORDER BY position;

Of course you developers could always try just communicating among yourselves, documenting changes, exchanging information ... no forget it ... sorry for thinking the unthinkable.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)