Solved

SQLLDR Date Format Error

Posted on 2006-12-01
11
5,424 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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
Try this:

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

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 23

Expert Comment

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

Accepted Solution

by:
awking00 earned 125 total points
Comment Utility
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
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 1

Author Comment

by:MikeHunt
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

728 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

11 Experts available now in Live!

Get 1:1 Help Now