• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

java.sql.SQLException: ORA-00903: invalid table name

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello all,

I'm trying to use a prepared statement to query a table. The name of the table to be queried, is passed into the prepared statement.
Here's a sample of my code:
stmt = oraConn.prepareStatement(SDO_FILTERS);
stmt.setString(1, tableName);
ors = (OracleResultSet) stmt.executeQuery();
- - - - - - - - - - - - - - - -
public static final String SDO_FILTERS =
"SELECT * FROM ? ";
When I run the code I get an invalid table name error. I think that the problem might be that the setString method stores the tableName with inverted commas around it.
Can anybody suggest a way around this problem please ?
Thanks in advance,
Trish.
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My guess is that Oracle is trying to parse your prepared statement and fails becuase at this point there is no table name, and therefore the error.
You can try creating a stored procedure that takes the tablename and uses Oracle's dynamic query package, then your query can call the stored procedure and be able to be a prepared statement.
Mark
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Trish,
Unfortunately, the "PreparedStatement" does not allow using "?" place-holders for things like table names or column names -- only for literal values. Hence your "PreparedStatement" is causing a runtime error.
In my opinion, you have two options:
  • Build the SQL string, example:

  • As Mark has suggested, there are Oracle specific alternatives available that you can use.


  • Regarding the Oracle alternatives.
    (Note that they all require the use of PL/SQL.)
  • Since Oracle version 7, there is the DBMS_SQL package that can be used to execute dynamic SQL.
  • Since Oracle 8, the is Native Dynamic SQL (NDS).


  • More details can be found in the Oracle documentation. Here are links to the relevant documentation (for Oracle 9i):
    http://www.developer.com/java/other/article.php/2212401
    http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam.htm#4376
    Hope this helps.
    Good Luck,
    Avi.
     
    Avi Abrami
    Ranch Hand
    Posts: 1143
    1
    Eclipse IDE Oracle Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Trish,
    Terribly sorry. That first link should be:
    http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql.htm#998100
    Pardon me,
    Avi.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    reply
      Bookmark Topic Watch Topic
    • New Topic