0

My filename .dat file includes 1000 records in below format.

SIC_ID|NAME|CHANGED_DATE|LOGICALLY_DELETED
110|Wheat|31/10/2010 29:46:00|N

Table in which I want to feed content has few more columns. I wish to leave these columns blank as content is not there in .dat file. Table Columns:

SIC_ID, NAME, CREATED_USER_ID ,CREATED_DATE ,CHANGED_USER_ID ,CHANGED_DATE,LOGICALLY_DELETED,RECORD_VERSION

My control file is as below:-

    OPTIONS (DIRECT=TRUE,SKIP=1)
    LOAD DATA CHARACTERSET WE8MSWIN1252
    INFILE "mic_file.dat"
    BADFILE "sql/mic_file.bad"
    REPLACE
    INTO TABLE SDS_SIC
    FIELDS TERMINATED BY "|"
    TRAILING NULLCOLS
    (SIC_ID, NAME,
DATE "DD/MM/YYYY HH24:MI:SS" NULLIF (CHANGED_DATE=BLANKS),
LOGICALLY_DELETED)

After running SQL*Loader, I see below error:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SIC_ID                              FIRST     *   |       CHARACTER
NAME                                 NEXT     *   |       CHARACTER
CHANGED_DATE                         NEXT     *   |       CHARACTER
LOGICALLY_DELETED                    NEXT     *   |       CHARACTER

Record 1: Rejected - Error on table SDS_SIC, column CHANGED_DATE.
ORA-26041: DATETIME/INTERVAL datatype conversion error

last 2 lines of error is thrown multiple times. This is fixed now :)

Error 2: LOGICALLY_DELETED has only 2 possible values - Y or N.

Record 51: Rejected - Error on table SDS_SIC, column LOGICALLY_DELETED.
ORA-12899: value too large for column LOGICALLY_DELETED (actual: 2, maximum: 1)

Above error is displayed multiple times.

Umesh Patil
  • 10,475
  • 16
  • 52
  • 80

2 Answers2

2

Remember that the control file column list is in the order fields are in the data file. Data is matched to the table columns by name. Your control file has the 3rd and 4th fields mapped to FILLER, that's why they are blank. FILLER only applies to a field in the data file you don't want.

You need something like this only in your column list section, the TRAILING NULLCOLS will handle the rest of the columns of the table:

(SIC_ID, 
 NAME, 
 CHANGED_DATE  DATE "DD/MM/YYYY HH24:MI:SS" NULLIF (CHANGED_DATE=BLANKS),
 LOGICALLY_DELETED
)

See this recent post which happens to describe the relationship by giving an example: Skipping data fields while loading delimited data using SQLLDR

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Thanks! Its useful. removed filler.but I wish to leave extra columns in table blank. – Umesh Patil Apr 30 '15 at 13:44
  • 2
    That's why you have TRAILING NULLCOLS :-) Columns in the table that are not named in the control file will be blank (of course assuming they allow nulls). From the docs: The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. – Gary_W Apr 30 '15 at 13:45
  • Correct. I checked. but I get an error. Kindly see updated question – Umesh Patil Apr 30 '15 at 13:49
  • 1
    You need to tell SQL*Loader what format the date is in: CHANGED_DATE DATE "DD/MM/YYYY HH24:MI:SS" NULLIF (CHANGED_DATE =BLANKS) – Gary_W Apr 30 '15 at 13:53
  • Thank you. Well. but now I get Error 2 in question :( – Umesh Patil Apr 30 '15 at 14:00
  • 2
    SQL*Loader sees 2 bytes of data for LOGICALLY_DELETED. Could be a non-viewable character there in the data file, or could be due to a characterset conflict. Also this is common if you are loading data on *NIX, but the data file came from a windows system where end-of-line is 2 characters but on *NIX the end-of-line character is a single one: http://blog.codinghorror.com/the-great-newline-schism/ – Gary_W Apr 30 '15 at 14:08
  • Everything is in UNIX, though file is uploaded on server from windows. – Umesh Patil Apr 30 '15 at 14:12
  • View the data file in an editor that will show you hex, to verify what is there after the Y or N. I believe the command is od -cx to display it in hex? I'm rusty on *NIX. You may need to run the file through sed to strip out the CTRL-M's before loading via SQL*Loader. Start a new question for that, this one has been answered. – Gary_W Apr 30 '15 at 14:16
  • Okay. Thanks for your effort ! – Umesh Patil Apr 30 '15 at 14:26
0

You can go to the MySQL command line client and for inserting the values into the desired columns you should do the following: like as you want to insert the value into

SIC_ID|NAME|CHANGED_DATE|LOGICALLY_ DELETED

And not in those extra columns. You should type:

insert into 'whatever the table name is'(SIC_ID,NAME,CHANGED_DATE,LOGICALLY_DELETED)
values(112,'wheat','31/10/2010 19:46:00',N);

Use single inverted commas only there where you have taken the property varchar of the columns try it....it'll work

LittlePanda
  • 2,496
  • 1
  • 21
  • 33
Ishak Ayub
  • 7
  • 4