Home » RDBMS Server » Server Utilities » sql loder problem... (oracle 10g)
sql loder problem... [message #290382] Fri, 28 December 2007 11:46 Go to next message
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 #290384 is a reply to message #290382] Fri, 28 December 2007 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

do i need to create any sequence on the primary key column.

This is the most used way.

Quote:

how do i update my control file.

PCS_CAUSE_CD_ID "mysequence.NEXTVAL" 


You can also use SEQUENCE statement from SQL*Loader:
PCS_CAUSE_CD_ID SEQUENCE(COUNT or MAX or an integer, increment)

if COUNT sequence number starts from the number of rows plus the increment in the table
if MAX, the sequence starts at the maximum value in the column plus the increment.

Regards
Michel
Re: sql loder problem... [message #290401 is a reply to message #290384] Fri, 28 December 2007 13:15 Go to previous messageGo to next message
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. 
Re: sql loder problem... [message #290402 is a reply to message #290401] Fri, 28 December 2007 13:23 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem comes from your data.
Loader is expected a number and it found something else.

Regards
Michel
Previous Topic: Can we have multiple delimiters in one control file?
Next Topic: import indexes to a specified tablespace
Goto Forum:
  


Current Time: Fri Dec 02 12:38:34 CST 2016

Total time taken to generate the page: 0.09302 seconds