We help IT Professionals succeed at work.

Dynamic SQL for ORACLE

Evan Cutler
Evan Cutler used Ask the Experts™
on
Greetings EE Gods...

I am in need of some SQL help:

I have a list of indicator columns spread throughout 47 tables...
I can find them using the below select statement:

select 'select distinct ' || COLUMN_NAME || ' from ' || TABLE_NAME ||';' from ALL_TAB_COLUMNS where COLUMN_NAME ='ATCHMNT_IND' and OWNER = 'AABASE';

now, this gives me a recordset output that gives me a list of select queries to run.
What I need is to have them all run consecutively where a single output recordset can be achieved without adding tables to the schema.

Here's the output I'm looking for:
TABLE_NAME     COLUMN_NAME     VALUE
TABLE1              IND1                       YES
TABLE1              IND1                       No
TABLE1              IND1                       3
TABLE1              IND2                       J
TABLE1              IND2                       No
TABLE2              IND1                       12

etc...

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
try this...


S ELECT c.table_name, c.column_name, x.VALUE
  FROM all_tab_columns c,
       XMLTABLE(
           '/ROWSET/ROW/X'
           PASSING DBMS_XMLGEN.getxmltype(
                       'select distinct ' || c.column_name || ' X from ' || c.table_name)
           COLUMNS VALUE VARCHAR2(4000) PATH '/X') x
 WHERE c.column_name = 'ATCHMNT_IND' AND c.owner = 'AABASE';
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
You are a GodSend...
what is XMLTABLE?????
and why is it used here?
Most Valuable Expert 2011
Top Expert 2012

Commented:
xmltable is a function that allows you to query an xmltype value as if it were a table or view.

dbms_xmlgen executes the query you are generating to create the xmltype value.