CODEWITHSUNDEEP
sqloracle-database
user3752511
user3752511

Reputation: 91

sequence doesn't exist ORA-02289

I have a problem getting my SEQUENCE. I have created a sequence as administrator and have grant select and alter privileges to the other user. 

CREATE SEQUENCE  "OWNER"."TOT_SEQ"  MINVALUE 1000 MAXVALUE 1000000000 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE ;

grant select,ALTER on TOT_SEQ to user;
commit;

When I do this : 

select sequence_name from all_sequences;

TOT_SEQ

I can see my SEQUENCE in the list.

But I can't access the sequence in my code. using : 

select <SEQUNCE_name>.nextval from dual;

What am I doing wrong?

Upvotes: 7

Views: 138256

Answers (5)

sachinKRT
sachinKRT

Reputation: 1

As it is unable to find the sequence from the table. So, create public SYNONYM for the sequence by which SYS will have the privilege's to access the sequence next value. your problem will be solve, So does mine.

Upvotes: 0

Ishaq
Ishaq

Reputation: 1

Double check the priviliges of the schema which you are using. Also verify by putting schema name in the code.

Upvotes: 0

tak3shi
tak3shi

Reputation: 2405

Make sure that you create the sequence in uppercase, even if you use lower case in trigger / select statement.

Upvotes: 5

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

Have you tried using the fully qualified name in your code?

select <owner>.<SEQUNCE_name>.nextval from dual;

If you already have, can you edit the question to post the output of the following commands. The "OWNER", "USER" in your examples are a bit confusing.

select sequence, owner from all_sequences where sequence_name = 'TOT_SEQ'; select grantor, table_name, privilege from all_tab_privs where sequence_name = 'TOT_SEQ';

Upvotes: 3

gvenzl
gvenzl

Reputation: 1891

You will either have to fully qualify your sequence via:

SELECT <owner>.<sequence name>.nextval FROM dual;

Or create a public synonym for it:

CREATE PUBLIC SYNONYM TOT_SEQ for OWNER.TOT_SEQ;
SELECT TOT_SEQ.nexval FROM DUAL;

Upvotes: 19

Related Questions