Solved

Oracle table or view does not exist

Posted on 2013-05-20
5
2,761 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

737 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