Oracle Cursor with conditional Select statement

Question

I am new to this group. I was trying to think of having a cursor with condition select statement. Some how like this pseudo code--

[B]cursor test_cursor is
  if condition == 't11'
  then 
    select * from test1;
  else 
    select * from test1;
  end if;[/B]
begin

  for cursorVal in test_cursor loop
    //Doing the actual task on cursor data.
  end loop;

commit;
end;

Actually, i came across with a scenario where need to work on two different tables with same DDL. Based on some user input, need to fetch data from either of the table and further manipulate in procedure. As i said both table are of same DDL so don't want to create two different cursor. The reason for this same business logic will be applied on both tables data. Its just the user input which decide which table need to fetch data. Some how one can think of this as latest data and historical data and the way DB is designed.

Hope i am clear with my scenario.

Thanks, Arfeen.

Use dynamic SQL. See docs.oracle.com/cd/E11882_01/appdev.112/e25519/… — Rachcha
– Rachcha, Commented Jan 24, 2014 at 7:23
Using ref cursor can solve your problem, based on user inputs you can open that cursor for desired table. — San
– San, Commented Jan 24, 2014 at 7:28
Thank you all for your help. I resolve this issue with the hint given by San. — Arfeen
– Arfeen, Commented Jan 30, 2014 at 20:23

Daniel B · Accepted Answer · 2014-01-24 08:31:15Z

1

The cursor can be declared as a union as described below. Depending on the content of variable condition, the cursor will either be based on Test1 or Test2.

SELECT * FROM Test1 WHERE condition = 't1'
UNION ALL
SELECT * FROM Test2 WHERE condition = 't2'

answered Jan 24, 2014 at 8:31

Daniel B

8075 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OraNob · Accepted Answer · 2014-01-24 10:07:58Z

0

What you are trying to achieve looks like it could either be achieved by better table or view design or by using a BULK COLLECT. If you can - always consider database design first over code.

BEGIN

if condition == 't11' then
 SELECT XXXXXX
 BULK COLLECT INTO bulk_collect_ids
 FROM your_table1;
else
 SELECT XXXXXX
 BULK COLLECT INTO bulk_collect_ids
 FROM your_table2;
end if;

FOR indx IN 1 .. bulk_collect_ids.COUNT
LOOP
 .
 //Doing the actual task on bulk_collect_ids data.
 .
END LOOP;

END;

answered Jan 24, 2014 at 10:07

OraNob

6943 silver badges9 bronze badges

Comments

rainu · Accepted Answer · 2023-11-28 12:20:12Z

I usually prefer to do the below if I have to conditionally open a cursor.

The condition can be passed as a function/procedure input or a simple variable.

declare 
  vSwitch varchar2(10):='FIRST';
  vRefCursor SYS_REFCURSOR;
  vLimit     NUMBER := 100;
  
  Type t_tbl is table of my_table%rowtype index by binary_integer;
  vtbl  t_tbl;

begin

  if vSwitch = 'FIRST' then 
   open vRefCursor FOR 
        select /*+ parallel (4) */
             column1,column2,column3
         from my_table 
           where
           some_complex_check;
  else
    open vRefCursor FOR 
        select /*+ parallel (4) */
             column1,column2,column3
         from my_table 
           where
           some_other_complex_check;
  end if;

 loop
      fetch vRefCursor bulk collect into vtbl limit vLimit;
       
      exit when vtbl.count=0;
      begin 
         forall idx in vtbl.first..vtbl.last
         insert /*+ enable_parallel_dml parallel(4)*/
            my_table(column1,column2,column3)
          values 
           (vtbl(idx).column1,vtbl(idx).column2,vtbl(idx).column3);
      commit;
      exception when others then 
       ---    handle DML exceptions
      end;


 end loop;

end;

Collectives™ on Stack Overflow

Oracle Cursor with conditional Select statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related