Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    15

    Unanswered: Getting error in PL/sql running drop statements

    I have a PL/SQL program that I am executing in SqlPlus from a unix shell script.
    This is my code:

    set serveroutput on;
    Declare
    Begin
    DROP sequence s_name_1.name_id_seq;
    CREATE sequence s_name_1.name_id_seq minvalue 100 START WITH 100 increment BY 1 cache 20;
    EXCEPTION
    When Others than
    dbms_output.put_line ('Error in drop or create seq"');
    END;
    /
    exit

    I am getting the following error:
    DROP sequence s_name_1.name_id_seq;
    *
    ERROR at line 6:
    OA-06550 line 6, column 5;
    Encountered the symbol "DROP" when expecting one of the following:
    (begin case declare exit for goto if loop mod null pragma.....)


    Not sure what I am doing wrong here. Any suggestions would be appreciated. Also, I am doing drop/creates on multiple sequences in this sql, just didn't list them all.
    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you can NOT do DDL from inside PL/SQL (directly)
    EXECUTE IMMEDIATE must be (ab)used to do so

    WHY using PL/SQL & not straight SQL?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2010
    Posts
    15

    executing drop and create seqence

    I originally had this as a sql that just contains line of drops and creates. We have a big conversion going on and the goal is to automate as much as possible by using unix scripts, in which I use SQLPlus to execute the sql program. So, I made this into a PL/Sql and wrapped the begin/exeception/end around all the drop/create statements. My thought was if there was a problem with one of the drops or creates, it would go to the exception "When others" and I would exit the program. I need to know for certain that all of these drops/create sequences were sucessfully before letting the next job in the unix script run. As I said, I have never used drop/create and built sequences so I'm not really sure the best way to handle this.
    Thanks for your reply. How would you suggest doing this.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I do not see where attempting to wrap SQL inside of PL/SQL gains you anything.
    Dropping a non-existent SEQUENCE will throw an error; while CREATE SEQUENCE should succeed.

    SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'SEQUENCE';

    If you get the desired/expected value from SELECT, you know next step can proceed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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