Oracle cursor with variable columns/tables/criteria

Question

I need to open a cursor while table name, columns and where clause are varying. The table name etc will be passed as parameter. For example

CURSOR batch_cur
IS
SELECT a.col_1, b.col_1
FROM table_1 a inner join table_2 b 
ON a.col_2 = b.col_2
WHERE a.col_3 = 123

Here, projected columns, table names, join criteria and where clause will be passed as parameters. Once opened, i need to loop through and process each fetched record.

Answer 1

You need to use dynamic SQL something like this:

procedure dynamic_proc
    ( p_table_1 varchar2
    , p_table_2 varchar2
    , p_value   number  
    )
is    
    batch_cur sys_refcursor;
begin
    open batch_cur for 
        'select a.col_1, b.col_1
         from ' || p_table_1 || ' a inner join || ' p_table_2 || ' b 
         on a.col_2 = b.col_2
         where a.col_3 = :bind_value1';
      using p_value;
    -- Now fetch data from batch_cur...
end;

Note the use of a bind variable for the data value - very important if you will re-use this many times with different values.