Dynamic PL/SQL - Get values from one cursor from another or alternatives?
Date: Mon, 11 Aug 2008 03:06:29 -0700 (PDT)
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):
I get all sorts of strange PL/SQL compilation errors.
So 3 questions:
- 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?
- 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?
- 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?
Daniel Received on Mon Aug 11 2008 - 05:06:29 CDT