ORA-01483: invalid length for DATE or NUMBER bind variable with CLOB column

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
upscene
Posts: 306
Joined: Thu 19 Oct 2006 08:13

ORA-01483: invalid length for DATE or NUMBER bind variable with CLOB column

Post by upscene » Mon 28 May 2018 08:50

Hi,

I'm transferring data from Firebird to Oracle using a parameterized INSERT statement in TOraQuery.

Options.TemporaryLobUpdate = True, using a direct connection.

Hundreds of rows are transferred fine, then all of the sudden, always on the same row, I get an
ORA-01483: invalid length for DATE or NUMBER bind variable
After that, I get 'no statement parsed'.

I've ensured the data is always transferred in the same order, to see where it fails. I can't figure out why it fails > before the failing row, larger text blob values are transferred just fine. However, if I modify the contents of the text blob to hold nothing but 'test', it works, but it fails on a later row.

The code used to set the string value is:

Code: Select all

FCommand.Params[Index].ParamType := ptInput;
FCommand.Params[Index].AsOraClob.AsWideString := Value;
I also tried

Code: Select all

Lob := FCommand.Params[Index].AsOraClob;
Lob.OCISvcCtx := FCommand.Session.OCISvcCtx; 
Lob.CreateTemporary(ltClob); 
Lob.AsWideString := Value; 
Lob.WriteLob; 
Lob.FreeTemporary;
I'm puzzled, what could cause this?

With regards,

Martijn Tonies


upscene
Posts: 306
Joined: Thu 19 Oct 2006 08:13

Re: ORA-01483: invalid length for DATE or NUMBER bind variable with CLOB column

Post by upscene » Tue 05 Jun 2018 12:39

I'm puzzled, what could cause this?
Anyone at DevArt got a clue?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ORA-01483: invalid length for DATE or NUMBER bind variable with CLOB column

Post by MaximG » Tue 05 Jun 2018 15:15

We tested ODAC work according to your description and found no problems. For testing we used the latest version of ODAC 10.1.5 with using Direct Mode. For further investigation, please compose and send us a small project execution of which causes the issue. In addition, to investigate the issue, we need DDL script to create DB objects used in this project. For your convenience, please use the e-support form ( https://www.devart.com/company/contactform.html ).

upscene
Posts: 306
Joined: Thu 19 Oct 2006 08:13

Re: ORA-01483: invalid length for DATE or NUMBER bind variable with CLOB column

Post by upscene » Tue 05 Jun 2018 15:24

Hello Maxim,

I'm using ODAC 10.1.5, but this problem is older than that version, it has existed for quite a while.
please compose and send us a small project execution of which causes the issue
Well, if I figured out what triggered it, I could, but that's simply not the case. I know it happens, during repeated use of an INSERT statement with parameter binding. I can't predict when it happens, but if it happens, it seems to happen at the same set of values every time I repeat the process.

Do you have any idea what could cause this?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ORA-01483: invalid length for DATE or NUMBER bind variable with CLOB column

Post by MaximG » Wed 06 Jun 2018 10:50

Thank you for the explanations. We understand that the probable cause of the described error is a specific value or data length passed to the parameter of the ftOraCLOB type when inserting data. After all, according to your description, the use of the same code allows you to correctly insert data using one parameter values ​​and get an error when using the others. However, neither our internal auto tests nor test projects built by us according to your description have revealed an issue in our environment. We hope that you will be able to determine, inserting of which values causes ​the ORA-01483 error, and provide us with the obtained information for investigation.

Post Reply