Blog

Sometimes in PL/SQL programming we need to switch the logic between different SQL queries, but fetching from cursors into predefined variables is painful.

In the following example we define 2 cursors and their field definitions are taken directly from their rowtype, without declaring them one by one:

declare
    
    cursor l_cursor_1 is 
        select 'a1' param1, 'b1' value1 from dual;  
        
    cursor l_cursor_2 is 
        select 'a2' param1, 'b2' value1, 'c2' config1 from dual;  
    
    l_cursor_1_rec l_cursor_1%rowtype;      
    l_cursor_2_rec l_cursor_2%rowtype; 
    
    l_switch_cursor number :=1;  
    procedure display(p_val in varchar2) is 
    begin
         dbms_output.put_line(p_val);
    end;                   
begin
    
    
    if(l_switch_cursor=1)then
        open l_cursor_1;
    elsif(l_switch_cursor=2)then
        open l_cursor_2;
    end if;
        
    loop

        if(l_switch_cursor=1)then
            fetch l_cursor_1 into l_cursor_1_rec;
            exit when l_cursor_1%notfound;
            display(l_cursor_1_rec.param1);
            display(l_cursor_1_rec.value1);                      
        elsif(l_switch_cursor=2)then
            fetch l_cursor_2 into l_cursor_2_rec;
            exit when l_cursor_2%notfound;
            display(l_cursor_2_rec.param1);
            display(l_cursor_2_rec.value1);
            display(l_cursor_2_rec.config1);               
        end if;        
    end loop;
       
    if(l_switch_cursor=1)then
        close l_cursor_1;
    elsif(l_switch_cursor=2)then
        close l_cursor_2;
    end if;
end;

The recordset has different columns in these 2 queries, in order to demonstrate the efficiency of this approach.

So, changing the l_switch_cursor betwen 1 and 2 the query changes.