5 Replies Latest reply: Jun 9, 2012 11:59 AM by 940850 RSS

    dynamic sql

    940850
      Hi all,
      i would like to store a result set form a select statement into a string.I explain better what i mean:

      I have a statement that is composed dynamically,suppose it is somenthing like this:
      stm:='select doc from Section';

      execute immediate stm into out_str;--i know it is wrong.out_str is the result of the function
      return out_str;

      The select statement returns one or more rows.Is it possible to store the result into a string and format it in some way?Thank you.
        • 1. Re: dynamic sql
          clcarter
          The exec immediate won't quite work that way, its intended for scalar result(s).

          If the SQL returned multiple columns, i.e. select a, b from <tab> the ... into has to have a matching variables list i.e. into :d, :e; or something like that, I think.

          So it would have to be a statement that returns one row, or an empty result set, that's the only two choices as far as I'm aware.

          But here's something that might be of help, setting up a package and using a table type: http://p2p.wrox.com/oracle/43625-return-table-rowset-pl-sql-procedure.html

          And {forum:id=75} forum would probably be a area that sort of question as well-
          • 2. Re: dynamic sql
            940850
            Thank you for your replay.I am thinking if there is possible to use execute immediate stm with a cursor.I add that the result consists of one column and zero or more rows.
            • 3. Re: dynamic sql
              JustinCave
              user3517650 wrote:
              Thank you for your replay.I am thinking if there is possible to use execute immediate stm with a cursor.I add that the result consists of one column and zero or more rows.
              You can certainly open a cursor dynamically. Something like
              CREATE OR REPLACE FUNCTION get_cursor( p_sql_stmt IN VARCHAR2 )
                RETURN sys_refcursor
              IS
                l_rc sys_refcursor;
              BEGIN
                OPEN l_rc FOR p_sql_stmt;
                RETURN l_rc;
              END;
              Depending on what you are trying to accomplish, you could also use various string aggregation techniques to return a single row in a delimited string. For example, if you're using 11.2 (always helpful to specify a version number) you could use LISTAGG
              SQL> select listagg(ename,',') within group( order by empno ) str
                2    from emp;
              
              STR
              --------------------------------------------------------------------------------
              SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL
              LER
              Justin
              • 4. Re: dynamic sql
                940850
                Ok i explain better what i mean:
                i have an execise where i have to write a function:
                input a string
                output a string
                The function have to use dynamic sql and has to build a sql statement.The problem is:how can i store the result of excute immediate into a string so that i can return it?

                1.build dynamically stm
                2.execute stm and store the result into a string.I don't need to pass any parameters.
                3.return string

                stm is like this
                stm:='select documento
                          from Sezione S1
                         where not exists ( select documento   from Sezione S2 where S2.documento=S1.documento and S2.sezione=S1.sezione and 
                (instr(S2.testo,'Grillo',1,1)=0 or  instr(S2.testo,'Tavolazzi',1,1)=0 or  instr(S2.testo,'Pizzarotti',1,1)=0));
                (instr(S2.testo,'Grillo',1,1)=0 or instr(S2.testo,'Tavolazzi',1,1)=0 or instr(S2.testo,'Pizzarotti',1,1)=0)); this line depends on the input string.Thank you for your help.
                • 5. Re: dynamic sql
                  940850
                  ok,i have solved with a SYS_REFCURSOR.
                  cur_doc SYS_REFCURSOR;
                  open cur_doc for stm;--stm builded dynamically
                  fetch cur_doc into doc;
                  loop
                  exit when cur_doc%NOTFOUND;
                  out_str:=out_str || doc || ',';
                  fetch cur_doc into doc;
                  end loop;
                  Thank you.