Solved

Oracle weird control file issue

Posted on 2015-02-08
4
223 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
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 setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

808 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