DotConnect got an excepction ... 01858

DotConnect got an excepction ... 01858

Postby sis01 » Thu 07 Mar 2013 17:18

Hello!

Please need help, because I have a problem:

Im using dotconnect 6.60 with C# in .NET

I have an Stored Procedure in Oracle :

Code: Select all
PROCEDURE AGREGA_CC_CREDITOS(pfcorteANT IN DATE, pfechafin IN DATE)
IS
   emesg VARCHAR2 (2500) := 'Testing';
BEGIN
     
    dbms_output.enable(1000000);
    BEGIN
       
                           
           INSERT INTO BURO.PRE_BDSALDOS
            (FCORTE, STATUS, FUENTEBD, CLNS, CDGCLNS, CICLO, CDGCL, INICICLO, FINCICLO, CANTENTRE, PLAZO, FREQPAGOS, PAGOESP, SALDO, SDOVENCIDO,
                    NUMPAGVEN, MOP, FCIERRECTA, CVEPREVENC, CDGRG, CDGCO)
             (SELECT pfechafin, CC.STATUSCC, CC.FUENTEBD, CC.CLNS, CC.CDGNS, CC.CICLO, CC.CDGCL, CC.INICICLO, CC.FINCICLO, CC.PRCCANTENT,
                     CC.NUMPAGOS, CC.FREQPAGOS, CC.PAGOPACCL, CC.SALDOCL, CC.SDOVENCL, CC.NUMPAGVEN, CC.PAGOACTUAL, CC.FCIERRECTA,
                     NULL AS CVEPREVENC, VPRC.CDGRG, VPRC.CDGCO -- null as cdgrg, null as cdgco
             FROM CIRCRED.SALDOS  CC,
                  ICARO.VW_PRC  VPRC
             WHERE CC.FCORTE = pfcorteANT AND CC.FUENTEBD = 'ESIACOM' AND CC.STATUSCC NOT IN ('CIERR')
                   AND CC.CLNS = VPRC.CLNS AND CC.CDGNS = VPRC.CDGCLNS AND CC.CICLO = VPRC.CICLO AND CC.CDGCL = VPRC.CDGCL); 

    EXCEPTION 
        WHEN OTHERS THEN  --  <-- Como indicar que atrapa CUALQUIER EXCEPCION SIN DEJAR QUE LA DETECTE .NET ??
        emesg := 'ERROR: ' || SQLCODE || '-' || SQLERRM ;
        DBMS_OUTPUT.PUT_LINE(emesg);
    END;   
             
END AGREGA_CC_CREDITOS;


When i execute it from TOAD or from Sqldeveloper (an oracle tool) it works fine
without any errors or exceptions.

But

This is the code im using to invoke that stored procedure from my app in C# with dotconnect

Code: Select all
//using System.Data.OracleClient;    //  --> If i use this, runs ok without exceptions!
using Devart.Data.Oracle;


      public void Agrega_CC_Creditos(DateTime pfcmesant, DateTime pffin)  // 02 -- AGREGA CREDITOS DE CC DE MES ANTERIOR
        {
           
            string connectionString = csutils.Varconst.BURO_cnstr;
           
            /* call stored procedure */
            using (OracleConnection cn = new OracleConnection(connectionString))
            {
                OracleCommand cmd = new OracleCommand("BURO.BC_CREDITOS.AGREGA_CC_CREDITOS", cn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("pfcorteANT", pfcmesant);
                cmd.Parameters.AddWithValue("pfechafin", pffin);
               
                try
                {   cn.Open();
                    cmd.ExecuteNonQuery();
                   
                }
                catch (OracleException ex)
                {
                    /* handles the exception and does not ignore it */
                   string mensaje = string.Empty;
                    mensaje = ex.Message;
                    MessageBox.Show(mensaje);
                }
                finally
                {   
                    cn.Close();
                    if (cmd != null) cmd.Dispose();
                }
            }
        }


****

when i excecute it, i get an exception error like this.

ORA-01858: a non-numeric character was found where a numeric was expected\n
ORA-06512: at \"ESIACOM.INICIOREAL\", line 152\n
ORA-06592: CASE not found while executing CASE statement\n
ORA-06512: at \"ESIACOM.FECHAPERIODO\", line 129\n
ORA-06512: at \"ESIACOM.VENCIMIENTOPOST\", line 99\n
ORA-06512: at \"ESIACOM.FINPR\", line 50\n
ORA-06512: at \"BURO.BC_CREDITOS\", line 217\n
ORA-06512: at line 2"


Is there any way to IGNORE that exceptions...?

Thanks in advance...
sis01
 
Posts: 9
Joined: Thu 07 Mar 2013 16:43

Re: DotConnect got an excepction ... 01858

Postby Pinturiccio » Tue 12 Mar 2013 12:19

sis01 wrote:ORA-01858: a non-numeric character was found where a numeric was expected

Please refer to http://forums.devart.com/viewtopic.php?t=17183.

If this doesn't help, send us the DDL/DML scripts for all objects used in the procedure: PRE_BDSALDOS, SALDOS, VW_PRC. Besides, according to your code, you use the procedure inside a package. Please send us the package definition script.

Please also provide the following information:
1) Do you use the direct or OCI mode?
2) the NLS_LANGUAGE, NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters of your Oracle server;
3) the NLS_LANG setting of your Oracle Client (in the registry);
4) regional settings of your operating system: Control Panel > Regional and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Re: DotConnect got an excepction ... 01858

Postby sis01 » Thu 21 Mar 2013 14:46

Hello

Here are the definition scrips of the tables

Code: Select all
CREATE TABLE BURO.PRE_BDSALDOS
(
  FCORTE      DATE,
  STATUS      VARCHAR2(5 BYTE),
  FUENTEBD    VARCHAR2(7 BYTE),
  CDGRG       VARCHAR2(3 BYTE),
  CDGCO       VARCHAR2(3 BYTE),
  CLNS        VARCHAR2(1 BYTE),
  CDGCLNS     VARCHAR2(6 BYTE),
  CICLO       VARCHAR2(2 BYTE),
  CDGCL       VARCHAR2(6 BYTE),
  INICICLO    DATE,
  FINCICLO    DATE,
  CANTENTRE   NUMBER(15,2),
  FREQPAGOS   VARCHAR2(1 BYTE),
  PLAZO       NUMBER(5),
  TIPOCONT    VARCHAR2(2 BYTE),
  TOTALPAGAR  NUMBER(15,4),
  PAGOESP     NUMBER(15,2),
  PAGADOCL    NUMBER(15,4),
  FULTPAGO    DATE,
  SALDO       NUMBER(15,4),
  SDOVENCIDO  NUMBER(15,2),
  NUMPAGVEN   NUMBER(3),
  DIASVEN     NUMBER(5),
  MOP         VARCHAR2(2 BYTE),
  CVEPREVENC  VARCHAR2(2 BYTE),
  FCIERRECTA  DATE,
  MULPER      NUMBER(3)                         DEFAULT 1,
  CURP        VARCHAR2(18 BYTE),
  SDOCALCPOR  VARCHAR2(8 BYTE),
  CAPVENCIDO  NUMBER(15,6),
  ESQUEMA     VARCHAR2(1 BYTE)
)

CREATE TABLE ICARO.VW_PRC
(
  FCORTE      DATE,
  STATUS      VARCHAR2(5 BYTE),
  FUENTEBD    VARCHAR2(7 BYTE),
  CDGRG       VARCHAR2(3 BYTE),
  CDGCO       VARCHAR2(3 BYTE),
  CLNS        VARCHAR2(1 BYTE),
  CDGCLNS     VARCHAR2(6 BYTE),
  CICLO       VARCHAR2(2 BYTE),
  CDGCL       VARCHAR2(6 BYTE),
  INICICLO    DATE,
  FINCICLO    DATE,
  CANTENTRE   NUMBER(15,2),
  FREQPAGOS   VARCHAR2(1 BYTE),
  PLAZO       NUMBER(5),
  TIPOCONT    VARCHAR2(2 BYTE),
  TOTALPAGAR  NUMBER(15,4)
)


and the package script.
(the body script was described before)

Code: Select all
CREATE OR REPLACE PACKAGE BURO.BC_CREDITOS AS
/******************************************************************************
   NAME:       prueba
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        19/11/2012             1. Created this package.
******************************************************************************/
    itime   number;
    ftime   number;

PROCEDURE AGREGA_CC_CREDITOS(pfcorteANT IN DATE, pfechafin IN DATE);

END BC_CREDITOS;
/
sis01
 
Posts: 9
Joined: Thu 07 Mar 2013 16:43

Re: DotConnect got an excepction ... 01858

Postby Pinturiccio » Mon 25 Mar 2013 15:25

Thank you for the scripts you have sent. However we also need the script for the CIRCRED.SALDOS table. Please also send us your connection string (roughly, without credentials).
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Re: DotConnect got an excepction ... 01858

Postby sis01 » Tue 02 Apr 2013 22:38

Pinturiccio wrote:Thank you for the scripts you have sent. However we also need the script for the CIRCRED.SALDOS table. Please also send us your connection string (roughly, without credentials).



Hello.!

Here is the CIRCRED.SALDOS script table.

Code: Select all
CREATE TABLE CIRCRED.SALDOS
(
  FCORTE      DATE,
  FUENTEBD    VARCHAR2(7 BYTE),
  CLNS        VARCHAR2(1 BYTE),
  CDGNS       VARCHAR2(6 BYTE),
  CICLO       VARCHAR2(2 BYTE),
  CDGCL       VARCHAR2(6 BYTE),
  STATUSCC    VARCHAR2(5 BYTE),
  INICICLO    DATE,
  FINCICLO    DATE,
  PRCCANTENT  NUMBER(15,2),
  TIPOCUENTA  VARCHAR2(1 BYTE),
  TIPOCONTRA  VARCHAR2(2 BYTE),
  NUMPAGOS    NUMBER(5),
  FREQPAGOS   VARCHAR2(1 BYTE),
  PAGOPACCL   NUMBER(15,2),
  SALDOCL     NUMBER(15,2),
  SDOVENCL    NUMBER(15,2),
  NUMPAGVEN   NUMBER(5),
  PAGOACTUAL  VARCHAR2(2 BYTE),
  TOTPAGREP   NUMBER(15,2),
  SUMSALDO    NUMBER(15,2),
  SUMSDOVEN   NUMBER(15,2),
  NUMCRED     NUMBER(7),
  NUMDIRECC   NUMBER(7),
  NUMEMPLEO   NUMBER(7),
  NUMCTAS     NUMBER(7),
  FCIERRECTA  DATE
)


And this is the connection string:

Code: Select all
public static string BURO_cnstr = "Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = servidor2)(PORT = 1522))(CONNECT_DATA = (SERVICE_NAME = DATOS)))"
                                + ";User ID=XXX;Password=XXXXXXX";


Thanks in advance!
sis01
 
Posts: 9
Joined: Thu 07 Mar 2013 16:43

Re: DotConnect got an excepction ... 01858

Postby Pinturiccio » Wed 03 Apr 2013 14:34

We could not reproduce the issue. In our environment everything works fine.

Try specifying the required type for the parameter. Instead of this:
Code: Select all
cmd.Parameters.AddWithValue("pfcorteANT", pfcmesant);
cmd.Parameters.AddWithValue("pfechafin", pffin);

Use this:
Code: Select all
cmd.Parameters.Add("pfcorteANT", OracleDbType.Date).Value = pfcmesant;
cmd.Parameters.Add("pfechafin", OracleDbType.Date).Value = pffin;


If this doesn't help, please tell us the values you pass the Agrega_CC_Creditos function and how you get these values. Please also specify the exact version of dotConnect for Oracle 6.60.xxx
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle