Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: PLSQL Help Please

    I'm in the process of porting an application to Oracle 9i. I'll be the first to admit that the PLSQL is a tad frustrating, mostly due to lack of descriptive error messages and my lack of experience... ; anyways....

    What I'm trying to do is write a stored proc that will determine the max(ID) of a table and create a sequence starting at ID + 1

    This is what I've got so far...

    1 (tblnme IN varchar2, sqname IN varchar2)
    2 as
    3
    4 s integer;
    5 begin
    6
    7 EXECUTE IMMEDIATE 'SELECT max(id) from ' || tblnme || ' ' INTO s;
    8 IF s is null then
    9 EXECUTE IMMEDIATE 'CREATE OR REPLACE SEQUENCE ' || sqname 10 || ' START WITH 1';
    11 else
    12
    13 EXECUTE IMMEDIATE 'CREATE OR REPLACE SEQUENCE ' || sqname 14 || 'START WITH ' || to_char(s + 1) ;
    15 end iF;
    16 end;

    I always get the message on either EXECUTE IMMEDIATE statements with the message

    ERROR at line 1:
    ORA-00922: missing or invalid option
    ORA-06512: at "AAFC.AUTOSEQUENCE", line 13
    ORA-06512: at line 1


    Occuring at line 7 or 13, depending if MAX(ID) returned Null or not. Can anyone describe the proper syntax of EXECUTE IMMEDIATE any why what I've got is giving me stroke....

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    1. I always wonder why it is SO important that every sequence HAS to increment exactly by 1 with no possible exceptions

    2. i think with some dynamic sql and a cursor and maybe a ref cursor
    you can do what you want.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: PLSQL Help Please

    Whenever I have trouble with an execute immediate call I build the string and run it from sql-plus. That way I know what is wrong with my statement.

    So I would try something like


    Code:
    (tblnme IN varchar2, sqname IN varchar2) as
    
       s    integer;
       str varchar2(250);
    BEGIN
     
       EXECUTE IMMEDIATE 'SELECT max(id) from ' || tblnme || ' ' INTO s;
    
       IF s is null then
          str :=  'CREATE OR REPLACE SEQUENCE ' || sqname 10 || ' START WITH 1';
       Else
          str := 'CREATE OR REPLACE SEQUENCE ' || sqname 14 || 'START WITH ' || to_char(s + 1) ;
       END IF;
    
       DBMS_OUTPUT.PUT_LINE(str);
       --EXECUTE IMMEDIATE str;
    
    END;
    Take what is displayed through dbms_output, check that is what you were expecting, and then execute it stand alone. That should point you in the right direction.

    HIH

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PLSQL Help Please

    1) There is no "OR REPLACE" option for sequences

    2) You need a space between the sequence name and "START WITH"

    3) You could simplify to:

    EXECUTE IMMEDIATE 'SELECT nvl(max(id),1) from ' || tblnme || ' ' INTO s;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || sqname || ' START WITH ' || to_char(s + 1) ;

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Since it looks like you are attempting to create gap-free sequence
    numbers I thought maybe I would save you time since it is impossible.

    For a follow-up on sequences and how you will never
    get perfect incremtals here is an except from asktom.com:
    Code:
    You should never count on a sequence generating anything even close to a gap 
    free sequence of numbers.  They are a high speed, extremely scalable multi-user 
    way to generate surrogate keys for a table.
    
    There is never a chance of a deadlock on a sequence -- they are designed to be 
    multi-user and highly scalable.  They remove the concurrency related issues 
    normally associated with unique key generation.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Feb 2004
    Posts
    45
    skinny, it might help to know what you are trying to accomplish. Please reply and describe what you need to do from a functional (i.e. something our Mothers would understand) perspective rather than the code.
    It was working just 5 minutes ago - I promise !

  7. #7
    Join Date
    Feb 2004
    Posts
    11

    Re: PLSQL Help Please

    Originally posted by skinny
    I'm in the process of porting an application to Oracle 9i. I'll be the first to admit that the PLSQL is a tad frustrating, mostly due to lack of descriptive error messages and my lack of experience... ; anyways....

    What I'm trying to do is write a stored proc that will determine the max(ID) of a table and create a sequence starting at ID + 1

    This is what I've got so far...

    1 (tblnme IN varchar2, sqname IN varchar2)
    2 as
    3
    4 s integer;
    5 begin
    6
    7 EXECUTE IMMEDIATE 'SELECT max(id) from ' || tblnme || ' ' INTO s;
    8 IF s is null then
    9 EXECUTE IMMEDIATE 'CREATE OR REPLACE SEQUENCE ' || sqname 10 || ' START WITH 1';
    11 else
    12
    13 EXECUTE IMMEDIATE 'CREATE OR REPLACE SEQUENCE ' || sqname 14 || 'START WITH ' || to_char(s + 1) ;
    15 end iF;
    16 end;

    I always get the message on either EXECUTE IMMEDIATE statements with the message

    ERROR at line 1:
    ORA-00922: missing or invalid option
    ORA-06512: at "AAFC.AUTOSEQUENCE", line 13
    ORA-06512: at line 1


    Occuring at line 7 or 13, depending if MAX(ID) returned Null or not. Can anyone describe the proper syntax of EXECUTE IMMEDIATE any why what I've got is giving me stroke....
    Hi I don't know the exact reason..but why did you use
    two single quotes in the 7th line..before INTO clause..if you used that for space i think you have to use another concatination symbol. (||)

    7 EXECUTE IMMEDIATE 'SELECT max(id) from ' || tblnme || ' ' INTO s;

    Thanks.
    learning Oracle

  8. #8
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    line 7 has unmatched quotes.
    line 13 lacks a space before START WITH.

    also, it's good practice to put your statement into a variable and then execute the variable. for example,

    declare
    v_mysql varchar2(1000);
    v_myid number;
    begin
    v_mysql := 'select max(id) from test_table';
    execute immediate v_mysql into v_myid;
    end;
    /

    one reason is you can run into bugs where you need to double assign prior to execution, so as long as you must assign to variables in some cases why not always do it. for example, if you build your dynamic SQL within a cursor declaration then it must be reassigned to a variable for proper execution.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  9. #9
    Join Date
    Jan 2004
    Posts
    6
    Thanks for the input guys.

    So many follow up questions it hard to know where to start...

    (having tried an obvious suggestion)

    Thanks to andrewst who pointed out to not use "CREATE OR REPLACE"... that was the ticket.

    Might I add, a gap free sequence WASN'T what I'm after; simply a sequence start point that would not violate the primary key. Thanks for comin out rubber ducky.

    Thanks to those with useful comments, your advise has been invaluble once again.

Posting Permissions

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