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;

Can you share the table data? – hemalp108 Aug 01 '17 at 12:11 — hemalp108, Aug 01 '17 at 12:11

score 0 · Answer 1 · answered Aug 01 '17 at 11:54

0

İf var_names(indx).SO_SQL_BODY output is a runnable sql text;

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);
   EXECUTE IMMEDIATE var_names(indx).SO_SQL_BODY;
END LOOP;
END a_proc;

answered Aug 01 '17 at 11:54

mehmet sahin

802
7
21

It gives an error. ORA-06535: statement string in EXECUTE IMMEDIATE is NULL or 0 length – 1010111100011 Aug 01 '17 at 12:02
Do 'var_names(indx).SO_SQL_BODY' this statement return a executable sql text? – mehmet sahin Aug 01 '17 at 12:04
I don't thinks so. It is a table has two columns and the column that keeps 'select to_char(sysdate,'day') from dual' kind of datas, type is varchar2. – 1010111100011 Aug 01 '17 at 12:06

score 0 · Answer 2 · answered Aug 01 '17 at 13:01

You don't need a full cursor for this example. An implicit one would make it a lot shorter.

create or replace procedure a_proc is
        lReturnValue varchar2(250);
    begin
        for q in (select so_sql_body from so_sub_vars group by so_sql_body)
        loop

            execute immediate q.so_sql_body into lReturnValue;
            dbms_output.put_line(lReturnValue);

        end loop;
    end a_proc;

You should add an exception handler that will care for cases where there is a bad SQL query in your table. Also note that executing querys saved in a database table is your entry point to SQL injection.

score 0 · Accepted Answer · edited Aug 04 '17 at 07:01

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

How to execute results of dbms_output.put_line

3 Answers3