Creating a sequence inside an anonymous block using Execute Immediate

Question

I'm trying to create a sequence inside an anonymous block (it's an script that will be launched on several environments) and I'm using execute immediate, this is the code:

SELECT MAX(ID_VINCULACION)
INTO vMAX_VINCULACION
FROM SA_ENTIDADES_VINCULADAS;

EXECUTE IMMEDIATE 'CREATE SEQUENCE "GPP"."SEQ_ENTIDADES_VINCULADAS" MINVALUE 1  MAXVALUE 99999999999999999999999999 INCREMENT BY 1 START WITH ' || vMAX_VINCULACION || ' NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL';

I'm using the select max for start with so i can get the biggest existing ID, but it is throwing this error:

ORA-00933: SQL command not properly ended ORA-06512: at line 40 ORA-06512: at line 40

I don't exactly know why, so I tried to use the Execute Immediate ... Using, this is the resulting code:

SELECT MAX(ID_VINCULACION)
    INTO vMAX_VINCULACION
    FROM SA_ENTIDADES_VINCULADAS;

EXECUTE IMMEDIATE 'CREATE SEQUENCE "GPP"."SEQ_ENTIDADES_VINCULADAS" MINVALUE 1  MAXVALUE 99999999999999999999999999 INCREMENT BY 1 START WITH :a NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL' USING vMAX_VINCULACION;

And this one throws another error... :

ORA-01722: invalid number

I don't get this one either, as the Max function returns a number. I have tried launching the query and it returns a 5.

So I'm pretty lost here, hope you can help me.

Thanks in advance.

EXECUTE IMMEDIATE is intended for statements without parameters. — jarlh

Roberto Hernandez Roberto Hernandez · Accepted Answer · 2020-08-27T08:24:05

Execute immediate is evaluated at runtime. I did a test and it works without issues ( in my test I remove double quotes, because you don't need them ). Try to print by dbms_output the result of the execute immediate. Probably your select max is not giving you the right number.

UPDATE

The options NOKEEP NOSCALE GLOBAL are not available in 10g

Create Sequence 10g

declare
v_max pls_integer;
begin
   SELECT 1000 INTO v_max FROM dual;
EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_SEQ MINVALUE 1  MAXVALUE 99999999999999999999999999 INCREMENT BY 1 START WITH '||v_max||' 
                   NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL' ;
end;
/

Test

SQL> declare
v_max pls_integer;
   begin
    SELECT 1000 INTO v_max FROM dual;
  EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_SEQ MINVALUE 1  MAXVALUE 99999999999999999999999999 INCREMENT BY 1 START WITH '||v_max||'
                    NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL' ;
end;
/  

PL/SQL procedure successfully completed.

SQL> select sequence_name, min_value, max_value from dba_sequences where sequence_name = 'MY_SEQ' ;

SEQUENCE_NAME
--------------------------------------------------------------------------------
 MIN_VALUE  MAX_VALUE
---------- ----------
MY_SEQ
         1 1.0000E+26


SQL> SQL> select my_seq.nextval from dual ;

   NEXTVAL
----------
      1000

Creating a sequence inside an anonymous block using Execute Immediate

1 Answers