This discussion is archived
2 Replies Latest reply: Aug 13, 2013 12:08 PM by KnightOfBlueArmor RSS

PLSQL changing XML file content

user12262355 Newbie
Currently Being Moderated

Hello Experts,

 

I have a task to do.

I have to replace special characters from an XML file placed on the server through PLSQL.

So that i created a plsql procedure.

PROCEDURE REMOVE_SPECIAL_CHARS (p_file_name IN VARCHAR2)

AS

l_tmp_clob       CLOB                             DEFAULT EMPTY_CLOB();

l_bfile          BFILE NULL;

l_dest_offset    INTEGER            := 1;

l_src_offset     INTEGER            := 1;

l_outfile        UTL_FILE.file_type;

lang_ctx         NUMBER             := DBMS_LOB.default_lang_ctx;

warning          NUMBER NULL;

l_dest_file      VARCHAR2(300) NULL;

BEGIN

mob_util_pck.log_write ('I','Inside REMOVE_SPECIAL_CHARS procedure(+)');

 

l_dest_file:=SUBSTR (p_file_name, (INSTR (p_file_name, '/', -1, 1))+1);

 

mob_util_pck.log_write ('I','Following File will be modified: '||l_dest_file);  

   l_bfile := BFILENAME ('MOBSEPAOUT', l_dest_file);

   DBMS_LOB.OPEN (l_bfile, DBMS_LOB.file_readonly);

   DBMS_LOB.createtemporary (l_tmp_clob, TRUE);

   DBMS_LOB.loadclobfromfile (dest_lob          => l_tmp_clob

                            , src_bfile         => l_bfile

                            , amount            => DBMS_LOB.getlength (l_bfile)

                            , dest_offset       => l_dest_offset

                            , src_offset        => l_src_offset

                            , bfile_csid        => DBMS_LOB.default_csid

                            , lang_context      => lang_ctx

                            , warning           => warning

                             );

   DBMS_LOB.CLOSE (l_bfile);

mob_util_pck.log_write ('I','Deleting Old File');

   BEGIN

      mob_util_pck.delete_files ('MOBSEPAOUT'

                               , l_dest_file

                                );

   END;

mob_util_pck.log_write ('I','Before Replacing Characters');

   l_tmp_clob := REPLACE (l_tmp_clob, '€', 'E');

   l_tmp_clob := REPLACE (l_tmp_clob, '@', '(at)');

   l_tmp_clob := REPLACE (l_tmp_clob, '&', '+');

   l_tmp_clob := REPLACE (l_tmp_clob, 'à', 'a');

   l_tmp_clob := REPLACE (l_tmp_clob, 'é', 'e');

   l_tmp_clob := REPLACE (l_tmp_clob, 'è', 'e');

   l_tmp_clob := REPLACE (l_tmp_clob, 'ç', 'c');

   l_tmp_clob := REPLACE (l_tmp_clob, 'ù', 'u');

   l_tmp_clob := REPLACE (l_tmp_clob, 'ï', 'i');

   l_tmp_clob := REPLACE (l_tmp_clob, 'ù', 'u');

   l_tmp_clob := REPLACE (l_tmp_clob, '[', '(');

   l_tmp_clob := REPLACE (l_tmp_clob, ']', ')');

   l_tmp_clob := REPLACE (l_tmp_clob, '\', '/');

   l_tmp_clob := REPLACE (l_tmp_clob, '^', '.');

   l_tmp_clob := REPLACE (l_tmp_clob, 'ù', 'u');

   --l_tmp_clob := REPLACE (l_tmp_clob, '_', '-');

   l_tmp_clob := REPLACE (l_tmp_clob, '`', '''');

   l_tmp_clob := REPLACE (l_tmp_clob, '{', '(');

   l_tmp_clob := REPLACE (l_tmp_clob, '|', '/');

   l_tmp_clob := REPLACE (l_tmp_clob, '}', ')');

   l_tmp_clob := REPLACE (l_tmp_clob, '~', '-');

   l_tmp_clob := REPLACE (l_tmp_clob, 'À', 'A');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Á', 'A');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Â', 'A');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ã', 'A');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ä', 'A');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Å', 'A');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ç', 'C');

   l_tmp_clob := REPLACE (l_tmp_clob, 'È', 'E');

   l_tmp_clob := REPLACE (l_tmp_clob, 'É', 'E');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ê', 'E');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ë', 'E');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ì', 'I');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Í', 'I');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Î', 'I');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ï', 'I');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ñ', 'N');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ò', 'O');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ó', 'O');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ô', 'O');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Õ', 'O');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ö', 'O');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ù', 'U');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ú', 'U');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Û', 'U');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ü', 'U');

   l_tmp_clob := REPLACE (l_tmp_clob, 'Ý', 'Y');

 

   mob_util_pck.log_write ('I','After Replacing Special Characters');

   mob_util_pck.log_write ('I','Before Creating New File '||l_dest_file);

  

   DBMS_XSLPROCESSOR.clob2file(l_tmp_clob, 'MOBSEPAOUT', l_dest_file);

  

   mob_util_pck.log_write ('I','After Creating New File '||l_dest_file);

   mob_util_pck.log_write ('I','Inside REMOVE_SPECIAL_CHARS procedure(-)');

END REMOVE_SPECIAL_CHARS;

This procedure is working fine for some special characters. But some special characters are getting replaced by different characters before performing the replace so it is causing wrong replacement of characters.

eg- i need to replace & by +

but it is changing it to +amp which is incorrect.

Is it something related to characters encoding?

Please help.

 

Regards-

Vikrant

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points