Código:
CREATE OR REPLACE PROCEDURE SP_SEQ_TEST
AS
NOM_SEQ VARCHAR2(100);
ULT_SEQ NUMBER;
VAL_SEQ NUMBER;
NEXT_SEQ NUMBER;
VAR_EXEC VARCHAR2(200);
CURSOR C_SEQ IS
SELECT seQuence_name, LAST_NUMBER
from all_sequenceS
where sequence_name like '%SPRIDEN_SURROGATE%'
or sequence_name like '%GTVZIPC_SURROGATE%'
or sequence_name like '%STVCNTY_SURROGATE%'
or sequence_name like '%SPRADDR_SURROGATE%'
or sequence_name like '%SPRTELE_SURROGATE%'
or sequence_name like '%SPBPERS_SURROGATE%'
or sequence_name like '%GOREMAL_SURROGATE%'
or sequence_name like '%SPREMRG_SURROGATE%'
or sequence_name like '%GOBINTL_SURROGATE%'
or sequence_name like '%SPRMEDI_SURROGATE%'
or sequence_name like '%SPRHOLD_SURROGATE%'
or sequence_name like '%SORFOLK_SURROGATE%'
or sequence_name like '%SGBSTDN_SURROGATE%'
or sequence_name like '%SGRSATT_SURROGATE%'
or sequence_name like '%SGRSCMT_SURROGATE%'
or sequence_name like '%SGRCHRT_SURROGATE%'
or sequence_name like '%SIBINST_SURROGATE%'
or sequence_name like '%SGRADVR_SURROGATE%'
or sequence_name like '%SHRTTRM_SURROGATE%'
or sequence_name like '%SHRTCKN_SURROGATE%'
or sequence_name like '%SHRTCKG_SURROGATE%'
or sequence_name like '%SHRTCKL_SURROGATE%'
or sequence_name like '%SARADAP_SURROGATE%'
or sequence_name like '%SARAPPD_SURROGATE%'
or sequence_name like '%SORHSCH_SURROGATE%'
or sequence_name like '%SYRCRMI_SURROGATE%'
or sequence_name like '%TBRACCD_SURROGATE%'
or sequence_name like '%STVSBGI_SURROGATE%'
or sequence_name like '%SORBTAG_SURROGATE%'
or sequence_name like '%SORBTAL_SURROGATE%'
or sequence_name like '%SHRTGRD_SURROGATE%'
or sequence_name like '%SHBTATC_SURROGATE%'
or sequence_name like '%SHRTATC_SURROGATE%'
or sequence_name like '%SORBDMO_SURROGATE%'
or sequence_name like '%SOBSBGI_SURROGATE%'
or sequence_name like '%STVBCHR_SURROGATE%'
or sequence_name like '%SORBCHR_SURROGATE%'
or sequence_name like '%SORBCMT_SURROGATE%'
or sequence_name like '%SORTEST_SURROGATE%'
or sequence_name like '%STVCIPC_SURROGATE%'
or sequence_name like '%STVTESC_SURROGATE%'
or sequence_name like '%STVTSRC_SURROGATE%'
or sequence_name like '%SSBSECT_SURROGATE%'
or sequence_name like '%SSRLINK_SURROGATE%'
or sequence_name like '%SSBXLST_SURROGATE%'
or sequence_name like '%SSRXLST_SURROGATE%'
or sequence_name like '%SSRMEET_SURROGATE%'
or sequence_name like '%SIRASGN_SURROGATE%'
or sequence_name like '%SFBETRM_SURROGATE%'
or sequence_name like '%SFRSTCR_SURROGATE%'
or sequence_name like '%SGRSATT_SURROGATE%'
or sequence_name like '%TBRMEMO_SURROGATE%'
or sequence_name like '%SARCHKL_SURROGATE%'
or sequence_name like '%SPRHOLD_SURROGATE%'
or sequence_name like '%SGRSCMT_SURROGATE%'
or sequence_name like '%SHRMRKS_SURROGATE%'
or sequence_name like '%SHRMRKA_SURROGATE%'
or sequence_name like '%SMBSLIB_SURROGATE%'
or sequence_name like '%SMRSWAV_SURROGATE%'
or sequence_name like '%SMRSWPV_SURROGATE%'
or sequence_name like '%SHRDGMR_SURROGATE%'
and sequence_owner ='SATURN';
V_CUR C_SEQ%ROWTYPE;
BEGIN
OPEN C_SEQ;
LOOP
FETCH C_SEQ INTO V_CUR;
EXIT WHEN C_SEQ%NOTFOUND;
VAR_EXEC:= 'SELECT SATURN.'||V_CUR.SEQUENCE_NAME||'.NEXTVAL INTO '||ULT_SEQ||' FROM DUAL';
EXECUTE IMMEDIATE (VAR_EXEC) ; -- esta es la linea 87
VAL_SEQ := V_CUR.LAST_NUMBER - ULT_SEQ;
IF VAL_SEQ > 0 THEN
VAR_EXEC :='ALTER SEQUENCE SATURN.'||V_CUR.SEQUENCE_NAME
||' INCREMENT BY '||VAL_SEQ;
EXECUTE IMMEDIATE (VAR_EXEC);
VAR_EXEC := 'SELECT SATURN.'||V_CUR.SEQUENCE_NAME||'.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE (VAR_EXEC);
VAR_EXEC :='ALTER SEQUENCE SATURN.'||V_CUR.SEQUENCE_NAME
||' INCREMENTE BY 1';
EXECUTE IMMEDIATE (VAR_EXEC);
END IF;
END LOOP;
CLOSE C_SEQ;
END;
ORA-00936: falta una expresión
ORA-06512: en "BANINST1.SP_SEQ_TEST", línea 87
ORA-06512: en línea 1
tambien intente hacerlo con un cursor de manera distinta pero casualmente llegue al mismo error xD
Código:
declare
NOM_SEQ VARCHAR2(100);
ULT_SEQ NUMBER;
VAL_SEQ NUMBER;
NEXT_SEQ NUMBER;
BEGIN
FOR CUR IN (
SELECT seQuence_name, LAST_NUMBER
from all_sequenceS
where sequence_name like '%SPRIDEN_SURROGATE%'
or sequence_name like '%GTVZIPC_SURROGATE%'
or sequence_name like '%STVCNTY_SURROGATE%'
or sequence_name like '%SPRADDR_SURROGATE%'
or sequence_name like '%SPRTELE_SURROGATE%'
or sequence_name like '%SPBPERS_SURROGATE%'
or sequence_name like '%GOREMAL_SURROGATE%'
or sequence_name like '%SPREMRG_SURROGATE%'
or sequence_name like '%GOBINTL_SURROGATE%'
or sequence_name like '%SPRMEDI_SURROGATE%'
or sequence_name like '%SPRHOLD_SURROGATE%'
or sequence_name like '%SORFOLK_SURROGATE%'
or sequence_name like '%SGBSTDN_SURROGATE%'
or sequence_name like '%SGRSATT_SURROGATE%'
or sequence_name like '%SGRSCMT_SURROGATE%'
or sequence_name like '%SGRCHRT_SURROGATE%'
or sequence_name like '%SIBINST_SURROGATE%'
or sequence_name like '%SGRADVR_SURROGATE%'
or sequence_name like '%SHRTTRM_SURROGATE%'
or sequence_name like '%SHRTCKN_SURROGATE%'
or sequence_name like '%SHRTCKG_SURROGATE%'
or sequence_name like '%SHRTCKL_SURROGATE%'
or sequence_name like '%SARADAP_SURROGATE%'
or sequence_name like '%SARAPPD_SURROGATE%'
or sequence_name like '%SORHSCH_SURROGATE%'
or sequence_name like '%SYRCRMI_SURROGATE%'
or sequence_name like '%TBRACCD_SURROGATE%'
or sequence_name like '%STVSBGI_SURROGATE%'
or sequence_name like '%SORBTAG_SURROGATE%'
or sequence_name like '%SORBTAL_SURROGATE%'
or sequence_name like '%SHRTGRD_SURROGATE%'
or sequence_name like '%SHBTATC_SURROGATE%'
or sequence_name like '%SHRTATC_SURROGATE%'
or sequence_name like '%SORBDMO_SURROGATE%'
or sequence_name like '%SOBSBGI_SURROGATE%'
or sequence_name like '%STVBCHR_SURROGATE%'
or sequence_name like '%SORBCHR_SURROGATE%'
or sequence_name like '%SORBCMT_SURROGATE%'
or sequence_name like '%SORTEST_SURROGATE%'
or sequence_name like '%STVCIPC_SURROGATE%'
or sequence_name like '%STVTESC_SURROGATE%'
or sequence_name like '%STVTSRC_SURROGATE%'
or sequence_name like '%SSBSECT_SURROGATE%'
or sequence_name like '%SSRLINK_SURROGATE%'
or sequence_name like '%SSBXLST_SURROGATE%'
or sequence_name like '%SSRXLST_SURROGATE%'
or sequence_name like '%SSRMEET_SURROGATE%'
or sequence_name like '%SIRASGN_SURROGATE%'
or sequence_name like '%SFBETRM_SURROGATE%'
or sequence_name like '%SFRSTCR_SURROGATE%'
or sequence_name like '%SGRSATT_SURROGATE%'
or sequence_name like '%TBRMEMO_SURROGATE%'
or sequence_name like '%SARCHKL_SURROGATE%'
or sequence_name like '%SPRHOLD_SURROGATE%'
or sequence_name like '%SGRSCMT_SURROGATE%'
or sequence_name like '%SHRMRKS_SURROGATE%'
or sequence_name like '%SHRMRKA_SURROGATE%'
or sequence_name like '%SMBSLIB_SURROGATE%'
or sequence_name like '%SMRSWAV_SURROGATE%'
or sequence_name like '%SMRSWPV_SURROGATE%'
or sequence_name like '%SHRDGMR_SURROGATE%'
and sequence_owner ='SATURN'
)
LOOP -- esta es la linea 78
EXECUTE IMMEDIATE 'SELECT SATURN.'||CUR.SEQUENCE_NAME||'.NEXTVAL INTO '||ULT_SEQ||' FROM DUAL';
VAL_SEQ := CUR.LAST_NUMBER - ULT_SEQ;
IF VAL_SEQ > 0 THEN
EXECUTE IMMEDIATE 'ALTER SEQUENCE SATURN.'||CUR.SEQUENCE_NAME
||' INCREMENT BY '||VAL_SEQ;
EXECUTE IMMEDIATE 'SELECT SATURN.'||CUR.SEQUENCE_NAME||'.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE 'ALTER SEQUENCE SATURN.'||CUR.SEQUENCE_NAME
||' INCREMENTE BY 1';
END IF;
END LOOP;
END;
ORA-00936: falta una expresión
ORA-06512: en línea 78
muchas gracias por su ayuda saludos