CODEWITHSUNDEEP
oracle-database
Patrick
Patrick

Reputation: 2577

Oracle select across a group of tables by partial name

I'm trying to select information from a group of tables. Originally, I had

 SELECT table_name,
                    to_number(
                    extractvalue(
                    xmltype(
                    dbms_xmlgen.getxml('select count(*) c '|| ' from ' ||owner||'.'||table_name))
                    ,'/ROWSET/ROW/C')) count
                    from all_tables
                    where table_name like 'PAY0%' OR  table_name like 'PAY1%'

Then looped through all the tables in my code using 

 foreach(table_name in tables){
    SELECT CUST_NUMBER 
    FROM #table_name#
}

Now I'm trying to combine the two by doing the following in order to get the cust_number from every table, but it's saying '"CUST_NUMBER": invalid identifier'

select CUST_NUMBER 
                    from  ( SELECT table_name,
                            to_number(
                            extractvalue(
                            xmltype(
                            dbms_xmlgen.getxml('select count(*) c '|| ' from ' ||owner||'.'||table_name))
                            ,'/ROWSET/ROW/C')) count
                            from all_tables
                            where table_name like 'PAY0%' OR  table_name like 'PAY1%') PayTables

I know the problem is the first query gets table names and not the data, but I've no idea how to get the data as well without looping through them seperately.

Upvotes: 1

Views: 577

Answers (1)

Aleksej
Aleksej

Reputation: 22949

If I correctly understand your need, something like the following could help:

declare
    vSQL varchar2(32767);
    type tabNum is table of number;
    vResult tabNum;
begin
    select listagg( 'select cust_number from ' || owner || '.' || table_name,
                    ' UNION ALL '
                  ) within group ( order by null)
    into vSQL
    from dba_tables
    where table_name like 'PAY0%' OR  table_name like 'PAY1%';    
    --
    dbms_output.put_line(vSQL);
    --
    execute immediate vSQL bulk collect into vResult;
    --    
    for i in vResult.first .. vResult.last loop
        dbms_output.put_line(vResult(i));
    end loop;
end;

It dynamically builds an SQL statement that extracts values from all the tables matching you criteria; in the example I run the statement fetching the result into a structure, but you can open a cursor, or do whatever you need.

Upvotes: 2

Related Questions