Solved

Oracle table or view does not exist

Posted on 2013-05-20
5
2,591 Views
Last Modified: 2013-05-22
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
Comment
Question by:pbruss
5 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 150 total points
ID: 39182411
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
ID: 39182422
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
 
LVL 7

Assisted Solution

by:Docteur_Z
Docteur_Z earned 50 total points
ID: 39186791
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
 

Author Comment

by:pbruss
ID: 39187411
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
 

Author Closing Comment

by:pbruss
ID: 39187425
All three answers provided essentially the same solution, so I am giving most of the credit to the intial response.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now