Home » RDBMS Server » Server Utilities » Re: SQLLDR, dates and null columns
| Re: SQLLDR, dates and null columns [message #482259] |
Wed, 10 November 2010 11:29  |
amaq44
Messages: 1
Registered: November 2010
|
Junior Member |
|
|
Hi,
I am experiencing somewhat same issue...but have been unable to resolve it(new to Oracle)
I am getting the infile from flat file(data dump from SQL) using sqlldr to upload data to the Oracle table...since the data is already in the flat file...I cannot do anything in the SQL to pre-format the data...
Sample of ERROR I am getting - Column CREATE_DATE which has date and time - happens to other date time columns also if remove the CREATE_DATE from Control file(happens to every single line of record):
==========================================
Record 2: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Record 3: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Record 4: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Record 5: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
=======================================
SQL Loader Control file
=======================================
OPTIONS (ERRORS=9999)
LOAD DATA
INFILE 'S:\CHSFull_METS\Mets_CHS_User_Priv\Mets_CHS_User_Priv.Dat'
BADFILE 'S:\CHSFull_METS\Mets_CHS_User_Priv\Mets_CHS_User_Priv.Bad'
DISCARDFILE 'S:\CHSFull_METS\Mets_CHS_User_Priv\Mets_CHS_User_Priv.Dsc'
TRUNCATE
INTO TABLE LGCY_CHS.Mets_CHS_User_Priv
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '""'
TRAILING NULLCOLS
(
USER_PRIV_KEY INTEGER EXTERNAL,
USER_ID INTEGER EXTERNAL,
USER_STATUS INTEGER EXTERNAL,
USER_INITIALS INTEGER EXTERNAL,
CSA_INDIC CHAR "TRIM(:CSA_INDIC)",
VIEW_RIGHTS_INDIC INTEGER EXTERNAL,
CHANGE_FACILITY_PRICING_INDIC INTEGER EXTERNAL,
CHANGE_EXAM_ACCT_INDIC INTEGER EXTERNAL,
VIEW_MED_SURV_INDIC INTEGER EXTERNAL,
SHOW_OUTPUT_INDIC INTEGER EXTERNAL,
FORMAT_TEXT_INDIC INTEGER EXTERNAL,
CREATE_USER INTEGER EXTERNAL,
CREATE_MACHINE INTEGER EXTERNAL,
CREATE_DATE TIMESTAMP "YYYY-MM-DD HH24.MI.SS.FF6",
UPDATE_USER CHAR "TRIM(:UPDATE_USER)",
UPDATE_MACHINE INTEGER EXTERNAL,
UPDATE_DATE TIMESTAMP (6),
VERSION_DATE TIMESTAMP (6),
NIDW_CREATE_DATE SYSDATE,
NIDW_UPDATE_DATE SYSDATE,
NIDW_CURRENT_FLAG CONSTANT "N"
)
===============================================
Flat file: (3 lines of data)
5|Annie|1|AR|84601D0A-6D9D-4D0F-86EB-2FDD9D7E680B|0|0|1|1|1|0|1|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Annie|VAXP60|2008-10-03 16:54:59.583|2008-10-03 16:54:59.583
11|Beverly|1|BA|9A2D6304-E997-4B40-96E5-2221E521B077|1|0|0|1|0|0|0|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|BEVERLY|VAXP60|2008-10-03 09:39:33.973|2008-10-03 09:39:33.973
29|KGarbin|1|KG|B229FCF9-BED0-4E50-9804-83324B677C67|0|0|1|1|1|1|0|1|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Gfoote|VAXP60|2008-09-08 10:05:01.690|2008-09-08 10:05:01.690
Any help/pointers are appreciated...
Regards
|
|
|
|
| Re: SQLLDR, dates and null columns [message #482268 is a reply to message #482259] |
Wed, 10 November 2010 12:38  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This was split from an unrelated thread. Your problem has nothing to do with null values for dates. The problem is that your posted control file does not match your posted data file for many columns. For example, your control file says that your second column is integer external, but your data contains characters. A null value would be when you have two delimiters with nothing inbetween, like ||. If you have columns in your table for which you do not have values in your data, then do not list those columns in your control file. If you have values in your data that do not belong in any column in your table, then make them filler datatype in your control file. You should post your table structure with your control file and data and example of the result that you expect for at least one row of data.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 14:10:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
