Oracle SQl Populating a cursor in a procedure from a sql statement saved in a table field

Question

We want to populate a cursor in a procedure that is populated from a select statement in a table.

We created a table named stored_sql_statments with 2 columns, Created_date & Sql_statement. In that table we will insert a select statement that selects other data from the database based on the clients needs. Example: insert into stored_sql_statments ( Created_date , Sql_statement) values('2/1/2011', 'Select Client_idn , something_neat from cool_table where animal = 'dog' ')

Then in the procedure we have a bunch of code that does what it needs to do, which will never change, but the select statement we have in the cursor changes periodically. We always need to return 2 fields but the rest of the select statement changes.

So now we need to populate the cursor in the procedure with what the select statement coming form the table. If it was returning only 1 row we have: declare x varchar2(600); rec1 number(10); rec2 varchar2(15); begin execute immediate select Sql_statement into x from stored_sql_statments where created_date = '2/1/2011'; execute immediate x into rec1, rec2; ...

This works, but we don't need it to go into 2 variables we need it to go into a cursor. The real select statement (the above code is just a simple example of what we need to do) is bringing back thousands of records so we need to use a cursor.

Hope this all makes sense

So if anyone knows how to do this, it would be appreciated.

Answer 1

Are you trying to dynamically populate a ref cursor? if so that isn't difficult to do:

set serveroutput on
declare


 sql1 varchar2(500);
 sql2 varchar2(500);
 procedure runProcess(sqlstatement IN varchar2)
 AS
     refcrs sys_refcursor;
     DTE DATE;
     LEVELB NUMBER;
 BEGIN
  dbms_output.put_line(sqlstatement);
     open refcrs for
      sqlstatement; -- use 'using' to bind those variables
        loop
          fetch refcrs into DTE, LEVELB;
          exit when refcrs%notfound;
          dbms_output.put_line(TO_CHAR(DTE,'MMDDYYYY') || '/' || LEVELB);
        end loop;     
    CLOSE REFCRS;     
 END runProcess;

begin
    sql1 := 'select (sysdate - level) a, level b from dual connect by level < 5';
    sql2:= 'select (sysdate + level) a, -level b from dual connect by level < 5';

    runProcess(SQL1);
    runProcess(SQL2);


end ;

/**
select (sysdate - level) a, level b from dual connect by level < 5
02102011/1
02092011/2
02082011/3
02072011/4
select (sysdate + level) a, -level b from dual connect by level < 5
02122011/-1
02132011/-2
02142011/-3
02152011/-4
**/

As you can see, I am dynamically executing two different select statements in the same procedure and outputting their results.

Answer 2

SQL> drop table stack_overflow;

Table dropped.

SQL> create table stack_overflow (created_date date constraint stack_overflow_pk primary key
  2      , sql_statement varchar2(4000) not null)
  3  /

Table created.

SQL> drop table source_data;

Table dropped.

SQL> create table source_data (vc varchar2(10) null
  2      , n number);

Table created.

SQL> insert into source_data values ('a', 100);

1 row created.

SQL> insert into source_data values ('a', 0);

1 row created.

SQL> insert into source_data values ('b', 50);

1 row created.

SQL> insert into source_data values ('c', null);

1 row created.

SQL> insert into stack_overflow values (sysdate - 3/24, 'select vc, sum(n)
  2      from source_data
  3      group by vc
  4      order by vc asc');

1 row created.

SQL> insert into stack_overflow values (sysdate - 2/24 , 'select vc, avg(n)
  2      from source_data
  3      group by vc
  4      order by vc desc');

1 row created.

SQL> insert into stack_overflow values (sysdate - 1/24 , 'select vc, count(*)
  2      from source_data
  3      group by vc');

1 row created.

SQL> insert into stack_overflow values (sysdate, 'select vc, count(n)
  2      from source_data
  3      group by vc');

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2      type stack_overflow_type is record (col_1 varchar2(10), col_2 number);
  3      type stack_overflow_cur_type is ref cursor return stack_overflow_type;
  4      cursor sql_statement_cur is select sql_statement
  5          from stack_overflow
  6          order by created_date;
  7      --
  8      function get_cursor_by_date (i_created_date in date) return stack_overflow_cur_type is
  9          l_return_cur sys_refcursor; -- stack_overflow_cur_type;
 10          l_sql_statement stack_overflow.sql_statement%TYPE;
 11      begin
 12          select sql_statement into l_sql_statement
 13          from stack_overflow
 14          where created_date = i_created_date;
 15          --
 16          open l_return_cur for l_sql_statement;
 17          return l_return_cur;
 18      end get_cursor_by_date;
 19      --
 20      procedure process_and_close_cursor (i_cursor in stack_overflow_cur_type) is
 21          l_current_rec stack_overflow_type;
 22      begin
 23          loop
 24              fetch i_cursor into l_current_rec;
 25              exit when i_cursor%NOTFOUND;
 26              dbms_output.put_line('col_1: ' || l_current_rec.col_1
 27                  || ' col_2: ' || to_number(l_current_rec.col_2));
 28          end loop;
 29          --
 30          close i_cursor;
 31      end;
 32      --
 33  begin
 34      for l_row in (select created_date
 35          from stack_overflow
 36          order by created_date)
 37      loop
 38          dbms_output.put_line('Processing the SQL statement created on: '
 39             || to_char(l_row.created_date, 'YYYY-MM-DD HH24:Mi:SS'));
 40          --
 41          process_and_close_cursor(get_cursor_by_date(l_row.created_date));
 42          --
 43          dbms_output.new_line;
 44      end loop;
 45  end;
 46  /
Processing the SQL statement created on: 2011-02-11 10:01:16
col_1: a col_2: 100
col_1: b col_2: 50
col_1: c col_2:

Processing the SQL statement created on: 2011-02-11 11:01:16
col_1: c col_2:
col_1: b col_2: 50
col_1: a col_2: 50

Processing the SQL statement created on: 2011-02-11 12:01:17
col_1: a col_2: 2
col_1: b col_2: 1
col_1: c col_2: 1

Processing the SQL statement created on: 2011-02-11 13:01:17
col_1: a col_2: 2
col_1: b col_2: 1
col_1: c col_2: 0


PL/SQL procedure successfully completed.