ORA-01483 after April fix with Oracle 19c

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
janopi
Posts: 3
Joined: Thu 20 May 2021 23:40

ORA-01483 after April fix with Oracle 19c

Post by janopi » Fri 21 May 2021 00:00

I am still getting error ORA-01483: invalid length for DATE or NUMBER bind variable when working with VarChar parameters, that are rather long. I am connecting to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 with direct mode. DevArt version 9.14.1234 (released on April 9th)

Exception occurrs under the realy specific, but deterministic conditions:
* string value of parameter is MORE then 2730 characters long. Ie length 2731 fails and 2730 does not. Content of the string does not matter, only length does.
* there must be another parameter (tested numeric type) in query. When you replace parameter with constant - exceptions does not occur.

DDL:

Code: Select all

CREATE TABLE DEVART_TEST_01483
(
ID NUMBER(12,0) NOT NULL,
TEXT VARCHAR(4000 CHAR) NULL
) ;
ALTER TABLE DEVART_TEST_01483 ADD CONSTRAINT DEVART_TEST_01483_PK PRIMARY KEY (ID) ;

insert into DEVART_TEST_01483(id, text) values (1, null);
C# code:

Code: Select all

String text = new String('0', 2731);			//with 2730 it works just fine

using (OracleConnection conn = new OracleConnection("User ID=user;SID=sid;Host=ip_adress;password=secret;Pooling=true;Min Pool Size=1;Max Pool Size=150;Direct=true;Unicode=true;"))
{
	conn.Open();

	OracleCommand cmd = conn.CreateCommand();
	cmd.CommandText = "UPDATE DEVART_TEST_01483 SET TEXT=:TEXT where id=:ID";
	var param = cmd.Parameters.Add("TEXT", text);
	param = cmd.Parameters.Add("ID", 1);

	var result = cmd.ExecuteNonQuery();    //ORA:01483
}


Workaround:
when I changed
param.OracleDbType = Devart.Data.Oracle.OracleDbType.Clob when the param.Size>2730 - it mirraculously worked

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

Re: ORA-01483 after April fix with Oracle 19c

Post by DmitryGm » Mon 24 May 2021 16:57

We do not see ORA-01483 error with your code example on Oracle 19c server. It may depend on some other settings. Please show us the full set of your Oracle server properties:

Code: Select all

SELECT * FROM props$

janopi
Posts: 3
Joined: Thu 20 May 2021 23:40

Re: ORA-01483 after April fix with Oracle 19c

Post by janopi » Fri 04 Jun 2021 19:58

Sorry for delay. I have to get results from customer.

NAME VALUE$ COMMENT$
------------------------------ ------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT
TDE_MASTER_KEY_ID
DBTIMEZONE +02:00 DB time zone
DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade
DST_PRIMARY_TT_VERSION 32 Version of primary timezone data file
DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET UTF8 Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS CHAR NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 19.0.0.0.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME CARLCZ.HOMECREDIT.NET Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress
WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress
NO_USERID_VERIFIER_SALT 4A6C0A509494FCF6A6B1027E495C207A
MAX_STRING_SIZE STANDARD MAX_STRING_SIZE parameter used for dictionary metadata
MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB
DICTIONARY_ENDIAN_TYPE LITTLE Endian type of the data dictionary

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

Re: ORA-01483 after April fix with Oracle 19c

Post by DmitryGm » Mon 07 Jun 2021 15:26

Thank you for your report.
The investigation is in progress.
We shall inform you as soon as we have any results.

janopi
Posts: 3
Joined: Thu 20 May 2021 23:40

Re: ORA-01483 after April fix with Oracle 19c

Post by janopi » Wed 07 Jul 2021 07:13

Hello,
did you have any luck with fixing or investigating this issue? One month passed since you last replied in this thread... Thanks

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

Re: ORA-01483 after April fix with Oracle 19c

Post by DmitryGm » Wed 07 Jul 2021 12:18

We reproduced the bug "ORA-01483: invalid length for DATE or NUMBER bind variable".
The necessary conditions for reproduction are:
  • NLS_CHARACTERSET=UTF8
  • Unicode=True
  • Direct mode
We are working on this issue now. It may take some time, but we will let you know as soon as we have a result.

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

Re: ORA-01483 after April fix with Oracle 19c

Post by DmitryGm » Thu 08 Jul 2021 14:28

The bug with ORA-01483 error in the Direct mode is fixed in the new release of dotConnect for Oracle 9.14.1298.
https://www.devart.com/dotconnect/oracle/download.html

Post Reply