| How to refer to fields in a dynamic multi-row SELECT query? [message #38161] |
Tue, 26 March 2002 07:46  |
MarcoC
Messages: 10
Registered: February 2002
|
Junior Member |
|
|
Hi all,
I am on orable 8i and I'd like to get the values of any fields specified in the select statement of a dynamic query. The procedure I have would only get me the first field of the select list.
Is there any way to get to all the OTHER fields in the list?
Example:
When stmt is equal to 'select id,first_name from students' "field_val" holds only the "id" values.
When stmt is equal to 'select first_name,id from students' "field_val" holds only the "first_name" values.
When stmt is equal to 'select * from students' "field_val" holds only the "id" values, since "id" is the first field in the table.
How can I refer to the other fields? Thanks. Marco
Here's the code:
CREATE OR REPLACE PROCEDURE showcol (stmt IN VARCHAR2)
IS
TYPE cur_type IS REF CURSOR;
cur cur_type;
field_val VARCHAR2(32767);
BEGIN
OPEN cv FOR
stmt;
LOOP
FETCH cur INTO field_val;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (field_val);
END LOOP;
CLOSE cur;
END;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
