Execute Immediate for Select [message #39840] |
Fri, 16 August 2002 03:01 |
ramesh
Messages: 123 Registered: August 2000
|
Senior Member |
|
|
Can I use Execute immediate to execute dynamic cursor.
The cursor will take inputs of column name and table name. Hence I should be able to retrieve records from the corresponding table with the columns provided. How do I bind these variables.
Thanks,
Ramesh
|
|
|
Re: Execute Immediate for Select [message #39845 is a reply to message #39840] |
Fri, 16 August 2002 08:36 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
If you are retrieving multiple rows, then you want to use dynamic SQL, but not with 'execute immediate'.
...
type rc is ref cursor
v_rc rc;
v_col1 varchar2(255);
v_col2 varchar2(255);
begin
open v_rc for 'select ' || p_column1 || ', ' || p_column2 || ' from ' || p_table_name;
loop
fetch v_rc into v_col1, v_col2;
exit when v_rc%notfound;
-- do something with values
end loop;
close v_rc;
end;
Now, this assumes that the number of columns and the datatypes are consistent. If not, you will need to use DBMS_SQL instead.
|
|
|