Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate for Select
Execute Immediate for Select [message #39840] Fri, 16 August 2002 03:01 Go to next message
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 Go to previous message
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.
Previous Topic: problem reg select into .......
Next Topic: SQL and PL/SQL Limitations
Goto Forum:
  


Current Time: Fri Mar 29 00:17:50 CDT 2024