CODEWITHSUNDEEP
oracle-database
nithin
nithin

Reputation: 73

how to select table name from a query in oracle

select SRI_PACK_INDEX_VAL 
from (select TABLE_NAME 
      from all_tables 
      where table_name like '%SE_RAO_INDEX_04_12_2015%');

Hi, The above query is not working in oracle.Can anyone help me to solve this

Upvotes: 1

Views: 9552

Answers (2)

Jon Heller
Jon Heller

Reputation: 36808

select
    extractvalue(
        xmltype(
            dbms_xmlgen.getxml(
                'select sri_pack_index_val a from '||owner||'.'||table_name
            )
        ), '/ROWSET/ROW/A'
    ) sri_pack_index_val 
from all_tables
where table_name like '%SE_RAO_INDEX_04_12_2015%';

This query is based on a post from Laurent Schneider. Here's a SQLFiddle demonstration.

This is a neat trick to create dynamic SQL in SQL, but it has a few potential issues. It's probably not as fast as the typical dynamic SQL approach as shown by Michael Broughton. And I've ran into some weird bugs when trying to use this for large production queries.

I recommend only using this approach for ad hoc queries.

Upvotes: 1

Michael Broughton
Michael Broughton

Reputation: 4055

You can't dynamically select from tables like that as Oracle's parser can't figure it out before fetch to know what tables it needs to read. If you want to do a dynamic fetch you need to do dynamic sql. Something like (forgive any minor syntax errors-I'm away from my database at the moment

declare
   index_val number; -- i am assuming it is a number, change as appropriate
 begin
   -- I'm doing this in a loop if there are multiple tables that meet your name mask. 
   -- If there will only be one, then just select it into a variable and use it that way instead
   for tab_name in (SELECT TABLE_NAME 
                     from all_tables 
                     where table_name like '%SE_RAO_INDEX_04_12_2015%')
    loop
        execute immediate 'select SRI_PACK_INDEX_VAL from '||tab_name.table_name 
        into index_val;
         -- do your stuff with it
    end loop;
 end;

now, that works if the select only brings back one row. if you are bringing back multiple rows, then you have to handle it differently. In that case you will either want to EXECUTE IMMEDIATE a pl/sql block and embed your processing of the results there, or execute immediate bulk collect into an array, an example on how to do that is here.

Upvotes: 3

Related Questions