topic Insert NULL date into ORACLE using ADBC in Application Development and Automation Discussions

Insert NULL date into ORACLE using ADBC

Former Member — Thu, 24 Nov 2011 13:10:17 GMT

Hello everyone,

I'm using ADBC (like EXEC SQL OO) to insert values into an external database.

I need to insert null value into the fields type date.

I've already tried all the possibilities I can imagine...

TO_DATE('null'), only 'null', 'NULL' yyyymmdd, ddmmyyyy, 00.00.0000, 00000000, 00-00-0000, 00/00/0000, 00.00.0000 00:00:00, DECODE, space, ' '.

The only one that worked was ie 24-nov-2011.

But with this format, I can't insert NULL.

Do someone know how could I do?

Tks in advance.

Flavio.

PS: I'm using the GET REFERENCE functionallity.

Re: Insert NULL date into ORACLE using ADBC

former_member222709 — Thu, 24 Nov 2011 13:29:18 GMT

Hi Flavio,

I'm able to pass 'NULL' value to a Date field in ORACLE using Native SQL provided the date field defined in ORACLE is of type 'DEAULT NULL'.

The value passed by the code is 'NULL' as is accepted in the destination system.

Regards,

Pranav.

Re: Insert NULL date into ORACLE using ADBC

Former Member — Thu, 24 Nov 2011 14:54:54 GMT

Pranav,

I've made the field receive NULL as default in Oracle database, but I still facing the same problem.

Tks anyway.

Flavio

Re: Insert NULL date into ORACLE using ADBC

former_member222709 — Fri, 25 Nov 2011 04:36:59 GMT

Hi Flavio,

I can suggest 2 ways to verify the porblem:

1. Try manually inserting the record in the ORACLE DB. If no errors then the data string needs to be verified.

2. If feasible, create another table in ORACLE DB without any foreign keys or complex data types. Simple table with a Unique Key and a NULL field. Send data to this Table and if successful, maybe you'll get some hints.

Regards,

Pranav.

Re: Insert NULL date into ORACLE using ADBC

deepak_dhamat — Fri, 25 Nov 2011 06:03:46 GMT

hi ,

First check whether in your oracle table - date is part of primary key or not ?

if it is part of primary key with constraint of not acccepting NULL values then it will give error .

regards

Deepak.

Re: Insert NULL date into ORACLE using ADBC

Former Member — Fri, 25 Nov 2011 13:17:10 GMT

Tks, but I've already tried these solutions...

I checked on the debug and I'm using exactly the same statement...

On TOAD( Oracle Viewer ) the statement works... On my method, don't.

The field has no constraints and is not part of the pk.

Regards,

Flavio.

Re: Insert NULL date into ORACLE using ADBC

deepak_dhamat — Sat, 26 Nov 2011 04:05:38 GMT

hi ,

Dont pass value in "NULL" Codes ,

just pass null .

regards

Deepak.

Re: Insert NULL date into ORACLE using ADBC

Former Member — Thu, 09 Jan 2014 19:34:15 GMT

Flavio,

have you found a solution for this?

regards, Darek