2

I have a dynamic select script which I build using pl/slq and I need to execute it in simple SQL.

How can I do these?

P.S.: I can't use views, because number of collumn's in returned table changes every time.

dududko
  • 601
  • 6
  • 5
  • A can't use these solution, cause I need to get result without using PL/SQL. I can store some functionality in procedures and functions, but I need to get the result using simple SELECT statement – dududko Feb 12 '14 at 13:43
  • Then use a refcursor. See here - http://stackoverflow.com/questions/6457085/ref-cursor-with-execute-immediate – OldProgrammer Feb 12 '14 at 13:53

2 Answers2

1

IF XML suites you well, then you can use DBMS_XMLGEN

Could be something like this:

select dbms_xmlgen.getxmltype('here comes your dynamic sql string').getstringval()
from dual;

Here is a sqlfiddle demo

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
1

If you don't know column set in advance this makes things complex. You can use such thing as XMLSequence + ref cursor, but again this returns XML-structured output you will need to treat somehow.

SQL> create or replace function get_rows
  2  (tab_name varchar2)
  3  return sys_refcursor
  4  is
  5   c sys_refcursor;
  6  begin
  7    open c for 'select * from ' ||tab_name;
  8    return c;
  9  end;
 10  /

SQL> select * from table(xmlsequence(get_rows('dual')));

COLUMN_VALUE                                                                    
--------------------------------------------------------------------------------
<ROW>                                                                           
  <DUMMY>X</DUMMY>                                                              
</ROW>                                                                          

SQL> select * from table(xmlsequence(get_rows('T')));

COLUMN_VALUE                                                                    
--------------------------------------------------------------------------------
<ROW>                                                                           
  <X>1</X>                                                                      
  <Y>A1</Y>                                                                     
</ROW>                                                                          

<ROW>                                                                           
  <X>2</X>                                                                      
  <Y>A2</Y>                                                                     
</ROW>                                                                          

<ROW>                                                                           
  <X>3</X>                                                                      
  <Y>A3</Y>                                                                     
</ROW>
Dmitry Nikiforov
  • 2,988
  • 13
  • 12