Re: print out structure of pl/sql record

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?