SQL/JDBC : select query on variable tablenames

Question

Hi all,

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 !

Answer 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;`

Answer 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.

1. Generate a separate SQL statement for each table name listed in MY_TABLE, and execute each in turn. Brute force, but effective.
2. Interrogate the system catalog directly.
3. 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.