ORA-01722 invalid number. I can't see why.
411930Apr 4 2004 — edited Apr 4 2004I'm trying the following code for updating the value of a sequence named the same as the column it is related:
.....................................
DECLARE
v_max NUMBER;
v_value NUMBER;
v_difference NUMBER;
v_aux NUMBER;
v_query VARCHAR2(500);
BEGIN
dbms_output.put_line('Inicio');
select max(SQ_GZL_01)
into v_max
from CSR_CT_GT_ZLOC;
dbms_output.put_line('v_max: '||v_max);
select SQ_GZL_01.nextval
into v_value
from dual;
dbms_output.put_line('v_value: '||v_value);
v_difference := v_max - v_value;
dbms_output.put_line('v_difference: '||v_difference);
IF (v_difference>0) THEN
v_query := 'alter sequence SQ_GZL_01 increment by :v_difference';
dbms_output.put_line('v_query: '||v_query);
execute immediate v_query using v_difference;
select SQ_GZL_01.nextval
into v_aux
from dual;
dbms_output.put_line('v_aux: '||v_aux);
v_query := 'alter sequence SQ_GZL_01 increment by 1';
execute immediate v_query;
select SQ_GZL_01.nextval
into v_aux
from dual;
dbms_output.put_line('v_aux: '||v_aux);
END IF;
END;
..............................
And I get:
Inicio
v_max: 12348
v_value: 13
v_difference: 12335
v_query: alter sequence SQ_GZL_01 increment by :v_difference
DECLARE
*
ERROR en línea 1:
ORA-01722: número incorrecto
ORA-06512: en línea 22
.........................
I can't see the problem, v_difference is a NUMBER so when implicitily casting it into a NUMBER I don't know what is worng