Skip to Content
author's profile photo Former Member
Former Member

EXEC SQL insert null date

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 08, 2012 at 07:03 PM

    Use decode function in sql statement where you have to_date function.

    decode(abap_date, '00000000', null, to_date(abap_date, 'YYYYMMDD))

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 08, 2012 at 08:05 PM

    Hi Sau Tong,

    This solution may not be the right way of doing it, but, you can achieve sending a 'NULL' value as follows:

    1. Define the date field in the structure as character variable of length 8

    2. Check the date field for value '00000000'.

    3. If it is '00000000', clear the field

    If you pass this table or work area to SQL, it should consider it as NULL as the cleared character variable will not have SPACE.

    Regards,

    Pranav.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 03, 2010 at 09:27 AM

    Hello,

    I think in the Oracle DB table by default the date is defined as NULL & not as 0000000 as in ABAP which might be causing this error.

    BR,

    Suhas

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Just had the same problem, and I have a table with 8 date fields. I decided to do an INSERT without the date fields (where the DB field is NULLABLE) and then make an UPDATE for each date field if the ABAP value is NOT INITIAL.

      This solution is ok as long as DB performance is not important.

      Edited by: Martin Lermen on Jul 14, 2011 4:10 PM

  • Posted on Jul 14, 2011 at 08:45 PM

    Hi Sau Tong Calvin,

    don't you mess up INITIAL ans NULL values.

    In ABAP, every data type has an initial value. As opposed to other programming languages, all variables are set to their initial value before use, numeric fields get 0, TYPE N which is numeric character will take 0 in all places, strings are empty and character fields are space. ABAP date fields are type N technically, so will be '00000000'.

    You can store as this using open SQL.

    NULL value means there is nothing stored for this field - this may save disk space if only few records have values stored for the field. But it will make problems in a WHERE clause of selection because SPACE matches initial values but not NULL values and opposite.

    I don't know too much about native EXEC SQL because I never needed in the last decade. Hopefully you can solve the task using ABAP open SQL.

    Regards,

    Clemens

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.