Fetching Multiple Rows with EXECUTE IMMEDIATE

execute immediate is very handy for executing dynamic SQL statements inside PLSQL block. It can accept IN and OUT parameters and can also return output values to PLSQL variables by into clause. But returning values can be tricky because of the multiple returning rows. For single row returns into clause works fine but for multiple rows, hitting the following exception is quite easy.

declare  
  
  v_cmd varchar2(100);
  v_table_owner varchar2(30) := 'SCH_OLTP';  
  v_table_name varchar2(30) := 'T_VISIT_STATS_DETAILED';
  
  v_column_name varchar2(30);
  
begin 

  v_cmd := 'select column_name from dba_tab_columns where owner=:v_table_owner and table_name=:v_table_name';
   
  execute immediate v_cmd into v_column_name
    using v_table_owner, v_table_name;
  
end;
/*
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 13
*/

So it is important to check the returning row count before using execute immediate is important. Or you can use the bulk collect clause to fetch multiple returning rows into an array.

declare 
  type typ_varcharlist is table of varchar2(30);
  
  col_list typ_varcharlist;  
  
  v_cmd varchar2(100);
  v_table_owner varchar2(30) := 'SCH_OLTP';  
  v_table_name varchar2(30) := 'T_VISIT_STATS_DETAILED';
  
begin 
  v_cmd := 'select column_name from dba_tab_columns where owner=:v_table_owner and table_name=:v_table_name';
  
  
  execute immediate v_cmd bulk collect into col_list 
    using v_table_owner, v_table_name;
  
  for n_ctl in 1 .. col_list.count() loop
    dbms_output.put_line (col_list(n_ctl));
  end loop;
  
end;
/*
DUMMY
SUCCESS
VISITED_USER_ID
FROM_IP
VISIT_DATE
PAGE_ID
*/

Reference:
Oracle PLSQL Users Guide
Thesis and Papers > Oracle 11g Execute Immediate