drop and create sequence from a stored procedure

[liorjsb]

Hi

I'm trying to drop and create a sequence from a stored procedure vut I get:

PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

begin function package pragma procedure subtype type use
for

I have the permissions to do it outside the stored procedure.

is there a special place on the procedure I should put it?

Thanks

[DaPi]

You'll want EXECUTE IMMEDIATE:
http://www.csee.umbc.edu/help/oracle...dynam.htm#8845

[liorjsb]

Thanks

I used dbms_sql.parse which worked:


FUNCTION GET_SEQ_FN
RETURN NUMBER IS
cur_id NUMBER;
BEGIN
cur_id := DBMS_SQL.OPEN_CURSOR;
dbms_sql.parse(cur_id,'DROP SEQUENCE TEST',dbms_sql.v7);
dbms_sql.parse(cur_id,'CREATE SEQUENCE TEST START WITH '||v_bid_objId||' INCREMENT BY 1 NOCACHE',dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cur_id);
END;

Thanks again.

[slimdave]

How come you use DBMS_SQL instead of "execute immediate"?

[liorjsb]

you are right it didn't work...

so I'm now trying the dynamic sql:

drop_seq_str := 'DROP SEQUENCE seq_bid';
create_seq_str := 'CREATE SEQUENCE seq_bid START WITH :v1 INCREMENT BY 1 NOCACHE';
EXECUTE IMMEDIATE drop_seq_str;
EXECUTE IMMEDIATE create_seq_str using v_bid_objId;

the drop is being done but the create isn't. though there is a value in the v_bid_objId;

so, I've tried a nother option:

CREATE OR REPLACE PROCEDURE test
IS
test number := 7;
drop_seq_str VARCHAR2(200);
create_seq_str VARCHAR2(200);
begin
drop_seq_str := 'DROP SEQUENCE TEST';
create_seq_str := 'CREATE SEQUENCE TEST START WITH '||test||' INCREMENT BY 1 NOCACHE';
EXECUTE IMMEDIATE drop_seq_str;
EXECUTE IMMEDIATE create_seq_str;
end;
/

but:
a. if the sequence doesn't exists it will give me an error on the drop. but I still need to recreate every time the procedure runs so how do I first check if it exists.
b. when I took off the drop I got:
1. if I did create_seq_str := 'CREATE SEQUENCE TEST START WITH :v1 INCREMENT BY 1 NOCACHE';
and the EXECUTE IMMEDIATE create_seq_str using test;
I got: ORA-01722: invalid number
2. when I changed it to the way you see it above I got insufficient privileges, even though I've got priviliges to drop and create.

can you please help?

Thanks

[slimdave]

Look out for name space conflicts ... you are trying to create a sequence called "TEST" when you already have a procedure called "TEST". That'd probably be an issue.

Wrap the 'drop sequence' block in it's own begin .. end block, and use an exception clause to get oracle errors ignored.

CREATE OR REPLACE PROCEDURE my_proc
IS
   l_seq_start_with number := 7;
begin
   begin
      execute immediate 'DROP SEQUENCE MY_SEQ';
   exception
      when others then null;
   end;
   execute immediate
      'CREATE SEQUENCE MY_SEQ START WITH '
         ||to_char(l_seq_start_with)
         ||' INCREMENT BY 1 NOCACHE';
end;
/

[liorjsb]

Thanks, it helped and I don't get the invalid number
but I still get insufficient privileges for creating the sequence even though the user has it and can create it.

how can I check if the sequence exists before I create it? or just set it to a different value.

Thanks

[efrijters]

Originally posted by liorjsb
how can I check if the sequence exists before I create it?

Maybe old news, but you can create a cursor like 'select view_name from user_views where view_name="my_view";'

[Lily_Liu_2004]

Originally posted by liorjsb
I have the permissions to do it outside the stored procedure.

For inside a stored procedure, make sure that you have the
permission directly granted to you, not just based on role.