| ORA-02289: sequence does not exist [message #473299] |
Fri, 27 August 2010 05:58  |
belboey28
Messages: 2
Registered: August 2010
|
Junior Member |
|
|
Hi, I created a stored procedure(A) in user1@db1. This stored procedure will insert data from db1 to tables in user2@db2 using synonyms created in user1@db1 referencing table at user2@db2.
In the stored procedure (A),
:
:
insert into synonym select seq_xx.nextval from table@db1;
:
:
Then I will execute (A) in user2@db1. Execute right on (A) is given to user2@db1.
Select and insert grants on db2 tables are given to user1@db1.
Seq is created in db1. Compilation is successful, however when i execute (A) it has an error of
ORA-02289: sequence does not exist
ORA-02063: preceding line from db1
ORA-02063: preceding 2 lines from db2
Please advise how to resolve this.
Thanks & Regards,
Bel
|
|
|
|
|
|
| Re: ORA-02289: sequence does not exist [message #473344 is a reply to message #473299] |
Fri, 27 August 2010 08:08  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
You wrote,
Quote:
insert into synonym select seq_xx.nextval from table@db1
is WRONG!!
SYNONYM is a RESERVED word.
You can NOT do insert nor you can create table by using word SYNONYM.
So, this is wrong!
To get the next sequence value you can do this way
select seq_xx.nextval from dual;
In 11g you can write simply
I am having a hard time here to understand what you wrote. I really did not understand.
Regards
Ved
[Updated on: Fri, 27 August 2010 08:38] Report message to a moderator |
|
|
|
| Re: ORA-02289: sequence does not exist [message #473351 is a reply to message #473344] |
Fri, 27 August 2010 08:39 |
cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Its_me_ved wrote on Fri, 27 August 2010 14:08You wrote,
Quote:
insert into synonym select seq_xx.nextval from table@db1[/email]
is WRONG!!
SYNONYM is a RESERVED word.
You can NOT do insert nor you can create table by using word SYNONYM.
So, this is wrong!
I would assume that was an over-simplification of the code in an attempt to make it easier for us to understand the problem. Unfortunately it had the opposite effect.
@belboey28 - How about you show us the actual code. Like Ved I'm struggling to understand exactly what you've done here.
|
|
|
|
| Re: ORA-02289: sequence does not exist [message #473396 is a reply to message #473299] |
Fri, 27 August 2010 15:33 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You mentioned both user2@db2 and user2@db1. I don't know if that was intentional or if you meant user@db2 for both. I only have one database to test with, but I think the example below is similar to what you are trying to do. I have used connect and resource roles for simplicity, but you would probably want to specify individual privileges and quotas on your system.
SCOTT@orcl_11gR2> -- create users and privileges for testing:
SCOTT@orcl_11gR2> CREATE USER user1 IDENTIFIED BY user1
2 /
User created.
SCOTT@orcl_11gR2> CREATE USER user2 IDENTIFIED BY user2
2 /
User created.
SCOTT@orcl_11gR2> GRANT CONNECT, RESOURCE TO user1, user2
2 /
Grant succeeded.
SCOTT@orcl_11gR2> GRANT CREATE PUBLIC SYNONYM TO user1
2 /
Grant succeeded.
SCOTT@orcl_11gR2> -- create table in user2 schema in db2 and grant privileges:
SCOTT@orcl_11gR2> CONNECT user2/user2@orcl
Connected.
USER2@orcl_11gR2> CREATE TABLE user2.table2
2 (table2_id NUMBER,
3 table2_col VARCHAR2 (30))
4 /
Table created.
USER2@orcl_11gR2> GRANT INSERT ON user2.table2 TO user1
2 /
Grant succeeded.
USER2@orcl_11gR2> -- create synonym, table, test data, sequence, and procedure in user1 schema in db1
USER2@orcl_11gR2> -- and grant privileges:
USER2@orcl_11gR2> CONNECT user1/user1@orcl
Connected.
USER1@orcl_11gR2> CREATE PUBLIC SYNONYM user2_table2_syn FOR user2.table2
2 /
Synonym created.
USER1@orcl_11gR2> CREATE TABLE user1.table1
2 (table1_col VARCHAR2 (30))
3 /
Table created.
USER1@orcl_11gR2> INSERT ALL
2 INTO user1.table1 (table1_col) VALUES ('first test row')
3 INTO user1.table1 (table1_col) VALUES ('second test row')
4 SELECT * FROM DUAL
5 /
2 rows created.
USER1@orcl_11gR2> CREATE SEQUENCE user1.seq_xx
2 /
Sequence created.
USER1@orcl_11gR2> CREATE OR REPLACE PROCEDURE user1.a
2 AS
3 BEGIN
4 INSERT INTO user2_table2_syn -- db2
5 SELECT user1.seq_xx.NEXTVAL, u1t1.table1_col
6 FROM user1.table1@orcl u1t1; -- db1
7 END a;
8 /
Procedure created.
USER1@orcl_11gR2> SHOW ERRORS
No errors.
USER1@orcl_11gR2> GRANT EXECUTE ON user1.a TO user2
2 /
Grant succeeded.
USER1@orcl_11gR2> -- connect as user2 in db2, execute procedure, and check results:
USER1@orcl_11gR2> CONNECT user2/user2@orcl
Connected.
USER2@orcl_11gR2> EXEC user1.a@orcl
PL/SQL procedure successfully completed.
USER2@orcl_11gR2> SELECT * FROM user2.table2@orcl -- db2
2 /
TABLE2_ID TABLE2_COL
---------- ------------------------------
1 first test row
2 second test row
2 rows selected.
USER2@orcl_11gR2> -- clean up test environment:
USER2@orcl_11gR2> CONNECT scott/tiger
Connected.
SCOTT@orcl_11gR2> DROP PUBLIC SYNONYM user2_table2_syn
2 /
Synonym dropped.
SCOTT@orcl_11gR2> DROP USER user2 CASCADE
2 /
User dropped.
SCOTT@orcl_11gR2> DROP USER user1 CASCADE
2 /
User dropped.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: ORA-02289: sequence does not exist [message #473538 is a reply to message #473396] |
Mon, 30 August 2010 02:10 |
belboey28
Messages: 2
Registered: August 2010
|
Junior Member |
|
|
Hi, I have attached my script below for better understanding. When the package is executed, I encounter the error as such:
ORA-02289: sequence does not exist
ORA-02063: preceding line from db1
ORA-02063: preceding 2 lines from db2
-- conn sys@db1
create user user1 identified by user1
default tablespace C_DATA temporary tablespace temp
quota unlimited on C_DATA
;
GRANT CONNECT, RESOURCE TO user1;
create user user2 identified by user2
default tablespace C_DATA temporary tablespace temp
quota unlimited on C_DATA
;
GRANT CONNECT, RESOURCE TO user2;
create role ROL_BW;
grant rol_bw to user2;
grant create database link to rol_bw;
-- conn user2@db1
create database link db2 using 'db2';
-- conn sys@db2
create user user2 identified by user2
default tablespace C_DATA temporary tablespace temp
quota unlimited on C_DATA
;
grant CONNECT, RESOURCE to user2;
create role ROL_BW;
grant rol_bw to user2;
-- create bw sequences at user1@db1
create sequence seq_bw_1 start with 1 increment by 1 nocycle cache 20 noorder;
-- grant seqs at user1@db1
grant select on user1.seq_bw_1 to rol_bw;
-- create tables at user1@db1
create table table1_2 as ....
-- grant tables
grant select on user1.table1_2 to rol_bw;
-- create tables at user2@db2
create table table1 as ....
-- grant tables
grant select, insert on user2.table1 to rol_bw;
-- create synonyms at user1@db1 for tables at user2@db2.
create synonym bw_table1 for user2.table1@db2;
-- Compile the packages at user1@db1.
@package1.pks
@package1.pkb
-- grant execute
grant execute on user1.package1 to rol_bw;
-- package
CREATE OR REPLACE PACKAGE BODY package1
IS
PROCEDURE proc1 (po_err OUT VARCHAR2)
IS
BEGIN
:
:
INSERT INTO bw_table1 (field1, field2, ...)
SELECT bw_seq_1.nextval,
field2, ...
FROM table1_2;
:
:
EXCEPTION WHEN PROG_QUIT THEN
ROLLBACK;
WHEN OTHERS THEN
ROLLBACK;
po_err := ' Error msg is ' || SQLERRM;
dbms_output.put_line(po_err);
END proc1;
END package1;
/
Thanks & Regards
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
