Solved

SQLLDR Date Format Error

Posted on 2006-12-01
11
5,453 Views
Last Modified: 2011-08-18
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.
0
Comment
Question by:MikeHunt
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 18055226
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 18055281
Try this:

VALID_FROM CHAR DATE_FORMAT TIMESTAMP MASK "fmMONDDYYYY HH:MI:SS:FFPM"
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18055296
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:paquicuba
ID: 18055329
Actually, You don't need the "fm" format modifier. I was just doing some test and forgot to remove it...
0
 
LVL 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 18056131
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
 
LVL 1

Author Comment

by:MikeHunt
ID: 18066893
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
 
LVL 32

Expert Comment

by:awking00
ID: 18068370
The ORA-00904 error occurs when the column name is invalid. Make sure the attribute name is VALID_FROM in the table.
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 18069246
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18069561
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
 
LVL 32

Expert Comment

by:awking00
ID: 18069624
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
 
LVL 1

Author Comment

by:MikeHunt
ID: 18071767
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
null value 15 99
Convert Oracle data into XML document 2 65
VB.Net - CSV to Oracle table 4 67
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 2 43
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now