ORA-01483: invalid length for DATE or NUMBER

bmarotta · Post by **bmarotta** » Thu 27 Jan 2011 18:05

While changing from OCI to DirectMode, we get the error "ORA-01483: invalid length for DATE or NUMBER" in one of our updates:

UPDATE PUMA_WORKFLOW_EXECUTION SET STATUS_ID = :p1, PROCESSING_SERVER = :p2 WHERE ID = :key1 AND STATUS_ID = :chk1 AND ((:nullchk2 = 1 AND PROCESSING_SERVER IS NULL) OR (PROCESSING_SERVER = :chk2))
-- p1: Input Number (Size = 0; DbType = Decimal) [2]
-- p2: Input NVarChar (Size = 36; DbType = String) [1c593409-991c-4fe5-b787-6bb10b6df9ae]
-- key1: Input Number (Size = 0; DbType = Decimal) [122336]
-- chk1: Input Number (Size = 0; DbType = Decimal) [1]
-- nullchk2: Input Integer (Size = 0; DbType = Int32) [1]
-- chk2: Input NVarChar (Size = 0; DbType = String) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: am Build: 1.0.43.0

This is the table creation SQL:

-- Create table
create table PUMA_WORKFLOW_EXECUTION
(
ID NUMBER(9) not null,
WORKFLOW_INSTANCE_ID NUMBER(9),
STATE_SEQUENCE NUMBER(9),
TASK_SEQUENCE NUMBER(9),
ACTION_SEQUENCE NUMBER(9),
STATUS_ID NUMBER(9) not null,
FINISHED NUMBER(1) default 0 not null,
COMMENTS NVARCHAR2(4000),
PROCESSING_SERVER NVARCHAR2(50),
MODIFICATION_DATE TIMESTAMP(6),
WORKFLOW_ID NUMBER(9) not null,
PRODUCT_ID NUMBER(9),
STATEFUL_OBJECT_ID NUMBER(9),
CHILD_PRODUCT_ID NUMBER(9),
PREDECESSOR_ID NUMBER(9),
PARENT_ID NUMBER(9),
RETRY_COUNT NUMBER(9) default 0 not null,
SOPHIS_USER_ID NUMBER(9)
);

Regards,

StanislavK · Post by **StanislavK** » Fri 28 Jan 2011 18:18

Could you please specify the following:
- the version of Oracle server you are connecting to;
- the NLS settings on the server;
- the original content of the row that should be updated in the sample;
- the regional settings on the machine where the error occurs;
- the column(s) that the table's primary key consists of.

We couldn't reproduce the problem with either 5.70.190 or the latest 6.10.96 versions of dotConnect for Oracle: the row was updated successfully.

bmarotta · Post by **bmarotta** » Wed 02 Feb 2011 20:49

Server Components
Component
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

NLS Database Parameters
Parameter Value
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_COMP BINARY
NLS_CURRENCY ?
NLS_DATE_FORMAT DD-MON-YYYY
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY ?
NLS_ISO_CURRENCY GERMANY
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET UTF8
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS ,.
NLS_RDBMS_VERSION 10.2.0.4.0
NLS_SORT BINARY
NLS_TERRITORY GERMANY
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

Original content
Id = 123456 (no matter what PK)
Status_Id = 1
Processing_Server = null

Regional Settings
English (United States)

Location
Germany

PK = Id

StanislavK · Post by **StanislavK** » Fri 04 Feb 2011 12:50

Am I correct supposing that the modification_date column value is auto-generated when updating rows? If yes, could you please specify how exactly this is done. For example, is it a trigger (in this case, please specify its definition) or, maybe, is the default value set to 'sysdate'?

Generally, such error may occur if session and server use different date formats. Could you please run the following code and specify its output to check this possibility:

Code: Select all

OracleGlobalization glob = OracleGlobalization.GetApplicationInfo();
Type type = glob.GetType();
Console.WriteLine("Application info:");
foreach (PropertyInfo propertyInfo in type.GetProperties())
  Console.WriteLine(propertyInfo.Name + " | " + propertyInfo.GetValue(glob, null));

context.Connection.Open();
glob = ((OracleConnection)context.Connection).GetSessionInfo();
type = glob.GetType();
Console.WriteLine("\nSession info:");
foreach (PropertyInfo propertyInfo in type.GetProperties())
  Console.WriteLine(propertyInfo.Name + " | " + propertyInfo.GetValue(glob, null));

(here 'context' is a DataContext object connecting to your database).

bmarotta · Post by **bmarotta** » Wed 09 Feb 2011 10:55

Code: Select all

SELECT t1.PRODUCT_ID, t1.WKN, t1.ISIN, t1.PRODUCTNAME, t1.PRODUCT_TYPE, t1.ISSUANCE_TYPE, t1.CURRENCY, t1.UNDERLYING_ID, t1.COUNTRY_CODE, t1.TradingCode AS "TradingCode"
FROM VW_PUMA_PRODUCT_BASICS t1
INNER JOIN (PUMA_PRODUCT t2
    INNER JOIN (
        SELECT DISTINCT t4.PRODUCT_ID AS "ProductId"
        FROM PUMA_PRODUCT_DATA t4
        INNER JOIN PUMA_PRODUCT_STATUS t5 ON t4.PRODUCT_ID = t5.PRODUCT_ID
        WHERE (t4.VALUE_DATE1 > :p0) AND (t4.VALUE_ID = :p1) AND (t5.STATUS_GROUP = :p2) AND (t5.STATUS_VALUE = :p3)
        ) t3 ON t2.ID = t3."ProductId") ON t1.PRODUCT_ID = t2.ID
ORDER BY t1.PRODUCTNAME
-- p0: Input Date (Size = 0; DbType = DateTime) [2/9/2011 12:00:00 AM]
-- p1: Input Number (Size = 0; DbType = Decimal) [2]
-- p2: Input NVarChar (Size = 22; DbType = String) [LaP1.A01.1 Draft Check]
-- p3: Input NVarChar (Size = 18; DbType = String) [Initialize Product]
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: am Build: 1.0.43.0

The below one DOES:

Code: Select all

SELECT t1.PRODUCT_ID, t1.WKN, t1.ISIN, t1.PRODUCTNAME, t1.PRODUCT_TYPE, t1.ISSUANCE_TYPE, t1.CURRENCY, t1.UNDERLYING_ID, t1.COUNTRY_CODE, t1.TradingCode AS "TradingCode"
FROM VW_PUMA_PRODUCT_BASICS t1
INNER JOIN (PUMA_PRODUCT t2
    INNER JOIN (
        SELECT DISTINCT t4.PRODUCT_ID AS "ProductId"
        FROM PUMA_PRODUCT_STATUS t4
        INNER JOIN PUMA_PRODUCT_DATA t5 ON t4.PRODUCT_ID = t5.PRODUCT_ID
        WHERE (t4.STATUS_GROUP = :p0) AND (t4.STATUS_VALUE = :p1) AND (t5.VALUE_DATE1 > :p2) AND (t5.VALUE_ID = :p3)
        ) t3 ON t2.ID = t3."ProductId") ON t1.PRODUCT_ID = t2.ID
ORDER BY t1.PRODUCTNAME
-- p0: Input NVarChar (Size = 22; DbType = String) [LaP1.A01.1 Draft Check]
-- p1: Input NVarChar (Size = 18; DbType = String) [Initialize Product]
-- p2: Input Date (Size = 0; DbType = DateTime) [2/9/2011 12:00:00 AM]
-- p3: Input Number (Size = 0; DbType = Decimal) [2]
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: am Build: 1.0.43.0

The error only happens on Direct Mode. Not on OCI mode.

It doesn't really looks like to be bound with the date format itself but with Unicode mode. We are currently using version 5.70.190, but the error occurs also on vrs 6.10.96

StanislavK · Post by **StanislavK** » Thu 10 Feb 2011 13:46

Please specify the following:

1) Whether any triggers, check constraints or default values not specified above are enabled for the PUMA_WORKFLOW_EXECUTION table.

The error you are receiving specifies that a bind variable is too long for the data type of the target column; we couldn't receive this error with the parameters of the first UPDATE command.

2) What are the StatusGroup and StatusValue properties; are these fields of some table related to PUMA_WORKFLOW_EXECUTION? If yes, please specify how this table can be affected by the update command for PUMA_WORKFLOW_EXECUTION.

3) The definitions of the objects used in the latter SELECT statements, and the LINQ queries for which these commands were generated.

4) The exception you are getting with the latter query; is it ORA-01483?

bmarotta · Post by **bmarotta** » Thu 10 Feb 2011 22:00

Hi,

I think you are missing the point. The problem is when there is Unicode a parameter followed by another parameter. Anyway, here follows the answers:

1) Yes, there are triggers on it:

Code: Select all

create or replace trigger "TR_AIU_PUMA_WORKFLOW_EXECUTION"
  after insert or update on puma_workflow_execution
  for each row
begin
  if (:new.status_id  :old.status_id or :new.processing_server  :old.processing_server) then
    insert into puma_workflow_execution_log
      (workflow_execution_id, log_datetime, old_status, new_status, processing_server, comments)
    values
      (:New.Id, systimestamp, :OLD.Status_Id, :NEW.Status_Id, :NEW.Processing_Server, :NEW.Comments);
  end if;
end TR_AIU_PUMA_WORKFLOW_EXECUTION;

create or replace trigger "TR_BIU_PUMA_WORKFLOW_EXECUTION" 
  before insert or update on puma_workflow_execution
  for each row
begin
  if inserting and :New.ID is null then
      select seq_puma_workflow_execution.nextval into :New.ID from dual;
  end if;

  if :NEW.Modification_date is null then
    :NEW.Modification_date := SYSTIMESTAMP AT TIME ZONE 'UTC';
  end if;

  if :NEW.Workflow_Id is null or :NEW.Workflow_Id = 0 then
    select wi.definition_id, product_id, stateful_object_id
      into :NEW.Workflow_Id, :NEW.Product_Id, :NEW.Stateful_Object_Id
      from puma_workflow_instance wi
     where wi.id = :NEW.workflow_instance_id;
  end if;
end TR_BIU_PUMA_WORKFLOW_EXECUTION;

-- Log Table
create table PUMA_WORKFLOW_EXECUTION_LOG
(
  WORKFLOW_EXECUTION_ID NUMBER(9) not null,
  LOG_DATETIME          TIMESTAMP(6) not null,
  OLD_STATUS            NUMBER(9),
  NEW_STATUS            NUMBER(9) not null,
  PROCESSING_SERVER     NVARCHAR2(50),
  COMMENTS              NVARCHAR2(1024)
);
alter table PUMA_WORKFLOW_EXECUTION_LOG
  add constraint FK_PUMA_WORKFLOW_EXECUTION_LOG foreign key (WORKFLOW_EXECUTION_ID)
  references PUMA_WORKFLOW_EXECUTION (ID) on delete cascade;

2) NO. There is no relationship at all. They are completely independent entities. Both tables uses NVARCHAR and here lies the problem!

3) The definitions follows below. The Linq is hard to reproduce because it is dinamically built in many different calls.

Code: Select all

create table PUMA_PRODUCT_STATUS
(
  PRODUCT_ID   NUMBER not null,
  STATUS_GROUP NVARCHAR2(120) not null,
  STATUS_VALUE NVARCHAR2(120) not null,
  ID           NUMBER not null
);
alter table PUMA_PRODUCT_STATUS
  add constraint PUMA_PROD_ST_ID primary key (ID);
alter table PUMA_PRODUCT_STATUS
  add constraint PUMA_PROD_ST_UK unique (PRODUCT_ID, STATUS_GROUP);
alter table PUMA_PRODUCT_STATUS
  add constraint PUMA_PS_P_FK foreign key (PRODUCT_ID)
  references PUMA_PRODUCT (ID) on delete cascade;
create index IX_PUMA_PRODUCT_STATUS_GRP on PUMA_PRODUCT_STATUS (STATUS_GROUP)
create index IX_PUMA_PRODUCT_STATUS_PROD on PUMA_PRODUCT_STATUS (PRODUCT_ID);

-- Create table
create table PUMA_PRODUCT_DATA
(
  ID            NUMBER not null,
  PRODUCT_ID    NUMBER not null,
  VALUE_ID      NUMBER not null,
  VALUE_NUMBER1 NUMBER,
  VALUE_NUMBER2 NUMBER,
  VALUE_DATE1   DATE,
  VALUE_DATE2   DATE,
  VALUE_TEXT    NVARCHAR2(500),
  MASTER_ID     NUMBER
);

alter table PUMA_PRODUCT_DATA
  add constraint PUMA_PRODUCT_DATA_PK primary key (ID);
alter table PUMA_PRODUCT_DATA
  add constraint FK_PUMA_PRODUCT_DATA_MASTER foreign key (MASTER_ID)
  references PUMA_PRODUCT_DATA (ID) on delete cascade;
alter table PUMA_PRODUCT_DATA
  add constraint PUMA_PRODUCT_META_P_DATA_FK foreign key (VALUE_ID)
  references PUMA_PRODUCT_META_DATA (ID) on delete cascade;
alter table PUMA_PRODUCT_DATA
  add constraint PUMA_PRODUCT_PRODUCT_DATA_FK foreign key (PRODUCT_ID)
  references PUMA_PRODUCT (ID) on delete cascade;
create index IX_PUMA_PRODUCT_DATA_MASTER on PUMA_PRODUCT_DATA (MASTER_ID);
create index IX_PUMA_PRODUCT_DATA_PROD on PUMA_PRODUCT_DATA (PRODUCT_ID);
create index IX_PUMA_PRODUCT_DATA_TXT on PUMA_PRODUCT_DATA (VALUE_TEXT);
create index IX_PUMA_PRODUCT_DATA_VALUE on PUMA_PRODUCT_DATA (VALUE_ID);

4) Yes, the error is ORA-01483

StanislavK · Post by **StanislavK** » Mon 14 Feb 2011 17:12

We couldn't reproduce the problem when updating the PUMA_WORKFLOW_EXECUTION table.

As for the query to the VW_PUMA_PRODUCT_BASICS, PUMA_PRODUCT and PUMA_PRODUCT_STATUS tables join, please specify the definitions of these tables. If possible, please send us a file with the exact script creating all database objects you've mentioned.

It would be appreciated if you could open a test server - i.e., the one with no real data but the same (relevant) structure and settings - to us so that we are able to reliably reproduce and analyze the issue.

bmarotta · Post by **bmarotta** » Tue 15 Feb 2011 12:25

Here it follows. Just create the table and import it to the LinqConnect model. Run the application. The last query will throw the exception

Code: Select all

using System;
using System.Linq;
using Devart.Data.Oracle;

/* DB Scripts
 
create table PUMA_TEST_UNICODE
(
 id number(9) Primary Key,
 name varchar2(50) not null,
 uname nvarchar2(50) not null,
 dt date not null,
 i number(9) default 0 not null 
);

insert into PUMA_TEST_UNICODE VALUES (1, 'Name1', 'Name1', sysdate, 1);
insert into PUMA_TEST_UNICODE VALUES (2, 'Name2', 'Name2', sysdate, 1);
insert into PUMA_TEST_UNICODE VALUES (3, 'Name3', 'Name3', sysdate, 1);
insert into PUMA_TEST_UNICODE VALUES (4, 'Name4', 'Name4', sysdate, 1);
insert into PUMA_TEST_UNICODE VALUES (5, 'Name5', 'Name5', sysdate, 1);
commit;

*/

namespace TestUnicode
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = ""; // Your connection string here

            var sb = new OracleConnectionStringBuilder(connString)
                         {
                             Password = "" // Set your password here
                         };
            connString = sb.ToString();

            // The error occurs with and without the Unicode=True
            using (var ctx = new DataContext1(connString /*+ ";Unicode=True"*/))
            {
                ctx.Log = Console.Out;
                var cnt =
                    ctx.PumaTestUnicodes.Where(n => n.Name == "Name1" && n.I == 1).Count();
                if (cnt != 1)
                    throw new Exception("Error 1");

                cnt =
                    ctx.PumaTestUnicodes.Where(n => n.I == 1 && n.Uname == "Name1").Count();
                if (cnt != 1)
                    throw new Exception("Error 2");

                cnt =
                    ctx.PumaTestUnicodes.Where(n => n.Uname == "Name1" && n.I == 1).Count();
                if (cnt != 1)
                    throw new Exception("Error 3");
            }
        }
    }
}

StanislavK · Post by **StanislavK** » Wed 16 Feb 2011 14:52

Thank you for your assistance, we have reproduced both ORA-1400 and ORA-1483 errors. We will inform you as soon as these problems are fixed.

bmarotta · Post by **bmarotta** » Fri 18 Feb 2011 07:53

Finally you believed in me!

You owe me a beer

bmarotta · Post by **bmarotta** » Mon 28 Feb 2011 16:45

Was this error fixed on the last build?

StanislavK · Post by **StanislavK** » Tue 01 Mar 2011 15:51

We did not fixed this problem yet. We are working on it, and will post here as soon as it is fixed.

StanislavK · Post by **StanislavK** » Thu 03 Mar 2011 15:40

We have fixed the Ora-1400 and Ora-1483 problems. The fix will be available in the nearest build, which we plan to release in about a week.

StanislavK · Post by **StanislavK** » Fri 11 Mar 2011 17:54

We have released the new 6.10.121 build of dotConnect for Oracle where the ORA-1400 and ORA-1483 issues are fixed. The new build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in dotConnect for Oracle 6.10.121, please refer to
http://www.devart.com/forums/viewtopic.php?t=20472