Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> integer vs integer external in .ctl file

integer vs integer external in .ctl file

From: cumin <jkilbourne_at_gmail.com>
Date: 8 Sep 2005 13:48:35 -0700
Message-ID: <1126212515.739051.49110@g44g2000cwa.googlegroups.com>


I had difficulty loading some data using sqlldr; A coworker offered a solution but no explanation as to why it works.

Looking on the web, I found that integer values in the .ctl file can be changed to char() with a number of digits spcified in the parens. Changing my .ctl file in that way does work. The other solution is to change "integer" to "integer external" in the .ctl file. My question is, "Why is this so?" I didn't find this well-documented.

Thank you,
John

kilbourj_at_MID2006> desc rxnsab;

 Name                   Null?     Type
 ----------------       --------   ----
 VCUI                             VARCHAR2(8)
 RCUI                             VARCHAR2(8)
 VSAB                             VARCHAR2(20)
 RSAB                   NOT NULL  VARCHAR2(20)
 SON                              VARCHAR2(3000)
 SF                               VARCHAR2(20)
 SVER                             VARCHAR2(20)
 VSTART                           VARCHAR2(10)
 VEND                             VARCHAR2(10)
 IMETA                            VARCHAR2(10)
 RMETA                            VARCHAR2(10)
 SLC                              VARCHAR2(1000)
 SCC                              VARCHAR2(1000)
 SRL                              NUMBER(38)
 TFR                              NUMBER(38)
 CFR                              NUMBER(38)
 CXTY                             VARCHAR2(50)
 TTYL                             VARCHAR2(100)
 ATNL                             VARCHAR2(1000)
 LAT                              VARCHAR2(3)
 CENC                             VARCHAR2(20)
 CURVER                           VARCHAR2(1)
 SABIN                            VARCHAR2(1)
 SSN                              VARCHAR2(3000)
 SCIT                             VARCHAR2(4000)


Here is the entire .ctl file:

options (direct=true, errors=0)
load data
infile 'RXNSAB.RRF' "str X'7c0d0a'"
badfile 'RXNSAB.bad'
discardfile 'RXNSAB.dsc'
truncate
into table RXNSAB
fields terminated by '|'
trailing nullcols
(

   VCUI	char(8),
   RCUI	char(8) ,
   VSAB	char(20) ,
   RSAB	char(20) ,
   SON	char(3000) ,
   SF	char(20) ,
   SVER	char(20),
   VSTART	char(10),
   VEND	char(10),
   IMETA	char(10) ,
   RMETA	char(10),
   SLC	char(1000),
   SCC	char(1000),
   SRL	integer,
   TFR	integer,
   CFR	integer,
   CXTY	char(50),
   TTYL	char(50),
   ATNL	char(1000),
   LAT	char(3),
   CENC	char(20) ,
   CURVER	char(1) ,
   SABIN	char(1) ,
   SSN	char(3000) ,
   SCIT	char(4000)

) Received on Thu Sep 08 2005 - 15:48:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US