7 Replies Latest reply on Aug 10, 2012 3:14 PM by odie_63

    Storing Chinese data as clob using dbms_xmlquery


      I am writing a procedure that creates a query and same is stored as CLOB in a database table. Below is a sample test program for same. I am using UTF-8 to accomodate Chinese data. The CLOB however doesn't contain valid chinese data. Please guide me on what needs to be done....

      l_ctx dbms_xmlquery.ctxtype;
      l_xml_schema NCLOB;
      l_ctx := dbms_xmlquery.newcontext('SELECT * FROM (SELECT "SEC","coretypeid","coreid","Record Type","您ID" FROM (SELECT "您MultiCurrency Object".coreid "SEC","您MultiCurrency Object".coretypeid "coretypeid", "您MultiCurrency Object".coreid "coreid", "您MultiCurrency Object".CoreTypeAssocAliasDescr "Record Type", "您MultiCurrency Object".F_3441961635 "您ID"
      FROM ( SELECT SUBSTR(RFN_FlexSQL_Cache.GetCTAliasDescr(CORETYPEID,3441940070,3443315783),0,50) CoreTypeAliasDescr,
      SUBSTR(RFN_FlexSQL_Cache.GetCTAssocAliasDescr(CORETYPEID,3441940070,3443315783),0,50) CoreTypeAssocAliasDescr,
      SUBSTR(RFN_FlexSQL_Cache.GetCTDescr(CORETYPEID,3441940070,3443315783),0,50) COREDESCRIPTION,
      COREID, CORETYPEID ,F_3441961635 FROM CT_3420185025
      WHERE coretypeid IN (3420185025)) "您MultiCurrency Object" )) WHERE 1 = 2');
      dbms_xmlquery.setraiseexception(l_ctx, TRUE);
      dbms_xmlquery.setsqltoxmlnameescaping(l_ctx, TRUE);
      -- dbms_xmlquery.setdateformat(l_ctx, l_xml_sformat);
      dbms_xmlquery.setencodingtag(l_ctx, 'ISO-8859-1');
      l_xml_schema := dbms_xmlquery.getxml(l_ctx,2);
      insert into testingXMLgen values (l_xml_schema);
      -- :xml_data := dbms_xmlquery.getxml(l_ctx);