[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5520
  • Last Modified:

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.

   
0
junior_soares
Asked:
junior_soares
  • 6
  • 2
  • 2
  • +3
1 Solution
 
Helena Markováprogrammer-analystCommented:
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.
0
 
DrJekyllCommented:
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.
0
 
junior_soaresAuthor Commented:
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.

 
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
DrJekyllCommented:
There were bugs for this error in earlier versions.  Are you using any TO_NUMBER functions or
implicitly doing character to number conversion?

DJ
0
 
Helena Markováprogrammer-analystCommented:
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.
0
 
junior_soaresAuthor Commented:
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.CONTRAPARTITEMLANCA%TYPE,
  PDEBITOCREDITO       IN CTBITLNC.DEBITOCREDITOITEMLANCA%TYPE,
  PHISTORICO           IN CTBITLNC.HISTORICOITEMLANCA%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.DEBITOCREDITOITEMLANCA%TYPE, -- Não aceita Varchar2(1)
  PEMPRESA             IN CTBLANCA.CODIGOEMPRESA%TYPE,
  PFILIAL              IN CTBLANCA.CODIGOFL%TYPE,
  PMENSAGEM            OUT STRING
)

IS

  vPERIODO2  CTBSALDO.PERIODOSALDO%TYPE;
  vPERIODO   CTBSALDO.PERIODOSALDO%TYPE;
  vCLASSCTB  CTBCONTA.CLASSIFICADOR%TYPE;
  vContaAux  CTBITLNC.CODCONTACTB%TYPE;
  v_Class1   CTBCONTA.CLASSIFICADOR%TYPE;
  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%TYPE;
  v_CRED     CTBSALDO.VLCREDITOSALDO%TYPE;
  v_DEBANT   CTBSALDO.VLDEBANTSALDO%TYPE;
  v_CREDANT  CTBSALDO.VLCREDANTSALDO%TYPE;

  EXISTE     BOOLEAN;


  FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
                               PLANO IN CTBCONTA.NROPLANO%TYPE )
                               RETURN CTBCONTA.CLASSIFICADOR%TYPE IS

  vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYPE;

  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%TYPE )
                         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%TYPE,
                               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
0
 
junior_soaresAuthor Commented:
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
0
 
junior_soaresAuthor Commented:
somebody have any answer?
0
 
FBIAGENTCommented:
you may want to try the following if you have know other way to debug

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.CONTRAPARTITEMLANCA%TYPE,
  PDEBITOCREDITO       IN CTBITLNC.DEBITOCREDITOITEMLANCA%TYPE,
  PHISTORICO           IN CTBITLNC.HISTORICOITEMLANCA%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.DEBITOCREDITOITEMLANCA%TYPE, -- Não aceita Varchar2(1)
  PEMPRESA             IN CTBLANCA.CODIGOEMPRESA%TYPE,
  PFILIAL              IN CTBLANCA.CODIGOFL%TYPE,
  PMENSAGEM            OUT STRING
)

IS

  stmt_no number := 0;

  vPERIODO2  CTBSALDO.PERIODOSALDO%TYPE;
  vPERIODO   CTBSALDO.PERIODOSALDO%TYPE;
  vCLASSCTB  CTBCONTA.CLASSIFICADOR%TYPE;
  vContaAux  CTBITLNC.CODCONTACTB%TYPE;
  v_Class1   CTBCONTA.CLASSIFICADOR%TYPE;
  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%TYPE;
  v_CRED     CTBSALDO.VLCREDITOSALDO%TYPE;
  v_DEBANT   CTBSALDO.VLDEBANTSALDO%TYPE;
  v_CREDANT  CTBSALDO.VLCREDANTSALDO%TYPE;

  EXISTE     BOOLEAN;


  FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
                               PLANO IN CTBCONTA.NROPLANO%TYPE )
                               RETURN CTBCONTA.CLASSIFICADOR%TYPE IS

  vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYPE;

  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%TYPE )
                         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%TYPE,
                               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 number := 100;

   vContaAux := PCODCONTACTB;

   stmt_no number := 200;

   SELECT CODCONTACTB
   INTO vCONTAAux
   FROM CTBCONTA
   WHERE NROPLANO = PNROPLANO
   AND   CODCONTACTB = PCODCONTACTB;

  stmt_no number := 400;

  vPERIODO2 := TO_CHAR( TO_NUMBER(PDATA) + 1 );

  stmt_no number := 500;

  IF SUBSTR( vPERIODO2, 5, 2 ) = '13' THEN
    vPERIODO2 := TO_CHAR( TO_NUMBER(vPERIODO2) + 88 );
  END IF;

  stmt_no number := 600;

--  vCLASSCTB := PEGACLASSIFICADOR( PCODCONTACTB , PNROPLANO );
  SELECT CLASSIFICADOR INTO vCLASSCTB FROM CTBCONTA
  WHERE CODCONTACTB = PCODCONTACTB AND NROPLANO = PNROPLANO;

  stmt_no number := 700;

  vCONTACTB(1) := PCODCONTACTB;

  stmt_no number := 800;
  v_Class1 := SUBSTR(vCLASSCTB,1,1) || '000000000';
  SELECT CODCONTACTB INTO vCONTACTB(2) FROM CTBCONTA
  WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;

  stmt_no number := 900;
 
  v_Class1 := SUBSTR(vCLASSCTB,1,2) ||  '00000000';
  SELECT CODCONTACTB INTO vCONTACTB(3) FROM CTBCONTA
  WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;

  stmt_no number := 1000;

  v_Class1 := SUBSTR(vCLASSCTB,1,4) ||  '000000';
  SELECT CODCONTACTB INTO vCONTACTB(4) FROM CTBCONTA
  WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;

  stmt_no number := 1100;

  v_Class1 := SUBSTR(vCLASSCTB,1,6) || '0000';
  SELECT CODCONTACTB INTO vCONTACTB(5) FROM CTBCONTA
  WHERE CLASSIFICADOR = v_Class1 AND NROPLANO = PNROPLANO;

  stmt_no number := 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 number := 1300;
 
  If p_execProcPeriodo = 'S' then
    p := 2;
  else
    p := 1;
  End If;

  stmt_no number := 1400;

  FOR I IN 1..P LOOP
  BEGIN
    FOR J IN 1..5 LOOP

      stmt_no number := 1500;
      vContaAux := vCONTACTB(J);

      stmt_no number := 1600;

      IF I = 1 THEN
        vPERIODO := PDATA;
      ELSE -- i = 2
        vPERIODO := vPERIODO2;
      end if;

      stmt_no number := 1700;

      EXISTE := VERIFICASALDONOMES( PEMPRESA, PFILIAL, VPERIODO, PNROPLANO, vCONTACTB(J) );

      stmt_no number := 1800;

      v_PossuiMovto := 'S';

      stmt_no number := 1900;

      IF I = 1 THEN
        IF PDEBITOCREDITO = 'D' THEN

          stmt_no number := 2000;
          v_DEB  := v_DEB  + PVRITEMLANCA;
        ELSE

          stmt_no number := 2100;
          v_CRED := v_CRED + PVRITEMLANCA;
        END IF;
      ELSE -- i = 2
      BEGIN
        IF PDEBITOCREDITO = 'D' THEN

          stmt_no number := 2200;
          v_DEBANT := v_DEBANT  + PVRITEMLANCA;
        ELSE

          stmt_no number := 2300;
          v_CREDANT := v_CREDANT + PVRITEMLANCA;
        END IF;
      END;
      END IF;

      stmt_no number := 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 number := 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( -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'||' at '||to_char(stmt_no) );
         when others then
           raise_application_error( -29999, 'others (???? sqlcode ????) occurred at '||to_char(stmt_no) );
           

END PR_CTB_GRAVALANC;
/
0
 
junior_soaresAuthor Commented:
thanks FBIAGENT
0
 
ratnerCommented:
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
0
 
junior_soaresAuthor Commented:
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.CONTRAPARTITEMLANCA%TYPE,
  PDEBITOCREDITO       IN CTBITLNC.DEBITOCREDITOITEMLANCA%TYPE,
  PHISTORICO           IN CTBITLNC.HISTORICOITEMLANCA%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.DEBITOCREDITOITEMLANCA%TYPE, -- Não aceita Varchar2(1)
  PEMPRESA             IN CTBLANCA.CODIGOEMPRESA%TYPE,
  PFILIAL              IN CTBLANCA.CODIGOFL%TYPE,
  PMENSAGEM            OUT STRING
)

IS

  stmt_no number;

  vPERIODO2  CTBSALDO.PERIODOSALDO%TYPE;
  vPERIODO   CTBSALDO.PERIODOSALDO%TYPE;
  vCLASSCTB  CTBCONTA.CLASSIFICADOR%TYPE;
  vContaAux  CTBITLNC.CODCONTACTB%TYPE;
  v_Class1   CTBCONTA.CLASSIFICADOR%TYPE;
  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%TYPE;
  v_CRED     CTBSALDO.VLCREDITOSALDO%TYPE;
  v_DEBANT   CTBSALDO.VLDEBANTSALDO%TYPE;
  v_CREDANT  CTBSALDO.VLCREDANTSALDO%TYPE;

  EXISTE     BOOLEAN;


--**************************
  FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
                               PLANO IN CTBCONTA.NROPLANO%TYPE )
                               RETURN CTBCONTA.CLASSIFICADOR%TYPE IS

  vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYPE;

  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%TYPE )
                         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%TYPE,
                               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;
/
0
 
FBIAGENTCommented:
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.CONTRAPARTITEMLANCA%TYPE := <INPUT VALUE>;
  PDEBITOCREDITO       CTBITLNC.DEBITOCREDITOITEMLANCA%TYPE := <INPUT VALUE>;
  PHISTORICO           CTBITLNC.HISTORICOITEMLANCA%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.DEBITOCREDITOITEMLANCA%TYPE := <INPUT VALUE>; -- Não aceita Varchar2(1)
  PEMPRESA             CTBLANCA.CODIGOEMPRESA%TYPE := <INPUT VALUE>;
  PFILIAL              CTBLANCA.CODIGOFL%TYPE := <INPUT VALUE>;
  PMENSAGEM            STRING -- ??????

  stmt_no number;

  vPERIODO2  CTBSALDO.PERIODOSALDO%TYPE;
  vPERIODO   CTBSALDO.PERIODOSALDO%TYPE;
  vCLASSCTB  CTBCONTA.CLASSIFICADOR%TYPE;
  vContaAux  CTBITLNC.CODCONTACTB%TYPE;
  v_Class1   CTBCONTA.CLASSIFICADOR%TYPE;
  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%TYPE;
  v_CRED     CTBSALDO.VLCREDITOSALDO%TYPE;
  v_DEBANT   CTBSALDO.VLDEBANTSALDO%TYPE;
  v_CREDANT  CTBSALDO.VLCREDANTSALDO%TYPE;

  EXISTE     BOOLEAN;


--**************************
  FUNCTION PEGACLASSIFICADOR ( CONTACTB IN CTBCONTA.CODCONTACTB%TYPE,
                               PLANO IN CTBCONTA.NROPLANO%TYPE )
                               RETURN CTBCONTA.CLASSIFICADOR%TYPE IS

  vCLASSIFICADOR CTBCONTA.CLASSIFICADOR%TYPE;

  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%TYPE )
                         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%TYPE,
                               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;
/
0
 
DMay1Commented:
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!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now