• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3093
  • Last Modified:

Oracle table or view does not exist

I am attempting to have some data inserted into an Oracle 10g database. Prior to insertion, I have a scritp run (I don't have direct access to the database and have to request the script be run by the customer's database group) that deletes all of the data from my tables, deletes my sequences and re-initializes them. I have this done at two separate locations. One works fine and the other generates the below stack trace. We have verified that the identified sequence exists. Any thoughts on what else may be causing the error and on what I would need to ask the database group to do to verify it would be greatly appreciate.

2013-05-20 11:56:11,872 DEBUG [org.hibernate.SQL] select DB.INC_HEADER_SEQ.nextval from dual
2013-05-20 11:56:11,903 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 942, SQLState: 42000
2013-05-20 11:56:11,903 ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-00942: table or view does not exist

2013-05-20 11:56:11,903 ERROR [org.jboss.ejb.plugins.LogInterceptor] RuntimeException in method: public abstract boolean com.db.facade.interfaces.IIMSDatabaseLocal.updateIncident(java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String) throws com.db.exception.SplitMergeException,com.db.exception.IncidentNotFoundException,com.gd.patterns.j2ee.ServiceLocatorException:
org.hibernate.exception.SQLGrammarException: could not get next sequence value
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96)
      at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:91)
      at org.hibernate.event.def.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:168)
      at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:105)
      at org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:644)
      at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:630)
      at org.hibernate.engine.CascadingAction$6.cascade(CascadingAction.java:156)
      at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:213)
      at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:157)
      at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:108)
      at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:290)
      at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:185)
      at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:160)
      at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:108)
      at org.hibernate.engine.Cascade.cascade(Cascade.java:248)
      at org.hibernate.event.def.DefaultMergeEventListener.cascadeOnMerge(DefaultMergeEventListener.java:330)
      at org.hibernate.event.def.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:244)
      at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:102)
      at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:53)
      at org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:636)
      at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:622)
      at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:626)
0
pbruss
Asked:
pbruss
3 Solutions
 
slightwv (䄆 Netminder) Commented:
This would be real strange but verify the user you are connecting to the database as can select from dual.

Then verify they can select from the sequence.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Which Oracle database user runs the script?  The error indicates that the INC_HEADER_SEQ sequence in the "DB" schema is not found (or is not available to the user trying to execute it).  If the Oracle user that tries to use this sequence is someone other than "DB", maybe the "DB" user hasn't granted permission to this user to access that sequence.
0
 
Docteur_ZCommented:
Unexisting sequence would get following message :
ORA-02289: sequence does not exist

Open in new window


Here the message is
ORA-00942: table or view does not exist

Open in new window

So I'd think about a grant issue, someone revoke DUAL from PUBLIC e.g.
0
 
pbrussAuthor Commented:
Thanks for the responses, and sorry for the delay in responding. As I mentioned, I don't have access to the database and thus am at the mercy of the customer's response time. As speculated this was a permissions issue that was caused by the script that we had the customer run. The issue was that we had them drop and re-create the sequences, which appears to have blown away the privileges for our user. Interestingly this does not cause an issue on one system (same project, but a different customer and different Oracle configuration). I am not a database expert so I don't have a lot of details on the different configurations. I do know that the one that fails is uses an Oracle RAC configuration is just a standard Oracle installation.
0
 
pbrussAuthor Commented:
All three answers provided essentially the same solution, so I am giving most of the credit to the intial response.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now