Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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)Received on Sat May 22 2004 - 14:23:42 CDT
![]() |
![]() |