0

I am developing a stored procedure with a cursor of bulk collect. I initially developed a static cursor with bulk collect as below:

procedure p_get_x ( p_x  in NUMBER )
    is
      l_var1 is number;
      cursor c_x is
      select 
        col1, col2
        from tbl1
      where col1 = l_var1
      ;
      t_x c_x%rowtype; 
      TYPE tab_x IS TABLE OF t_x%TYPE;
      tb_x tab_x; 

    BEGIN      
             open c_x;
             fetch c_x bulk collect into tb_x ;
             close c_x
           ;
          for idx in 1 .. tb_x.count 
            loop
              insert into tbl2
               (
                col1,
                col2
               )
               values (
               tb_x(idx).col1,
               tb_x(idx).col2
               );
          end loop;
          commit;
      end if;    
end p_get_x;

The requirement is to create a generic procedure. Based on input - p_x, the procedure have to execute different cursor definition, but only 1 cursor in an execution. The cursor table name will be different and each table will have some common column names and few specific column names. example :- table a - col1, col2 ,col3 table b - col1, col3, col4

How to create a dynamic cursor wilt bulk collect as on average there would be 5 Million rows in that table?

References I tried: Dynamic Variable in Cursor in Oracle stored procedure dynamic table name in cursor

Thanks

Aavik
  • 967
  • 19
  • 48
  • Are there really only two options? If so, you're going to be much better off with a simple `IF` statement that branches to one of two paths each using static SQL than trying to resort to dynamic SQL. – Justin Cave Dec 12 '19 at 17:38
  • if statement does not work in declare statement – Aavik Dec 13 '19 at 09:41
  • Is it possible to have ref cursor and bulk binding together? I am using Oracle 18 – Aavik Dec 13 '19 at 09:47
  • I'm suggesting that you declare two different cursors (assuming you want to use explicit cursors) and have an `IF` statement in the body of your procedure that processes one or the other depending on the input. – Justin Cave Dec 13 '19 at 14:47
  • can you give reference please – Aavik Dec 13 '19 at 15:03

1 Answers1

0

If there are really only two paths, it likely doesn't make sense to resort to dynamic SQL. Use static SQL and just add branching logic to your code. Something like

create or replace procedure do_something( p_table_name in varchar2 )
as
  type t1_tbl is table of t1%rowtype;
  type t2_tbl is table of t2%rowtype;

  l_t1s t1_tbl;
  l_t2s t2_tbl;
begin
  if( p_table_name = 'T1' )
  then
    select *
      bulk collect into l_t1s
      from t1;

    forall i in 1 .. l_t1s.count
      insert into dest_table( columns )
        values( l_t1s(i).col1, ... l_t1s(i).colN );
  elsif( p_table_name = 'T2' )
  then
    select *
      bulk collect into l_t2s
      from t2;

    forall i in 1 .. l_t2s.count
      insert into dest_table( columns )
        values( l_t2s(i).colA, ... l_t2s(i).colN );
  end if;
end;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384