SQL/JDBC : select query on variable tablenames

Question

I'm using Oracle DB and I would like to write a SQL query that I could then call with JDBC. I'm not very familiar with SQL so if someone can help me, that could be great ! Here is the problem. I have a table MY_TABLE wich contains a list of another tables, and I would like to keep only the nonempty tables and those that their names start by a particular string. The query I wrote is the following :

 select TABLE_NAME 
 from MY_TABLE 
 where TABLE_NAME like '%myString%' 
 and (select count(*) from TABLE_NAME where rownum=1)<>0 
 order by TABLE_NAME;`

The problem comes from the second SELECT, but I don't know how can I do to use the TABLE_NAME value.

Does someone have an idea ?

Thanks.

[Added from comments]

Actually, I need to test the V$ views contained in the ALL_CATALOG table. But if I can find another table where all these views are contained too and with a NUM_ROWS column too, it would be perfect !

There's a problem in your datamodel. It's at least not related to JDBC, it's just "a messenger" to fire and process SQL queries using Java. So I removed the tag. — BalusC
– BalusC, Commented Sep 8, 2010 at 13:32
Because JDBC doesn't allow you to bind on table or column names. — duffymo
– duffymo, Commented Sep 8, 2010 at 18:13
Isn't it possible to create a SQL function that can do that and then call the prepareCall() method from JDBC ? — user442462
– user442462, Commented Sep 8, 2010 at 18:48
Views don't have rows, and the v$view don't even have 'real' table data. If you explain what you are trying to achieve in practical terms, rather than how, you may get more useful answers — Gary Myers
– Gary Myers, Commented Sep 9, 2010 at 0:43

Jonathan Leffler · Accepted Answer · 2010-09-08 13:44:15Z

2

Standard versions of SQL do not allow you to replace 'structural elements' of the query, such as table name or column name, with variable values or place-holders.

There are a few ways to approach this.

Generate a separate SQL statement for each table name listed in MY_TABLE, and execute each in turn. Brute force, but effective.
Interrogate the system catalog directly.
Investigate whether there are JDBC metadata operations that allow you to find out about the number of rows in a table without being tied to the system catalog of the specific DBMS you are using.

edited Sep 8, 2010 at 13:44

answered Sep 8, 2010 at 13:35

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user442462 Over a year ago

1. I have done something similar the first time, it works but it takes around 20 seconds to be executed. My goal is to reduce this execution time. 2. Actually, MY_TABLE is the ALL_CATALOG table. 3. JDBC metadata operations give only information on columns, not rows...

Michael Pakhantsov · Accepted Answer · 2010-09-08 13:40:28Z

1

Can you use oracle view USER_TABLES? then query will be much easier

select TABLE_NAME 
from USER_TABLES 
where TABLE_NAME like '%myString%' 
and Num_ROWS > 0
order by TABLE_NAME;`

edited Sep 8, 2010 at 13:40

answered Sep 8, 2010 at 13:33

Michael Pakhantsov

25.5k7 gold badges63 silver badges60 bronze badges

1 Comment

user442462 Over a year ago

Actually, I need to test the V$ views contained in the ALL_CATALOG table. But if I can find another table where all these views are contained too and with a NUM_ROWS column too, it would be perfect !

Collectives™ on Stack Overflow

SQL/JDBC : select query on variable tablenames

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related