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, Aug 27 '20 at 08:12

Roberto Hernandez · Accepted Answer · 2020-08-27T09:02:25.847

0

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

edited Aug 27 '20 at 09:02

answered Aug 27 '20 at 08:24

Roberto Hernandez

8,231
3
14
43

Thanks for the reply. I tried what you said and removed the double quotes. I also tried the dbms_ouput from the max and is returning a 5 and also used dbms_ouput to see how the execute would look like: EXECUTE IMMEDIATE CREATE SEQUENCE GPP.SEQ_ENTIDADES_VINCULADAS MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 5 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL And still throws the first error, ORA-00933 SQL command not properly ended – robexpo Aug 27 '20 at 08:38
if you run this command without using PLSQL, does it work ? I mean , it should work – Roberto Hernandez Aug 27 '20 at 08:41
Ok, tried what you said and with a little test I think I saw the problem. I have to remove the last three parameteres when creating the sequence: NOKEEP, NOSCALE and GLOBAL. Don't know why but it works this way. Thanks for you help man. – robexpo Aug 27 '20 at 08:54
@robexpo, can you upvote the answer ? by the way, which version of Oracle are you using ? my test was done in 19c – Roberto Hernandez Aug 27 '20 at 08:55
you are welcome. Indeed, that is the reason. those options are not available in that old version. https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm#:~:text=To%20create%20a%20sequence%20in%20another%20user's%20schema%2C%20you%20must,CREATE%20ANY%20SEQUENCE%20system%20privilege.&text=Specify%20the%20schema%20to%20contain,sequence%20in%20your%20own%20schema.&text=Specify%20the%20name%20of%20the%20sequence%20to%20be%20created. – Roberto Hernandez Aug 27 '20 at 09:00
I would also get rid of `MINVALUE 1 MAXVALUE 99999999999999999999999999 INCREMENT BY 1` and anything else that is already the default value for any new sequence, as it's just taking up space and adding needless complication. – William Robertson Aug 27 '20 at 15:41

Creating a sequence inside an anonymous block using Execute Immediate

1 Answers1