Oracle select stmt

I do not understand what "how to sub ''JSMITH'' with multiple names" means.

Please provide some sample data and expected results.

How to use in clause and change "JSMITH" to "TOM","FRANK","DAVE"?

thx

try this...

SELECT table_name, column_name, column_data
  FROM (SELECT table_name,
               column_name,
               EXTRACT(
                   xmltype(
                       NVL(
                           DBMS_XMLGEN.getxml(
                                  'select '
                               || column_name
                               || ' from '
                               || table_name
                               || ' where rownum=1 and '
                               || column_name
                               || q'[ in  (select column_value from table(ORA_MINING_VARCHAR2_NT('TOM','FRANK','DAVE')))]'
                           ),
                           '<a/>'
                       )
                   ),
                   '//' || column_name || '/text()'
               ).getstringval()
                   column_data
          FROM (SELECT column_name, table_name
                  FROM user_tab_columns
                 WHERE column_name LIKE '%USER%' AND table_name IN ('TAB1', 'TAB2')))
 WHERE column_data IS NOT NULL

Select allOpen in new window

or

SELECT table_name, column_name, column_data
  FROM (SELECT table_name,
               column_name,
               EXTRACT(
                   xmltype(
                       NVL(
                           DBMS_XMLGEN.getxml(
                                  'select '
                               || column_name
                               || ' from '
                               || table_name
                               || ' where rownum=1 and '
                               || column_name
                               || q'[ MEMBER OF ora_mining_varchar2_nt('TOM', 'FRANK', 'DAVE')]'
                           ),
                           '<a/>'
                       )
                   ),
                   '//' || column_name || '/text()'
               ).getstringval()
                   column_data
          FROM (SELECT column_name, table_name
                  FROM user_tab_columns
                 WHERE column_name LIKE '%USER%' AND table_name IN ('TAB1', 'TAB2')))
 WHERE column_data IS NOT NULL

Select allOpen in new window

sdstuber and sdstuber,

I tried both of your code and found the same problem below.
Say I got 32 returned records for "TOM" alone and 80 returned records for "FRANK" alone.
Your code gives me 80 returned records when I put both "TOM" and "FRANK", instead of 112 records.

Thanks.

sdstuber and sdstuber,

there's only one of me  :)


It's probably the  

where rownum=1

filter from the original query, I didn't change anything except the part you asked for

I'll look at the rest of the functionality and post an update

I wasn't able to duplicate.

Can you provide some sample data that shows the failure?

I made an even simpler version - I waaaaay over complicated it by not taking into account
the dynamic sql which allows embedding the list easily


SELECT table_name, column_name, column_data
  FROM (SELECT table_name,
               column_name,
               EXTRACT(
                   xmltype(
                       NVL(
                           DBMS_XMLGEN.getxml(
                                  'select '
                               || column_name
                               || ' from '
                               || table_name
                               || ' where rownum =1 and '
                               || column_name
                               || q'[ in ('TOM', 'FRANK', 'DAVE')]'
                           ),
                           '<a/>'
                       )
                   ),
                   '//' || column_name || '/text()'
               ).getstringval()
                   column_data
          FROM (SELECT column_name, table_name
                  FROM user_tab_columns
                 WHERE column_name LIKE '%USER%' AND table_name IN ('TAB1', 'TAB2')))
 WHERE column_data IS NOT NULL


note, for me, this produces the same results as my other queries, so, while it should be more efficient, it's not likely to be more correct.  So, I'm still going to need some sample data that illustrates the failure.

Say for "TOM", I got the following return
tbl1 user TOM
tbl2 user  TOM
tbl3 user  TOM

For "FRANK", I got the following return
tbl2 user FRANK
tbl3 user FRANK
tbl4 user FRANK

If I put both of TOM and FRANK into the code, returned the following
tbl1 user TOM
tbl2 user  TOM
tbl3 user  TOM
tbl4 user FRANK

The correct output should be
tbl1 user TOM
tbl2 user  TOM
tbl3 user  TOM
tbl2 user FRANK
tbl3 user FRANK
tbl4 user FRANK

thank you


try this...


  SELECT table_name, column_name, EXTRACTVALUE(x.COLUMN_VALUE, '/text()')
    FROM user_tab_columns,
         XMLTABLE(
             ('//' || column_name || '/text()')
             PASSING xmltype(
                         NVL(
                             DBMS_XMLGEN.getxml(
                                    'select '
                                 || column_name
                                 || ' from '
                                 || table_name
                                 || ' where  '
                                 || column_name
                                 || q'[ in ('TOM','FRANK')]'
                             ),
                             '<a/>'
                         )
                     )
         ) x
   WHERE column_name LIKE '%USER%' AND table_name LIKE 'TBL_'
ORDER BY 1, 2, 3

('//' || column_name || '/text()')
             *
ERROR at line 4:
ORA-19102: XQuery string literal expected

hmmm, worked for me exactly as posted.

This has a string literal that will still allow for semi-dynamic content in the column names.


  SELECT table_name, column_name, EXTRACTVALUE(x.COLUMN_VALUE, '/text()')
    FROM user_tab_columns,
         XMLTABLE(
             '//ROW/*/text()'
             PASSING xmltype(
                         NVL(
                             DBMS_XMLGEN.getxml(
                                    'select '
                                 || column_name
                                 || ' from '
                                 || table_name
                                 || ' where  '
                                 || column_name
                                 || q'[ in ('TOM','FRANK')]'
                             ),
                             '<a/>'
                         )
                     )
         ) x
   WHERE column_name LIKE '%USER%' AND table_name LIKE 'TBL_'
ORDER BY 1, 2, 3;

sdstuber,

Tried your suggestion and taking forever to execute it.....

Thanks.

what is "ORDER BY 1, 2, 3" for?
thanks.

just to order the data by table,column,name

you don't have to use it

Tried your suggestion and taking forever to execute it.....

How many distinct table/column combinations do you have that match your WHERE clause?

If you have 10 tables with one USER column each, then that sql is going to run 10 other sqls, each of which will be turned into xml output which is then parsed.

That's a lot of work

I have over 1000 tables match WHERE clause.

I have over 1000 tables match WHERE clause.

that's the problem

you're doing this...

select username from table1 where username in ('TOM','FRANK')
select username from table2 where username in ('TOM','FRANK')
select username from table3 where username in ('TOM','FRANK')
select username from table4 where username in ('TOM','FRANK')
select username from table5 where username in ('TOM','FRANK')
...
select username from table998 where username in ('TOM','FRANK')
select username from table999 where username in ('TOM','FRANK')
select username from table1000 where username in ('TOM','FRANK')
...

and that's assuming there is only one USER column in each table.
It'll be even more work if there are more than one column.

It's not surprising that might take a long time

what if use distinct?

what are you going to try to make distinct?

why do you think that would be faster?  

Distinct does all the work of a non-distinct query, but then adds the extra step of a sort and search/removal of duplicates.

I removed the

 rownum=1

clause, you could put that back, it should make the looping queries faster

It'll change the results, but only you can determine which is correct for your needs

with rownum=1 you'll get one row for each table/column that has any of your searched for names, and it'll report the first one it gets (which is effectively random)

without rownum=1 you'll get one row for each table/column/name that has any of your searched for names, including rows where the name appears more than once


I'm going to guess that "with rownum=1" is what you want, but it is a little odd with the quasi-random results

I want to know if is there a record in the table which has user field=tom or frank. That is the reason used rownum=1
Thx

I think it only return the table name once even if both tom and frank are fund in that table. How to make it showing table names for each user?
Thx

Thanks!