Solved

Oracle table or view does not exist

Posted on 2013-05-20
5
2,615 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

816 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

8 Experts available now in Live!

Get 1:1 Help Now