• 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

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

 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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=?");
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
them good ole boys were drinking whiskey and rye singin' this'll be the day that I die. Drink tiny ad.
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic