Error saving timestamp value in oracle.

[José Luiz]

I have a complex application that needs to insert records with TIMESTAMP(6) columns in Oracle 19c database, using OTL 4. This is an old C++ cross compiled application, on Windows and Linux.
The query being executed in OTL is:

Insert into XXXXXXXXX values(:UPDATEFLAG_<char<span>[3]>,to_number(:ORGXXX<double>),:ADDRTYPECODE<double>,:ISPUBLIC<double>,:IPODATE<timestamp>,:ISO2CNTRYCODE<char<span>[5]>,:LEGALNAME<varchar_long>,:ADDRLINE1<varchar_long>,:ADDRLINE2<varchar_long>,:CITY<varchar_long>,:STATEPROV<varchar_long>,:POSTALCODE<varchar_long>,to_number(:PRITRBCINDPERMID<double>),to_number(:CNTRYGEOID<double>)))</double></double></varchar_long></varchar_long></varchar_long></varchar_long></varchar_long></varchar_long></char<span></timestamp></double></double></double></char<span>

If the input data have the column IPODATE = NULL, it works properly, so the problem here is the IPODATE column, declared in the database as TIMESTAMP(6).
When running in Windows, everything works fine, but under Linux, I have the following error:

{9}<2>ORA-01483: invalid length for DATE or NUMBER bind variable

I am parsing the date string, and converting to a otl_datetime value, that is piped to the stream, as shown in the examples.
I am already out of ideas for solving this problem. Does anybody have any?

[Sergei Kuchin]

Jose,

I'm not sure where / what the problem is. You can always use a char[XXX] variable bind variable + a conversion function call to convert strings into timestamps for passing in timestamp values if nothing else works. It may take weeks to track down the actual issue, and it may take hours to implement a char[XXX] workaround.

Sergei

[José Luiz]

I already tried, and had another error:
{10}<2>ORA-01830: date format picture ends before converting entire input string

Insert into XXXXX values(:UPDATEFLAG_<char<span>[3]>,to_number(:ORGPXX<double>),:ADDRTYPECODE<double>,:ISPUBLIC<double>,to_timestamp(:IPODATE<char<span>[27]>,'yyyy-mm-dd HH24:MI:SS.FF6'),:ISO2CNTRYCODE<char<span>[5]>,:LEGALNAME<varchar_long>,:ADDRLINE1<varchar_long>,:ADDRLINE2<varchar_long>,:CITY<varchar_long>,:STATEPROV<varchar_long>,:POSTALCODE<varchar_long>,to_number(:PRITRBCINDPERMID<double>),to_number(:CNTRYGEOID<double>)))</double></double></varchar_long></varchar_long></varchar_long></varchar_long></varchar_long></varchar_long></char<span></char<span></double></double></double></char<span>

I checked the input data, and apparently is correct. I created an statement to execute this convertion interactively, and it worked fine with the same values processed by the application.

I am really out of ideas here.

[Sergei Kuchin]

Jose,

Can you put together a self-contained code example that reproduces the problem and email it to me? I need a code example that creates a similar table and tries to insert a row into it.

BTW, did you enable #define OTL_ORA_TIMESTAMP: https://otl.sourceforge.net/otl3_compile.htm#OTL_ORA_TIMESTAMP

Sergei

[Sergei Kuchin]

I put together the following quick test code to test timestamp:

include <iostream></iostream>

using namespace std;

define OTL_ORA18C

include "otlv4.h"

int main(int argc,char* argv[])
{
try{
otl_connect db;
db.rlogon("system/oracle@myora_tns");
db.direct_exec("drop table test_tab",0);
db<<"create table test_tab(update_flag varchar2(1), ipodate timestamp(6), f1 CLOB, f2 CLOB) ";
otl_stream o;
o.open(1,
"INSERT INTO test_tab VALUES( "
" :UPDATEFLAG_<char<span>[3]>, "
" to_timestamp(:IPODATE<char<span>[27]>,'yyyy-mm-dd HH24:MI:SS.FF6'), "
" :f1<varchar_long>, :f2<varchar_long>) ",
db);
o<<"U"<<"2022-11-16 13:00:36.123456";
otl_long_string f1("f1_value",8,8);
otl_long_string f2("f2_value",8,8);
o<<f1<<f2;
o.close();
db.commit();
db.logoff();
}catch(otl_exception& e){
cout<<"DB Error Msg="<<e.msg<<endl;
cout<<"DB Error Code="<<e.code<<endl;
cout<<"DB SQL Stm="<<e.stm_text<<endl;
}
return 0;
}</varchar_long></varchar_long></char<span></char<span>

The result in the table looks correct to me:

U 16-NOV-22 01.00.36.123456000 PM f1_value f2_value

At this point I'm not sure where the problem is.

Sergei

[José Luiz]

Thanks a lot, Sergei.
I'll compare with my code, because is a system a lot more complex, and see if I find anything. If not, I'll try to extract my code and post it here.

[Sergei Kuchin]

Jose,

You can email me directly. My email address can be found on OTL Web pages.

Cheers,
Sergei