Dynamic PL/SQL - Get values from one cursor from another or alternatives?
in [Oracle Help]
|
Prev: Access frontal interface for Oracle database
Next: Dynamic PL/SQL - Get values from one cursor from another or alternatives?
From: bpfh on 11 Aug 2008 06:06 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 |