why this code does not work in sqlplus?

From: Guang Mei <zlmei_at_hotmail.com>
Date: Mon, 22 May 2000 16:43:12 GMT
Message-Id: <10505.106252@fatcity.com>

Hi:

I asked the question a couple of hours ago and now I already solve the problem. I am using dynamic sql and wrote a package to do it. Initially I thought there might be a "quicker" or "easier" way to do it in sqlplus.

Thanks for all the replies.

By the way, here is the code:

PROCEDURE cre_seq_promotion
IS

  lSQL                         VARCHAR2(2000);
  lCURSOR_HANDLE               INTEGER;
  lSTATUS                      INTEGER;

  lPROMOTIONID_MAX          NUMBER;
  lPROMOTIONID_NEXT         NUMBER;

BEGIN   SELECT MAX(PROMOTIONID)
  INTO lPROMOTIONID_MAX
  FROM HPXPROMOTION;   lPROMOTIONID_NEXT := lPROMOTIONID_MAX + 1 ;

  --
  -- Create new oracle seuence
  --

  lSQL := 'CREATE SEQUENCE HPXSEQ_Promotion INCREMENT BY 1 '


          || ' START WITH ' || lPROMOTIONID_NEXT

          || ' MAXVALUE   999999999'

          || ' MINVALUE   1'

          || ' NOCYCLE'

          || ' NOCACHE';

DBMS_OUTPUT.PUT_LINE (lSQL);

lCURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (lCURSOR_HANDLE, lSQL,dbms_SQL.V7); DBMS_SQL.CLOSE_CURSOR (lCURSOR_HANDLE);

END cre_seq_promotion;

I am trying to create some oracle sequence based on the current max ID value. Does any one know why it does not work in sqlplus? I got the following error:

MAX(PROMOTIONID)

           69819
       START WITH :maxpromotionid +1
                  *

ERROR at line 3:
ORA-01722: invalid number

Here is the script:

variable maxpromotionid NUMBER;

select max(promotionID)
into :maxpromotionid
from hpxpromotion;

CREATE SEQUENCE HPXSEQ_Promotion

       INCREMENT BY 1
       START WITH :maxpromotionid +1
       MAXVALUE   999999999
       MINVALUE   1
       NOCYCLE
       NOCACHE;
----------------------

If you reply, please send me a copy to zlmei_at_hotmail.com

Thanks.

Guang

Received on Mon May 22 2000 - 11:43:12 CDT

why this code does not work in sqlplus? - issue resolved