Improve company productivity with a Business Account.Sign Up

x
?
Solved

Oracle weird control file issue

Posted on 2015-02-08
4
Medium Priority
?
284 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 36

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 36

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
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…

602 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