Home » SQL & PL/SQL » SQL & PL/SQL » Problem in accessing sequence (11g, server 2005)
Problem in accessing sequence [message #420887] Thu, 03 September 2009 01:16 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello all,

I have some doubt about accessing NEXTVAL of SEQUENCE.
As I have read from all sources, USER views gives list of all the objects owned by that user. While ALL views gives list of all objects on which user has rights to access.
And according to that, I should not get the error at last in following code.

SQL>select sequence_name from user_sequences;

SEQUENCE_NAME
------------------------------
SEQ

1 row selected.

SQL>select seq.nextval from dual;

   NEXTVAL
----------
  67080442

1 row selected.

SQL>select sequence_name from all_sequences;

SEQUENCE_NAME
------------------------------
SCHEDULER$_JOBSUFFIX_S
DM$EXPIMP_ID_SEQ
HS_BULK_SEQ
XDB$NAMESUFF_SEQ
SDO_WS_CONFERENCE_IDS
DBMS_MVSEQ
SDO_GEOR_SEQ
SDO_IDX_TAB_SEQUENCE
TMP_COORD_OPS
SAMPLE_SEQ
WWV_FLOW_SESSION_SEQ
WWV_SEQ
WWV_FLOW_SESSION_SEQ
WWV_SEQ
RMAN_SEQ
SEQ

16 rows selected.

SQL>select sample_seq.nextval from dual;
select sample_seq.nextval from dual
       *
ERROR at line 1:
ORA-02289: sequence does not exist


Although I have access rights on all above sequences, last select statement should not raise error.

Please correct me, wherever I am wrong.

regards,
Delna
Re: Problem in accessing sequence [message #420888 is a reply to message #420887] Thu, 03 September 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The sequence is NOT in your schema, so you have to prefix it with the schema name.

Regards
Michel
Re: Problem in accessing sequence [message #420891 is a reply to message #420888] Thu, 03 September 2009 01:46 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks Michel sir for reply.
But that is not in case of TABLE.
What can be the reason?

regards,
Delna
Re: Problem in accessing sequence [message #420893 is a reply to message #420891] Thu, 03 September 2009 02:17 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
But that is not in case of TABLE.
What can be the reason?


Not always


XE@SQL> sho user
USER is "OPS$RANIYENG\ADMINISTRATOR"

XE@SQL> select table_name,owner from all_tables where table_name in ('MAP_OBJECT
','HELP');

TABLE_NAME                     OWNER
------------------------------ ------------------------------
MAP_OBJECT                     SYS
HELP                           SYSTEM

2 rows selected.

XE@SQL> select count(*) from HELP;
select count(*) from HELP
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


XE@SQL> select count(*) from system.HELP;

  COUNT(*)
----------
       980

1 row selected.

XE@SQL> select count(*) from map_object;

  COUNT(*)
----------
         0

1 row selected.

XE@SQL>


XE@SQL> select synonym_name,owner,table_name from all_synonyms where table_name
in ('MAP_OBJECT','HELP')
  2  /

SYNONYM_NAME         OWNER      TABLE_NAME
-------------------- ---------- ----------
MAP_OBJECT           PUBLIC     MAP_OBJECT

1 row selected.


XE@SQL>

1 row selected.

XE@SQL>


1 row selected.

XE@SQL>



It works for some because of Public synonym.

[Updated on: Thu, 03 September 2009 02:23]

Report message to a moderator

Re: Problem in accessing sequence [message #420894 is a reply to message #420891] Thu, 03 September 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Thu, 03 September 2009 08:46
Thanks Michel sir for reply.
But that is not in case of TABLE.
What can be the reason?

regards,
Delna

This is the same case for a table or any object unless you have a synonym.

Regards
Michel

Re: Problem in accessing sequence [message #420903 is a reply to message #420887] Thu, 03 September 2009 03:13 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ohhhk sir,
I got it.
Thanks to Bonker sir and Michel sir.

regards,
Delna
Previous Topic: database link
Next Topic: PRAGMA AUTONOMOUS_TRANSACTION
Goto Forum:
  


Current Time: Wed Sep 28 12:40:05 CDT 2016

Total time taken to generate the page: 0.12035 seconds