SQL & PL/SQL

Hi all,

I have a CLOB containing a XML code and all XML tags where created using the DB charset: WE8ISO8859P15.

For example:

<?xml version="1.0" encoding="ISO-8859-1"?>
<certidao xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <funcionario>
        <nome>José Moreira</nome>
        <funcao>Head of the Administration Office</funcao>
        <instituicao>Faculty of Engineering</instituicao>
        <assinatura>ADMNISTRATIVE RESPONSIBLE</assinatura>
        <titulo> </titulo>
    </funcionario>
</certidao>

I need to maintain this charset value.

But now I need to convert all my CLOB data into UTF-8 charset for external requirements and stored this new CLOB in a table column.

I can't change the charset of the session. I've tried to use a function

| | FUNCTION convert_xml (pcl_xml IN CLOB) |
|     | RETURN CLOB |
| | IS |
|     | lcb_return   CLOB; |
| | BEGIN |
|     | SELECT CONVERT (pcl_xml, 'WE8ISO8859P1', 'AL32UTF8') |
|       | INTO lcb_return |
|       | FROM DUAL; |
|     | RETURN lcb_return; |
| |
END convert_xml;

This function apparently works, but when I try to validate the XML content (after I replace the encoding="ISO-8859-1" to encoding="UTF-8") the validation software tells me that I have some characters that should not be present in a file using UTF-8 encoding.

Any help?

Thanks in advance.

Filipe Almeida