SQL & PL/SQL

Hi,

select * from v$version;
1 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
2 PL/SQL Release 10.2.0.3.0 - Production
3 CORE 10.2.0.3.0 Production
4 TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
5 NLSRTL Version 10.2.0.3.0 - Production

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
1 WE8MSWIN1252

select nls_charset_id('WE8ISO8859P1') csid from dual;
1 31


I have an application, passing a xml file to the database as a blob.

My table stores it as a clob, using a stored procedure that calls a function to make the conversion.

FUNCTION FNC_Blob_Para_Clob(pBLOB BLOB) RETURN CLOB IS

nCLOB CLOB;
nSRC_OFFSET NUMBER;
nDEST_OFFSET NUMBER;
nBLOB_CSID NUMBER := DBMS_LOB.DEFAULT_CSID;
nLANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
nWARNING NUMBER;
nAMOUNT NUMBER;

BEGIN
IF DBMS_LOB.GETLENGTH(pBLOB) > 0 THEN
DBMS_LOB.CREATETEMPORARY(nCLOB, TRUE);
nSRC_OFFSET := 1;
nDEST_OFFSET := 1;
nAMOUNT := DBMS_LOB.GETLENGTH(pBLOB);
DBMS_LOB.CONVERTTOCLOB( nCLOB
, pBLOB
, nAMOUNT
, nSRC_OFFSET
, nDEST_OFFSET
, 1
, nLANG_CONTEXT
, nWARNING);
RETURN nCLOB;
ELSE
nCLOB := TO_CLOB('');
RETURN nCLOB;
END IF;
DBMS_LOB.FREETEMPORARY(nCLOB);
END;

After inserting the record, some characters are changed to ¿¿ (ã, à, etc.)

Couldn't find a specific solution to my problem.

Could somebody point out what I'm doing wrong?!


Thanks a lot!