DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: drop and create sequence from a stored procedure

  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

  3. #3
    Join Date
    Feb 2004
    Posts
    11
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How come you use DBMS_SQL instead of "execute immediate"?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2004
    Posts
    11
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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;
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2004
    Posts
    11
    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

  8. #8
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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";'
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  9. #9
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    Re: drop and create sequence from a stored procedure

    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.

  10. #10
    Join Date
    Nov 2015
    Posts
    1
    Quote Originally Posted by liorjsb View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width