Tuesday, 10 September 2013

Oracle characterset conversion

Oracle characterset conversion

I will try to include all the relevant information..
DB: oracle 11gr2
I'm trying to convert a table column from 'WE8IS08859P1' to 'AL32UTF8' and
find out all rowids whose column data length exceeds 4000 char. The idea
is to identify rows which exceed 4000, so that we can truncate data before
import.
I tried the below select query to get MAX length but it fails.
SELECT MAX(length(CONVERT(comment1,'AL32UTF8'))) FROM comments;
The above select fails with
"ORA-01401: inserted value too large for column"
Also below doesn't display any result
SELECT length(CONVERT(comment,'AL32UTF8')) as c1
FROM comments
WHERE length(c1) > 4000;
I researched in the Oracle manual but can't get much help. Has anybody
here faced such an issue in the past?
I know about the Oracle csscan utility but I'm trying not to use it.
Thanks heaps in advance.

No comments:

Post a Comment