Sequence neither SELECT nor CREATE in Oracle

Question

I am facing a problem while using sequence in Oracle 11g Express Edition. It's neither accessible nor created. I tried this query to get NEXTVAL of sequence.

select SEQ_PATIENT.nextval from dual;

It displays error

ORA-02289: sequence does not exist

Then I tried to CREATE the SYNONYM for above sequence as below

create synonym SEQ_PATIENT
for scott.SEQ_PATIENT;

and it returns

ORA-00955: name is already used by an existing object

Why is it so?

How about `select scott.SEQ_PATIENT.nextval from dual;` ? – GMB Feb 04 '19 at 12:43 — GMB, Feb 04 '19 at 12:43
It doesn't make any effect at all – Muhammad Waheed Feb 04 '19 at 13:13 — Muhammad Waheed, Feb 04 '19 at 13:13

score 3 · Accepted Answer · answered Feb 04 '19 at 13:08

This:

select SEQ_PATIENT.nextval from dual;

means that you want to select next value from a sequence whose name is SEQ_PATIENT and it belongs to current schema (i.e. the user you're connected to). Oracle says that you don't have that sequence.

This:

create synonym SEQ_PATIENT for scott.SEQ_PATIENT;

tries to create a synonym (NOT a sequence!) named SEQ_PATIENT for object whose name is SEQ_PATIENT which belongs to user Scott. Oracle says that object with name SEQ_PATIENT already exists.

So: how are you connected to the database? Which user is it?

What is the result of

select * from all_objects where object_name = 'SEQ_PATIENT';

It should tell you who owns it and what it is. Depending on its result, we'll be able to suggest further steps.

score 1 · Answer 2 · answered Feb 04 '19 at 13:16

1

I just used DROP SYNONYM and again used CREATE it worked fine.

answered Feb 04 '19 at 13:16

Muhammad Waheed

1,048
1
13
30

Sequence neither SELECT nor CREATE in Oracle

2 Answers2