Sequence object access in 9i

[Mike]

Being a relative newbie to Oracle, I hope this question is coherent... (and
in the correct place)
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.

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

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

[FredBear]

"Mike" <mberger@skypoint.com> a écrit dans le message de news:Vr6dnVYeV5lqBF3cRVnyiQ@skypoint.com...
| Being a relative newbie to Oracle, I hope this question is coherent... (and
| in the correct place)
| 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.
|
| So, I google around and read up on Sequence Objects. Figured out what they
| are, and came to the conclusion that the user 'sauser' must not have proper
| permissions.
|
| 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
|
|

connected as sauser create a synonym:
create synonym table-name-seq for sa5.table-name-seq;

Regards
Michel Cadot

[Mike]

Perfect !!! Thanks for the quick response Michel

Mike

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:41c09afe$0$11577$626a14ce@news.free.fr... "Mike" <mberger@skypoint.com> a écrit dans le message de
news:Vr6dnVYeV5lqBF3cRVnyiQ@skypoint.com... | Being a relative newbie to Oracle, I hope this question is coherent...
(and | in the correct place) | 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. | | So, I google around and read up on Sequence Objects. Figured out what
they | are, and came to the conclusion that the user 'sauser' must not have
proper | permissions. | | 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 | | connected as sauser create a synonym: create synonym table-name-seq for sa5.table-name-seq; Regards Michel Cadot