Solved

ora-06502

Posted on 2003-12-04
14
5,506 Views
Last Modified: 2012-05-04
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
Comment
Question by:junior_soares
  • 6
  • 2
  • 2
  • +3
14 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 9874111
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
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9874121
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
 

Author Comment

by:junior_soares
ID: 9874256
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
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9874299
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 9874354
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
 

Author Comment

by:junior_soares
ID: 9874462
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
 

Author Comment

by:junior_soares
ID: 9875189
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:junior_soares
ID: 9876141
somebody have any answer?
0
 
LVL 5

Accepted Solution

by:
FBIAGENT earned 50 total points
ID: 9877116
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
 

Author Comment

by:junior_soares
ID: 9895745
thanks FBIAGENT
0
 

Expert Comment

by:ratner
ID: 10053719
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
 

Author Comment

by:junior_soares
ID: 10064399
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
 
LVL 5

Expert Comment

by:FBIAGENT
ID: 10064672
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
 

Expert Comment

by:DMay1
ID: 10538838
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now