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

Thank you, Martin! I had not realized that a sequence was a seperate object. Once I applied proper permissions to it, everything worked great.

I plan on taking your advice and create a new user to create tables and such. The link you provided has some great documentation. I have much reading ahead...

Thanks again,
Scott

"Martin Doherty" <martin.doherty_at_oracle.com> wrote in message news:3D6C18B6.20207F86_at_oracle.com...
> 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:54:30 CDT