SQLLDR Date Format Error

Hi,

I'm getting the following errors when loading data into a 10.2.0.2 DB using SQL LOADER.

Record 1: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 2: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 3: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


-------------------------
Here is the control file:

LOAD DATA
INFILE 'test1.csv'
REPLACE
INTO TABLE CIT_BS70.TEST1
FIELDS TERMINATED BY "|"
ENCLOSED BY '"'
TRAILING NULLCOLS
(
PERIOD,
BUSINESS_UNIT,
ACCOUNT,
ACCOUNT_DESC,
ACCOUNT_DEBIT,
ACCOUNT_DEBIT_DESC,
ACCOUNT_CREDIT,
ACCOUNT_CREDIT_DESC,
VALID_FROM DATE "YYYYMMDD",
RESERVE
)

--------------------------------
Here is the flat file: test1.csv (I have put a blank line between each entry to make it easier to read)


"200512"|"0895"|"*******"|" "|"AZ9542"|"Suspense Account General"|"AZ9542"|"Suspense Account General"|"Oct  9 2001 12:00:00:000AM"|" "

"200512"|"0895"|"10*****"|" "|"AZ9559"|"Pend.Subledg.BS EKBF-Error"|"AZ9559"|"Pend.Subledg.BS EKBF-Error"|"Oct  6 2004 12:00:00:000AM"|" "

"200512"|"0895"|"14*****"|" "|"AZ9549"|"Susp Acct Subledg GM -EKBF"|"AZ9549"|"Susp Acct Subledg GM -EKBF"|"Oct  6 2004 12:00:00:000AM"|" "


------------------------

Obviously this is due to the ..

VALID_FROM DATE "YYYYMMDD",

.. line. So if I remove the date reference I get the following errors:

Record 1: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 2: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 3: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-01858: a non-numeric character was found where a numeric was expected
--------------


Can anybody tell me what I need to put in my control file to load the dates in the flat file? Or do I need to edit my flat files? If so what is the best way to do this?

One other thing. If I have all my fields enclosed in quotes then what do I do when some of the char columns contain quotes in them?

Thanks,
Mike.
LVL 1
MikeHuntAsked:
Who is Participating?
 
awking00Commented:
What I have done on occasion to handle dates from a flat file is to create a function to validate the data first, then apply it in my sql*loader control file.

CREATE OR REPLACE FUNCTION valid_date
  (test_date  IN VARCHAR2) RETURN TIMESTAMP IS
  v_return_date TIMESTAMP;
BEGIN
  BEGIN
    IF test_date IS NOT NULL
    THEN
       v_return_date := TO_TIMESTAMP(test_date, 'Mon  DD YYYY HH:MI:SS:FF3AM');
    ELSE
       v_return_date := NULL;
    END IF;

    EXCEPTION
      WHEN OTHERS
      THEN v_return_date := NULL;
  END;
RETURN v_return_date;
END;
/

Then in the control file -
LOAD DATA
INFILE 'test1.csv'
REPLACE
INTO TABLE CIT_BS70.TEST1
FIELDS TERMINATED BY "|"
ENCLOSED BY '"'
TRAILING NULLCOLS
(
PERIOD,
BUSINESS_UNIT,
ACCOUNT,
ACCOUNT_DESC,
ACCOUNT_DEBIT,
ACCOUNT_DEBIT_DESC,
ACCOUNT_CREDIT,
ACCOUNT_CREDIT_DESC,
VALID_FROM
"valid_date(:VALID_FROM)"
RESERVE
)
0
 
Mark GeerlingsDatabase AdministratorCommented:
I have rarely succeeded in loading "date" information from ASCII files directly into "date" columns in Oracle.  Usually, there are invalid, incomplete or missing values that cause problems.  I usually set up a "work" table for SQL*Loader to use that has all varchar2 columns and load the data there first.  Then I use SQL commands and/or PL\SQL procedures to clean up the data and move it to the intended table(s).

It should work to edit the file first if you prefer, if it is not so large and if you carefully spot all of the invalid, incomplete or missing values.
0
 
paquicubaCommented:
Try this:

VALID_FROM CHAR DATE_FORMAT TIMESTAMP MASK "fmMONDDYYYY HH:MI:SS:FFPM"
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
VALID_FROM DATE "YYYYMMDD",

try with the below instead of the above

VALID_FROM DATE "Mon DD YYYY HH:MI:SS:SSSAM"

I have not tested it and tried it but give it a try. It wont take a minute to do it. Note there is a space between YYYY HH in the above line. if you flat file doesn't have remove the space in the above line.

Thanks
0
 
paquicubaCommented:
Actually, You don't need the "fm" format modifier. I was just doing some test and forgot to remove it...
0
 
MikeHuntAuthor Commented:
Thanks for the input, however I can't get any of the the suggestions to actually work .. except for the first one about loading it into a varchar and then manipulating the data afterwards (which I haven't tried but I'm sure will work eventually). Or even editing the date format in the flat files before loading. I'd like to avoid both of these methods if possible.

awking00, I created the function you suggested and ran the sqlldr cmd again with the updated ctl file but I recieved the following error in the logfile:


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PERIOD                              FIRST     *   |    "  CHARACTER
BUSINESS_UNIT                        NEXT     *   |    "  CHARACTER
P09_ACCOUNT                          NEXT     *   |    "  CHARACTER
P09_ACCOUNT_DESC                     NEXT     *   |    "  CHARACTER
PSGL_ACCOUNT_DEBIT                   NEXT     *   |    "  CHARACTER
PSGL_ACCOUNT_DEBIT_DESC              NEXT     *   |    "  CHARACTER
PSGL_ACCOUNT_CREDIT                  NEXT     *   |    "  CHARACTER
PSGL_ACCOUNT_CREDIT_DESC             NEXT     *   |    "  CHARACTER
VALID_FROM                           NEXT     *   |    "  CHARACTER
    SQL string for column : "valid_date(:VALID_FROM)"
RESERVE                              NEXT     *   |    "  CHARACTER

Record 1: Rejected - Error on table CIT_BS70.PS_MAPPING, column VALID_FROM.
ORA-00904: "VALID_DATE": invalid identifier
.........


Any ideas?
0
 
awking00Commented:
The ORA-00904 error occurs when the column name is invalid. Make sure the attribute name is VALID_FROM in the table.
0
 
MikeHuntAuthor Commented:
The column name is correct. In the end I got it to work by using:

VALID_FROM date "Mon DD YYYY HH:MI:SS",

in the control file and editing the dates in the flat file to match. Obviously this is not the ideal way of doing things so it would be good to get your function to work here. Does sqlldr work using bind variables in the ctl file?

Cheers,
Mike.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you can manipulate the field by using expressions:

C_E_USER_DEFINED_CMBAL_2 POSITION(516:529)  DECIMAL EXTERNAL  ":C_E_USER_DEFINED_CMBAL_2/power(10,2)"  

C_E_MATURITY_DATE POSITION(488:493)  DATE  "RRMMDD" "decode(:C_E_MATURITY_DATE,'000000',NULL,'      ',NULL,:C_E_MATURITY_DATE)"

The above are just samples.

Thanks


0
 
awking00Commented:
MikeHunt,
I got it to run fine using the function I showed earlier. However, I had to add a comma that was missing in the control file after the "valid_date(:VALID_FROM)" and before the RESERVE that may have been the problem.
0
 
MikeHuntAuthor Commented:
awking00, thanks for your reply. I ran the test again but this time just using the info I had given you earlier and it worked.

Originally I was using a huge flat file (not just 3 entries) but I had put the comma into the control file so I guess I must have come across a seperate issue and failed to realize the error msg was different.

Thanks for your help here. I can use your solution to get around several other similar problems I have with different loads.

Cheers,
Mike.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.