Oracle weird control file issue

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
LVL 1
hi4pplAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
hi4pplAuthor Commented:
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
johnsoneSenior Oracle DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hi4pplAuthor Commented:
Hi ... thanks for your help... I had to mentioned the character in control file and it works now...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.