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 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
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