ORA-02289 drop and create sequence using execute immediate
Hi All,
i've copied a schema from one database to another.
In the old database my package is working fine and the procedure inside the package PKG_REFRESH below works like a charm.
PROCEDURE FILL_TABLE(P_PROC_ID INTEGER) AS
BEGIN
EXECUTE IMMEDIATE('drop sequence sq1');
EXECUTE IMMEDIATE('create sequence sq1 start with 1 cache 500');
v_kenmerk := 'dim_1';
INSERT INTO dim_1(
field1
, field2
...<snip>
END;
Now I've imported the package into another database and while all sequences used by procedures inside the package exist, it gives the errors below:
begin
*
FOUT in regel 1:
.ORA-02289: sequence does not exist
ORA-06512: at "OWNER.PKG_REFRESH", line 117
ORA-06512: at "OWNER.PKG_REFRESH", line 80
ORA-01031: insufficient privileges
ORA-06512: at "OWNER.PKG_REFRESH", line 20
ORA-06512: at line 3
I checked and the sequence is dropped but hasnt been recreated. However it doesnt give me an error message when trying to create the sequence.
I'm puzzled... search on this forum, google and metalink didnt help me either.
Does anyone have a clue why this is happening? Strange part is that it does work on the old database.
Robin
Edited by: RobbieNerve on 30-jun-2011 10:27 forgot to tell the procedure is inside the package