Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using table name from user_tab_columns in a select

Ramky99Aug 18 2010 — edited Aug 19 2010
Hi all,

I need to generate a list of tables that contain rows in a certain condition. For the real situation there are hundreds of tables so I've been trying to use all_tab_columns in the query to generate the list of tables to search and then delimit by condition.

e.g.

create table adam1
(
adamname char(10)
);

create table adam2
(
adamname char(10)
);

insert into adam1 values ('ADAM');
insert into adam1 values ('BOB');
insert into adam2 values ('BOB');

...I'd like to see a list of tables that contain 'ADAM' in column 'adamname' so in this instance I'd only see table adam1 in the results.

I've tried using a cursor loop but it doesn't like me using table_name ... do I have to bring this into a variable or some such?

Thanks!
Adam
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 16 2010
Added on Aug 18 2010
3 comments
2,039 views