Solved

Oracle weird control file issue

Posted on 2015-02-08
4
203 Views
Last Modified: 2015-02-12
Hi,

I have control file with following option

OPTIONS(DIRECT=TRUE,ROWS=100,BINDSIZE=209700000,readsize=209700000)
load data 
infile 'd:\test.DH' 
"str '\n'"
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols

Open in new window


sample or records, the terminator is "!"

9334!376!15950!9109!0!29109!109!0!!10003!05.02.2015 03:51:27!05.02.2015 03:51:46!05.02.2015 03:51:27!0!0!0!S!00c08309ed178b3f!005683540!6829109!079015!0!0!!!0!F!299!!!0!0!!0!-1, 0, -1, 1423075906663, 0, 0, 0!{, 1, 24307, 3000-12-31 23:59:59, 0}!!{60200103, 0, 0, 0, 0, 0, 2, 2, 1, 0, 0}!!!!!!!!!!!!!!!!=2, =0, =0, =0, =255, =829109, =510, , =1!1!00067!!!F,079015,,2993007,290009,5,02993007,005683540,6,6829109,,,,010006743081,0,10006743081,5,F,,,,290009,2079015,2079015,829109,93007,079015,2079015,829109,0,,0,07000,,,0,,,,,'00c08309ed178b3fH',,,,,,,0,0,0,0,0,0,0,299,0,,a2040000005b6424,7205,36899550,
338!8376!11230!333777!0!33777!333777!0!!10003!05.02.2015 03:51:04!05.02.2015 03:51:14!05.02.2015 03:51:04!0!0!0!S!6d!004382577!3333777!3407582!0!0!!!0!F!299!!!0!0!!0!-1, 0, -1, 1423075874285, 0, 0, 0!{, 1, 24927, 3000-12-31 23:59:59, 0}!!{60200103, 0, 0, 0, 0, 0, 2, 2, 1, 0, 0}!!!!!!!!!!!!!!!!=2, =0, =0, =0, =255, =33777, =600, , =1!10595!02020!!!F,3407582,,993001,20000,5,993001,004382577,6,3333777,,,,010595,0,0202010595,5,F,,,,220000,407582,407582,33777,993001,,407582,03407582,3333777,0,,0,5874000,,,0,,,,,'6dH',,,,,,,0,0,0,0,0,0,0,299,0,_1281,a2820000005d213d,7205,36899550,

Open in new window


when I run this in I get exceed

Record 1: Rejected - Error on table name, column logs.
Field in data file exceeds maximum length

Open in new window


this field is the last column of the record... the column is 3000Byte... I know it' snot the issue of the length of the record as I tried importing the same file with navicate and it loaded all without any issue... there something wrong with str... and it try to load all data on column logs

thanks
0
Comment
Question by:hi4ppl
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40596850
Can you post the full  control file you are using as well as the create statement for the table you are loading into?  That way someone can replicate what you are seeing.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40596893
Hi,

in my control file there is 90 fields and that is why I could not put all of them... as I know there is no issue in table it self but control file options... as I manually load the same file with navicate to check if it's true but no it go through and didn't give any error it load all records... so something is wrong with option of control file where it does not go to next line and treat the rest of records as one record.....

and the other reason is that 90 fields is some sensitive records is their... to remove that and also create statement would be a lot of time consumption and maybe I do mistake removing sensitive data... it somehow don't recognize end of the line and think it's all one field data
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 40596919
From what I could figure out, there are 59 fields of data in the sample that you provided.

I created a table with this statement:
drop table name;
create table name (
col01 varchar2(3000 byte),
col02 varchar2(3000 byte),
col03 varchar2(3000 byte),
col04 varchar2(3000 byte),
col05 varchar2(3000 byte),
col06 varchar2(3000 byte),
col07 varchar2(3000 byte),
col08 varchar2(3000 byte),
col09 varchar2(3000 byte),
col10 varchar2(3000 byte),
col11 varchar2(3000 byte),
col12 varchar2(3000 byte),
col13 varchar2(3000 byte),
col14 varchar2(3000 byte),
col15 varchar2(3000 byte),
col16 varchar2(3000 byte),
col17 varchar2(3000 byte),
col18 varchar2(3000 byte),
col19 varchar2(3000 byte),
col20 varchar2(3000 byte),
col21 varchar2(3000 byte),
col22 varchar2(3000 byte),
col23 varchar2(3000 byte),
col24 varchar2(3000 byte),
col25 varchar2(3000 byte),
col26 varchar2(3000 byte),
col27 varchar2(3000 byte),
col28 varchar2(3000 byte),
col29 varchar2(3000 byte),
col30 varchar2(3000 byte),
col31 varchar2(3000 byte),
col32 varchar2(3000 byte),
col33 varchar2(3000 byte),
col34 varchar2(3000 byte),
col35 varchar2(3000 byte),
col36 varchar2(3000 byte),
col37 varchar2(3000 byte),
col38 varchar2(3000 byte),
col39 varchar2(3000 byte),
col40 varchar2(3000 byte),
col41 varchar2(3000 byte),
col42 varchar2(3000 byte),
col43 varchar2(3000 byte),
col44 varchar2(3000 byte),
col45 varchar2(3000 byte),
col46 varchar2(3000 byte),
col47 varchar2(3000 byte),
col48 varchar2(3000 byte),
col49 varchar2(3000 byte),
col50 varchar2(3000 byte),
col51 varchar2(3000 byte),
col52 varchar2(3000 byte),
col53 varchar2(3000 byte),
col54 varchar2(3000 byte),
col55 varchar2(3000 byte),
col56 varchar2(3000 byte),
col57 varchar2(3000 byte),
col58 varchar2(3000 byte),
col59 varchar2(3000 byte));

Open in new window

Since I am not going to try to determine type, I created them all with the size you say is failing

Then I attempted to load it with this control file:
OPTIONS(DIRECT=TRUE,ROWS=100,BINDSIZE=209700000,readsize=209700000)
load data 
infile 'x.dat' 
"str '\n'"
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols
(
col01,
col02,
col03,
col04,
col05,
col06,
col07,
col08,
col09,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col18,
col19,
col20,
col21,
col22,
col23,
col24,
col25,
col26,
col27,
col28,
col29,
col30,
col31,
col32,
col33,
col34,
col35,
col36,
col37,
col38,
col39,
col40,
col41,
col42,
col43,
col44,
col45,
col46,
col47,
col48,
col49,
col50,
col51,
col52,
col53,
col54,
col55,
col56,
col57,
col58,
col59)

Open in new window

The only change is the name of the file loading from and the addition of the column list.

It loaded just fine.  No errors, no problem, nothing.  2 rows successfully loaded.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40607230
Hi ... thanks for your help... I had to mentioned the character in control file and it works now...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now