ORA-01483 and ORA-03120 with newest version
Posted: Fri 19 Feb 2021 07:50
Hello
We have reproduced two errors with the newest version of dotConnect for Oracle.
It seems that the error happens only with newer oracle Version (18 and 19). In Oracle Database 11g Release 11.2.0.3.0 it works without problems. Maybe it has olso to do something the NLS_CHARACTERSET = WE8MSWIN1252.
The last version which seems to work is 9.13.1127.
We have reproduced two errors with the newest version of dotConnect for Oracle.
It seems that the error happens only with newer oracle Version (18 and 19). In Oracle Database 11g Release 11.2.0.3.0 it works without problems. Maybe it has olso to do something the NLS_CHARACTERSET = WE8MSWIN1252.
The last version which seems to work is 9.13.1127.
Code: Select all
class Program
{
static void Main(string[] args)
{
using (var connection = new OracleConnection(GetConnectionString(server, sid, 1521, user, password)))
{
connection.Open();
//NLS_CHARACTERSET = WE8MSWIN1252
SimulateORA01483(connection);
SimulateORA03120(connection);
}
}
public static void SimulateORA01483(OracleConnection connection)
{
//Devart.Data.Oracle.OracleException (0x80004005): ORA-01483: invalid length for DATE or NUMBER bind variable
var cmd = connection.CreateCommand("UPDATE TESTDEVART2 SET TXDOKBETREFF=:p_1,UPDATECNT=:p_2 where TXDOKID = '00100000003AJJ'", CommandType.Text);
AddParam(cmd, "p_1", OracleDbType.VarChar, ParameterDirection.Input, 4000, 0, "testtesttesttesttesttes"); //error happens only with >= 23 characters
AddParam(cmd, "p_2", OracleDbType.Number, ParameterDirection.Input, 10, 0, 1);
try
{
var res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
connection.Rollback();
}
private static void AddParam(OracleCommand cmd, string name, OracleDbType dbType, ParameterDirection parameterDirection, int size, byte scale, object value)
{
var param = cmd.Parameters.Add(name, dbType, parameterDirection);
param.Size = size;
param.Scale = scale;
param.Value = value;
}
public static void SimulateORA03120(OracleConnection connection)
{
//Devart.Data.Oracle.OracleException (0x80004005): ORA-01483: invalid length for DATE or NUMBER bind variable
var cmd = connection.CreateCommand("INSERT INTO TESTDEVART2 (TXDOKID,TXDOKKODE,FIRMAID,TXDOKBEZEICHNUNG1,TXDOKBEZEICHNUNG2,TXDOKBEZEICHNUNG3,TXDOKTYP,TXDOKTEMPORAER,TXDOKDATUM,TXDOKHERKUNFT,TXDOKEMAIL,TXDOKBETREFF,TXDOKTEXTHTML,BENID,TXDPROPWERT3,UPDATECNT) VALUES (:p_1,:p_2,:p_3,:p_4,:p_5,:p_6,:p_7,:p_8,:p_10,:p_11,:p_15,:p_17,:p_19,:p_20,:p_23,:p_40 ) ", CommandType.Text);
AddParam(cmd, "p_1", OracleDbType.Char, ParameterDirection.Input, 14, 0, "01234567891234");
AddParam(cmd, "p_2", OracleDbType.Char, ParameterDirection.Input, 100, 0, "DOC_156883");
AddParam(cmd, "p_3", OracleDbType.Char, ParameterDirection.Input, 3, 0, "001");
AddParam(cmd, "p_4", OracleDbType.VarChar, ParameterDirection.Input, 200, 0, "DOC_156883");
AddParam(cmd, "p_5", OracleDbType.VarChar, ParameterDirection.Input, 200, 0, "DOC_156883");
AddParam(cmd, "p_6", OracleDbType.VarChar, ParameterDirection.Input, 200, 0, "DOC_156883");
AddParam(cmd, "p_7", OracleDbType.Number, ParameterDirection.Input, 2, 0, 21);
AddParam(cmd, "p_8", OracleDbType.Number, ParameterDirection.Input, 1, 0, 1);
AddParam(cmd, "p_10", OracleDbType.Date, ParameterDirection.Input, 1, 0, DateTime.Now);
AddParam(cmd, "p_11", OracleDbType.Number, ParameterDirection.Input, 2, 0, 21);
AddParam(cmd, "p_15", OracleDbType.VarChar, ParameterDirection.Input, 4000, 0, "[email protected];[email protected]");
AddParam(cmd, "p_17", OracleDbType.VarChar, ParameterDirection.Input, 4000, 0, "Lieferschein/Rechnung UT_R - 2016 - 9");
AddParam(cmd, "p_19", OracleDbType.Clob, ParameterDirection.Input, 10, 0, null);
AddParam(cmd, "p_20", OracleDbType.Char, ParameterDirection.Input, 14, 0, "00100000000421");
AddParam(cmd, "p_23", OracleDbType.VarChar, ParameterDirection.Input, 250, 0, "C00100000000K1Q");
AddParam(cmd, "p_40", OracleDbType.Number, ParameterDirection.Input, 10, 0, 0);
try
{
var res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
connection.Rollback();
}
public static string GetConnectionString(string server, string sid, int port, string userid, string password)
{
OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Server = server;
oraCSB.Sid = sid;
oraCSB.Port = port;
oraCSB.Direct = true;
oraCSB.UserId = userid;
oraCSB.Password = password;
oraCSB.LicenseKey = licenceKey;
return oraCSB.ConnectionString;
}
}
Code: Select all
CREATE TABLE TESTDEVART2
( TXDOKID CHAR(14 BYTE) NOT NULL ENABLE,
TXDOKKODE CHAR(100 BYTE) NOT NULL ENABLE,
FIRMAID CHAR(3 BYTE) NOT NULL ENABLE,
TXDOKBEZEICHNUNG1 VARCHAR2(200 BYTE),
TXDOKBEZEICHNUNG2 VARCHAR2(200 BYTE),
TXDOKBEZEICHNUNG3 VARCHAR2(200 BYTE),
TXDOKTYP NUMBER(2,0) DEFAULT 1 NOT NULL ENABLE,
CREATEDATE DATE,
UPDATEDATE DATE,
MODIFYDATE DATE,
UPDATECNT NUMBER(10,0),
CREATEUSER CHAR(14 BYTE),
UPDATEUSER CHAR(14 BYTE),
TXDOKTEMPORAER NUMBER(1,0) DEFAULT 1,
TXDOKEMAIL VARCHAR2(4000 BYTE),
TXDOKBETREFF VARCHAR2(4000 BYTE),
TXDOKNOTIZ VARCHAR2(4000 BYTE),
BENID CHAR(14 BYTE),
TXDOKDATUM DATE,
TXDOKHERKUNFT NUMBER(2,0),
TXDOKGEDRUCKT NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
TXDOKEMAILSENDER VARCHAR2(100 BYTE),
TXDPROPWERT1 VARCHAR2(250 BYTE),
TXDPROPWERT2 VARCHAR2(250 BYTE),
TXDPROPWERT3 VARCHAR2(250 BYTE),
TXDPROPWERT4 VARCHAR2(250 BYTE),
TXDPROPWERT5 VARCHAR2(250 BYTE),
TXDPROPWERT6 VARCHAR2(250 BYTE),
TXDPROPWERT7 VARCHAR2(250 BYTE),
TXDPROPWERT8 VARCHAR2(250 BYTE),
TXDPROPWERT9 VARCHAR2(250 BYTE),
TXDPROPWERT10 VARCHAR2(250 BYTE),
TXDOKTEXTHTML CLOB,
CPKODE CHAR(20 BYTE),
TXDEFORDNUMMER NUMBER(3,0) DEFAULT 0 NOT NULL ENABLE,
TXDOKTEXTVONDATEI NUMBER(2,0),
TXDOKEXPORTIERT DATE,
TXDOKARCHIVIERT DATE,
TXDOKARCHIVIERTTOKEN VARCHAR2(50 BYTE),
TXDOKPROTOKOLLVERGEBEN NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
TXDOKPROTOKOLLNUMMER NUMBER(12,0) DEFAULT 0 NOT NULL ENABLE,
TXDOKPECEMAIL NUMBER(1,0),
TXDOKTEXTVONDATEINAMEMACRO CLOB,
TXDOKTEXTVONDATEINAMEEDITABLE NUMBER(2,0) DEFAULT 0 NOT NULL ENABLE
) ;