Sequence object access in 9i

From: Mike <mberger_at_skypoint.com>
Date: Wed, 15 Dec 2004 14:07:22 -0600
Message-ID: <Vr6dnVYeV5lqBF3cRVnyiQ_at_skypoint.com>



[Quoted] [Quoted] Being a relative newbie to Oracle, I hope this question is coherent... (and in the correct place)
[Quoted] I have a schema 'sa5' with admin user of 'sa5'

I execute (via a VB program) an SQL statement INSERT INTO table-name(x1, x2 ,x3...) VALUES (table-name-seq.NextVal, x2-val, x3-val...)

all this is good.

I also have a user 'sauser', and when I try the VB program using that user, I get the error
ORA-02289 sequence does not exist.

[Quoted] So, I google around and read up on Sequence Objects. Figured out what they [Quoted] are, and came to the conclusion that the user 'sauser' must not have proper permissions.

[Quoted] Using pl/sql, signing in as 'sauser' and executing........ select table-name-seq.NextVal from DUAL
ORA-02289 sequence does not exist
select sa5.table-name-seq.NextVal from DUAL ORA-01031 insufficent privileges

So, I use OEM and go to the sa5 schema and for Sequence object table-name-seq, I right-click and 'Grant Privileges On...' I select 'SELECT' for the 'sauser' user (I do not click the With grant option)

Now, using pl/sql, signing in as 'sauser' and executing

select table-name-seq.NextVal from DUAL
ORA-02289 sequence does not exist
select sa5.table-name-seq.NextVal from DUAL seems to work

What I want to happen is to get
select table-name-seq.NextVal from DUAL
to work (so I don't need the sa5. prefix on table-name-seq.NextVal)

What am I missing ?

Thanks
Mike Received on Wed Dec 15 2004 - 21:07:22 CET

Original text of this message