ORA-01483 and ORA-03120 with newest version

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hansjoergp
Posts: 39
Joined: Wed 31 May 2017 14:33

ORA-01483 and ORA-03120 with newest version

Post by hansjoergp » 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.

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
   ) ;

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: ORA-01483 and ORA-03120 with newest version

Post by DmitryGm » Wed 24 Feb 2021 08:41

Thank you for your report and the test project.
The investigation of the issue is in progress.
We shall inform you as soon as we have any results.
Note that newest version is dotConnect for Oracle 9.14.1204

hansjoergp
Posts: 39
Joined: Wed 31 May 2017 14:33

Re: ORA-01483 and ORA-03120 with newest version

Post by hansjoergp » Fri 12 Mar 2021 08:00

Are there any news?

We can non use any version newer than 9.13.1127!

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: ORA-01483 and ORA-03120 with newest version

Post by DmitryGm » Mon 15 Mar 2021 08:46

We have reproduced the bug on the Oracle server with NLS_CHARACTERSET = WE8MSWIN1252.
The investigation of the issue is in progress.
We shall inform you as soon as we have any results.

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: ORA-01483 and ORA-03120 with newest version

Post by DmitryGm » Mon 15 Mar 2021 17:32

Thank you for your report.
We have fixed the issue.
The fix will be available in the next release.

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: ORA-01483 and ORA-03120 with newest version

Post by DmitryGm » Fri 02 Apr 2021 13:23

New version of dotConnect for Oracle (9.14.1228 01-Apr-21) is available for download:
https://www.devart.com/dotconnect/oracle/download.html

The ORA-01483 and ORA-03120 errors on a server with NLS_CHARACTERSET=WE8MSWIN1252 are fixed (Direct mode)

Post Reply