Dynamic PL/SQL - Get values from one cursor from another or alternatives?

From: bpfh <numerista_at_gmail.com>
Date: Mon, 11 Aug 2008 03:06:29 -0700 (PDT)
Message-ID: <32aecc2a-dbbc-42e8-8f60-616d89df9afa@r66g2000hsg.googlegroups.com>


Hi,

I am fighting with PL/SQL and loosing...

I have a database that changes on a regular basis and has over 200 columns... and the data needs to be exported to a file.

My idea was to create the following cursors:

CURSOR cAnnonceFields IS SELECT column_name FROM SYS.USER_TAB_COLUMNS where table_name = 'ANNONCE';
CURSOR cAnnonceData IS SELECT * FROM annonce where id_annonce = s_id_annonce;

From there I wanted to get values from cAnnonceData cursor by referencing it with the column names got from cAnnonceFields:

FETCH cAnnonceData INTO aFieldValues;
FETCH cAnnonceFields INTO aFieldNames;

Up to here, no problems, but from here things go downhill. cAnnonceFields has 1 column (column_name) and cAnnonceFields has x fields, but I *have* to provide a static text lable to aFieldNames to get the value - and it seems impossible to get somthing like (in pseudo-code):

put_line(aFieldValues.(aFieldNames.column_name))

I get all sorts of strange PL/SQL compilation errors.

So 3 questions:

  1. Can I access the values FETCHed from a cursor by an index value rather than a label so that I can control it from a loop?
  2. Is there a way of generating the label in a similar method as above in pseudo-code as to get the label out of one FETCH'ed array and passing it to another as an argument?
  3. Is there a way of getting the field names out of the cAnnonceData cursor, so that in effect, executing a SELECT * from a table gets all data *and* field names out of a table?

Cheers,
Daniel Received on Mon Aug 11 2008 - 05:06:29 CDT

Original text of this message