drop and create sequence from a stored procedure

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

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

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.

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

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

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.

Code:

---

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;
/

---

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

Quote:

---

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";'

Quote:

---

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.

Quote:

---

Originally Posted by 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

---

When you use execute immediate with a DDL command, it is sometimes helpful to change the creation of your stored procedure so that it uses the credentials of the user that you have logged in with and tested the DDL with. as follows:

CREATE OR REPLACE PROCEDURE myProcedureName AUTHID CURRENT_USER IS

by adding AUTHID CURRENT_USER, you are assured that the procedure is using the same credentials that you have when you log on and run the DDL command directly. Still, I experience exceptions whenever I attempt to drop a sequence using "execute immediate". I'd appreciate if anyone has a suggestion as to why I might not be able to execute drop sequence commands within my stored proc as dynamic SQL.

Thank you,

Dave