Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQLLDR Date Format Error

Posted on 2006-12-01
11
Medium Priority
?
5,600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 500 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

604 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