ORA-01722 when Unicode=true in connectionstring

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
vmakkenz
Posts: 19
Joined: Tue 26 Aug 2008 13:30

ORA-01722 when Unicode=true in connectionstring

Post by vmakkenz » Wed 01 Oct 2014 11:05

Hi,

We get an ORA-01722 invalid number exception when the connectionstring contains Unicode=true, without the Unicode part everything works as expected.

We are using Devart.8.3.146

-- Update statement
UPDATE WOZ_OBJECT
SET GECORRIGEERD_DOOR = :GECORRIGEERD_DOOR
WHERE WOZ_OBJECT_NUMMER = :WOZ_OBJECT_NUMMER
AND GECORRIGEERD_DOOR = 0
AND BEGIN_GELDIGHEID_GEGEVENS >= :BEGIN_GELDIGHEID_GEGEVENS
AND NVL(EINDE_GELDIGHEID_GEGEVENS, 30001231235959999) <= :EINDE_GELDIGHEID_GEGEVENS

dbMonitor
Report the parameter datatypes as
:GECORRIGEERD_DOOR Long
:WOZ_OBJECT_NUMMER Number
:BEGIN_GELDIGHEID_GEGEVENS Long
:EINDE_GELDIGHEID_GEGEVENS Long

-- Relevant parts of the table
CREATE TABLE WOZ_OBJECT"
( "ID" NUMBER(38,0) NOT NULL ENABLE,
"WOZ_OBJECT_NUMMER" NUMBER(12,0) NOT NULL ENABLE,
"BEGIN_GELDIGHEID_GEGEVENS" NUMBER(17,0) NOT NULL ENABLE,
"EINDE_GELDIGHEID_GEGEVENS" NUMBER(17,0),
"GECORRIGEERD_DOOR" NUMBER(38,0) NOT NULL ENABLE)

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: ORA-01722 when Unicode=true in connectionstring

Post by Pinturiccio » Fri 03 Oct 2014 10:13

We could not reproduce the issue. Please create and send us a small test project with the corresponding DDL/DML scripts for reproducing the issue.

vmakkenz
Posts: 19
Joined: Tue 26 Aug 2008 13:30

Re: ORA-01722 when Unicode=true in connectionstring

Post by vmakkenz » Fri 03 Oct 2014 17:11

I changed the parameter types from Long to Number and now it works on both unicode and non unicode databases. Still weird because the parameters are really longs.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: ORA-01722 when Unicode=true in connectionstring

Post by Pinturiccio » Wed 08 Oct 2014 13:57

If the OracleDbType property is not set for a parameter, it is determined automatically based upon the Value property. Probably you have assigned a value, corresponding to the Long data type, and that's why you get such value of the OracleDbType property.

Post Reply