How to execute results of dbms_output.put_line

Question

There is a table contains this kind of data: select to_char(sysdate,'day') from dual in a column. I want to get results of the every query that the table keeps.

My result set should be the result of select to_char(sysdate,'day') from dual query. So in this case it is a tuesday.

SO_SQL_BODY is Varchar2.

I wrote this code but it returns only table data.

CREATE  or replace PROCEDURE a_proc
AS

 CURSOR var_cur IS

select  SO_SQL_BODY FROM SO_SUB_VARS group by SO_SQL_BODY;

var_t  var_cur%ROWTYPE;

   TYPE var_ntt IS TABLE OF var_t%TYPE;

var_names  var_ntt;
BEGIN
OPEN  var_cur;
FETCH var_cur BULK COLLECT INTO var_names;
CLOSE var_cur;

FOR indx IN 1..var_names.COUNT LOOP

   DBMS_OUTPUT.PUT_LINE(var_names(indx).SO_SQL_BODY);

END LOOP;
END a_proc;

Answer 1

DECLARE
   res varchar2(4000);
   sql_str varchar2(1000);
BEGIN
  FOR r IN
    (select  SO_SQL_BODY FROM SO_SUB_VARS  WHERE SO_SQL_BODY IS NOT NULL
  )
  LOOP
     sql_str := r.SO_SQL_BODY;
     EXECUTE immediate sql_str INTO res;
     dbms_output.put_line(sql_str);
     dbms_output.put_line('***********************');
     dbms_output.put_line(res);
     dbms_output.put_line('***********************');
  END LOOP;
END;
/

Try this - iterate to not null records - execute them and print the result.This script works supposing the fact that SO_SQL_BODY contains a query which projects only one column.Also if the projection is with more than two columns then try to use a refcursor and dbms_sql package