sql loder problem... [message #290382] |
Fri, 28 December 2007 11:46 |
kumar.joy
Messages: 69 Registered: August 2007 Location: morrisville
|
Member |
|
|
Hi,
I am facing one problem with sql loder. please find details.
cause: I have a table with 7 columns. out of 7 one column had primary key. please find my table desc and control file.
CREATE TABLE PCS_CAUSE_CDS
(
PCS_CAUSE_CD_ID NUMBER(22) NOT NULL,
PCS_CAUSE_CD NUMBER(22) NOT NULL,
APPLICBL_PRODS VARCHAR2(256 BYTE),
CAUSE_CAT VARCHAR2(256 BYTE),
CAUSE_SUB_CAT VARCHAR2(256 BYTE),
CAUSE_DESC VARCHAR2(256 BYTE),
CAUSE_STAT VARCHAR2(256 BYTE)
)
ALTER TABLE PCS_CAUSE_CDS ADD (
CONSTRAINT XPKPCS_CAUSE_CODES PRIMARY KEY (PCS_CAUSE_CD_ID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
----------------------------------------------------------------
This is my control file
load data
infile 'D:\pcs_cause_codes.CSV'
badfile 'D:\pcs_cause_code.bad'
into table PCS_CAUSE_CDS
fields terminated by "," optionally enclosed by '"'
(PCS_CAUSE_CD,
APPLICBL_PRODS,
CAUSE_CAT,
CAUSE_SUB_CAT,
CAUSE_DESC,
CAUSE_STAT)
-----------------------------------------------------------------
My csv file have 6 columns seperated by ","
In my csv file i don't have any values to primary key column.
In this case how do i run sql loder. is there any way to insert data in primary key column.
i don't have any sequence on this table based on primary key column.
how do i load data using sql loader.
do i need to create any sequence on the primary key column.
if yes how do i update my control file.
|
|
|
|
Re: sql loder problem... [message #290401 is a reply to message #290384] |
Fri, 28 December 2007 13:15 |
kumar.joy
Messages: 69 Registered: August 2007 Location: morrisville
|
Member |
|
|
Michel,
Thanks for your reply. i am getting one more problem. i am not able to analyze it. could you please help me.
i created one sequence(pcs_cause_cds_seq) on PCS_CAUSE_CDS and i have changed the my control file like this
load data
infile 'D:\pcs_cause_codes.CSV'
badfile 'D:\pcs_cause_code.bad'
into table PCS_CAUSE_CDS
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
( PCS_CAUSE_CD_ID "PCS_CAUSE_CDS_SEQ.NEXTVAL",
PCS_CAUSE_CD,
APPLICBL_PRODS,
CAUSE_CAT,
CAUSE_SUB_CAT,
CAUSE_DESC,
CAUSE_STAT)
--------------------------------------------------------
when i ran sqlldr i am getting this error in logfile.
please find the error
Record 1: Rejected - Error on table PCS_CAUSE_CDS, column PCS_CAUSE_CD.
ORA-01722: invalid number
Record 2: Rejected - Error on table PCS_CAUSE_CDS, column PCS_CAUSE_CD.
ORA-01722: invalid number
what could be the problem.
|
|
|
|