Tuesday, December 9, 2014

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


8 comments:

  1. what is the best way to learn oracle in short time...
    Tableau Training

    ReplyDelete
  2. Learning new technolgy would help oneself at hard part of their career. And staying updated is the only way to survive in current position. Your content tells the same. Thanks for sharing this information in here. Keep blogging like this.
    PHP Training in Chennai

    ReplyDelete

  3. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well
    iOS App Development Company
    iOS App Development Company

    ReplyDelete
  4. Hey there, You have done a fantastic job. I’ll definitely digg it and personally suggest to my friends. I am confident they will be benefited from this web site.

    PSD to Wordpress
    wordpress website development

    ReplyDelete