Column Name and Column Values [message #21708] |
Fri, 23 August 2002 14:47 |
Robinson
Messages: 5 Registered: August 2002
|
Junior Member |
|
|
Look,
I have the name of a column table, then i need to know the value of this column_name, ex.
v:= 'CODZON';
Select v from Zone Where nom = 'XX';
But it doesn't Work !
the problem is that i recovery the v variable of user_tab_columns. Please Help !!!
|
|
|
Re: Column Name and Column Values [message #21709 is a reply to message #21708] |
Fri, 23 August 2002 15:46 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
You need to Native Dynamic SQL or DBMS_SQL. Search for "weak ref cursor" or see the following dbms_sql example.
/*
|| spec!
*/
CREATE OR REPLACE PACKAGE dynquery
IS
TYPE tdatatab IS TABLE OF VARCHAR2 (256)
INDEX BY BINARY_INTEGER;
PROCEDURE retrieve_data (query IN VARCHAR2, data IN OUT tdatatab);
END;
/
/*
|| body!
*/
CREATE OR REPLACE PACKAGE BODY dynquery
IS
PROCEDURE retrieve_data (query IN VARCHAR2, data IN OUT tdatatab)
IS
sel_cursor INTEGER;
err# NUMBER (8);
retval VARCHAR2 (256);
rec_count NUMBER (8);
BEGIN
rec_count := 0;
data.delete;
sel_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (sel_cursor, query, DBMS_SQL.v7);
DBMS_SQL.define_column (sel_cursor, 1, retval, 256);
err# := DBMS_SQL.execute (sel_cursor);
LOOP
rec_count := rec_count + 1;
IF DBMS_SQL.fetch_rows (sel_cursor) > 0
THEN
DBMS_SQL.column_value (sel_cursor, 1, retval);
data (rec_count) := retval;
ELSE
EXIT;
END IF;
END LOOP;
END;
END;
/
set serveroutput on
/*
|| Test it
*/
declare
d dynquery.tdatatab;
i NUMBER (8);
BEGIN
dynquery.retrieve_data ('select object_name || '','' || object_type from user_objects where rownum < 21',
d
);
FOR i IN 1 .. d.COUNT
LOOP
DBMS_OUTPUT.put_line (d (i));
END LOOP;
END;
/
|
|
|