title: How can I convert Oracle VARCHAR2 values to UTF-8 from a list of possible encodings?
body: <p>For legacy reasons, we have a VARCHAR2 column in our Oracle 10 database—where the character encoding is set to <code>AL32UTF8</code>—that contain some non-UTF-8 values. The values are always in one of these character sets:</p>
  
  <ul>
  <li>US-ASCII</li>
  <li>UTF-8</li>
  <li>CP1252</li>
  <li>Latin-1</li>
  </ul>
  
  <p>I've written a Perl function to fix broken values outside the database. For a value from this database column, it loops through this list of encodings and tries to convert the value to UTF-8. If the conversion fails, it tries the next encoding. The first one to convert without error is the value we keep. Now, I would like to replicate this functionality inside the database so that anyone can use it.</p>
  
  <p>However, all I can find for this is the <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm"><code>CONVERT</code> function</a>, which never fails, but inserts a replacement character for characters it does not recognize. So there is no way, as far as I can tell, to know when the conversion failed.</p>
  
  <p>Therefor, I have two questions:</p>
  
  <ol>
  <li>Is there some existing interface that tries to convert a string into one of list of encodings, returning the first that succeeds?</li>
  <li>And if not, is there some other interface that indicates failure if it's not able to convert a string to an encoding? If so, then I could write the previous function.</li>
  </ol>
  
  <hr>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>For reference, I have written this PostgreSQL function in PL/pgSQL that does exactly what I need:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION encoding_utf8(
      bytea
  ) RETURNS TEXT LANGUAGE PLPGSQL STRICT IMMUTABLE AS $$
  DECLARE
      encoding TEXT;
  BEGIN
      FOREACH encoding IN ARRAY ARRAY[
          'UTF8',
          'WIN1252',
          'LATIN1'
      ] LOOP
          BEGIN
              RETURN convert_from($1, encoding);
          EXCEPTION WHEN character_not_in_repertoire OR untranslatable_character THEN
              CONTINUE;
          END;
      END LOOP;
  END;
  $$;
  </code></pre>
  
  <p>I'd dearly love to know how to do the equivalent in Oracle.</p>