KUP-04020 Found record longer than buffer size reported

[MikeTruscott]

Hi everyone,

I have a csv file with 6 million records in. When I split it into quarters I can import it (via an external table) to my ORACLE database with no problems. However, when I try to import the whole lot I get the error:

"KUP-04020 Found record longer than buffer size reported"

I get no BAD file but at the same time can't query the database at all.

I've done google searches on the error and nothing suggests a solution. Given that what I'm trying to do is probably very small compared to what some of you guys do I believe there must be a solution.

Can anyone help please? I'm guessing that I need to access the buffer sizes and change them to something more suitable? Are there different buffer sizes and which are the ones I need to change....and how do I go about doing this?

Many thanks

Mike

 

[SantaMufasa]

Mike,

Could you please post your current external-table code?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MikeTruscott]

Hi Mufasa....we meet again!

My code is below.

-----------------------------------------------

CREATE TABLE EXT_MT
(
MT1 VARCHAR2(255),
MT2 VARCHAR2(255),
<etc up to MT100>
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY EXTERNAL_DATA
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION (MIKES_DATA.CSV')
)
PARALLEL
REJECT LIMIT UNLIMITED;

-----------------------------------------------

Does it help?

Cheers

Mike

 

[KenCunningham]

Hi Mike, there's a Russian (I think) thread here:

http://sql.ru/forum/actualthread.aspx?tid=82061

but there seems to have been an answer from someone in the US using the readsize variable (further down the thread). Hope this helps.

I want to be good, is that not enough?

 

[MikeTruscott]

Hello....

Have tried changing the READSIZE variable and I get a different error:

------------------------------------------------

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "readsize": expecting one of: "exit, (, reject"
KUP-01007: at line 4 column 2
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

-----------------------------------------------

I'll have a quick search for it now.....but any ideas?

Mike

 

[MikeTruscott]

Right....update!

I've been splitting the csv file up by size (of file) which meant that some lines were being cut off.

When I go into the new file and delete the incomplete records completely it works, but when I try to copy and paste the missing parts of lines back, to create complete records, then it fails....I think!

I've been doing the cutting and pasting in NotePad as WordPad doesn't seem to let me do it. Is notepad doing something to mess things up?

Cheers

Mike