5 Replies Latest reply: Aug 5, 2013 2:06 AM by Senthilkumar S RSS

    Special character

    Senthilkumar S

      Hi

       

      we are generating XML to build the email body from Oracle database (set as UTF8 character set ).

      But when we generating file instead of box , special character () is coming in xml which is intern failing email notifications.

       

      In mail this is appear as below

      SUPPLY CHAIN SPL DC (it is actually a box rather than a question mark , non utf8)

       

      Thanks,

      Senthilkumar S

        • 1. Re: Special character
          chris227

          To little details to say something on this.

          Where do you get the text from, a table? Than post a select dump (text, 1010) from thetexttable of the crtical text portion.

          How is the text generated.

          Construct a simple testcase.

          • 2. Re: Special character
            Oraclenewbie

            Hi,

             

            you could replace special character whe you generate the xml for the Email.

             

            for example:

             

            REPLACE(DBMS_XMLGEN.CONVERT(Textfield from the table),'special character,'character for the Mail')

            • 3. Re: Special character
              Senthilkumar S

              We have to remove non utf-8 chracters from xml as genarated below.

                l_xmlfile  := GET_XML_STR(p_message_tab_i   --=> XXCTS_OM_PCA_MESSAGE_T
                                                 ,p_return_status_o  => l_retcode
                                                 ,p_return_message_o => l_retmesg
                                                 );
              -- Encode the xml string
                        l_enc_xmlfile := DBMS_XMLGEN.CONVERT(l_xmlfile,DBMS_XMLGEN.ENTITY_ENCODE);
              

               

               

               

              Here l_xmlfile and l_enc_xmlfile are CLOB. And below function used to form clob to generate XML file.

               

                FUNCTION GET_XML_STR (
                    p_message_tab_i         IN     XXCTS_OM_PCA_MESSAGE_T,
                    p_return_status_o       OUT    XXCTS_PCA_WEBSERVICE.g_ret_sts_type%TYPE,
                    p_return_message_o      OUT    VARCHAR2
                 )
                    RETURN CLOB
                 AS
                    STOP_PROCESS_EXCEPTION EXCEPTION;
                    l_doc              xmldom_sys.domdocument;
                    l_main_node        xmldom_sys.domnode;
                    l_node_in          xmldom_sys.domnode;
                    l_node_actions     xmldom_sys.domnode;
                    l_node_noop        xmldom_sys.domnode;
                    l_node_title       xmldom_sys.domnode;
                    l_node_rec         xmldom_sys.domnode;
                    l_node_item        xmldom_sys.domnode;
                    l_node             xmldom_sys.domnode;
                    l_elmt             xmldom_sys.domelement;
                    l_text             xmldom_sys.domtext;
              
              
                    l_template         VARCHAR2(50);
                    l_xml_str          CLOB := '1';
                    l_count            NUMBER;
                    l_index            NUMBER;
                    l_tkn_lst_tab_idx  NUMBER;
                    l_tkn_lst_idx      NUMBER;
                    l_last             NUMBER;
                    l_retcode          XXCTS_PCA_WEBSERVICE.g_ret_sts_type%TYPE
                                          := XXCTS_PCA_WEBSERVICE.g_ret_sts_success;
                    l_retmesg          VARCHAR2 (4000);
                    l_parameter        XXCTS_OM_PARAMETER_T;
                    l_tkn_lst_tab      XXCTS_OM_PARAMETERLIST_TAB_T;
                    l_tkn_list         XXCTS_OM_PARAMETERLIST_T;
              
              
                    l_createdate       VARCHAR2(100);
                    l_expirydate       VARCHAR2(100);
              
              
                    BEGIN
                          SELECT ((to_char(sysdate,'YYYY-MM-DD')) || 'T' || (to_char(sysdate, 'HH:MI:SS')) || '.000Z') INTO l_createdate FROM DUAL;
                          SELECT ((to_char(sysdate + 4,'YYYY-MM-DD')) || 'T' || (to_char(sysdate, 'HH:MI:SS')) || '.000Z') INTO l_expirydate FROM DUAL;
              
              
                           -- new document handler --get document
                          l_doc := xmldom_sys.newdomdocument;
                          -- create main node for this new doc handler
                          l_main_node := xmldom_sys.makenode (l_doc);
              
              
                          l_elmt := xmldom_sys.createelement (l_doc,  'in0');
                          xmldom_sys.setAttribute(l_elmt, 'xmlns', 'urn:Message');
                          xmldom_sys.setAttribute(l_elmt, 'xmlns:ext', 'urn:MessageExtension');
                          l_node_in := xmldom_sys.appendchild (l_main_node, xmldom_sys.makenode (l_elmt));
              
              
                          l_index := 1;
                          l_count := P_MESSAGE_TAB_I.Receiver_list.COUNT;
              
              
                          IF l_count <> 0
                          THEN
                              FOR l_index IN P_MESSAGE_TAB_I.Receiver_list.FIRST..P_MESSAGE_TAB_I.Receiver_list.LAST
                              LOOP
              
              
                                  l_elmt := xmldom_sys.createelement (l_doc, 'ext:Recipients');
                                  l_node_rec := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
              
              
                                  l_elmt := xmldom_sys.createelement (l_doc, 'ext:name');
                                  l_node := xmldom_sys.appendchild (l_node_rec, xmldom_sys.makenode (l_elmt));
                                  IF P_MESSAGE_TAB_I.Communication_Mode = 'EMAIL'
                                  THEN
                                      l_template := 'email';
                                  ELSIF P_MESSAGE_TAB_I.Communication_Mode = 'SMS'
                                  THEN
                                      l_template := 'mobile_phone';
                                  ELSIF P_MESSAGE_TAB_I.Communication_Mode = 'VOICE'
                                  THEN
                                      l_template := 'voice_phone';
                                  ELSE
                                      l_template := P_MESSAGE_TAB_I.Communication_Mode;
                                  END IF;
                                  --dbms_output.put_line('P_MESSAGE_TAB_I.Communication_Mode -- ' || P_MESSAGE_TAB_I.Communication_Mode);
                                  l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_template));
                                  l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                                  l_elmt := xmldom_sys.createelement (l_doc, 'ext:value');
                                  l_node := xmldom_sys.appendchild (l_node_rec, xmldom_sys.makenode (l_elmt));
                                  l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(P_MESSAGE_TAB_I.Receiver_list(l_index).Value));
                                  l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                              END LOOP;
                          END IF;
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'type');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          l_template := p_message_tab_i.CEBT_Template_Name || '_' || p_message_tab_i.Communication_Mode;
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_template));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'data');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          l_template := p_message_tab_i.CEBT_Template_Name || ' ' || p_message_tab_i.Language;
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_template));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'subject');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_template));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'priority');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('3'));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'creationDate');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          --dbms_output.put_line('l_createdate ....' || l_createdate);
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_createdate));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'expiryDate');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          --dbms_output.put_line('l_expirydate ....' || l_expirydate);
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_expirydate));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'lang');
                          l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(P_MESSAGE_TAB_I.Language_Code));
                          l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                          IF P_MESSAGE_TAB_I.Communication_Mode = 'VOICE'
                          THEN
                              l_elmt := xmldom_sys.createelement (l_doc, 'actions');
                              l_node_actions := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
              
              
                              -- acknowledge
              
              
                              l_elmt := xmldom_sys.createelement (l_doc, 'item');
                              l_node_item := xmldom_sys.appendchild (l_node_actions, xmldom_sys.makenode (l_elmt));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc, 'noOpAction');
                              l_node_noop := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'title');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '0');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('acknowledge'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'isFinal');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '1');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('false'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'hasParameter');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '2');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('false'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              -- transfer
              
              
                              l_elmt := xmldom_sys.createelement (l_doc, 'item');
                              l_node_item := xmldom_sys.appendchild (l_node_actions, xmldom_sys.makenode (l_elmt));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc, 'noOpAction');
                              l_node_noop := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'title');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '0');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('transfer'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'isFinal');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '1');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('false'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'hasParameter');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '2');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('false'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              -- answer
              
              
                              l_elmt := xmldom_sys.createelement (l_doc, 'item');
                              l_node_item := xmldom_sys.appendchild (l_node_actions, xmldom_sys.makenode (l_elmt));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc, 'noOpAction');
                              l_node_noop := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'title');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '0');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('answer'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'isFinal');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '1');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('false'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                              l_elmt := xmldom_sys.createelement (l_doc,  'hasParameter');
                              xmldom_sys.setAttribute(l_elmt, 'propIdx', '2');
                              l_node_title := xmldom_sys.appendchild (l_node_noop, xmldom_sys.makenode (l_elmt));
                              l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert('false'));
                              l_node := xmldom_sys.appendchild (l_node_title, xmldom_sys.makenode (l_text));
              
              
                          ELSE
                              l_elmt := xmldom_sys.createelement (l_doc, 'actions');
                              l_node := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
                          END IF;
              
              
              
              
              
              
                          l_elmt := xmldom_sys.createelement (l_doc, 'contextualData');
                          l_node_rec := xmldom_sys.appendchild (l_node_in, xmldom_sys.makenode (l_elmt));
              
              
                          l_count := P_MESSAGE_TAB_I.Token_List.COUNT;
                          IF l_count <> 0
                          THEN
                              FOR l_index IN P_MESSAGE_TAB_I.Token_List.FIRST..P_MESSAGE_TAB_I.Token_List.LAST
                              LOOP
                                  l_elmt := xmldom_sys.createelement (l_doc, 'item');
                                  l_node_item := xmldom_sys.appendchild (l_node_rec, xmldom_sys.makenode (l_elmt));
              
              
                                  l_elmt := xmldom_sys.createelement (l_doc, 'title');
                                  l_node := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
                                  l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(P_MESSAGE_TAB_I.Token_List(l_index).Name));
                                  l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                                  l_elmt := xmldom_sys.createelement (l_doc, 'data');
                                  l_node := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
                                  l_text := xmldom_sys.createTextNode(l_doc, P_MESSAGE_TAB_I.Token_List(l_index).Value);
                                  l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
                                  --dbms_output.put_line('P_MESSAGE_TAB_I.Token_List(l_index).Name ------' || P_MESSAGE_TAB_I.Token_List(l_index).Name || P_MESSAGE_TAB_I.Token_List(l_index).Value);
                              END LOOP;
              
              
                              l_count := P_MESSAGE_TAB_I.Detail_Msg_Token_List.COUNT;
                              IF l_count <> 0
                              THEN
                                  FOR l_index IN P_MESSAGE_TAB_I.Detail_Msg_Token_List.FIRST..P_MESSAGE_TAB_I.Detail_Msg_Token_List.LAST
                                  LOOP
                  --dbms_output.put_line('P_MESSAGE_TAB_I.Detail_Msg_Token_List(l_index).Name ------' || P_MESSAGE_TAB_I.Detail_Msg_Token_List(l_index).Name);
                  --dbms_output.put_line('P_MESSAGE_TAB_I.Detail_Msg_Token_List(l_index).Value.count ------' || P_MESSAGE_TAB_I.Detail_Msg_Token_List(l_index).Value.count);
              
              
                                      IF P_MESSAGE_TAB_I.Detail_Msg_Token_List(l_index).Value.count <> 0
                                      THEN
              
              
                                          l_tkn_lst_tab := P_MESSAGE_TAB_I.Detail_Msg_Token_List(l_index).Value;
                                          FOR l_tkn_lst_tab_idx IN l_tkn_lst_tab.FIRST..l_tkn_lst_tab.LAST
                                          LOOP
                                              l_tkn_list := l_tkn_lst_tab(l_tkn_lst_tab_idx);
              
              
                                              IF l_tkn_list.count <> 0
                                              THEN
                                                  FOR l_tkn_lst_idx IN l_tkn_list.FIRST..l_tkn_list.LAST
                                                  LOOP
              
              
                                                      l_elmt := xmldom_sys.createelement (l_doc, 'item');
                                                      l_node_item := xmldom_sys.appendchild (l_node_rec, xmldom_sys.makenode (l_elmt));
              
              
                                                      l_elmt := xmldom_sys.createelement (l_doc, 'title');
                                                      l_node := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
                                                      l_text := xmldom_sys.createTextNode(l_doc, dbms_xmlgen.convert(l_tkn_list(l_tkn_lst_idx).Name));
                                                      l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                                                      l_elmt := xmldom_sys.createelement (l_doc, 'data');
                                                      l_node := xmldom_sys.appendchild (l_node_item, xmldom_sys.makenode (l_elmt));
                                                      l_text := xmldom_sys.createTextNode(l_doc, l_tkn_list(l_tkn_lst_idx).Value);
                                                      l_node := xmldom_sys.appendchild (l_node, xmldom_sys.makenode (l_text));
              
              
                                                      --dbms_output.put_line(l_tkn_list(l_tkn_lst_idx).Name || '  l_tkn_list(l_tkn_lst_idx) ------' || l_tkn_list(l_tkn_lst_idx).Value );
              
              
                                                  END LOOP;
                                              END IF;
                                          END LOOP;
                                      END IF;
              
              
                                  END LOOP;
                              END IF;
                          END IF;
              
              
                          xmldom_sys.writetoclob (l_doc, l_xml_str);
                          xmldom_sys.freeDocument (l_doc);              --Freeing the resources
                          RETURN l_xml_str;
                    EXCEPTION
                        WHEN STOP_PROCESS_EXCEPTION
                        THEN
                           --dbms_output.put_line('Failed AT l_xml_str Exception Stop_process_Exception!');
                           xxcts_fea_utl_log.error('-- PCA CEBT Web Service - Failed AT l_xml_str Exception Stop_process_Exception!');
                           p_return_status_o := XXCTS_PCA_WEBSERVICE.g_ret_sts_error;
                           p_return_message_o := 'Func GET_XML_STR : ' || l_retmesg;
              
              
                           IF NOT xmldom_sys.IsNull (l_doc)
                           THEN
                              xmldom_sys.freeDocument (l_doc);
                           END IF;
              
              
                           RETURN NULL;
                        WHEN OTHERS
                        THEN
                          --dbms_output.put_line('l_xml_str Exception when others - '||sqlerrm);
                          xxcts_fea_utl_log.error('-- PCA CEBT Web Service - l_xml_str Exception when others - '||sqlerrm);
                          --DBMS_OUTPUT.put_line (dbms_utility.format_error_backtrace());
                          p_return_status_o := XXCTS_PCA_WEBSERVICE.g_ret_sts_error;
                          p_return_message_o :=
                                 'Func GET_XML_STR : '
                              || TO_CHAR (SQLCODE)
                              || ':'
                              || SUBSTR (SQLERRM, 1, 2000);
              
              
                          IF NOT xmldom_sys.IsNull (l_doc)
                          THEN
                              xmldom_sys.freeDocument (l_doc);
                          END IF;
              
              
                          RETURN NULL;
                    END;
              
              {code}
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              
              • 4. Re: Special character
                odie_63

                Hi,

                 

                No one here can run your code.

                So please, just give us a simple test case that shows the issue : input/output/expected output

                 

                Thanks.

                • 5. Re: Special character
                  Senthilkumar S

                  HI,

                   

                  As per the code we are generating XML by using xmldom_sys. Then   xmldom_sys.writetoclob (l_doc, l_xml_str) is used to write into clob.

                   

                  Our requirement is remove Non UTF8 chracters from XML. If this clob variable is having some special character ('SUPPLY CHAIN SPL DC'), then we need to remove those characters.

                   

                  Sample code as below

                  declare

                  l_xml clob:='SUPPLY CHAIN � SPL DC ';

                  l_e_xml clob;

                  begin

                  l_e_xml := replace(l_xml,'WE8ISO8859P15');

                  dbms_output.put_line(l_xml);

                  dbms_output.put_line(l_e_xml);

                  end;

                  /

                  l_e_xml file has to hold only 'SUPPLY CHAIN SPL DC '. But this is just sample. We are having many such special chracters generated from our database(Chracter set is UTF8).