Skip to Content
0
Former Member
Feb 03, 2010 at 09:17 AM

EXEC SQL insert null date

1514 Views

Hello,

Could anyone please tell me how I can insert a null date into an oracle table dynamically? Here are my codes:

DATA: LV_KEY(10),

LV_DATE TYPE D.

TRY.

EXEC SQL.

INSERT INTO Z_ORACLE_TABLE

( KEY_FIELD, OPTION_DATE )

VALUES

( :LV_KEY, TO_DATE( :LV_DATE, 'YYYYMMDD' ) )

ENDEXEC.

ENDTRY.

Somehow, Oracle gives an error if LV_DATE is initial ('00000000'), saying the date is not between -49xx to ....

I could have done the following but it is kind of dumb:

TRY.

IF :LV_DATE IS INITIAL.

EXEC SQL.

INSERT INTO Z_ORACLE_TABLE

( KEY_FIELD, OPTION_DATE )

VALUES

( :LV_KEY, NULL )

ENDEXEC.

ELSE.

EXEC SQL.

INSERT INTO Z_ORACLE_TABLE

( KEY_FIELD, OPTION_DATE )

VALUES

( :LV_KEY, TO_DATE( :LV_DATE, 'YYYYMMDD' ) )

ENDEXEC.

ENDTRY.

Thanks in advance for any suggestions.