It's not a secret anymore!*
The moose likes Oracle/OAS and the fly likes JDBC ORACLE - java.sql.SQLException: ORA-3160 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "JDBC ORACLE - java.sql.SQLException: ORA-3160" Watch "JDBC ORACLE - java.sql.SQLException: ORA-3160" New topic
Author

JDBC ORACLE - java.sql.SQLException: ORA-3160

Tim Jones
Greenhorn

Joined: Apr 06, 2008
Posts: 28
Hi

Receiving the following sql exception after executing a jdbc prepared statement to retrieve all Index's that belong to a particular owner (see below). There are no index's in the schema and should return an empty result set, instead it points to an object that does not exist when I run the "select" statement from sql-plus command prompt - returns a message stating no index's within the schema.

Error Message:

INDEX=> BIN$k5756ypjQbW1+nVK39KJ3g==$0
java.sql.SQLException: ORA-31603: object "BIN$k5756ypjQbW1+nVK39KJ3g==$0" of type INDEX not found in schema "TEST"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1


JDBC Statement:

ps = conn.prepareStatement("SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE IN \'INDEX\' AND OWNER=?");
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

This is an index stored in the Recycle bin - a feature introduced in Oracle 10g. Try to look up how to distinguish objects in Recycle bin from normal valid objects, there might be some flag in the dictionary views for it. When I last needed it, I modified the where clause to exclude objects like 'BIN$%', but I don't know whether this is the correct way to do it.

Moreover, you're selecting from DBA_OBJECTS. This view contains all objects in the database, including those you don't have any grants for (and you need quite a strong privilege to do so, most DBA's should object to grant you these privileges if your application is not the only one in the database). Depending on what you need to do, ALL_OBJECTS might be more appropriate. The error message might be actually related to this issue, not the recycle bin.
Tim Jones
Greenhorn

Joined: Apr 06, 2008
Posts: 28
Martin Vajsar wrote:This is an index stored in the Recycle bin - a feature introduced in Oracle 10g. Try to look up how to distinguish objects in Recycle bin from normal valid objects, there might be some flag in the dictionary views for it. When I last needed it, I modified the where clause to exclude objects like 'BIN$%', but I don't know whether this is the correct way to do it.

Moreover, you're selecting from DBA_OBJECTS. This view contains all objects in the database, including those you don't have any grants for (and you need quite a strong privilege to do so, most DBA's should object to grant you these privileges if your application is not the only one in the database). Depending on what you need to do, ALL_OBJECTS might be more appropriate. The error message might be actually related to this issue, not the recycle bin.


I have changed DBA_OBJECTS to ALL_OBJECTS throughout my code and for added measure I think I will modify the above statement with a WHERE CLAUSE to ignore objects name 'BIN$%', once I figure out the correct syntax (not very familiar with SQL).

Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC ORACLE - java.sql.SQLException: ORA-3160
 
Similar Threads
Oracle custom object from java
PL / SQL to create synonyms for all tables
SQL Exception Weblogic9.1 Oracle 10g
SQL injection?
CallableStatement Error on Oracle Stored Procedure.