CODEWITHSUNDEEP
sqloracle-databasedatabase-sequence
Muhammad Waheed
Muhammad Waheed

Reputation: 1088

Sequence neither SELECT nor CREATE in Oracle

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?

Upvotes: 2

Views: 387

Answers (2)

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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.

Upvotes: 3

Related Questions