Re: print out structure of pl/sql record

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1085253827.851900_at_yasure>...
> 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.

Thanks Daniel, exactly what I wanted.

Like your truism as well :)

David/ Received on Sun May 23 2004 - 07:22:30 CDT