Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
I'm trying to use sqlldr to load data from our production database to our dev database. Identical schemas/tables...
Executing as : XXXX's are actual password of course.
sqlldr userid=fsdba/XXXXX control=1_nrn_proxy_data_LOADER.sql
and I get the following error:
SQL*Loader: Release 10.2.0.4.0 - Production on Thu May 23 13:35:18 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 58
Here is the first part of the file.
LOAD DATA
INFILE *
BADFILE './NRN_PROXY_DATA.BAD'
DISCARDFILE './NRN_PROXY_DATA.DSC'
APPEND INTO TABLE FS_NRIS_NVUM.NRN_PROXY_DATA
Fields terminated by ";" Optionally enclosed by '"'
(
PROXY_DATA_CN,
SITE_CN_FK,
TOTAL_COUNT NULLIF (TOTAL_COUNT="NULL"),
TOTAL_COMP_ADJ_COUNT NULLIF (TOTAL_COMP_ADJ_COUNT="NULL"),
OCT_DEC_COUNT NULLIF (OCT_DEC_COUNT="NULL"),
OCT_DEC_COMP_ADJ_COUNT NULLIF (OCT_DEC_COMP_ADJ_COUNT="NULL"),
JAN_MAR_COUNT NULLIF (JAN_MAR_COUNT="NULL"),
JAN_MAR_COMP_ADJ_COUNT NULLIF (JAN_MAR_COMP_ADJ_COUNT="NULL"),
APR_JUN_COUNT NULLIF (APR_JUN_COUNT="NULL"),
APR_JUN_COMP_ADJ_COUNT NULLIF (APR_JUN_COMP_ADJ_COUNT="NULL"),
JUL_SEP_COUNT NULLIF (JUL_SEP_COUNT="NULL"),
JUL_SEP_COMP_ADJ_COUNT NULLIF (JUL_SEP_COMP_ADJ_COUNT="NULL"),
PROXY_NOTES,
VPDUNIT_ID,
LAST_UPDATE TIMESTAMP "MM/DD/YYYY HH24:MI:SS.FF9 TZR" NULLIF (LAST_UPDATE="NULL"),
FINAL,
PROXY_YEAR
)
BEGINDATA
"1543ef7c-d263-4ae3-9ae8-c370e2e65d3d";"9A4D3F54C2F33501E040DDA51E2A5D04";287482;287482;111408;111408;132599;132599;36938;36938;6537;6537;"";"0215";01/23/2013 16:36:42.953556000 -06:00;"Y";"F2012"
"f8a833ae-3657-4360-a3f4-29e1b1204e17";"9A4D3F54B6993501E040DDA51E2A5D04";65665;65665;0;0;0;0;10612;10612;55053;55053;"";"0215";01/23/2013 16:36:42.819538000 -06:00;"Y";"F2012"
"924e3de5-5729-46a8-8f11-b6fc0710f7b0";"9A4D3F54B6A53501E040DDA51E2A5D04";28567;28567;0;0;0;0;2250;2250;26317;26317;"";"0215";01/23/2013 16:36:42.821485000 -06:00;"Y";"F2012"
"9c837de4-de3c-4d2b-8036-e10e86764766";"9A4D3F54BA7B3501E040DDA51E2A5D04";295749;295749;77030;77030;193379;193379;25340;25340;0;0;"";"0215";01/23/2013 16:36:42.845158000 -06:00;"Y";"F2012"
{code}
There are many many more lines, but each is identical in structure....I don't see the need to waste the space.
Here is the output from the log:
{code}
Record 37: Rejected - Error on table FS_NRIS_NVUM.NRN_PROXY_DATA, column LAST_UPDATE.
ORA-01821: date format not recognized
Record 38: Rejected - Error on table FS_NRIS_NVUM.NRN_PROXY_DATA, column LAST_UPDATE.
ORA-01821: date format not recognized
Record 39: Rejected - Error on table FS_NRIS_NVUM.NRN_PROXY_DATA, column LAST_UPDATE.
ORA-01821: date format not recognized
Record 40: Rejected - Error on table FS_NRIS_NVUM.NRN_PROXY_DATA, column LAST_UPDATE.
ORA-01821: date format not recognized
Record 41: Rejected - Error on table FS_NRIS_NVUM.NRN_PROXY_DATA, column LAST_UPDATE.
ORA-01821: date format not recognized
.
.
.
.
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table FS_NRIS_NVUM.NRN_PROXY_DATA:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 254388 bytes(58 rows)
Read buffer bytes: 1048576
{code}
I checked and both of the tables are again identical, and both utilize the same data type for the date. (snippet from table code source)
{code}
LAST_UPDATE TIMESTAMP(6) WITH TIME ZONE DEFAULT systimestamp NOT NULL,
{code}
This isn't a new table. This table has been used for years. The only thing I'm doing is updating fiscal data. So I go into the dev table and wipe certain data out, then go to the production table and copy, then insert back into dev.
I'm using TOAD to query the data I need from the production table, and generate the sqlldr control/data file.
Thanks.