junior_soares
asked on
ora-06502
Hi, A have a procedure that work and its status is valid but in order moment i run my aplication and appear error ora06502 . I recompile my procedure and run aplication. This error desappear and work correctly.In time, its error return to happen.
Why its happen?
thank´s.
Why its happen?
thank´s.
It is probably a data problem. Read the following note from MetaLink.
Take a look at your code for any type of data conversion. What is your Oracle version?
Error: ORA 6502
Text: PL/SQL: numeric or value error
-------------------------- ---------- ---------- ---------- ---------- ---------- ---
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Take a look at your code for any type of data conversion. What is your Oracle version?
Error: ORA 6502
Text: PL/SQL: numeric or value error
--------------------------
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
ASKER
Hi, Henka. Thanks.
I Have version 8.1.5,8.1.6,9.0.1 and 9.2 but this error ocrrour in Oracle 8.1.7 and Oracle 8.1.6.
I Have version 8.1.5,8.1.6,9.0.1 and 9.2 but this error ocrrour in Oracle 8.1.7 and Oracle 8.1.6.
There were bugs for this error in earlier versions. Are you using any TO_NUMBER functions or
implicitly doing character to number conversion?
DJ
implicitly doing character to number conversion?
DJ
I don't understand - you run the same application on 8.1.5,8.1.6,9.0.1 and 9.2 and this error occurs in Oracle 8.1.7 and Oracle 8.1.6 ?
OR
you cannot find a description of this error in a documentation ?
In Oracle9i documentation there is the same description:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e4100.htm#1003618
ORA-06502 PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Oracle8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76999/e4100.htm#1656
ORA-06502 PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
OR
you cannot find a description of this error in a documentation ?
In Oracle9i documentation there is the same description:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e4100.htm#1003618
ORA-06502 PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Oracle8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76999/e4100.htm#1656
ORA-06502 PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
ASKER
This is the problem. In every documentation, i found the explanation above, but in 9i and 8.1.5 this dont happen.
Below, my code.
REM Data: 29/10/2002 Hora: 15:00
CREATE OR REPLACE PROCEDURE PR_CTB_GRAVALANC
(
PCODLANCA IN CTBITLNC.CODLANCA%TYPE,
PCODITEMLANCA IN CTBITLNC.CODITEMLANCA%TYPE ,
PNROPLANO IN CTBITLNC.NROPLANO%TYPE,
PVRITEMLANCA IN CTBITLNC.VRITEMLANCA%TYPE,
PCONTRAPARTITEMLANCA IN CTBITLNC.CONTRAPARTITEMLAN CA%TYPE,
PDEBITOCREDITO IN CTBITLNC.DEBITOCREDITOITEM LANCA%TYPE ,
PHISTORICO IN CTBITLNC.HISTORICOITEMLANC A%TYPE,
PCONJUNTO IN CTBITLNC.CONJUNTOITEMLANCA %TYPE,
PCODCONTACTB IN CTBITLNC.CODCONTACTB%TYPE,
PCODCUSTO IN CTBITLNC.CODCUSTO%TYPE,
PDATA IN CTBSALDO.PERIODOSALDO%TYPE , -- Não aceita Varchar2(6)
p_execProcPeriodo IN CTBITLNC.DEBITOCREDITOITEM LANCA%TYPE , -- Não aceita Varchar2(1)
PEMPRESA IN CTBLANCA.CODIGOEMPRESA%TYP E,
PFILIAL IN CTBLANCA.CODIGOFL%TYPE,
PMENSAGEM OUT STRING
)
IS
vPERIODO2 CTBSALDO.PERIODOSALDO%TYPE ;
vPERIODO CTBSALDO.PERIODOSALDO%TYPE ;
vCLASSCTB CTBCONTA.CLASSIFICADOR%TYP E;
vContaAux CTBITLNC.CODCONTACTB%TYPE;
v_Class1 CTBCONTA.CLASSIFICADOR%TYP E;
v_PossuiMovto CTBSALDO.POSSUIMOVTO%TYPE;
v_PossuiMovtoAnt CTBSALDO.POSSUIMOVTO%TYPE;
j NUMBER;
I NUMBER;
P NUMBER;
TYPE T_CONTACTB IS TABLE OF CTBCONTA.CODCONTACTB%TYPE INDEX BY BINARY_INTEGER;
vCONTACTB T_CONTACTB;
v_DEB CTBSALDO.VLDEBITOSALDO%TYP E;
v_CRED CTBSALDO.VLCREDITOSALDO%TY PE;
v_DEBANT CTBSALDO.VLDEBANTSALDO%TYP E;
v_CREDANT CTBSALDO.VLCREDANTSALDO%TY PE;
EXISTE BOOLEAN;
FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
PLANO IN CTBCONTA.NROPLANO%TYPE )
RETURN CTBCONTA.CLASSIFICADOR%TYP E IS
vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYP E;
BEGIN
SELECT
CLASSIFICADOR
INTO
vCLASSIFICADOR
FROM CTBCONTA
WHERE CODCONTACTB = CONTACTB
AND NROPLANO = PLANO;
RETURN vCLASSIFICADOR;
END PEGACLASSIFICADOR;
FUNCTION PEGACONTACTB( PLANO IN CTBITLNC.NROPLANO%TYPE,
CLASSCTB IN CTBCONTA.CLASSIFICADOR%TYP E )
RETURN CTBCONTA.CODCONTACTB%TYPE IS
vCONTA CTBCONTA.CODCONTACTB%TYPE;
BEGIN
SELECT
CODCONTACTB
INTO
vCONTA
FROM CTBCONTA
WHERE NROPLANO = PLANO
AND CLASSIFICADOR = CLASSCTB;
RETURN vCONTA;
END PEGACONTACTB ;
FUNCTION VERIFICASALDONOMES( EMPRESA IN CTBSALDO.CODIGOEMPRESA%TYP E,
FILIAL IN CTBSALDO.CODIGOFL%TYPE,
PERIODO IN CTBSALDO.PERIODOSALDO%TYPE ,
PLANO IN CTBSALDO.NROPLANO%TYPE,
CONTACTB IN CTBSALDO.CODCONTACTB%TYPE )
RETURN BOOLEAN IS
CURSOR C_SALDOS IS
SELECT * FROM CTBSALDO
WHERE CODIGOEMPRESA = EMPRESA AND
CODIGOFL = FILIAL AND
NROPLANO = PLANO AND
PERIODOSALDO = PERIODO AND
CODCONTACTB = CONTACTB;
W_C_SALDOS C_SALDOS%ROWTYPE;
BEGIN
OPEN C_SALDOS;
FETCH C_SALDOS INTO W_C_SALDOS;
IF C_SALDOS%NOTFOUND THEN
BEGIN
v_DEB := 0;
v_CRED := 0;
v_DEBANT := 0;
v_CREDANT := 0;
CLOSE C_SALDOS;
RETURN FALSE;
END;
ELSE
BEGIN
v_DEB := W_C_SALDOS.VLDEBITOSALDO;
v_CRED := W_C_SALDOS.VLCREDITOSALDO;
v_DEBANT := W_C_SALDOS.VLDEBANTSALDO;
v_CREDANT := W_C_SALDOS.VLCREDANTSALDO;
CLOSE C_SALDOS;
RETURN TRUE;
END;
END IF;
END VERIFICASALDONOMES;
BEGIN
vContaAux := PCODCONTACTB;
SELECT CODCONTACTB
INTO vCONTAAux
FROM CTBCONTA
WHERE NROPLANO = PNROPLANO
AND CODCONTACTB = PCODCONTACTB;
vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );
IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
END IF;
-- vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;
vCONTACTB(1) := PCODCONTACTB;
v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
v_Class1 := SUBSTR(vCLASSCTB,1,2) || '00000000';
SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
v_Class1 := SUBSTR(vCLASSCTB,1,4) || '000000';
SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
INSERT INTO CTBITLNC
(
CODLANCA
, CODITEMLANCA
, NROPLANO
, CODCONTACTB
, CONTRAPARTITEMLANCA
, CODCUSTO
, VRITEMLANCA
, DEBITOCREDITOITEMLANCA
, HISTORICOITEMLANCA
, CONJUNTOITEMLANCA
)
VALUES
(
PCODLANCA
, PCODITEMLANCA
, PNROPLANO
, PCODCONTACTB
, PCONTRAPARTITEMLANCA
, PCODCUSTO
, PVRITEMLANCA
, PDEBITOCREDITO
, PHISTORICO
, PCONJUNTO
);
If p_execProcPeriodo = 'S' then
p := 2;
else
p := 1;
End If;
FOR I IN 1..P LOOP
BEGIN
FOR J IN 1..5 LOOP
vContaAux := vCONTACTB(J);
IF I = 1 THEN
vPERIODO := PDATA;
ELSE -- i = 2
vPERIODO := vPERIODO2;
end if;
EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );
v_PossuiMovto := 'S';
IF I = 1 THEN
IF PDEBITOCREDITO = 'D' THEN
v_DEB := v_DEB + PVRITEMLANCA;
ELSE
v_CRED := v_CRED + PVRITEMLANCA;
END IF;
ELSE -- i = 2
BEGIN
IF PDEBITOCREDITO = 'D' THEN
v_DEBANT := v_DEBANT + PVRITEMLANCA;
ELSE
v_CREDANT := v_CREDANT + PVRITEMLANCA;
END IF;
END;
END IF;
IF EXISTE THEN
UPDATE CTBSALDO
SET VLDEBITOSALDO = v_DEB,
VLCREDITOSALDO = v_CRED,
VLDEBANTSALDO = v_DEBANT,
VLCREDANTSALDO = v_CREDANT,
POSSUIMOVTO = v_PossuiMovto
WHERE CODIGOEMPRESA = PEMPRESA AND
CODIGOFL = PFILIAL AND
PERIODOSALDO = vPERIODO AND
NROPLANO = PNROPLANO AND
CODCONTACTB = vCONTACTB(J);
ELSE
INSERT INTO CTBSALDO
(
CODIGOEMPRESA
, CODIGOFL
, PERIODOSALDO
, NROPLANO
, CODCONTACTB
, VLDEBITOSALDO
, VLCREDITOSALDO
, VLDEBANTSALDO
, VLCREDANTSALDO
, POSSUIMOVTO
)
VALUES
(
PEMPRESA
, PFILIAL
, VPERIODO
, PNROPLANO
, vCONTACTB(J)
, v_DEB
, v_CRED
, v_DEBANT
, v_CREDANT
, v_PossuiMovto
);
END IF;
END LOOP;
END;
END LOOP;
PMENSAGEM := 'PROCESSAMENTO FINALIZADO COM SUCESSO.';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20001, 'CONTA CONTABIL ' || VCONTAAUX || ' OU CLASSIFICADOR ' || V_CLASS1 || ' NAO CADASTRADA(O) NO PLANO ' || PNROPLANO || '.' );
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR( -20001, 'DUPLICACAO DE REGISTRO. TENTE NOVAMENTE.' );
WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR( -06502, 'VALORES INVALIDOS' );
END PR_CTB_GRAVALANC;
/
thaks for you regard
Below, my code.
REM Data: 29/10/2002 Hora: 15:00
CREATE OR REPLACE PROCEDURE PR_CTB_GRAVALANC
(
PCODLANCA IN CTBITLNC.CODLANCA%TYPE,
PCODITEMLANCA IN CTBITLNC.CODITEMLANCA%TYPE
PNROPLANO IN CTBITLNC.NROPLANO%TYPE,
PVRITEMLANCA IN CTBITLNC.VRITEMLANCA%TYPE,
PCONTRAPARTITEMLANCA IN CTBITLNC.CONTRAPARTITEMLAN
PDEBITOCREDITO IN CTBITLNC.DEBITOCREDITOITEM
PHISTORICO IN CTBITLNC.HISTORICOITEMLANC
PCONJUNTO IN CTBITLNC.CONJUNTOITEMLANCA
PCODCONTACTB IN CTBITLNC.CODCONTACTB%TYPE,
PCODCUSTO IN CTBITLNC.CODCUSTO%TYPE,
PDATA IN CTBSALDO.PERIODOSALDO%TYPE
p_execProcPeriodo IN CTBITLNC.DEBITOCREDITOITEM
PEMPRESA IN CTBLANCA.CODIGOEMPRESA%TYP
PFILIAL IN CTBLANCA.CODIGOFL%TYPE,
PMENSAGEM OUT STRING
)
IS
vPERIODO2 CTBSALDO.PERIODOSALDO%TYPE
vPERIODO CTBSALDO.PERIODOSALDO%TYPE
vCLASSCTB CTBCONTA.CLASSIFICADOR%TYP
vContaAux CTBITLNC.CODCONTACTB%TYPE;
v_Class1 CTBCONTA.CLASSIFICADOR%TYP
v_PossuiMovto CTBSALDO.POSSUIMOVTO%TYPE;
v_PossuiMovtoAnt CTBSALDO.POSSUIMOVTO%TYPE;
j NUMBER;
I NUMBER;
P NUMBER;
TYPE T_CONTACTB IS TABLE OF CTBCONTA.CODCONTACTB%TYPE INDEX BY BINARY_INTEGER;
vCONTACTB T_CONTACTB;
v_DEB CTBSALDO.VLDEBITOSALDO%TYP
v_CRED CTBSALDO.VLCREDITOSALDO%TY
v_DEBANT CTBSALDO.VLDEBANTSALDO%TYP
v_CREDANT CTBSALDO.VLCREDANTSALDO%TY
EXISTE BOOLEAN;
FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
PLANO IN CTBCONTA.NROPLANO%TYPE )
RETURN CTBCONTA.CLASSIFICADOR%TYP
vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYP
BEGIN
SELECT
CLASSIFICADOR
INTO
vCLASSIFICADOR
FROM CTBCONTA
WHERE CODCONTACTB = CONTACTB
AND NROPLANO = PLANO;
RETURN vCLASSIFICADOR;
END PEGACLASSIFICADOR;
FUNCTION PEGACONTACTB( PLANO IN CTBITLNC.NROPLANO%TYPE,
CLASSCTB IN CTBCONTA.CLASSIFICADOR%TYP
RETURN CTBCONTA.CODCONTACTB%TYPE IS
vCONTA CTBCONTA.CODCONTACTB%TYPE;
BEGIN
SELECT
CODCONTACTB
INTO
vCONTA
FROM CTBCONTA
WHERE NROPLANO = PLANO
AND CLASSIFICADOR = CLASSCTB;
RETURN vCONTA;
END PEGACONTACTB ;
FUNCTION VERIFICASALDONOMES( EMPRESA IN CTBSALDO.CODIGOEMPRESA%TYP
FILIAL IN CTBSALDO.CODIGOFL%TYPE,
PERIODO IN CTBSALDO.PERIODOSALDO%TYPE
PLANO IN CTBSALDO.NROPLANO%TYPE,
CONTACTB IN CTBSALDO.CODCONTACTB%TYPE )
RETURN BOOLEAN IS
CURSOR C_SALDOS IS
SELECT * FROM CTBSALDO
WHERE CODIGOEMPRESA = EMPRESA AND
CODIGOFL = FILIAL AND
NROPLANO = PLANO AND
PERIODOSALDO = PERIODO AND
CODCONTACTB = CONTACTB;
W_C_SALDOS C_SALDOS%ROWTYPE;
BEGIN
OPEN C_SALDOS;
FETCH C_SALDOS INTO W_C_SALDOS;
IF C_SALDOS%NOTFOUND THEN
BEGIN
v_DEB := 0;
v_CRED := 0;
v_DEBANT := 0;
v_CREDANT := 0;
CLOSE C_SALDOS;
RETURN FALSE;
END;
ELSE
BEGIN
v_DEB := W_C_SALDOS.VLDEBITOSALDO;
v_CRED := W_C_SALDOS.VLCREDITOSALDO;
v_DEBANT := W_C_SALDOS.VLDEBANTSALDO;
v_CREDANT := W_C_SALDOS.VLCREDANTSALDO;
CLOSE C_SALDOS;
RETURN TRUE;
END;
END IF;
END VERIFICASALDONOMES;
BEGIN
vContaAux := PCODCONTACTB;
SELECT CODCONTACTB
INTO vCONTAAux
FROM CTBCONTA
WHERE NROPLANO = PNROPLANO
AND CODCONTACTB = PCODCONTACTB;
vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );
IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
END IF;
-- vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;
vCONTACTB(1) := PCODCONTACTB;
v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
v_Class1 := SUBSTR(vCLASSCTB,1,2) || '00000000';
SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
v_Class1 := SUBSTR(vCLASSCTB,1,4) || '000000';
SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
INSERT INTO CTBITLNC
(
CODLANCA
, CODITEMLANCA
, NROPLANO
, CODCONTACTB
, CONTRAPARTITEMLANCA
, CODCUSTO
, VRITEMLANCA
, DEBITOCREDITOITEMLANCA
, HISTORICOITEMLANCA
, CONJUNTOITEMLANCA
)
VALUES
(
PCODLANCA
, PCODITEMLANCA
, PNROPLANO
, PCODCONTACTB
, PCONTRAPARTITEMLANCA
, PCODCUSTO
, PVRITEMLANCA
, PDEBITOCREDITO
, PHISTORICO
, PCONJUNTO
);
If p_execProcPeriodo = 'S' then
p := 2;
else
p := 1;
End If;
FOR I IN 1..P LOOP
BEGIN
FOR J IN 1..5 LOOP
vContaAux := vCONTACTB(J);
IF I = 1 THEN
vPERIODO := PDATA;
ELSE -- i = 2
vPERIODO := vPERIODO2;
end if;
EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );
v_PossuiMovto := 'S';
IF I = 1 THEN
IF PDEBITOCREDITO = 'D' THEN
v_DEB := v_DEB + PVRITEMLANCA;
ELSE
v_CRED := v_CRED + PVRITEMLANCA;
END IF;
ELSE -- i = 2
BEGIN
IF PDEBITOCREDITO = 'D' THEN
v_DEBANT := v_DEBANT + PVRITEMLANCA;
ELSE
v_CREDANT := v_CREDANT + PVRITEMLANCA;
END IF;
END;
END IF;
IF EXISTE THEN
UPDATE CTBSALDO
SET VLDEBITOSALDO = v_DEB,
VLCREDITOSALDO = v_CRED,
VLDEBANTSALDO = v_DEBANT,
VLCREDANTSALDO = v_CREDANT,
POSSUIMOVTO = v_PossuiMovto
WHERE CODIGOEMPRESA = PEMPRESA AND
CODIGOFL = PFILIAL AND
PERIODOSALDO = vPERIODO AND
NROPLANO = PNROPLANO AND
CODCONTACTB = vCONTACTB(J);
ELSE
INSERT INTO CTBSALDO
(
CODIGOEMPRESA
, CODIGOFL
, PERIODOSALDO
, NROPLANO
, CODCONTACTB
, VLDEBITOSALDO
, VLCREDITOSALDO
, VLDEBANTSALDO
, VLCREDANTSALDO
, POSSUIMOVTO
)
VALUES
(
PEMPRESA
, PFILIAL
, VPERIODO
, PNROPLANO
, vCONTACTB(J)
, v_DEB
, v_CRED
, v_DEBANT
, v_CREDANT
, v_PossuiMovto
);
END IF;
END LOOP;
END;
END LOOP;
PMENSAGEM := 'PROCESSAMENTO FINALIZADO COM SUCESSO.';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20001, 'CONTA CONTABIL ' || VCONTAAUX || ' OU CLASSIFICADOR ' || V_CLASS1 || ' NAO CADASTRADA(O) NO PLANO ' || PNROPLANO || '.' );
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR( -20001, 'DUPLICACAO DE REGISTRO. TENTE NOVAMENTE.' );
WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR( -06502, 'VALORES INVALIDOS' );
END PR_CTB_GRAVALANC;
/
thaks for you regard
ASKER
On Metalink (Doc ID: Note:139548.1), the support solicit setting following event in init.ora and set sql_trace.
event="6502 trace name errorstack level 12"
alter system set timed_statistics = true;
alter system set sql_trace = true;
thanks
event="6502 trace name errorstack level 12"
alter system set timed_statistics = true;
alter system set sql_trace = true;
thanks
ASKER
somebody have any answer?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks FBIAGENT
I'm having the same problem as mentioned in the original question. Nobody gave an answer to why the stored proc call would fail, and after a recompile, suddenly start working. Can anyone answer that? Note: I am using JDBC and Oracle 9i.
Thanks,
Ian
Thanks,
Ian
ASKER
Hi FbiAgent. See the code below, the debug stop at line stmt_no := 1200;
Why this happened?
REM Data: 08/12/2003 Hora: 11:40
CREATE OR REPLACE PROCEDURE PR_CTB_GRAVALANC
(
PCODLANCA IN CTBITLNC.CODLANCA%TYPE,
PCODITEMLANCA IN CTBITLNC.CODITEMLANCA%TYPE ,
PNROPLANO IN CTBITLNC.NROPLANO%TYPE,
PVRITEMLANCA IN CTBITLNC.VRITEMLANCA%TYPE,
PCONTRAPARTITEMLANCA IN CTBITLNC.CONTRAPARTITEMLAN CA%TYPE,
PDEBITOCREDITO IN CTBITLNC.DEBITOCREDITOITEM LANCA%TYPE ,
PHISTORICO IN CTBITLNC.HISTORICOITEMLANC A%TYPE,
PCONJUNTO IN CTBITLNC.CONJUNTOITEMLANCA %TYPE,
PCODCONTACTB IN CTBITLNC.CODCONTACTB%TYPE,
PCODCUSTO IN CTBITLNC.CODCUSTO%TYPE,
PDATA IN CTBSALDO.PERIODOSALDO%TYPE , -- Não aceita Varchar2(6)
p_execProcPeriodo IN CTBITLNC.DEBITOCREDITOITEM LANCA%TYPE , -- Não aceita Varchar2(1)
PEMPRESA IN CTBLANCA.CODIGOEMPRESA%TYP E,
PFILIAL IN CTBLANCA.CODIGOFL%TYPE,
PMENSAGEM OUT STRING
)
IS
stmt_no number;
vPERIODO2 CTBSALDO.PERIODOSALDO%TYPE ;
vPERIODO CTBSALDO.PERIODOSALDO%TYPE ;
vCLASSCTB CTBCONTA.CLASSIFICADOR%TYP E;
vContaAux CTBITLNC.CODCONTACTB%TYPE;
v_Class1 CTBCONTA.CLASSIFICADOR%TYP E;
v_PossuiMovto CTBSALDO.POSSUIMOVTO%TYPE;
v_PossuiMovtoAnt CTBSALDO.POSSUIMOVTO%TYPE;
j NUMBER;
I NUMBER;
P NUMBER;
TYPE T_CONTACTB IS TABLE OF CTBCONTA.CODCONTACTB%TYPE INDEX BY BINARY_INTEGER;
vCONTACTB T_CONTACTB;
v_DEB CTBSALDO.VLDEBITOSALDO%TYP E;
v_CRED CTBSALDO.VLCREDITOSALDO%TY PE;
v_DEBANT CTBSALDO.VLDEBANTSALDO%TYP E;
v_CREDANT CTBSALDO.VLCREDANTSALDO%TY PE;
EXISTE BOOLEAN;
--************************ **
FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
PLANO IN CTBCONTA.NROPLANO%TYPE )
RETURN CTBCONTA.CLASSIFICADOR%TYP E IS
vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYP E;
BEGIN
SELECT
CLASSIFICADOR
INTO
vCLASSIFICADOR
FROM CTBCONTA
WHERE CODCONTACTB = CONTACTB
AND NROPLANO = PLANO;
RETURN vCLASSIFICADOR;
END PEGACLASSIFICADOR;
--************************ **
FUNCTION PEGACONTACTB( PLANO IN CTBITLNC.NROPLANO%TYPE,
CLASSCTB IN CTBCONTA.CLASSIFICADOR%TYP E )
RETURN CTBCONTA.CODCONTACTB%TYPE IS
vCONTA CTBCONTA.CODCONTACTB%TYPE;
BEGIN
SELECT
CODCONTACTB
INTO
vCONTA
FROM CTBCONTA
WHERE NROPLANO = PLANO
AND CLASSIFICADOR = CLASSCTB;
RETURN vCONTA;
END PEGACONTACTB ;
--************************ **
FUNCTION VERIFICASALDONOMES( EMPRESA IN CTBSALDO.CODIGOEMPRESA%TYP E,
FILIAL IN CTBSALDO.CODIGOFL%TYPE,
PERIODO IN CTBSALDO.PERIODOSALDO%TYPE ,
PLANO IN CTBSALDO.NROPLANO%TYPE,
CONTACTB IN CTBSALDO.CODCONTACTB%TYPE )
RETURN BOOLEAN IS
CURSOR C_SALDOS IS
SELECT * FROM CTBSALDO
WHERE CODIGOEMPRESA = EMPRESA AND
CODIGOFL = FILIAL AND
NROPLANO = PLANO AND
PERIODOSALDO = PERIODO AND
CODCONTACTB = CONTACTB;
W_C_SALDOS C_SALDOS%ROWTYPE;
BEGIN
OPEN C_SALDOS;
FETCH C_SALDOS INTO W_C_SALDOS;
IF C_SALDOS%NOTFOUND THEN
BEGIN
v_DEB := 0;
v_CRED := 0;
v_DEBANT := 0;
v_CREDANT := 0;
CLOSE C_SALDOS;
RETURN FALSE;
END;
ELSE
BEGIN
v_DEB := W_C_SALDOS.VLDEBITOSALDO;
v_CRED := W_C_SALDOS.VLCREDITOSALDO;
v_DEBANT := W_C_SALDOS.VLDEBANTSALDO;
v_CREDANT := W_C_SALDOS.VLCREDANTSALDO;
CLOSE C_SALDOS;
RETURN TRUE;
END;
END IF;
END VERIFICASALDONOMES;
BEGIN
stmt_no := 100;
vContaAux := PCODCONTACTB;
stmt_no := 200;
SELECT
CODCONTACTB
INTO
vCONTAAux
FROM
CTBCONTA
WHERE
NROPLANO = PNROPLANO AND CODCONTACTB = PCODCONTACTB;
stmt_no := 400;
vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );
stmt_no := 500;
IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
END IF;
stmt_no := 600;
-- vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;
stmt_no := 700;
vCONTACTB(1) := PCODCONTACTB;
stmt_no := 800;
v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 900;
v_Class1 := SUBSTR(vCLASSCTB,1,2) || '00000000';
SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1000;
v_Class1 := SUBSTR(vCLASSCTB,1,4) || '000000';
SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1100;
v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1200;
INSERT INTO CTBITLNC
(
CODLANCA
, CODITEMLANCA
, NROPLANO
, CODCONTACTB
, CONTRAPARTITEMLANCA
, CODCUSTO
, VRITEMLANCA
, DEBITOCREDITOITEMLANCA
, HISTORICOITEMLANCA
, CONJUNTOITEMLANCA
)
VALUES
(
PCODLANCA
, PCODITEMLANCA
, PNROPLANO
, PCODCONTACTB
, PCONTRAPARTITEMLANCA
, PCODCUSTO
, PVRITEMLANCA
, PDEBITOCREDITO
, PHISTORICO
, PCONJUNTO
);
stmt_no := 1300;
If p_execProcPeriodo = 'S' then
p := 2;
else
p := 1;
End If;
stmt_no := 1400;
FOR I IN 1..P LOOP
BEGIN
FOR J IN 1..5 LOOP
stmt_no := 1500;
vContaAux := vCONTACTB(J);
stmt_no := 1600;
IF I = 1 THEN
vPERIODO := PDATA;
ELSE -- i = 2
vPERIODO := vPERIODO2;
end if;
stmt_no := 1700;
EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );
stmt_no := 1800;
v_PossuiMovto := 'S';
stmt_no := 1900;
IF I = 1 THEN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2000;
v_DEB := v_DEB + PVRITEMLANCA;
ELSE
stmt_no := 2100;
v_CRED := v_CRED + PVRITEMLANCA;
END IF;
ELSE -- i = 2
BEGIN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2200;
v_DEBANT := v_DEBANT + PVRITEMLANCA;
ELSE
stmt_no := 2300;
v_CREDANT := v_CREDANT + PVRITEMLANCA;
END IF;
END;
END IF;
stmt_no := 2400;
IF EXISTE THEN
UPDATE CTBSALDO
SET VLDEBITOSALDO = v_DEB,
VLCREDITOSALDO = v_CRED,
VLDEBANTSALDO = v_DEBANT,
VLCREDANTSALDO = v_CREDANT,
POSSUIMOVTO = v_PossuiMovto
WHERE CODIGOEMPRESA = PEMPRESA AND
CODIGOFL = PFILIAL AND
PERIODOSALDO = vPERIODO AND
NROPLANO = PNROPLANO AND
CODCONTACTB = vCONTACTB(J);
ELSE
stmt_no := 2500;
INSERT INTO CTBSALDO
(
CODIGOEMPRESA
, CODIGOFL
, PERIODOSALDO
, NROPLANO
, CODCONTACTB
, VLDEBITOSALDO
, VLCREDITOSALDO
, VLDEBANTSALDO
, VLCREDANTSALDO
, POSSUIMOVTO
)
VALUES
(
PEMPRESA
, PFILIAL
, VPERIODO
, PNROPLANO
, vCONTACTB(J)
, v_DEB
, v_CRED
, v_DEBANT
, v_CREDANT
, v_PossuiMovto
);
END IF;
END LOOP;
END;
END LOOP;
PMENSAGEM := 'PROCESSAMENTO FINALIZADO COM SUCESSO.';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20010, 'CONTA CONTABIL ' || VCONTAAUX || ' OU CLASSIFICADOR ' || V_CLASS1 || ' NAO CADASTRADA(O) NO PLANO ' || PNROPLANO || '.' );
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR( -20020, 'DUPLICACAO DE REGISTRO. TENTE NOVAMENTE.' );
WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR( -06502, 'Avisar a BgmRodotec que o erro "1" foi no ponto "'||to_char(stmt_no)||'"' );
when others then
raise_application_error( -20030, 'Avisar a BgmRodotec que o erro "2" foi no ponto "'||to_char(stmt_no)||'"' );
END PR_CTB_GRAVALANC;
/
Why this happened?
REM Data: 08/12/2003 Hora: 11:40
CREATE OR REPLACE PROCEDURE PR_CTB_GRAVALANC
(
PCODLANCA IN CTBITLNC.CODLANCA%TYPE,
PCODITEMLANCA IN CTBITLNC.CODITEMLANCA%TYPE
PNROPLANO IN CTBITLNC.NROPLANO%TYPE,
PVRITEMLANCA IN CTBITLNC.VRITEMLANCA%TYPE,
PCONTRAPARTITEMLANCA IN CTBITLNC.CONTRAPARTITEMLAN
PDEBITOCREDITO IN CTBITLNC.DEBITOCREDITOITEM
PHISTORICO IN CTBITLNC.HISTORICOITEMLANC
PCONJUNTO IN CTBITLNC.CONJUNTOITEMLANCA
PCODCONTACTB IN CTBITLNC.CODCONTACTB%TYPE,
PCODCUSTO IN CTBITLNC.CODCUSTO%TYPE,
PDATA IN CTBSALDO.PERIODOSALDO%TYPE
p_execProcPeriodo IN CTBITLNC.DEBITOCREDITOITEM
PEMPRESA IN CTBLANCA.CODIGOEMPRESA%TYP
PFILIAL IN CTBLANCA.CODIGOFL%TYPE,
PMENSAGEM OUT STRING
)
IS
stmt_no number;
vPERIODO2 CTBSALDO.PERIODOSALDO%TYPE
vPERIODO CTBSALDO.PERIODOSALDO%TYPE
vCLASSCTB CTBCONTA.CLASSIFICADOR%TYP
vContaAux CTBITLNC.CODCONTACTB%TYPE;
v_Class1 CTBCONTA.CLASSIFICADOR%TYP
v_PossuiMovto CTBSALDO.POSSUIMOVTO%TYPE;
v_PossuiMovtoAnt CTBSALDO.POSSUIMOVTO%TYPE;
j NUMBER;
I NUMBER;
P NUMBER;
TYPE T_CONTACTB IS TABLE OF CTBCONTA.CODCONTACTB%TYPE INDEX BY BINARY_INTEGER;
vCONTACTB T_CONTACTB;
v_DEB CTBSALDO.VLDEBITOSALDO%TYP
v_CRED CTBSALDO.VLCREDITOSALDO%TY
v_DEBANT CTBSALDO.VLDEBANTSALDO%TYP
v_CREDANT CTBSALDO.VLCREDANTSALDO%TY
EXISTE BOOLEAN;
--************************
FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
PLANO IN CTBCONTA.NROPLANO%TYPE )
RETURN CTBCONTA.CLASSIFICADOR%TYP
vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYP
BEGIN
SELECT
CLASSIFICADOR
INTO
vCLASSIFICADOR
FROM CTBCONTA
WHERE CODCONTACTB = CONTACTB
AND NROPLANO = PLANO;
RETURN vCLASSIFICADOR;
END PEGACLASSIFICADOR;
--************************
FUNCTION PEGACONTACTB( PLANO IN CTBITLNC.NROPLANO%TYPE,
CLASSCTB IN CTBCONTA.CLASSIFICADOR%TYP
RETURN CTBCONTA.CODCONTACTB%TYPE IS
vCONTA CTBCONTA.CODCONTACTB%TYPE;
BEGIN
SELECT
CODCONTACTB
INTO
vCONTA
FROM CTBCONTA
WHERE NROPLANO = PLANO
AND CLASSIFICADOR = CLASSCTB;
RETURN vCONTA;
END PEGACONTACTB ;
--************************
FUNCTION VERIFICASALDONOMES( EMPRESA IN CTBSALDO.CODIGOEMPRESA%TYP
FILIAL IN CTBSALDO.CODIGOFL%TYPE,
PERIODO IN CTBSALDO.PERIODOSALDO%TYPE
PLANO IN CTBSALDO.NROPLANO%TYPE,
CONTACTB IN CTBSALDO.CODCONTACTB%TYPE )
RETURN BOOLEAN IS
CURSOR C_SALDOS IS
SELECT * FROM CTBSALDO
WHERE CODIGOEMPRESA = EMPRESA AND
CODIGOFL = FILIAL AND
NROPLANO = PLANO AND
PERIODOSALDO = PERIODO AND
CODCONTACTB = CONTACTB;
W_C_SALDOS C_SALDOS%ROWTYPE;
BEGIN
OPEN C_SALDOS;
FETCH C_SALDOS INTO W_C_SALDOS;
IF C_SALDOS%NOTFOUND THEN
BEGIN
v_DEB := 0;
v_CRED := 0;
v_DEBANT := 0;
v_CREDANT := 0;
CLOSE C_SALDOS;
RETURN FALSE;
END;
ELSE
BEGIN
v_DEB := W_C_SALDOS.VLDEBITOSALDO;
v_CRED := W_C_SALDOS.VLCREDITOSALDO;
v_DEBANT := W_C_SALDOS.VLDEBANTSALDO;
v_CREDANT := W_C_SALDOS.VLCREDANTSALDO;
CLOSE C_SALDOS;
RETURN TRUE;
END;
END IF;
END VERIFICASALDONOMES;
BEGIN
stmt_no := 100;
vContaAux := PCODCONTACTB;
stmt_no := 200;
SELECT
CODCONTACTB
INTO
vCONTAAux
FROM
CTBCONTA
WHERE
NROPLANO = PNROPLANO AND CODCONTACTB = PCODCONTACTB;
stmt_no := 400;
vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );
stmt_no := 500;
IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
END IF;
stmt_no := 600;
-- vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;
stmt_no := 700;
vCONTACTB(1) := PCODCONTACTB;
stmt_no := 800;
v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 900;
v_Class1 := SUBSTR(vCLASSCTB,1,2) || '00000000';
SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1000;
v_Class1 := SUBSTR(vCLASSCTB,1,4) || '000000';
SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1100;
v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1200;
INSERT INTO CTBITLNC
(
CODLANCA
, CODITEMLANCA
, NROPLANO
, CODCONTACTB
, CONTRAPARTITEMLANCA
, CODCUSTO
, VRITEMLANCA
, DEBITOCREDITOITEMLANCA
, HISTORICOITEMLANCA
, CONJUNTOITEMLANCA
)
VALUES
(
PCODLANCA
, PCODITEMLANCA
, PNROPLANO
, PCODCONTACTB
, PCONTRAPARTITEMLANCA
, PCODCUSTO
, PVRITEMLANCA
, PDEBITOCREDITO
, PHISTORICO
, PCONJUNTO
);
stmt_no := 1300;
If p_execProcPeriodo = 'S' then
p := 2;
else
p := 1;
End If;
stmt_no := 1400;
FOR I IN 1..P LOOP
BEGIN
FOR J IN 1..5 LOOP
stmt_no := 1500;
vContaAux := vCONTACTB(J);
stmt_no := 1600;
IF I = 1 THEN
vPERIODO := PDATA;
ELSE -- i = 2
vPERIODO := vPERIODO2;
end if;
stmt_no := 1700;
EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );
stmt_no := 1800;
v_PossuiMovto := 'S';
stmt_no := 1900;
IF I = 1 THEN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2000;
v_DEB := v_DEB + PVRITEMLANCA;
ELSE
stmt_no := 2100;
v_CRED := v_CRED + PVRITEMLANCA;
END IF;
ELSE -- i = 2
BEGIN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2200;
v_DEBANT := v_DEBANT + PVRITEMLANCA;
ELSE
stmt_no := 2300;
v_CREDANT := v_CREDANT + PVRITEMLANCA;
END IF;
END;
END IF;
stmt_no := 2400;
IF EXISTE THEN
UPDATE CTBSALDO
SET VLDEBITOSALDO = v_DEB,
VLCREDITOSALDO = v_CRED,
VLDEBANTSALDO = v_DEBANT,
VLCREDANTSALDO = v_CREDANT,
POSSUIMOVTO = v_PossuiMovto
WHERE CODIGOEMPRESA = PEMPRESA AND
CODIGOFL = PFILIAL AND
PERIODOSALDO = vPERIODO AND
NROPLANO = PNROPLANO AND
CODCONTACTB = vCONTACTB(J);
ELSE
stmt_no := 2500;
INSERT INTO CTBSALDO
(
CODIGOEMPRESA
, CODIGOFL
, PERIODOSALDO
, NROPLANO
, CODCONTACTB
, VLDEBITOSALDO
, VLCREDITOSALDO
, VLDEBANTSALDO
, VLCREDANTSALDO
, POSSUIMOVTO
)
VALUES
(
PEMPRESA
, PFILIAL
, VPERIODO
, PNROPLANO
, vCONTACTB(J)
, v_DEB
, v_CRED
, v_DEBANT
, v_CREDANT
, v_PossuiMovto
);
END IF;
END LOOP;
END;
END LOOP;
PMENSAGEM := 'PROCESSAMENTO FINALIZADO COM SUCESSO.';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20010, 'CONTA CONTABIL ' || VCONTAAUX || ' OU CLASSIFICADOR ' || V_CLASS1 || ' NAO CADASTRADA(O) NO PLANO ' || PNROPLANO || '.' );
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR( -20020, 'DUPLICACAO DE REGISTRO. TENTE NOVAMENTE.' );
WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR( -06502, 'Avisar a BgmRodotec que o erro "1" foi no ponto "'||to_char(stmt_no)||'"' );
when others then
raise_application_error( -20030, 'Avisar a BgmRodotec que o erro "2" foi no ponto "'||to_char(stmt_no)||'"' );
END PR_CTB_GRAVALANC;
/
I surely don't know why the error occurred at statement number 1200; i.e., at the insert statement
If you know exactly the values of the input parameters which cause the procedure to fail, then my recommendation is rewrite the procedure as a pl/sql block without the EXCEPTION (see below) and run it in sqlplus directly to see exactly where and why it fails
DECLARE
PCODLANCA CTBITLNC.CODLANCA%TYPE := <INPUT VALUE>;
PCODITEMLANCA CTBITLNC.CODITEMLANCA%TYPE := <INPUT VALUE>;
PNROPLANO CTBITLNC.NROPLANO%TYPE := <INPUT VALUE>;
PVRITEMLANCA CTBITLNC.VRITEMLANCA%TYPE := <INPUT VALUE>;
PCONTRAPARTITEMLANCA CTBITLNC.CONTRAPARTITEMLAN CA%TYPE := <INPUT VALUE>;
PDEBITOCREDITO CTBITLNC.DEBITOCREDITOITEM LANCA%TYPE := <INPUT VALUE>;
PHISTORICO CTBITLNC.HISTORICOITEMLANC A%TYPE := <INPUT VALUE>;
PCONJUNTO CTBITLNC.CONJUNTOITEMLANCA %TYPE := <INPUT VALUE>;
PCODCONTACTB CTBITLNC.CODCONTACTB%TYPE := <INPUT VALUE>;
PCODCUSTO CTBITLNC.CODCUSTO%TYPE := <INPUT VALUE>;
PDATA CTBSALDO.PERIODOSALDO%TYPE := <INPUT VALUE>; -- Não aceita Varchar2(6)
p_execProcPeriodo CTBITLNC.DEBITOCREDITOITEM LANCA%TYPE := <INPUT VALUE>; -- Não aceita Varchar2(1)
PEMPRESA CTBLANCA.CODIGOEMPRESA%TYP E := <INPUT VALUE>;
PFILIAL CTBLANCA.CODIGOFL%TYPE := <INPUT VALUE>;
PMENSAGEM STRING -- ??????
stmt_no number;
vPERIODO2 CTBSALDO.PERIODOSALDO%TYPE ;
vPERIODO CTBSALDO.PERIODOSALDO%TYPE ;
vCLASSCTB CTBCONTA.CLASSIFICADOR%TYP E;
vContaAux CTBITLNC.CODCONTACTB%TYPE;
v_Class1 CTBCONTA.CLASSIFICADOR%TYP E;
v_PossuiMovto CTBSALDO.POSSUIMOVTO%TYPE;
v_PossuiMovtoAnt CTBSALDO.POSSUIMOVTO%TYPE;
j NUMBER;
I NUMBER;
P NUMBER;
TYPE T_CONTACTB IS TABLE OF CTBCONTA.CODCONTACTB%TYPE INDEX BY BINARY_INTEGER;
vCONTACTB T_CONTACTB;
v_DEB CTBSALDO.VLDEBITOSALDO%TYP E;
v_CRED CTBSALDO.VLCREDITOSALDO%TY PE;
v_DEBANT CTBSALDO.VLDEBANTSALDO%TYP E;
v_CREDANT CTBSALDO.VLCREDANTSALDO%TY PE;
EXISTE BOOLEAN;
--************************ **
FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
PLANO IN CTBCONTA.NROPLANO%TYPE )
RETURN CTBCONTA.CLASSIFICADOR%TYP E IS
vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYP E;
BEGIN
SELECT
CLASSIFICADOR
INTO
vCLASSIFICADOR
FROM CTBCONTA
WHERE CODCONTACTB = CONTACTB
AND NROPLANO = PLANO;
RETURN vCLASSIFICADOR;
END PEGACLASSIFICADOR;
--************************ **
FUNCTION PEGACONTACTB( PLANO IN CTBITLNC.NROPLANO%TYPE,
CLASSCTB IN CTBCONTA.CLASSIFICADOR%TYP E )
RETURN CTBCONTA.CODCONTACTB%TYPE IS
vCONTA CTBCONTA.CODCONTACTB%TYPE;
BEGIN
SELECT
CODCONTACTB
INTO
vCONTA
FROM CTBCONTA
WHERE NROPLANO = PLANO
AND CLASSIFICADOR = CLASSCTB;
RETURN vCONTA;
END PEGACONTACTB ;
--************************ **
FUNCTION VERIFICASALDONOMES( EMPRESA IN CTBSALDO.CODIGOEMPRESA%TYP E,
FILIAL IN CTBSALDO.CODIGOFL%TYPE,
PERIODO IN CTBSALDO.PERIODOSALDO%TYPE ,
PLANO IN CTBSALDO.NROPLANO%TYPE,
CONTACTB IN CTBSALDO.CODCONTACTB%TYPE )
RETURN BOOLEAN IS
CURSOR C_SALDOS IS
SELECT * FROM CTBSALDO
WHERE CODIGOEMPRESA = EMPRESA AND
CODIGOFL = FILIAL AND
NROPLANO = PLANO AND
PERIODOSALDO = PERIODO AND
CODCONTACTB = CONTACTB;
W_C_SALDOS C_SALDOS%ROWTYPE;
BEGIN
OPEN C_SALDOS;
FETCH C_SALDOS INTO W_C_SALDOS;
IF C_SALDOS%NOTFOUND THEN
BEGIN
v_DEB := 0;
v_CRED := 0;
v_DEBANT := 0;
v_CREDANT := 0;
CLOSE C_SALDOS;
RETURN FALSE;
END;
ELSE
BEGIN
v_DEB := W_C_SALDOS.VLDEBITOSALDO;
v_CRED := W_C_SALDOS.VLCREDITOSALDO;
v_DEBANT := W_C_SALDOS.VLDEBANTSALDO;
v_CREDANT := W_C_SALDOS.VLCREDANTSALDO;
CLOSE C_SALDOS;
RETURN TRUE;
END;
END IF;
END VERIFICASALDONOMES;
BEGIN
stmt_no := 100;
vContaAux := PCODCONTACTB;
stmt_no := 200;
SELECT
CODCONTACTB
INTO
vCONTAAux
FROM
CTBCONTA
WHERE
NROPLANO = PNROPLANO AND CODCONTACTB = PCODCONTACTB;
stmt_no := 400;
vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );
stmt_no := 500;
IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
END IF;
stmt_no := 600;
-- vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;
stmt_no := 700;
vCONTACTB(1) := PCODCONTACTB;
stmt_no := 800;
v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 900;
v_Class1 := SUBSTR(vCLASSCTB,1,2) || '00000000';
SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1000;
v_Class1 := SUBSTR(vCLASSCTB,1,4) || '000000';
SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1100;
v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1200;
INSERT INTO CTBITLNC
(
CODLANCA
, CODITEMLANCA
, NROPLANO
, CODCONTACTB
, CONTRAPARTITEMLANCA
, CODCUSTO
, VRITEMLANCA
, DEBITOCREDITOITEMLANCA
, HISTORICOITEMLANCA
, CONJUNTOITEMLANCA
)
VALUES
(
PCODLANCA
, PCODITEMLANCA
, PNROPLANO
, PCODCONTACTB
, PCONTRAPARTITEMLANCA
, PCODCUSTO
, PVRITEMLANCA
, PDEBITOCREDITO
, PHISTORICO
, PCONJUNTO
);
stmt_no := 1300;
If p_execProcPeriodo = 'S' then
p := 2;
else
p := 1;
End If;
stmt_no := 1400;
FOR I IN 1..P LOOP
BEGIN
FOR J IN 1..5 LOOP
stmt_no := 1500;
vContaAux := vCONTACTB(J);
stmt_no := 1600;
IF I = 1 THEN
vPERIODO := PDATA;
ELSE -- i = 2
vPERIODO := vPERIODO2;
end if;
stmt_no := 1700;
EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );
stmt_no := 1800;
v_PossuiMovto := 'S';
stmt_no := 1900;
IF I = 1 THEN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2000;
v_DEB := v_DEB + PVRITEMLANCA;
ELSE
stmt_no := 2100;
v_CRED := v_CRED + PVRITEMLANCA;
END IF;
ELSE -- i = 2
BEGIN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2200;
v_DEBANT := v_DEBANT + PVRITEMLANCA;
ELSE
stmt_no := 2300;
v_CREDANT := v_CREDANT + PVRITEMLANCA;
END IF;
END;
END IF;
stmt_no := 2400;
IF EXISTE THEN
UPDATE CTBSALDO
SET VLDEBITOSALDO = v_DEB,
VLCREDITOSALDO = v_CRED,
VLDEBANTSALDO = v_DEBANT,
VLCREDANTSALDO = v_CREDANT,
POSSUIMOVTO = v_PossuiMovto
WHERE CODIGOEMPRESA = PEMPRESA AND
CODIGOFL = PFILIAL AND
PERIODOSALDO = vPERIODO AND
NROPLANO = PNROPLANO AND
CODCONTACTB = vCONTACTB(J);
ELSE
stmt_no := 2500;
INSERT INTO CTBSALDO
(
CODIGOEMPRESA
, CODIGOFL
, PERIODOSALDO
, NROPLANO
, CODCONTACTB
, VLDEBITOSALDO
, VLCREDITOSALDO
, VLDEBANTSALDO
, VLCREDANTSALDO
, POSSUIMOVTO
)
VALUES
(
PEMPRESA
, PFILIAL
, VPERIODO
, PNROPLANO
, vCONTACTB(J)
, v_DEB
, v_CRED
, v_DEBANT
, v_CREDANT
, v_PossuiMovto
);
END IF;
END LOOP;
END;
END LOOP;
PMENSAGEM := 'PROCESSAMENTO FINALIZADO COM SUCESSO.';
END;
/
If you know exactly the values of the input parameters which cause the procedure to fail, then my recommendation is rewrite the procedure as a pl/sql block without the EXCEPTION (see below) and run it in sqlplus directly to see exactly where and why it fails
DECLARE
PCODLANCA CTBITLNC.CODLANCA%TYPE := <INPUT VALUE>;
PCODITEMLANCA CTBITLNC.CODITEMLANCA%TYPE
PNROPLANO CTBITLNC.NROPLANO%TYPE := <INPUT VALUE>;
PVRITEMLANCA CTBITLNC.VRITEMLANCA%TYPE := <INPUT VALUE>;
PCONTRAPARTITEMLANCA CTBITLNC.CONTRAPARTITEMLAN
PDEBITOCREDITO CTBITLNC.DEBITOCREDITOITEM
PHISTORICO CTBITLNC.HISTORICOITEMLANC
PCONJUNTO CTBITLNC.CONJUNTOITEMLANCA
PCODCONTACTB CTBITLNC.CODCONTACTB%TYPE := <INPUT VALUE>;
PCODCUSTO CTBITLNC.CODCUSTO%TYPE := <INPUT VALUE>;
PDATA CTBSALDO.PERIODOSALDO%TYPE
p_execProcPeriodo CTBITLNC.DEBITOCREDITOITEM
PEMPRESA CTBLANCA.CODIGOEMPRESA%TYP
PFILIAL CTBLANCA.CODIGOFL%TYPE := <INPUT VALUE>;
PMENSAGEM STRING -- ??????
stmt_no number;
vPERIODO2 CTBSALDO.PERIODOSALDO%TYPE
vPERIODO CTBSALDO.PERIODOSALDO%TYPE
vCLASSCTB CTBCONTA.CLASSIFICADOR%TYP
vContaAux CTBITLNC.CODCONTACTB%TYPE;
v_Class1 CTBCONTA.CLASSIFICADOR%TYP
v_PossuiMovto CTBSALDO.POSSUIMOVTO%TYPE;
v_PossuiMovtoAnt CTBSALDO.POSSUIMOVTO%TYPE;
j NUMBER;
I NUMBER;
P NUMBER;
TYPE T_CONTACTB IS TABLE OF CTBCONTA.CODCONTACTB%TYPE INDEX BY BINARY_INTEGER;
vCONTACTB T_CONTACTB;
v_DEB CTBSALDO.VLDEBITOSALDO%TYP
v_CRED CTBSALDO.VLCREDITOSALDO%TY
v_DEBANT CTBSALDO.VLDEBANTSALDO%TYP
v_CREDANT CTBSALDO.VLCREDANTSALDO%TY
EXISTE BOOLEAN;
--************************
FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
PLANO IN CTBCONTA.NROPLANO%TYPE )
RETURN CTBCONTA.CLASSIFICADOR%TYP
vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYP
BEGIN
SELECT
CLASSIFICADOR
INTO
vCLASSIFICADOR
FROM CTBCONTA
WHERE CODCONTACTB = CONTACTB
AND NROPLANO = PLANO;
RETURN vCLASSIFICADOR;
END PEGACLASSIFICADOR;
--************************
FUNCTION PEGACONTACTB( PLANO IN CTBITLNC.NROPLANO%TYPE,
CLASSCTB IN CTBCONTA.CLASSIFICADOR%TYP
RETURN CTBCONTA.CODCONTACTB%TYPE IS
vCONTA CTBCONTA.CODCONTACTB%TYPE;
BEGIN
SELECT
CODCONTACTB
INTO
vCONTA
FROM CTBCONTA
WHERE NROPLANO = PLANO
AND CLASSIFICADOR = CLASSCTB;
RETURN vCONTA;
END PEGACONTACTB ;
--************************
FUNCTION VERIFICASALDONOMES( EMPRESA IN CTBSALDO.CODIGOEMPRESA%TYP
FILIAL IN CTBSALDO.CODIGOFL%TYPE,
PERIODO IN CTBSALDO.PERIODOSALDO%TYPE
PLANO IN CTBSALDO.NROPLANO%TYPE,
CONTACTB IN CTBSALDO.CODCONTACTB%TYPE )
RETURN BOOLEAN IS
CURSOR C_SALDOS IS
SELECT * FROM CTBSALDO
WHERE CODIGOEMPRESA = EMPRESA AND
CODIGOFL = FILIAL AND
NROPLANO = PLANO AND
PERIODOSALDO = PERIODO AND
CODCONTACTB = CONTACTB;
W_C_SALDOS C_SALDOS%ROWTYPE;
BEGIN
OPEN C_SALDOS;
FETCH C_SALDOS INTO W_C_SALDOS;
IF C_SALDOS%NOTFOUND THEN
BEGIN
v_DEB := 0;
v_CRED := 0;
v_DEBANT := 0;
v_CREDANT := 0;
CLOSE C_SALDOS;
RETURN FALSE;
END;
ELSE
BEGIN
v_DEB := W_C_SALDOS.VLDEBITOSALDO;
v_CRED := W_C_SALDOS.VLCREDITOSALDO;
v_DEBANT := W_C_SALDOS.VLDEBANTSALDO;
v_CREDANT := W_C_SALDOS.VLCREDANTSALDO;
CLOSE C_SALDOS;
RETURN TRUE;
END;
END IF;
END VERIFICASALDONOMES;
BEGIN
stmt_no := 100;
vContaAux := PCODCONTACTB;
stmt_no := 200;
SELECT
CODCONTACTB
INTO
vCONTAAux
FROM
CTBCONTA
WHERE
NROPLANO = PNROPLANO AND CODCONTACTB = PCODCONTACTB;
stmt_no := 400;
vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );
stmt_no := 500;
IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
END IF;
stmt_no := 600;
-- vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;
stmt_no := 700;
vCONTACTB(1) := PCODCONTACTB;
stmt_no := 800;
v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 900;
v_Class1 := SUBSTR(vCLASSCTB,1,2) || '00000000';
SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1000;
v_Class1 := SUBSTR(vCLASSCTB,1,4) || '000000';
SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1100;
v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;
stmt_no := 1200;
INSERT INTO CTBITLNC
(
CODLANCA
, CODITEMLANCA
, NROPLANO
, CODCONTACTB
, CONTRAPARTITEMLANCA
, CODCUSTO
, VRITEMLANCA
, DEBITOCREDITOITEMLANCA
, HISTORICOITEMLANCA
, CONJUNTOITEMLANCA
)
VALUES
(
PCODLANCA
, PCODITEMLANCA
, PNROPLANO
, PCODCONTACTB
, PCONTRAPARTITEMLANCA
, PCODCUSTO
, PVRITEMLANCA
, PDEBITOCREDITO
, PHISTORICO
, PCONJUNTO
);
stmt_no := 1300;
If p_execProcPeriodo = 'S' then
p := 2;
else
p := 1;
End If;
stmt_no := 1400;
FOR I IN 1..P LOOP
BEGIN
FOR J IN 1..5 LOOP
stmt_no := 1500;
vContaAux := vCONTACTB(J);
stmt_no := 1600;
IF I = 1 THEN
vPERIODO := PDATA;
ELSE -- i = 2
vPERIODO := vPERIODO2;
end if;
stmt_no := 1700;
EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );
stmt_no := 1800;
v_PossuiMovto := 'S';
stmt_no := 1900;
IF I = 1 THEN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2000;
v_DEB := v_DEB + PVRITEMLANCA;
ELSE
stmt_no := 2100;
v_CRED := v_CRED + PVRITEMLANCA;
END IF;
ELSE -- i = 2
BEGIN
IF PDEBITOCREDITO = 'D' THEN
stmt_no := 2200;
v_DEBANT := v_DEBANT + PVRITEMLANCA;
ELSE
stmt_no := 2300;
v_CREDANT := v_CREDANT + PVRITEMLANCA;
END IF;
END;
END IF;
stmt_no := 2400;
IF EXISTE THEN
UPDATE CTBSALDO
SET VLDEBITOSALDO = v_DEB,
VLCREDITOSALDO = v_CRED,
VLDEBANTSALDO = v_DEBANT,
VLCREDANTSALDO = v_CREDANT,
POSSUIMOVTO = v_PossuiMovto
WHERE CODIGOEMPRESA = PEMPRESA AND
CODIGOFL = PFILIAL AND
PERIODOSALDO = vPERIODO AND
NROPLANO = PNROPLANO AND
CODCONTACTB = vCONTACTB(J);
ELSE
stmt_no := 2500;
INSERT INTO CTBSALDO
(
CODIGOEMPRESA
, CODIGOFL
, PERIODOSALDO
, NROPLANO
, CODCONTACTB
, VLDEBITOSALDO
, VLCREDITOSALDO
, VLDEBANTSALDO
, VLCREDANTSALDO
, POSSUIMOVTO
)
VALUES
(
PEMPRESA
, PFILIAL
, VPERIODO
, PNROPLANO
, vCONTACTB(J)
, v_DEB
, v_CRED
, v_DEBANT
, v_CREDANT
, v_PossuiMovto
);
END IF;
END LOOP;
END;
END LOOP;
PMENSAGEM := 'PROCESSAMENTO FINALIZADO COM SUCESSO.';
END;
/
We are using Forms 6i with Patch 15 for Microsoft Windows environment on an Oracle 9 Release 2 database and we are getting this ORA-06502 very often on application programs that have been running for years without problems. By placing messages into the source code, we trace the procedure that is offending, but with these messages, the software works. We have found that taking the messages out but leaving a Synchronize command keeps the software running. The trouble is that we are now gradually swamping the source code with Synchronize commands!
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.