Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle weird control file issue

Posted on 2015-02-08
4
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

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 35

Accepted Solution

by:
johnsone earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
OfficeMate Freezes on login or does not load after login credentials are input.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
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…

636 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