0

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 !

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
user442462
  • 1
  • 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 Sep 08 '10 at 13:32
  • Can't be done; shouldn't be done. – duffymo Sep 08 '10 at 13:38
  • Because JDBC doesn't allow you to bind on table or column names. – duffymo Sep 08 '10 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 Sep 08 '10 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 Sep 09 '10 at 00:43
  • I have a list of V$ views that contain interesting values (if they are not empty). The program I wrote (Java) use this list to know in which tables I can get theses values and then get regularly these value. I would like to be able to do a (quick) first passage in this list to see which tables are empty. – user442462 Sep 09 '10 at 15:22

2 Answers2

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.
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • 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... – user442462 Sep 08 '10 at 14:28
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;`
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • 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 ! – user442462 Sep 08 '10 at 14:31