CODEWITHSUNDEEP
sqloracle-databasedynamicnative
user3108489
user3108489

Reputation: 363

Executing resulting rows for the result of Dynamic Native SQL query

I'm going mental over this. I'm fairly new to dynamic SQL, so I may just not be asking Google the right question, but here's what I'm trying to do... I have a query with dynamic SQL. When I run that query, it produces several rows. All of these rows (about 30) make up a single union query. I can copy all of those rows and paste into a new query and run - works fine, but what I need to do is run this all in a single query. I've looked up examples of using execute immediate and fetch, but I cannot seem to get them to actually spit out the data...they just end up saying something like "Executed Successfully", but doesn't actually produce any resulting rows. The resulting column name of the below SQL is "qry_txt" - instead of producing it at face value, I want to execute it as a query. Again, I may not be articulating this well, but I'm basically trying to turn 2 queries (with a manual copy/paste step involved) into a single query. Hope this makes sense...

Here's my SQL:

Select CASE when 
lead(ROWNUM) over(order by ROWNUM) is null then
'SELECT '||''''||T.TABLE_NAME||''''||' as TABLE_NAME,'||''''||T.COLUMN_NAME||''''||' as COLUMN_NAME, cast('|| T.COLUMN_NAME ||' as 
varchar2(100)) as SAMPLE_DATA ||
from rpt.'||T.TABLE_NAME ||' where '||T.COLUMN_NAME||' is not null and ROWNUM=1;'  
else
'SELECT '||''''||T.TABLE_NAME||''''||' as TABLE_NAME,'||''''||T.COLUMN_NAME||''''||' as COLUMN_NAME, cast('|| T.COLUMN_NAME ||' as 
varchar2(100)) as SAMPLE_DATA from rpt.'||T.TABLE_NAME ||' where '||T.COLUMN_NAME||' is not null and ROWNUM=1 union ' end as qry_txt
from all_tab_columns t where T.OWNER='rpt' and T.DATA_TYPE != 'BLOB' and T.DATA_TYPE != 'LONG' and T.TABLE_NAME = 'NME_DMN' 
ORDER BY ROWNUM asc;

Upvotes: 1

Views: 344

Answers (1)

XING
XING

Reputation: 9886

You cannot write a dynamic query in a SQL. You need to use PLSQL block to accomploish that. Please see how you can do it. PS: Code is not tested.

declare
  var1 <decalration same of column in select list> ;
  var2 <decalration same of column in select list> ;
  var3 <decalration same of column in select list> ;
  ....
  varn   ;

 begin
  for i in ( SELECT LEAD (ROWNUM) OVER (ORDER BY ROWNUM) COl1
              FROM all_tab_columns t
               WHERE     T.OWNER = 'rpt'
                     AND T.DATA_TYPE != 'BLOB'
                     AND T.DATA_TYPE != 'LONG'
                     AND T.TABLE_NAME = 'NME_DMN'
             ORDER BY ROWNUM ASC)

  Loop

     If i.col1 IS NULL Then

     execute immediate   'SELECT '
                           || ''''
                           || T.TABLE_NAME
                           || ''''
                           || ' as TABLE_NAME,'
                           || ''''
                           || T.COLUMN_NAME
                           || ''''
                           || ' as COLUMN_NAME, cast('
                           || T.COLUMN_NAME
                           || ' as 
                              varchar2(100)) as SAMPLE_DATA ||
                            from rpt.'
                           || T.TABLE_NAME
                           || ' where '
                           || T.COLUMN_NAME
                           || ' is not null and ROWNUM=1' into var1 , var2 ,var3 ....varn;

    Else

       execute immediate  'SELECT '
                           || ''''
                           || T.TABLE_NAME
                           || ''''
                           || ' as TABLE_NAME,'
                           || ''''
                           || T.COLUMN_NAME
                           || ''''
                           || ' as COLUMN_NAME, cast('
                           || T.COLUMN_NAME
                           || ' as 
            varchar2(100)) as SAMPLE_DATA from rpt.'
                           || T.TABLE_NAME
                           || ' where '
                           || T.COLUMN_NAME
                           || ' is not null and ROWNUM=1' into var1 , var2 ,var3 ....varn;

    end if;

  End Loop;             

  exception
   when others then
   dbms_output.put_lin(sqlcode ||'--'||sqlerrm);

End;

Upvotes: 1

Related Questions