3

I am looking to repair a table of data in Oracle, ideally via unprivileged SQL, which has had UTF-8 data inserted into a UTF-8 database, using the Latin-1 character set by mistake.

The symbol β GREEK SMALL LETTER BETA should have gone into the database, but instead the two characters β have gone in...as the two UTF-8 characters Î LATIN CAPITAL LETTER I WITH CIRCUMFLEX followed by ² SUPERSCRIPT TWO.

This example code demonstrates the problem and the fix, however it only works with VARCHAR columns. As soon as a CLOB is used, the conversion fails:

-- This must return AL32UTF8 for this example to be valid
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

CREATE TABLE EXAMPLE (T VARCHAR2(20));
INSERT INTO EXAMPLE (T) VALUES ('Example β');
SELECT T FROM EXAMPLE; -- Should return 'Example β'
SELECT CONVERT(T, 'WE8ISO8859P1') FROM EXAMPLE;
UPDATE EXAMPLE SET T=CONVERT(T, 'WE8ISO8859P1');
SELECT T FROM EXAMPLE; -- Should return 'Example β', problem fixed
DROP TABLE EXAMPLE;

However if the VARCHAR2(20) is changed to CLOB then this no longer works. CONVERT() returns garbage characters. I can use TO_CHAR() to work around the problem, but eventually I get an error that the CLOB is longer than 4000 chars so TO_CHAR() fails.

Is there a way to get the above example working, when using a CLOB column that's longer than 4000 chars?

2
  • Before you start any conversion you should carefully check whether you have really wrong data in your DB or just a display issue of your client application. Use DUMP function for that, see also stackoverflow.com/questions/33783902/…. Actually it is fairly difficult to insert wrong data into DB (once I tried by intention but I did not manage it), either they are correct or Oracle automatically substitutes wrong data with placeholder ¿. Jun 6, 2017 at 7:35
  • It's definitely the wrong data. The full explanation is too long to fit in a comment, but we definitely have Latin-1 characters encoded as UTF-8 data. It is apparently easy to insert the wrong data into the DB, if the DB is set to the US7ASCII character set. This should only allow character codes 0-127 but Oracle will happily let you use the full 255 available characters. Depending on the client you either extract your original data unchanged, or you get � REPLACEMENT CHARACTER. Converting the whole DB to AL32UTF8 with this kind of incorrect data is the source of my problem.
    – Malvineous
    Jun 7, 2017 at 0:37

1 Answer 1

5

DBMS_LOB package has conversion functions as well. Unfortunately there is no support for converting CLOB to CLOB and change characterset in one step, so the data is first converted to BLOB, then back to CLOB.

CREATE TABLE EXAMPLE (T CLOB);
INSERT INTO EXAMPLE (T) VALUES ('Example β ');

begin
  for i in 1..12
  loop
    update example set t = t || t;
  end loop;
  commit;
end;
/

select dbms_lob.getlength(t) from example;

DBMS_LOB.GETLENGTH(T)
---------------------
                45056

Now the conversion part:

create or replace function repair_data (p_clob clob) return clob as
  l_blob blob; 
  l_dest_offset number := 1;
  l_src_offset number := 1;
  l_lang_context number := 0;
  l_warning number;
  l_result clob;
begin
  dbms_lob.createtemporary(l_blob, true, dbms_lob.call);
  dbms_lob.createtemporary(l_result, true, dbms_lob.call);
  dbms_lob.converttoblob(l_blob, p_clob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset,
                         nls_charset_id('WE8ISO8859P1'), l_lang_context, l_warning);
  l_dest_offset := 1;
  l_src_offset := 1;
  l_lang_context := 0;
  dbms_lob.converttoclob(l_result, l_blob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset,
                         nls_charset_id('AL32UTF8'), l_lang_context, l_warning);
  return l_result;
end;
/

select repair_data(T) from example;

REPAIR_DATA(T)                          
----------------------------- 
Example β Example β Example β ...

update example set t = repair_data(t);
...
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.