1 Reply Latest reply: May 29, 2013 4:46 PM by MLBrown RSS

    Error while Creating sequence. Please help

    SGUN
      I'm using below script to create sequence but getting error
      Error report:
      SQL Error: ORA-01722: invalid number
      01722. 00000 - "invalid number"


      CREATE SEQUENCE BL_BTN_MASTER_SEQ
      MINVALUE 1
      MAXVALUE 999999999999999999
      INCREMENT BY 1
      START WITH (SELECT MAX(BULLETIN_MASTER_ID)+1
      FROM BL_BTN_MASTER)
      NOCACHE;

      FYI..Data type of bulletin_master_id column is NUMBER(22,0)
      PLease help.

      Edited by: user11228834 on May 29, 2013 10:22 AM

      Edited by: user11228834 on May 29, 2013 10:23 AM

      Edited by: user11228834 on May 29, 2013 10:25 AM
        • 1. Re: Error while Creating sequence. Please help
          MLBrown
          Oracle doesn't like the "(select max(bulletin_master_id)+1 from bl_btn_master)' statement embedded in the CREATE SEQUENCE statement because if you look at the syntax it is expecting an acual number. You could use execute immediate to create the sequence this way:

          {code}
          declare
          v_seq number;
          v_statement varchar2(200);
          begin
          select max(bulletin_master_id)+1
          into v_seq
          from bl_btn_master;

          v_statement := 'CREATE SEQUENCE BL_BTN_MASTER_SEQ ' ||
          'MINVALUE 1 ' ||
          'MAXVALUE 999999999999999999 ' ||
          'INCREMENT BY 1 ' ||
          'START WITH ' || v_seq ||
          'NOCACHE';

          execute immediate(v_statement);
          end;
          {code}