Home » RDBMS Server » Server Utilities » sqlldr errors
| sqlldr errors [message #262813] |
Tue, 28 August 2007 05:37  |
gram77
Messages: 3
Registered: August 2007
|
Junior Member |
|
|
I am getting the following error while load data through sqlldr,
how do i get about it?
As columns that allowed null before are not null now, i have decided to
disable constraints, insert data into table , and enable constratins.
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: [], [], [], [], [], [], [], []
back to top
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));
*************
disable constraints:
*************
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT EAN_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT STS_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT ITM_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT ART_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT TTL_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MER_CDE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT PRD_LNE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT CLS_CDE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT CTG_IND_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT FOR_CDE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT SHP_CDE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT CAT_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT SEG_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT SUB_SEG_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MER_TYP_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT VND_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MFG_LBL_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MFG_PFX_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MFG_SFX_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT BSE_COS_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT VND_MSL_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MSL_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT OSP_FLG_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT BOX_LOT_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT ITM_VOL_FCT_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT WGT_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT HGT_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT WDT_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT DPH_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT CLM_SHL_FLG_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT REL_DTE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT ITM_RTN_POL_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT MNT_CDE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT CUR_DEC_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT HDL_ORG_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT VEW_RTE_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT ORG_ID_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT COU_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT COR_NN;
ALTER TABLE XXINV_ITEM_MAINFRAME DISABLE CONSTRAINT XXINV_ITEM_MAINFRAME_PK;
*************
control file:
*************
LOAD DATA
INFILE '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 replace(test_date, ' ') IS NOT NULL --(error seems to come from here)
THEN
v_return_date := TO_DATE(test_date, 'YYYYMMDD', NULL);
ELSE
v_return_date := NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN v_return_date := NULL;
END;
RETURN v_return_date;
END validate_date;
/
*************
.bad file: (item_maint.dat)
*************
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| |
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|2 725|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| | |
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 22 22:22:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
