Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3749
  • Last Modified:

sqlldr errors

I have null values to be inserted into a table whose column is created as NOT NULL.
How do i go about it?
Here is the sqlldr log:

Record 1: Rejected - Error on table XYZ.
ORA-01400: cannot insert NULL into ("XYZ"."MER_CDE")

Record 2: Rejected - Error on table XYZ.
ORA-01400: cannot insert NULL into ("XYZ"."MER_CDE")

Record 3: Rejected - Error on table XYZ.
ORA-01400: cannot insert NULL into ("XYZ"."MER_CDE")

Data:
0096741168826|A|0200001|A XMAS                |CHR CROO                    |  |01|002|Y|0|C |280|2000|2030|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0096741169021|A|0200002|C XMAS                |COU CHR VOL                 |  |01|002|Y|0|C |280|0300|0330|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0049656513472|A|0200003|C                     |XM073                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|397  |CD     |00000.90|00004.98|003.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0049656513489|A|0200004|C                     |XM076                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|687  |CD     |00002.75|00006.98|006.92|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0018111770422|A|0200005|C                     |FROSTY                      |  |01|002|Y|0|C |280|0700|0730|6000|04630|LDL|18111|76104  |00003.30|00007.99|006.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070529|RR|        |        |A|D|20070502|2725|2725|20070502|A|  |084|1|001|        |        |


MER_CDE is 6th column from right.
while creating the table, the following columns shown under have been created as not null.
While in the data in these columns may have a null value.

For Example MER_CODE column has null values in the sample data shown below

MER_CDE                  VARCHAR2(30)      NOT NULL, --null values in data
PRD_LNE                  VARCHAR2(40)      NOT NULL, --may have null values in data
CLS_CDE                  VARCHAR2(40)      NOT NULL, --may have null values in data
CTG_IND                  VARCHAR2(40)      NOT NULL,
FOR_CDE                  VARCHAR2(40)      NOT NULL,
SHP_CDE                  VARCHAR2(40)      NOT NULL,

Q Should i disable all constraints in the table, and load all data through sqlldr and then enable the constraints?
0
gram77
Asked:
gram77
  • 21
  • 10
  • 5
  • +1
8 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you have a column in a table as not null, then we mean to say that
null values will not accepted for that column.

So you have to decide whether you want null values or not for that column
first.

If you want to allow null values, then

alter table xyz modify mer_code null;  -- similarly do it for other columns and then run sql loader

If you do not want to allow null values, then you have get data for those columns before you run sql loader.

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you disable the constraint, then you can load the null values but you will not be able
to enable the constraint with validate option.

You may have to use novalidate option while enabling the constraint again but that will lead
to confusion because the table definition will have not null defined for columns and we will
have null values for those columns. But if your requirement is like that meaning to say,
whatever happens you want to push these null values and constraints should be there, then
disable the constraint, load the data, enable the constraint with novalidate option so that
it will not give errors for the constraint violations of null values. Novalidate means do not
validate the existing data but going forward ensure that the constraint is met.
0
 
gram77Author Commented:
nav_kumar:
I have changed the ctl file with this. However, I plan to use No Validate Option instead as you suggested.

LOAD DATA
INFILE 'C:\loaddata\INV.dat'
REPLACE INTO TABLE XYZ
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS              
(EAN             "nvl(:EAN,' ')",
STS             "nvl(:STS,' ')",
ITM             "nvl(:ITM,' ')",
ART             "nvl(:ART,' ')",
TTL             "nvl(:TTL,' ')",
..
..
REL_DTE       "to_date(:REL_DTE,'YYYYMMDD')",
ITM_RTN_POL       "nvl(:ITM_RTN_POL,' ')",
LAS_REQ_RTN_DTE "to_date(:REL_DTE,'YYYYMMDD')",
CLL_BCK_DTE       "to_date(:REL_DTE,'YYYYMMDD')",
MNT_CDE       "nvl(:MNT_CDE,' ')",
CUR_DEC       "nvl(:CUR_DEC,' ')",
LAST_UPDATE_DATE "to_date(:REL_DTE,'YYYYMMDD')",

Now my log shows:
Record 1: Rejected - Error on table XYZ, column REL_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 2: Rejected - Error on table XYZ, column REL_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Data:
0096741168826|A|0200001|A XMAS                |CHR CROO                    |  |01|002|Y|0|C |280|2000|2030|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0096741169021|A|0200002|C XMAS                |COU CHR VOL                 |  |01|002|Y|0|C |280|0300|0330|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0049656513472|A|0200003|C                     |XM073                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|397  |CD     |00000.90|00004.98|003.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0049656513489|A|0200004|C                     |XM076                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|687  |CD     |00002.75|00006.98|006.92|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0018111770422|A|0200005|C                     |FROSTY                      |  |01|002|Y|0|C |280|0700|0730|6000|04630|LDL|18111|76104  |00003.30|00007.99|006.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070529|RR|        |        |A|D|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

..
..
REL_DTE is the 17th record from left.

I do not know why is sqlldr not able to recognize the REL_DTE. It is defined in control file as:

REL_DTE       "to_date(:REL_DTE,'YYYYMMDD')",
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
REL_DTE       "to_date(:REL_DTE,'YYYYMMDD')",

try as shown below

REL_DTE   date "YYYYMMDD",

This is the one which i use for date fields. If this works, you can do it for other date fields as well.
0
 
gram77Author Commented:
Still i face the same problem
0
 
gram77Author Commented:
nav_kum_v:

I guess sqlldr is accepting the date values like 20070801.

However, when this date format is inserted into the database table by sqlldr;
it is being denied

REL_DTE      DATE NOT NULL,      

How can i provide the date format in the create statement like YYYYMMDD?

0
 
johnsoneSenior Oracle DBACommented:
The only fields that look like they hold dates are 31,37, and 40.   Field 17 holds the value LDL in all of the sample records.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
while creating date field with date data type, we cannot give format like YYYYMMDD.

This should work
REL_DTE   date "YYYYMMDD"

paste the error and data for one sample record.

Also create some dummy data file like data1.dat which will contain just one record with data "20070101' and try to load this data into some oracle table which has a date data type. If we get this working, then we can just use the same format in your actual .ctl





0
 
gram77Author Commented:
Error from Error Log:
Record 1: Rejected - Error on table XYZ, column REL_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 2: Rejected - Error on table XYZ, column REL_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 3: Rejected - Error on table XYZ, column REL_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 4: Rejected - Error on table XYZ, column REL_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

**************************************************

Data:
0096741168826|A|0200001|A XMAS                |CHR CROO                    |  |01|002|Y|0|C |280|2000|2030|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0096741169021|A|0200002|C XMAS                |COU CHR VOL                 |  |01|002|Y|0|C |280|0300|0330|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0049656513472|A|0200003|C                     |XM073                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|397  |CD     |00000.90|00004.98|003.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0049656513489|A|0200004|C                     |XM076                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|687  |CD     |00002.75|00006.98|006.92|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0018111770422|A|0200005|C                     |FROSTY                      |  |01|002|Y|0|C |280|0700|0730|6000|04630|LDL|18111|76104  |00003.30|00007.99|006.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070529|RR|        |        |A|D|20070502|2725|2725|20070502|A|  |084|1|001|        |        |


REL_DAT is 17 column from left

**************************************************

Control file:
LOAD DATA
INFILE 'C:\INV.dat'
REPLACE INTO TABLE XYZ
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS              
(EAN             "nvl(:EAN,' ')",
STS             "nvl(:STS,' ')",
ITM             "nvl(:ITM,' ')",
ART             "nvl(:ART,' ')",
TTL             "nvl(:TTL,' ')",
MER_CDE       "nvl(:MER_CDE,' ')",
PRD_LNE       "nvl(:PRD_LNE,' ')",
CLS_CDE       "nvl(:CLS_CDE,' ')",
CTG_IND       "nvl(:CTG_IND,' ')",
FOR_CDE       "nvl(:FOR_CDE,' ')",
SHP_CDE       "nvl(:SHP_CDE,' ')",
CAT             "nvl(:CAT,' ')",
SEG             "nvl(:SEG,' ')",
SUB_SEG       "nvl(:SUB_SEG,' ')",
MER_TYP       "nvl(:MER_TYP,' ')",
VND             "nvl(:VND,' ')",
MFG_LBL       "nvl(:MFG_LBL,' ')",
MFG_PFX       "nvl(:MFG_PFX,' ')",
MFG_SFX       "nvl(:MFG_SFX,' ')",
BSE_COS       "nvl(:BSE_COS,' ')",
VND_MSL       "nvl(:VND_MSL,' ')",
MSL             "nvl(:MSL,' ')",
OSP_FLG       "nvl(:OSP_FLG,' ')",
BOX_LOT       "nvl(:BOX_LOT,' ')",
ITM_VOL_FCT       "nvl(:ITM_VOL_FCT,' ')",
WGT             "nvl(:WGT,' ')",
HGT             "nvl(:HGT,' ')",
DPH             "nvl(:DPH,' ')",
CLM_SHL_FLG       "nvl(:CLM_SHL_FLG,' ')",
REL_DTE       date "YYYYMMDD",
ITM_RTN_POL       "nvl(:ITM_RTN_POL,' ')",
LAS_REQ_RTN_DTE "to_date(:REL_DTE,'YYYYMMDD')",
CLL_BCK_DTE       "to_date(:REL_DTE,'YYYYMMDD')",
MNT_CDE       "nvl(:MNT_CDE,' ')",
CUR_DEC       "nvl(:CUR_DEC,' ')",
LAST_UPDATE_DATE "to_date(:REL_DTE,'YYYYMMDD')",
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE       "to_date(:REL_DTE,'YYYYMMDD')",
HDL_ORGANIZATION "nvl(:HDL_ORGANIZATION,' ')",
VEW_RTE_CDE       "nvl(:VEW_RTE_CDE,' ')",
ORGANIZATION_ID "nvl(:ORGANIZATION_ID,' ')",
COU             "nvl(:COU,' ')",
COR             "nvl(:COR,' ')",
INA_DTE       "to_date(:REL_DTE,'YYYYMMDD')",
REA_DTE       "to_date(:REL_DTE,'YYYYMMDD')")
0
 
gram77Author Commented:
Also I have created a table with all not null constraints at column level.

1. I want to disable all constraints in the table.

2. Load data through sqlldr

3. and then want to enable all constraints with deferred constraints

What is the syntax of 1. and 3. options

0
 
gram77Author Commented:
CREATE TABLE XYZ
(EAN                  VARCHAR2(40)      NOT NULL,
STS                  VARCHAR2(10)      NOT NULL,
ITM                  VARCHAR2(25)      NOT NULL,
ART                  VARCHAR2(30)      NOT NULL,       
TTL                  VARCHAR2(30)      NOT NULL,
MER_CDE                  VARCHAR2(30)      NOT NULL,
PRD_LNE                  VARCHAR2(40)      NOT NULL,
CLS_CDE                  VARCHAR2(40)      NOT NULL,
CTG_IND                  VARCHAR2(40)      NOT NULL,
FOR_CDE                  VARCHAR2(40)      NOT NULL,
SHP_CDE                  VARCHAR2(40)      NOT NULL,
CAT                  VARCHAR2(40)      NOT NULL,
SEG                  VARCHAR2(40)      NOT NULL,
SUB_SEG                  VARCHAR2(40)      NOT NULL,
MER_TYP                  VARCHAR2(40)      NOT NULL,
VND                  VARCHAR2(40)      NOT NULL,
MFG_LBL                  VARCHAR2(40)      NOT NULL,
MFG_PFX                  VARCHAR2(25)      NOT NULL,
MFG_SFX                  VARCHAR2(25)      NOT NULL,
BSE_COS                  NUMBER            NOT NULL,
VND_MSL                  VARCHAR2(40)      NOT NULL,
MSL                  VARCHAR2(40)      NOT NULL,
OSP_FLG                  VARCHAR2(40)      NOT NULL,
BOX_LOT                  NUMBER            NOT NULL,
ITM_VOL_FCT            NUMBER            NOT NULL,
WGT                  NUMBER            NOT NULL,
HGT                  NUMBER            NOT NULL,
WDT                  NUMBER            NOT NULL,
DPH                  NUMBER            NOT NULL,
CLM_SHL_FLG            VARCHAR2(40)      NOT NULL,
REL_DTE                  DATE            NOT NULL,      
ITM_RTN_POL            VARCHAR2(240)      NOT NULL,
LAS_REQ_RTN_DTE            DATE,      
CLL_BCK_DTE            DATE,      
MNT_CDE                  VARCHAR2(1)      NOT NULL,
CUR_DEC                  VARCHAR2(40)      NOT NULL,
LAST_UPDATE_DATE      DATE,      
LAST_UPDATED_BY            NUMBER,      
CREATED_BY             NUMBER,      
CREATION_DATE             DATE,      
HDL_ORGANIZATION       VARCHAR2(240)      NOT NULL,
VEW_RTE_CDE            VARCHAR2(10)      NOT NULL,
ORGANIZATION_ID            NUMBER            NOT NULL,
COU                  VARCHAR2(1)      NOT NULL,
COR                  VARCHAR2(3)      NOT NULL,
INA_DTE                  DATE,
REA_DTE                  DATE,
CONSTRAINT XXINV_ITEM_MAINFRAME_PK PRIMARY KEY (COU,COR,ITM));
0
 
johnsoneSenior Oracle DBACommented:
There is a field missing in your control file.  In your control file REL_DATE is field 30, but in the data it is in field 31.  I added a dummy column before REL_DATE and everything loaded fine.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
create table xyz ( a number constraint my_not_null not null );

alter table xyz disable constraint my_not_null;

load data here...

alter table xyz enable novalidate constraint my_not_null;

Above are samples for the syntax
0
 
gram77Author Commented:
There are only 7 errors in the load process.

Here is the log output:
Table XXINV_ITEM_MAINFRAME:
  229354 Rows successfully loaded.
  7 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.

Data:
0000000000000|I|1111728|DOORS                         |DOORS                         |  |01|011|Y|0|C |103|9999|9999|9999|13847|ELE|EKS  |  74007|00000.00|00000.01|006.98|N|001|000.0|00000001.00000|01.000|01.000|01.000|N|19000000|RR|        |        |C|D|20030812|2725|2725|19000000|A|  |084|1|001|19780721|        |

0075679329929|I|2597392|BRANDY                        |"TALK ABOUT" FEAT KA          |  |01|025|Y|0|C |123|0420|0410|2000|13847|HDL|2    |93299  |00004.42|00000.01|007.49|N|030|001.2|00000000.10000|05.000|05.500|00.500|N|20040713|PR|        |        |C|D|20061005|2725|2725|20040615|A|  |084|1|001|20061005|        |

0711767115721|A|3256139|HERMANOS JIMENEZ              |"ESCUADRAS" CORRIDOS          |  |01|032|Y|0|C |913|8100|8190|4000|32449|HDL|COS  |1157   |00006.15|00009.98|009.96|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20060411|RR|        |        |C|D|20070313|2725|2725|20060515|A|  |084|1|001|        |        |

0877313000467|A|3397690|FANIA ALL STARS               |"LIVE" JUNE 11-1994-PUERTO RIC|  |01|033|Y|0|C |926|8900|8980|2000|31922|HDL|77313|00462  |00008.50|00012.98|015.85|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20061107|PC|        |        |C|D|20070206|2725|2725|20061009|A|  |084|1|001|        |        |

0602498221631|I|3397899|V/A LATIN POP                 |"INEDITOS" LO MEJOR DE OPERAC |  |01|033|Y|0|C |971|8500|8520|2000|31922|HDL|B000 |31230-2|00009.75|00014.98|017.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20040810|PR|        |        |C|D|20060517|2725|2725|20040712|A|  |084|1|001|20060517|        |

0602498222362|I|3397900|V/A LATIN POP                 |"INEDITOS" LO MEJOR DE OPERACI|  |01|033|Y|0|C |942|8500|8510|4000|31922|HDL|B000 |31300-2|00009.75|00014.98|017.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20040810|PR|        |        |C|D|20060622|2725|2725|20040712|A|  |084|1|001|20051209|        |

0049656580412|I|1501981|Z-VARIOUS ARTISTS             |JAZZ ESSENTIALS               |  |01|015|Y|0|C |155|9900|9999|9999|02958|CAP|S@|L |  18276|00003.60|00000.00|006.99|N|025|001.2|00000001.00000|01.000|01.000|01.000|N|19970110|RR|        |        |C|D|20060128|2725|2725|19970113|C|  |090|2|002|20000204|        |


Bad file data:
Record 119035: Rejected - Error on table XXINV_ITEM_MAINFRAME, column TTL.
no terminator found after TERMINATED and ENCLOSED field
Record 123207: Rejected - Error on table XXINV_ITEM_MAINFRAME, column TTL.
no terminator found after TERMINATED and ENCLOSED field
Record 139899: Rejected - Error on table XXINV_ITEM_MAINFRAME, column TTL.
no terminator found after TERMINATED and ENCLOSED field
Record 140014: Rejected - Error on table XXINV_ITEM_MAINFRAME, column TTL.
no terminator found after TERMINATED and ENCLOSED field
Record 140015: Rejected - Error on table XXINV_ITEM_MAINFRAME, column TTL.
no terminator found after TERMINATED and ENCLOSED field
Record 183665: Rejected - Error on table XXINV_ITEM_MAINFRAME, column BOX_LOT.
ORA-01722: invalid number

I guess that there is a problem is with quotes. either they are missing or some strings don't have them.

How to get about this?
0
 
johnsoneSenior Oracle DBACommented:
You can remove the optionally enclosed by and load the bad file.  That will load most of the errors, but the quotes will be in the data.  If you don't want that, remove the quotes from the bad file and reload.

Looks like the last record (the one that is reporting the invalid number) is missing a field.  It appears there is a N in the BOX_LOT field.
0
 
gram77Author Commented:
The sqllder took about 2 hours to load 2,40,000 rows.

I want to run is faster though direct loading.

Is direct loading activated by DIRECT=TRUE only? Or something else is required too?

SQLLDR USERID=xyz/abc@def.WORLD,
CONTROL=C:\loaddata\item_maint.ctl,DIRECT=TRUE,
DATA=C:\loaddata\item_maint.dat,
LOG=C:\loaddata\item_maint.log,
BAD=C:\loaddata\item_maint.bad,
DISCARD=C:\loaddata\item_maint.dsc;
0
 
gram77Author Commented:
sorry,

SQLLDR USERID=xyz/abc@def.WORLD,
DIRECT=TRUE,
CONTROL=C:\loaddata\item_maint.ctl,DIRECT=TRUE,
DATA=C:\loaddata\item_maint.dat,
LOG=C:\loaddata\item_maint.log,
BAD=C:\loaddata\item_maint.bad,
DISCARD=C:\loaddata\item_maint.dsc;
0
 
gram77Author Commented:
Finally the log shows 2 records with errors:

Record 12709: Rejected - Error on table XXINV_ITEM_MAINFRAME.
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month

Record 183665: Rejected - Error on table XXINV_ITEM_MAINFRAME, column BOX_LOT.
ORA-01722: invalid number

bad file output:

0000000000000|I|1111728|DOORS                         |DOORS                         |  |01|011|Y|0|C |103|9999|9999|9999|13847|ELE|EKS  |  74007|00000.00|00000.01|006.98|N|001|000.0|00000001.00000|01.000|01.000|01.000|N|19000000|RR|        |        |C|D|20030812|2725|2725|19000000|A|  |084|1|001|19780721|        |

0049656580412|I|1501981|Z-VARIOUS ARTISTS             |JAZZ ESSENTIALS               |  |01|015|Y|0|C |155|9900|9999|9999|02958|CAP|S@|L |  18276|00003.60|00000.00|006.99|N|025|001.2|00000001.00000|01.000|01.000|01.000|N|19970110|RR|        |        |C|D|20060128|2725|2725|19970113|C|  |090|2|002|20000204|        |

0
 
gram77Author Commented:
What could be the problem with them?
0
 
johnsoneSenior Oracle DBACommented:
Looks like the first one has an invalid date -> 19000000

The seond one looks like there is a pipe in the data -> S@|L
0
 
gram77Author Commented:
I have a legacy table and a new table into which i will insert data.

Many columns in the legacy table are defined as NULL columns.

The same columns are defined as NOT NULL in the new table.

So, i have a table with NOT NULL column that has null values.

I have 2 ways to handle NULL values into NOT NULL column

1. Deactivate the NOT NULL constraints; use sqlldr to load data; Re-Enable the constraints using NOVALIDATE.

2. Insert "NULL" into NOT NULL columns with null values.

Which approach is better for application building later on.
0
 
johnsoneSenior Oracle DBACommented:
Depends on what happens with the legacy data.

If the records with NULLs are updated at a later point, you will get not null violations when you update the records that were loaded.  NOVALIDATE is only at the time the constraint is enabled, all the constraints will be checked when the record is updated.  If you use the first option, then you need to code around this.
0
 
awking00Commented:
It seems to me you have numerous problems with your data that need to be addressed.
1) The data contains NULL values in fields that are designated as NOT NULL in the table. If you are going to accept these values, then you should remove the constraints entirely and NOT just disable them.
2) The data contains values for date fields that are not convertable to dates (i.e. '19000000'). Since these will fail just using the to_date function, you need to create a validate_date function that will return a null value or some non-meaningful date (e.g. '19000101'). This can be done as follows:
CREATE OR REPLACE FUNCTION validate_date(test_date  IN VARCHAR2)
RETURN DATE IS
  v_return_date DATE;
BEGIN
  BEGIN
    IF test_date IS NOT NULL
    THEN
       v_return_date := TO_DATE(test_date, 'YYYYMMDD');
    ELSE
       v_return_date := NULL;
    END IF;
    EXCEPTION
      WHEN OTHERS
      THEN v_return_date := NULL;
  END;
RETURN v_return_date;
END validate_date;
/
If you prefer to return a "non-meaningful date" rather than null, just change the v_return_date := null to v_return_date := to_date('19000101','yyyymmdd').
Then replace your to_date() functions in the control file with the validate_date() function.
3) You have double quotes in the middle of data fields while you are defining those fields as optionally enclosed by double quotes. I think the best way to handle this may be to use the replace() function on those fields which may contain the quotes - FIELD "REPLACE(:FIELD,CHR(34),'')"
4) You have a pipe ("|") in the middle of some data fields while defining those fields as being terminated by the pipe. This is the most troubling issue, since most of your data records contain 47 pipes indicating there are 47 fields, while records with a pipe in the middle would indicate there are 48 fields and completely "screw up" the alignment. In looking at your sample data, it actually appears that all of the fields are the same length and, if that's the case, I would consider using positions to define your fields rather that a terminating delimiter. You could then use the FILLER keyword to eliminate the loading of any of the pipe characters except those that are falling in the middle of the intended field. So now your control file would look something like this -
LOAD DATA
INFILE 'C:\INV.dat'
REPLACE INTO TABLE XYZ
TRAILING NULLCOLS              
(EAN    POSITION(1:13)
         "REPLACE(:EAN,CHR(34),'')",
FIRST_PIPE FILLER POSITION(14:14),
STS     POSITION(15:15)
         "REPLACE(:STS,CHR(34),'')",
SECOND_PIPE FILLER POSITION(16:16),
ITM     POSITION(17:23)
         "REPLACE(:ITM,CHR(34),'')",
...
REL_DTE POSITION(213:220)
        "VALIDATE_DATE(:REL_DTE)",
THIRTY_FIRST_PIPE FILLER POSITION(221:221),
ITM_RTN_POL POSITION(223:224)
         "REPLACE(:ITM_RTN_POL,CHR(34),'')",
THIRTY_SAECOND_PIPE FILLER POSITION(225:225),
...
etc.
0
 
gram77Author Commented:
There are some date columns allow null values in the table defination like:

LAS_REQ_RTN_DTE "to_date(:LAS_REQ_RTN_DTE,'YYYYMMDD')",
CLL_BCK_DTE       "to_date(:CLL_BCK_DTE,'YYYYMMDD')",
..
LAST_UPDATE_DATE "to_date(:LAST_UPDATE_DATE,'YYYYMMDD')",
..
CREATION_DATE       "to_date(:CREATION_DATE,'YYYYMMDD')",

So while loading data, some columns provide null values to sqlldr
and an error is reported:

The error is that the date should be any range between -4713 and +9999, and not be 0

Record 1: Rejected - Error on table xyz
ORA-00604: error occurred at recursive SQL level 1
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 2: Rejected - Error on table xyz.
ORA-00604: error occurred at recursive SQL level 1
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Record 3: Rejected - Error on table xyz.
ORA-00604: error occurred at recursive SQL level 1
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

I believe that sqlldr should insert null values whereever required in date fields that allow null values?
0
 
awking00Commented:
Flat files such as your datafile only contain characters and not data types and, therefore, cannot contain nulls, only blanks or spaces. So to_date('        ','YYYYMDD') will return an error and not a null. You need to create the validate_date function like I showed you earlier, which will return a null in these instances then change your control file to be -
LAS_REQ_RTN_DTE "validate_date(:LAS_REQ_RTN_DTE,'YYYYMMDD')",
CLL_BCK_DTE       "validate_date(:CLL_BCK_DTE,'YYYYMMDD')",
..
LAST_UPDATE_DATE "validate_date(:LAST_UPDATE_DATE,'YYYYMMDD')",
..
CREATION_DATE       "validate_date(:CREATION_DATE,'YYYYMMDD')",

0
 
gram77Author Commented:
awking00:
why is that null character and number values get inserted into columns; however, null date values do not
0
 
gram77Author Commented:
I am getting the following errror while load:

log file output: Seems function is not taking null values or something
Record 65378: Rejected - Error on table XXINV_ITEM_MAINFRAME.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Record 65379: Rejected - Error on table XXINV_ITEM_MAINFRAME.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
0
 
johnsoneSenior Oracle DBACommented:
Typically, to_date is not used in a SQL*Loader control file.  When specifying a date, it is usually specified as:

LAST_REQ_RTN_DATE   date  "YYYYMMDD"

This will certainly allow nulls, but if the date is an invalid date, it will not be changed to NULL by SQL*Load.
0
 
gram77Author Commented:
I am providing the table, control file, datafile and the function defination:
*************
Table:
*************
CREATE TABLE xyz
(EAN                  VARCHAR2(40)      CONSTRAINT EAN_NN             NOT NULL,
STS                  VARCHAR2(10)      CONSTRAINT STS_NN             NOT NULL,
ITM                  VARCHAR2(25)      CONSTRAINT ITM_NN             NOT NULL,
ART                  VARCHAR2(30)      CONSTRAINT ART_NN             NOT NULL,       
TTL                  VARCHAR2(30)      CONSTRAINT TTL_NN             NOT NULL,
MER_CDE                  VARCHAR2(30)      CONSTRAINT MER_CDE_NN             NOT NULL,
PRD_LNE                  VARCHAR2(40)      CONSTRAINT PRD_LNE_NN             NOT NULL,
CLS_CDE                  VARCHAR2(40)      CONSTRAINT CLS_CDE_NN             NOT NULL,
CTG_IND                  VARCHAR2(40)      CONSTRAINT CTG_IND_NN             NOT NULL,
FOR_CDE                  VARCHAR2(40)      CONSTRAINT FOR_CDE_NN             NOT NULL,
SHP_CDE                  VARCHAR2(40)      CONSTRAINT SHP_CDE_NN             NOT NULL,
CAT                  VARCHAR2(40)      CONSTRAINT CAT_NN             NOT NULL,
SEG                  VARCHAR2(40)      CONSTRAINT SEG_NN             NOT NULL,
SUB_SEG                  VARCHAR2(40)      CONSTRAINT SUB_SEG_NN             NOT NULL,
MER_TYP                  VARCHAR2(40)      CONSTRAINT MER_TYP_NN             NOT NULL,
VND                  VARCHAR2(40)      CONSTRAINT VND_NN             NOT NULL,
MFG_LBL                  VARCHAR2(40)      CONSTRAINT MFG_LBL_NN             NOT NULL,
MFG_PFX                  VARCHAR2(25)      CONSTRAINT MFG_PFX_NN             NOT NULL,
MFG_SFX                  VARCHAR2(25)      CONSTRAINT MFG_SFX_NN             NOT NULL,
BSE_COS                  NUMBER            CONSTRAINT BSE_COS_NN             NOT NULL,
VND_MSL                  VARCHAR2(40)      CONSTRAINT VND_MSL_NN             NOT NULL,
MSL                  VARCHAR2(40)      CONSTRAINT MSL_NN             NOT NULL,
OSP_FLG                  VARCHAR2(40)      CONSTRAINT OSP_FLG_NN             NOT NULL,
BOX_LOT                  NUMBER            CONSTRAINT BOX_LOT_NN             NOT NULL,
ITM_VOL_FCT            NUMBER            CONSTRAINT ITM_VOL_FCT_NN       NOT NULL,
WGT                  NUMBER            CONSTRAINT WGT_NN             NOT NULL,
HGT                  NUMBER            CONSTRAINT HGT_NN             NOT NULL,
WDT                  NUMBER            CONSTRAINT WDT_NN             NOT NULL,
DPH                  NUMBER            CONSTRAINT DPH_NN             NOT NULL,
CLM_SHL_FLG            VARCHAR2(40)      CONSTRAINT CLM_SHL_FLG_NN       NOT NULL,
REL_DTE                  DATE            CONSTRAINT REL_DTE_NN             NOT NULL,      
ITM_RTN_POL            VARCHAR2(240)      CONSTRAINT ITM_RTN_POL_NN       NOT NULL,
LAS_REQ_RTN_DTE            DATE,      
CLL_BCK_DTE            DATE,      
MNT_CDE                  VARCHAR2(1)      CONSTRAINT MNT_CDE_NN             NOT NULL,
CUR_DEC                  VARCHAR2(40)      CONSTRAINT CUR_DEC_NN             NOT NULL,
LAST_UPDATE_DATE      DATE,      
LAST_UPDATED_BY            NUMBER,      
CREATED_BY             NUMBER,      
CREATION_DATE             DATE,      
HDL_ORGANIZATION       VARCHAR2(240)      CONSTRAINT HDL_ORG_NN             NOT NULL,
VEW_RTE_CDE            VARCHAR2(10)      CONSTRAINT VEW_RTE_NN             NOT NULL,
ORGANIZATION_ID            NUMBER            CONSTRAINT ORG_ID_NN             NOT NULL,
COU                  VARCHAR2(1)      CONSTRAINT COU_NN             NOT NULL,
COR                  VARCHAR2(3)      CONSTRAINT COR_NN             NOT NULL,
INA_DTE                  DATE,
REA_DTE                  DATE,
CONSTRAINT XXINV_ITEM_MAINFRAME_PK PRIMARY KEY (COU,COR,ITM));

*************
control file:
*************
LOAD DATA
INFILE 'C:\Aman\Handleman\loaddata\item_maint.dat'
REPLACE INTO TABLE xyz
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS              
(EAN,
STS,
ITM,
ART,
TTL,
MER_CDE,
PRD_LNE,
CLS_CDE,
CTG_IND,
FOR_CDE,
SHP_CDE,
CAT,
SEG,
SUB_SEG,
MER_TYP,
VND,
MFG_LBL,
MFG_PFX,
MFG_SFX,
BSE_COS,
VND_MSL,
MSL,
OSP_FLG,
BOX_LOT,
ITM_VOL_FCT,
WGT,
HGT,
WDT,
DPH,
CLM_SHL_FLG,
REL_DTE       "validate_date(:REL_DTE)",
ITM_RTN_POL,
LAS_REQ_RTN_DTE "validate_date(:LAS_REQ_RTN_DTE)",
CLL_BCK_DTE       "validate_date(:CLL_BCK_DTE)",
MNT_CDE,
CUR_DEC,
LAST_UPDATE_DATE "validate_date(:LAST_UPDATE_DATE)",
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE       "validate_date(:CREATION_DATE)",
HDL_ORGANIZATION,
VEW_RTE_CDE,
ORGANIZATION_ID,
COU,
COR,
INA_DTE       "validate_date(:INA_DTE)",
REA_DTE       "validate_date(:REA_DTE)")

*************
function:
*************
CREATE OR REPLACE FUNCTION validate_date(test_date  IN VARCHAR2)
RETURN DATE IS
  v_return_date DATE;
BEGIN
  BEGIN
    IF test_date IS NOT NULL
    THEN
       v_return_date := TO_DATE(test_date, 'YYYYMMDD');
    ELSE
       v_return_date := NULL;
    END IF;
    EXCEPTION
      WHEN OTHERS
      THEN v_return_date := NULL;
  END;
RETURN v_return_date;
END validate_date;
/

*************
.dat file:
*************
0827969283523|I|1354311|SEEGER, PETE                  |ESSENTIAL PETE SEEGER, THE    |AM|01|013|Y|0|C |113|2000|2010|2000|03616|HDL|CK   |92835  |00007.81|00011.98|013.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20050419|RR|        |        |C|D|20060220|2725|2725|20050324|A|  |084|1|001|20060220|        |

0067003027229|I|1354312|LUCECITA                      |LUCE                          |AM|01|013|Y|0|C |103|0100|0150|2000|03616|HDL|D21S |30272-2|00009.20|00013.98|015.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20020604|NR|20051127|20051128|C|H|20060817|2725|2725|20020910|A|  |084|1|001|20050309|        |

0746105029729|A|1354318|BETWEEN THE BURIED AND ME     |ANATOMY OF                    |  |01|013|Y|0|C |105|0200|0210|2000|06906|HDL|VR   |7461050|00009.00|00013.98|014.98|D|030|001.3|00000000.50000|05.000|05.500|00.500|N|20060613|PR|        |        |C|D|20060612|2725|2725|20060503|A|  |084|1|001|        |        |

0827969365328|I|1354319|ARMSTRONG, LOUIS              |JAZZ MOODS: HOT               |AM|01|013|Y|0|C |155|0400|0490|2000|03616|HDL|CK   |93653  |00007.81|00011.98|014.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20050419|RR|        |        |C|D|20060227|2725|2725|20050324|A|  |084|1|001|20060227|        |

0016351512925|I|1354320|V/A JAZZ                      |TOUCH ME IN THE MORNING       |AM|01|013|Y|0|C |155|0400|0490|2000|04693|HDL|SHA  |5129   |00011.41|00017.98|019.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20050524|RR|        |        |C|D|20060227|2725|2725|20050324|A|  |084|1|001|20060227|        |
0
 
johnsoneSenior Oracle DBACommented:
Your problem is that in the date fields that are to be NULL, they contain strings of spaces.  Therefore the validate function you are using will not work, as the vlaue being passed in is not null, it is a string of 8 spaces.  If you modify the validate function to the function below, it should work.

CREATE OR REPLACE FUNCTION validate_date(test_date  IN VARCHAR2)
RETURN DATE IS
  v_return_date DATE;
BEGIN
  BEGIN
    IF replace(test_date, ' ') IS NOT NULL
    THEN
       v_return_date := TO_DATE(test_date, 'YYYYMMDD');
    ELSE
       v_return_date := NULL;
    END IF;
    EXCEPTION
      WHEN OTHERS
      THEN v_return_date := NULL;
  END;
RETURN v_return_date;
END validate_date;
/
0
 
gram77Author Commented:
johnsone:
I am again getting the same error even after modifying the function:

IF replace(test_date, ' ') IS NOT NULL

Log file output:

Record 65378: Rejected - Error on table XXINV_ITEM_MAINFRAME.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Record 65379: Rejected - Error on table XXINV_ITEM_MAINFRAME.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Record 65380: Rejected - Error on table XXINV_ITEM_MAINFRAME.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Date in bad file:
0827969283523|I|1354311|SEEGER, PETE                  |ESSENTIAL PETE SEEGER, THE    |AM|01|013|Y|0|C |113|2000|2010|2000|03616|HDL|CK   |92835  |00007.81|00011.98|013.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20050419|RR|        |        |C|D|20060220|2725|2725|20050324|A|  |084|1|001|20060220|        |

0067003027229|I|1354312|LUCECITA                      |LUCE                          |AM|01|013|Y|0|C |103|0100|0150|2000|03616|HDL|D21S |30272-2|00009.20|00013.98|015.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20020604|NR|20051127|20051128|C|H|20060817|2725|2725|20020910|A|  |084|1|001|20050309|        |

0746105029729|A|1354318|BETWEEN THE BURIED AND ME     |ANATOMY OF                    |  |01|013|Y|0|C |105|0200|0210|2000|06906|HDL|VR   |7461050|00009.00|00013.98|014.98|D|030|001.3|00000000.50000|05.000|05.500|00.500|N|20060613|PR|        |        |C|D|20060612|2725|2725|20060503|A|  |084|1|001|        |        |

0827969365328|I|1354319|ARMSTRONG, LOUIS              |JAZZ MOODS: HOT               |AM|01|013|Y|0|C |155|0400|0490|2000|03616|HDL|CK   |93653  |00007.81|00011.98|014.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20050419|RR|        |        |C|D|20060227|2725|2725|20050324|A|  |084|1|001|20060227|        |

0016351512925|I|1354320|V/A JAZZ                      |TOUCH ME IN THE MORNING       |AM|01|013|Y|0|C |155|0400|0490|2000|04693|HDL|SHA  |5129   |00011.41|00017.98|019.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20050524|RR|        |        |C|D|20060227|2725|2725|20050324|A|  |084|1|001|20060227|        |

0660662099424|A|1354321|FROM FIRST TO LAST            |AESTHETIC EP                  |  |01|013|Y|0|C |103|0100|0170|3000|09530|HDL|2    |1      |00006.50|00011.98|012.96|N|025|001.2|00000000.50000|05.000|05.500|00.500|N|20051220|RR|        |        |C|D|20070313|2725|2725|20060503|A|  |084|1|001|        |        |

0027297917323|A|1354322|SUBLIME- A TRIBUTE TO         |HAWAIIAN TRIBUTE TO SUBLIME:  |  |01|013|Y|0|C |103|0100|0170|2000|03700|HDL|CD   |9173   |00009.75|00016.98|018.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20060627|RR|        |        |C|D|20070313|2725|2725|20060503|A|  |084|1|001|        |        |

0027297914421|A|1354324|THREE DAYS GRACE - A TRIBUTE  |STRUNG OUT ON THREE DAYS GRACE|  |01|013|Y|0|C |103|0100|0110|2000|03700|HDL|CD   |9144   |00009.75|00016.98|018.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20060627|RR|        |        |A|D|20060503|2725|2725|20060503|A|  |084|1|001|        |        |

0602498551301|A|1354326|SOUNDTRACK                    |WAIST DEEP (ED)               |CL|01|013|Y|0|C |160|0400|0440|2000|07979|HDL|B0006|593-02 |00007.50|00010.98|013.96|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20060613|PR|        |        |C|D|20070606|2725|2725|20060503|A|  |084|1|001|        |        |

0724353900724|I|1354328|BON VOYAGE                    |THE RIGHT AMOUNT              |  |01|013|Y|0|C |145|0300|0370|2000|02958|HDL|G21C |39007-2|00006.60|00009.98|011.98|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20021008|PC|        |        |C|D|20050325|2725|2725|20020910|A|  |084|1|001|20040903|        |
0
 
gram77Author Commented:
This error comes after inserting 65600 rows.
0
 
johnsoneSenior Oracle DBACommented:
Then that has nothing to do with the function.  I loaded the test data you have supplied with the control file and changed function and everything works fine.

Can you check in your alert log?  More information about the ORA-00600 should be there.  ORA-00600 is an internal Oracle error and is not related to SQL*Loader.
0
 
gram77Author Commented:
In the sqlldr command, I removed direct=true and it worked without giving error!
Can anyone tell me why was Oracle giving internal error on direct load?
0
 
johnsoneSenior Oracle DBACommented:
Can you give the full error from the alert log?  The parameters on the ORA-00600 can tell why you get the error.
0
 
gram77Author Commented:
**********************
Log file for sqlldr:
**********************

SQL*Loader: Release 9.2.0.1.0 - Production on Tue Aug 28 16:11:31 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Control File:   item_maint.ctl
Data File:      item_maint.dat
  Bad File:     item_maint.bad
  Discard File: item_maint.dsc;
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table xyz, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EAN                                 FIRST     *   |       CHARACTER            
STS                                  NEXT     *   |       CHARACTER            
ITM                                  NEXT     *   |       CHARACTER            
ART                                  NEXT     *   |       CHARACTER            
TTL                                  NEXT     *   |       CHARACTER            
MER_CDE                              NEXT     *   |       CHARACTER            
PRD_LNE                              NEXT     *   |       CHARACTER            
CLS_CDE                              NEXT     *   |       CHARACTER            
CTG_IND                              NEXT     *   |       CHARACTER            
FOR_CDE                              NEXT     *   |       CHARACTER            
SHP_CDE                              NEXT     *   |       CHARACTER            
CAT                                  NEXT     *   |       CHARACTER            
SEG                                  NEXT     *   |       CHARACTER            
SUB_SEG                              NEXT     *   |       CHARACTER            
MER_TYP                              NEXT     *   |       CHARACTER            
VND                                  NEXT     *   |       CHARACTER            
MFG_LBL                              NEXT     *   |       CHARACTER            
MFG_PFX                              NEXT     *   |       CHARACTER            
MFG_SFX                              NEXT     *   |       CHARACTER            
BSE_COS                              NEXT     *   |       CHARACTER            
VND_MSL                              NEXT     *   |       CHARACTER            
MSL                                  NEXT     *   |       CHARACTER            
OSP_FLG                              NEXT     *   |       CHARACTER            
BOX_LOT                              NEXT     *   |       CHARACTER            
ITM_VOL_FCT                          NEXT     *   |       CHARACTER            
WGT                                  NEXT     *   |       CHARACTER            
HGT                                  NEXT     *   |       CHARACTER            
WDT                                  NEXT     *   |       CHARACTER            
DPH                                  NEXT     *   |       CHARACTER            
CLM_SHL_FLG                          NEXT     *   |       CHARACTER            
REL_DTE                              NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:REL_DTE)"
ITM_RTN_POL                          NEXT     *   |       CHARACTER            
LAS_REQ_RTN_DTE                      NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:LAS_REQ_RTN_DTE)"
CLL_BCK_DTE                          NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:CLL_BCK_DTE)"
MNT_CDE                              NEXT     *   |       CHARACTER            
CUR_DEC                              NEXT     *   |       CHARACTER            
LAST_UPDATE_DATE                     NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:LAST_UPDATE_DATE)"
LAST_UPDATED_BY                      NEXT     *   |       CHARACTER            
CREATED_BY                           NEXT     *   |       CHARACTER            
CREATION_DATE                        NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:CREATION_DATE)"
HDL_ORGANIZATION                     NEXT     *   |       CHARACTER            
VEW_RTE_CDE                          NEXT     *   |       CHARACTER            
ORGANIZATION_ID                      NEXT     *   |       CHARACTER            
COU                                  NEXT     *   |       CHARACTER            
COR                                  NEXT     *   |       CHARACTER            
INA_DTE                              NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:INA_DTE)"
REA_DTE                              NEXT     *   |       CHARACTER            
    SQL string for column : "validate_date(:REA_DTE)"


Record 65378: Rejected - Error on table xyz.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Record 65379: Rejected - Error on table xyz.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Record 65380: Rejected - Error on table xyz.
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []




Table xyz:
  229360 Rows successfully loaded.
  1 Row 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:                 254646 bytes(21 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:        229361
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Tue Aug 28 16:11:31 2007
Run ended on Tue Aug 28 18:15:40 2007

Elapsed time was:     02:04:08.58
CPU time was:         00:00:27.84
0
 
johnsoneSenior Oracle DBACommented:
That is the log from SQL*Loader.  There should be other message(s) in the database alert log.
0
 
gram77Author Commented:
Material on ora-600 errors:
1.ora-600 means untrapped error which shouldn't surface.

This can be resolved only using the ora-600 lookup tool on Metalink, as there are 1000s of untrapped errors.

2. Metalink is a better place to post this kind of a question (who can remember all those ORA-00600 errors anyway??).

Nevertheless, you'll find some information about possible causes for your error if you search
Metalink with keywords: ora-00600 [17302] . Don't use complete error line in search box, just the important part.
Then you can decide if you wish to open TAR.

3.Try searching the root cause for ORA-600 error with the following ORA-600 argument lookup tool

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=153788.1 

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 21
  • 10
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now