Question

Problems with Oracle data type "CHAR (10 CHAR)"

E
Hi all,

 

 

I have two Oracle spatial tables that are carbon copies of each other, both contain an attribute KL_CODE of Oracle type "CHAR (10 CHAR)". When I use an Oracle Spatial Object reader, it reads this attribute as "CHAR (40)". This is the byte size of "10 CHAR" so only half incorrect, it seems :) 

 

 

When running a workspace that copies all data from the input table to the output table, the error I get is ORA-12899: value too large for column "[SCHEMA]"."[TABLE]"."KL_CODE" (actual: 40, maximum: 10). This happens on the 1st feature, for which KL_CODE contains the value "O00007    " (note the spaces).

 

 

I have tried: AttributeTrimmer, SubstringExtractor, changing attribute type on input and output table to "CHAR(10)", nothing so far changes the error. Version is FME 2015.1, Oracle 11

 

 

Thanks in advance!

 

Eva

 

 

 

3 replies

pratap
Rank
Badge +2
Hi,

 

 

When we read attributes which have CHAR, it will read with spaces at the end in oracle. Similarly, when we write attributes which have CHAR, it will add the spaces at the end in oracle. If you have problem while writing then change the TYPE in writer.
david_r
Rank
Userlevel 4
Hi

 

 

I believe that CHAR(10 CHAR) means a field sized for 10 unicode characters, which is not the same as 10 bytes. See here for a longer discussion: http://stackoverflow.com/questions/81448/difference-between-byte-and-char-in-column-datatypes

 

 

If you send your KL_CODE attribute to the Logger before writing it back to Oracle, what does it say?

 

 

David
E
Thanks for the answers. I ended up changing the field type from CHAR(10 CHAR) to VARCHAR. I don't think FME reads/writes the original data type correctly, and I haven't been able to find a solution that works.

 

 

@Pratap: changing the type in the writer doesn't work (I tried that already). @David R: the output says "O00007    ", this is 10 characters, but Oracle throws the error regardless.

 

 

Eva

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.