Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: SQLLDR with fixed length data

    is it possible to setup a conditional clause to default the column value to the table's schema?

    eg.
    table test(
    insert_date date default sysdate
    );

    i want to tell SQLLDR to use the default value if it sees a blank line.

    TIA

    Mark

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQLLDR with fixed length data

    Originally posted by mchih
    is it possible to setup a conditional clause to default the column value to the table's schema?

    eg.
    table test(
    insert_date date default sysdate
    );

    i want to tell SQLLDR to use the default value if it sees a blank line.

    TIA

    Mark
    I can't see any way to do that, i.e. for SQL Loader to find out the column default and use that. Of course, you can do this:

    insert_date DATE "dd-mon-yyyy" "NVL(:insert_date,TO_CHAR(SYSDATE,'dd-mon-yyyy'))"

    (Hope I have that syntax right!)

  3. #3
    Join Date
    Nov 2002
    Posts
    98

    Thumbs up

    thankx!

    had some other projects on hand which distracted my db design :P
    didn't know i can add SQL functions in sqlldr. this will help a lot

    and just for reference, the correct syntax is:


    disagree_date POSITION(280:287) date 'YYYYMMDD' "NVL(:disagree_date,to_char(sysdate,'YYYYMMDD' ))"

    i tried with CASE and DECODE functions and they worked perfectly as well

    thanks for the advice!

    Mark

  4. #4
    Join Date
    May 2003
    Posts
    4
    It seems to me it would be possible. Maybe not in one step.

    Check out this page and see if it helps: http://www.orafaq.com/faqloadr.htm

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •