Home » SQL & PL/SQL » SQL & PL/SQL » External Table and Blank spaces (Oracle 12C Windows NT)
External Table and Blank spaces [message #652532] Mon, 13 June 2016 01:49 Go to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
I have a table called material_number with material_number of varchar2(18).
I have a text file with data. Below is the script I used to load the data.
But I got few records not loaded in to the table saying the log as value is too large for column.
Attached is my text file for your reference also added the log details in quote.

Request your help to resolve this issue.

create table mat_num
    (
MATERIAL_NUMBER varchar2(18))
   organization external
	   (type oracle_loader
	    default directory MIGRATION_DIR
	    access parameters
	    (
	     records delimited BY '\n'
	     BADFILE MIGRATION_DIR:'mat_num_xt.bad'
	     LOGFILE MIGRATION_DIR:'mat_num_xt.log'
	     NODISCARDFILE
	     CHARACTERSET UTF8
	     STRING SIZES ARE IN CHARACTERS 
	     READSIZE 134217728
	     SKIP 1
	     fields optionally enclosed by '"' LRTRIM
     missing field values are null
     (
     		  MATERIAL_NUMBER CHAR(4000)
		)
   )
    location ('mat_#.txt')
   )
	parallel 2
   reject limit unlimited
   nomonitoring;
Quote:


LOG file opened at 06/13/16 02:37:58

Field Definitions for table MAT_NUM
Record format DELIMITED, delimited by

Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

MATERIAL_NUMBER CHAR (4000)
Trim whitespace from left and right
error processing column MATERIAL_NUMBER in row 266 for datafile /var/opt/oracle/prsdata/mat_#.txt
ORA-12899: value too large for column MATERIAL_NUMBER (actual: 20, maximum: 24)


Thanks for your support.
  • Attachment: mat_#.txt
    (Size: 20.89KB, Downloaded 306 times)
Re: External Table and Blank spaces [message #652534 is a reply to message #652532] Mon, 13 June 2016 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post complete log file and bad file.
But for me data in lines 31 and 32, for instance, exceed 18 characters: I count 22:
"""ITMONITOR 19""""-C"""
"""ITMONITOR 19""""-O"""

[Updated on: Mon, 13 June 2016 02:29]

Report message to a moderator

Re: External Table and Blank spaces [message #652535 is a reply to message #652532] Mon, 13 June 2016 02:34 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's in the bad file?
Re: External Table and Blank spaces [message #652537 is a reply to message #652534] Mon, 13 June 2016 02:54 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
here is the bad file for your reference. It has only one entry.
Re: External Table and Blank spaces [message #652538 is a reply to message #652534] Mon, 13 June 2016 02:54 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
Here is the complete log file for your reference.
Re: External Table and Blank spaces [message #652539 is a reply to message #652534] Mon, 13 June 2016 02:56 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
for the below two records if I changed the size to varchar2(24) I'm able to insert those records.
But again one record is failing which is captured in the bad file and the size of the rejected record is comes within the varchar2(24) limit.
And unable to find where it execeeds the size.

Request your help.
Re: External Table and Blank spaces [message #652540 is a reply to message #652539] Mon, 13 June 2016 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is your database character set?

Re: External Table and Blank spaces [message #652541 is a reply to message #652540] Mon, 13 June 2016 03:23 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
My database characterset is as follows.

NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_CHARACTERSET=AL32UTF8
Re: External Table and Blank spaces [message #652542 is a reply to message #652541] Mon, 13 June 2016 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This explains the error.
In this line:
"001599-010        "
spaces are not spaces but ctl-spaces which are coded with 2 bytes in your character set and so exceed the limit of 24 bytes you told Oracle (as I bet you have nls_length_semantics set to BYTE).

Re: External Table and Blank spaces [message #652543 is a reply to message #652542] Mon, 13 June 2016 04:21 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Yes mike you are correct. I checked the NLS_DATABASE_PARAMETERS table where the NLS_LENGTH_SEMANTICS=BYTE is defined.
So please guide me the next step.

Thanks.
Re: External Table and Blank spaces [message #652544 is a reply to message #652543] Mon, 13 June 2016 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Set it to CHAR to prevent further problems
2/ Try "ALTER TABLE mat_num modify MATERIAL_NUMBER varchar2(24 CHAR);"

Re: External Table and Blank spaces [message #652545 is a reply to message #652544] Mon, 13 June 2016 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But note that the data will then not be trimmed as these are not real spaces.
If you want them to be trimmed then you have to fix your file at OS level (or using a preprocessor command).

Re: External Table and Blank spaces [message #652584 is a reply to message #652545] Tue, 14 June 2016 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: External Table and Blank spaces [message #652604 is a reply to message #652584] Tue, 14 June 2016 09:55 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi Mike,

Many thanks for your guidance. It works well.
I'm now able to load the data with the original size with your inputs.
Between that how did you find the ctl spaces?

As I have connected to VPN and couldn't check the forum from last day, apologies for late reply.


Thanks.
Re: External Table and Blank spaces [message #652606 is a reply to message #652604] Tue, 14 June 2016 11:01 Go to previous message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have a spirit of hacker, I read the file with an hexadecimal editor. Smile

Previous Topic: order of geting value from sequence by trigger
Next Topic: regexp replace
Goto Forum:
  


Current Time: Sun Aug 01 18:00:06 CDT 2021