| Oracle Sequence NEXTVAL Problem [message #167187] |
Tue, 11 April 2006 13:39  |
f018342
Messages: 5
Registered: April 2006
|
Junior Member |
|
|
I am trying to get the next value from the sequence to use in populating two tables that require the same "ID". It seems no matter what I do, I cannot get the value. I am using C#.
----------------------------------------------------------------
REM AVMMGR AVM_HISTORY_SEQ
CREATE SEQUENCE "AVMMGR"."AVM_HISTORY_SEQ" MINVALUE 1 MAXVALUE 999999999999999999 INCREMENT BY 1 START WITH 20 NOCACHE NOORDER NOCYCLE ;
----------------------------------------------------------------
SELECT AVM_HISTORY_SEQ.NEXTVAL AS NEXT_ID FROM DUAL
{"ORA-02289: sequence does not exist" }
----------------------------------------------------------------
SELECT AVMMGR.AVM_HISTORY_SEQ.NEXTVAL AS NEXT_ID FROM DUAL
{"ORA-00942: table or view does not exist" }
----------------------------------------------------------------
I am using Oracle's Oracle.DataAccess.Client .NET driver.
|
|
|
|
| Re: Oracle Sequence NEXTVAL Problem [message #167214 is a reply to message #167187] |
Tue, 11 April 2006 21:37  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
ORA-00942 is telling you that the table DUAL doesn't exist. If the sequence didn't exist, you'd get a different error:select a.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist
Check that you somehow haven't been denied access from DUAL.
Try as a test to be sure that this is the problem.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Oracle Sequence NEXTVAL Problem [message #167514 is a reply to message #167416] |
Thu, 13 April 2006 08:02 |
f018342
Messages: 5
Registered: April 2006
|
Junior Member |
|
|
Here's what I discovered. It appears that if I set the user and password to the owner, it works. But when I log on as another user, it fails; even if I use the "owner." with the SQL. Definitely a permissions issue.
orConn = new OracleConnection("user id=avmmgr;data source=InfoDev;password=tree02;Persist Security Info=true;");
OracleDataAdapter orDA = new OracleDataAdapter("SELECT AVMMGR.AVM_HISTORY_SEQ.NEXTVAL AS NextID FROM dual",orConn);
...works.
orConn = new OracleConnection("user id=avmapp;data source=InfoDev;password=flower4;Persist Security Info=true;");
OracleDataAdapter orDA = new OracleDataAdapter("SELECT AVMMGR.AVM_HISTORY_SEQ.NEXTVAL AS NextID FROM dual",orConn);
...does not work.
OracleDataAdapter orDA = new OracleDataAdapter("SELECT AVMAPP.AVM_HISTORY_SEQ.NEXTVAL AS NextID FROM dual",orConn);
...does not work either.
I am using the new Oracle SQL Developer tool, and it seems to have no way to set permissions for a sequence.
BTW, I am also having permissions problems with "some" tables; even though they are set up the same way. Using "Grant" to AVMAPP for each table.
Very baffling and very frustrating.
|
|
|
|
| Re: Oracle Sequence NEXTVAL Problem [message #167529 is a reply to message #167514] |
Thu, 13 April 2006 10:02 |
f018342
Messages: 5
Registered: April 2006
|
Junior Member |
|
|
I have found the problem!
It appears that Oracle's security model was getting in the way. Even though Oracle's new SQL Developer tool allows you to CREATE tables, indexes, sequences, triggers, and GRANT rights, unless you have some "super-user" rights (as the DBA), you actually cannot GRANT anything!
It gives you no error message, warning,....nothing. Once I had the DBA here set the AVMAPP user rights to the sequence (as well as other stuff I created), everything started working.
The Oracle error messages don't help much either.
Thanks for all your attention, effort, and help.
Bob
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
