I work with Delphi 6 with Update2, component ODACnet 4.50.3.24 and Oracle 9i and I am tried to restart the accountant of a sequence, but I have the following problems:
The procedure called "inicia_sequence" is compiled well in ORACLE, but when executing it in tOraStoredProc produces the following error:
ORA-02286: any option has not been specified for ALTER SEQUENCE
ORA-06512: in "DESARROLLO.INICIA_SEQUENCE" line 15
ORA-06512: on-line 2
It seems that the problem is in that tOraStoredProc doesn't recognize the symbols ||, because if the line 15 modify it in the following way:
EXECUTE IMMEDIATE 'alter sequence ' || TA_LIQ_NUM_ORDEN || string || curr_val ;
EXECUTE IMMEDIATE 'alter sequence TA_LIQ_NUM_ORDEN increment by curr_val';
Although it functions, now another error is produced. Not if it is produced because curr_val is an integer and N_NUM_ORDER is NUMBER(5,0)?
ORA-01722: number invalid
ORA-06512: in "DESARROLLO.INICIA_SEQUENCE" line 15
ORA-06512: on-line 2
Somebody can help me to solve these problems, that I am making bad.
Best Regards.
Jose Manuel
Code: Select all
CREATE TABLE "DESARROLLO"."TA_LIQUIDACIONES"
(
N_NUM_YEAR VARCHAR2(4) NOT NULL,
N_NUM_ORDEN NUMBER(5,0) NOT NULL,
)
TABLESPACE "TASA_TSD04";Code: Select all
CREATE SEQUENCE "DESARROLLO"."TA_LIQ_NUM_ORDEN" INCREMENT BY 1
START WITH 5000 MAXVALUE 99999 MINVALUE 1 NOCYCLE
NOCACHE ORDER;Code: Select all
CREATE OR REPLACE TRIGGER "DESARROLLO"."TA_LIQ_GENERAR_NUM_ORDEN"
BEFORE INSERT OR UPDATE OF "NUM_ORDEN" ON "TA_LIQUIDACIONES"
FOR EACH ROW
begin
if :new.n_num_orden is null then
select TA_LIQ_NUM_ORDEN.NextVal
into :new.n_num_orden
from Dual;
end if;
end;Code: Select all
1.CREATE OR REPLACE PROCEDURE "DESARROLLO"."INICIA_SEQUENCE" (
2.TA_LIQ_NUM_ORDEN IN VARCHAR2, startvalue in integer) AS
3.curr_val INTEGER;
4.string varchar2(25);
5.BEGIN
6.EXECUTE IMMEDIATE 'alter sequence ' || TA_LIQ_NUM_ORDEN ||' MINVALUE 0';
7.EXECUTE IMMEDIATE 'SELECT ' || TA_LIQ_NUM_ORDEN ||'.nextval FROM dual' INTO curr_val;
8.curr_val := curr_val - startvalue + 1;
9.if curr_val < 0 then
10.string := ' increment by ';
11.curr_val:= abs(curr_val);
12.else
13.string := ' increment by -';
14.end if;
15.EXECUTE IMMEDIATE 'alter sequence ' || TA_LIQ_NUM_ORDEN || string || curr_val ;
16.EXECUTE IMMEDIATE 'SELECT ' || TA_LIQ_NUM_ORDEN ||'.nextval FROM dual' INTO curr_val;
17.EXECUTE IMMEDIATE 'alter sequence ' || TA_LIQ_NUM_ORDEN ||' increment by 1';
18.END inicia_sequence;