Re: Q: ORA-02289 error when using SELECT statement on sequence

Scott,

Does STUDENT_ID exist as a sequence object in your current schema? In Oracle, a sequence is a totally separate object from a table, and there is no implicit relationship between any given sequence and any given table. You can select number values from sequence X and insert them into table A, or table B, or display them to the user - it's completely unrestricted what you do with the numbers.

If STUDENT_ID is a column in the STUDENTS table, then you're barking up the wrong tree ... check the SQL reference manual at http://tahiti.oracle.com to find out how to use sequences properly.

Check the following info:
1. the username you are logged in as (this is your default schema owner) 2. select sequence_owner, sequence_name from all_sequences where sequence_name = 'STUDENT_ID' To select from a sequence without specifying the object owner, it has to exist in your current schema or you need a synonym which points to the sequence in another schema.

Also it seems a bit odd that your STUDENTS table is in the SYS schema - as a rule I'd suggest you always create a new user for creating new objects (the default SCOTT schema seems appropriate in your case) and not create junk tables in the SYS schema.

Let's say SCOTT is the owner of STUDENTS table and the STUDENT_ID sequence, and you create a new user PETE, then as SCOTT you GRANT SELECT ON STUDENTS TO PETE and also GRANT SELECT ON STUDENT_ID TO PETE.

Now log on as PETE and you should be able to SELECT SCOTT.STUDENT_ID.NEXTVAL FROM DUAL;
(DUAL is a default dummy table that only ever contains one row, so this will return one number from the sequence).

If you SELECT SCOTT.STUDENT_ID.NEXTVAL FROM SCOTT.STUDENTS then you'll get a list of sequential numbers as many as there are rows in the STUDENTS table. Note that this action is completely meaningless, it just illustrates the operation of the sequence.

Regards
Martin

• P.S. tip for future postings: always mention your operating system, version of Oracle, and useful things like the structure of any tables that you want to discuss (e.g. DESCRIBE STUDENTS would have helped in this case), also any titbits like are you using SQL*Plus to access the database or some other tool (not relevant in this case though) ****

Scott Stoecker wrote:

> I'm a new Oracle DB wanna be (just practicing right now), and, out of
> curiosity, I created a new user who has SELECT permission to my STUDENTS
> table. I can select all the rows just fine using a SELECT * statement, but
> when I use this statement:
>
> select STUDENT_ID.nextval from sys.STUDENTS
>
> I receive the following error:
>
> ERROR at line 1:
> ORA-02289: sequence does not exist
>
> What is happening, and how can I correct it?
>
> Thank you,
> Scott

Received on Tue Aug 27 2002 - 19:26:30 CDT