8 Replies Latest reply: May 7, 2013 6:44 AM by 816802 RSS

    How to store more than 32767 bytes in a CLOB PLSQL variable

    816802
      Hello All,

      I am trying to build an XML document in a CLOB PLSQL variable. We are using Oracle 11gr2 database.

      But when I am reaching more than 32767 bytes my code is failing.

      Is there anyway we can store more than 32767 bytes of data in a PLSQL variable of type CLOB.

      I am capturing the below error message
      (ORA-06512: at "SCMSA_HIST.SCMSA_POC_HANDSET_MOBILITY_PKG", line 1480
      AND LENGTH OF xmlfile -> 33078
      )


      I am adding my code also here for further clarification

      PROCEDURE GET_HANDSET_DATA_PRC (p_ntlogin_id IN VARCHAR2,
      p_handset_data OUT NOCOPY CLOB)
      IS
      /******************************************************************************
      NAME: GET_HANDSET_DATA_PRC
      PURPOSE:
      Date Ver By Description
      ---------- --- --- -----------

      ******************************************************************************/

      CURSOR c_region_data
      IS
      SELECT NVL2 (T.ntlogin, T.ntlogin, pos.ntlogin) AS ntlogin,
      NVL2 (T.first_name, T.first_name, pos.first_name)
      AS first_name,
      NVL2 (T.last_name, T.last_name, pos.last_name) AS last_name,
      NVL2 (T.job_title, T.job_title, pos.job_title) AS job_title,
      NVL2 (T.store_id, T.store_id, pos.store_id) AS store_id,
      NVL2 (T.store_name, T.store_name, pos.store_name)
      AS store_name,
      NVL2 (T.sap_number, T.sap_number, pos.sap_number)
      AS sap_number,
      NVL2 (T.district, T.district, pos.district) AS District,
      NVL2 (T.region, T.region, pos.region) AS region,
      NVL2 (T.division, T.division, pos.division) AS division,
      TO_CHAR (
      NVL2 (T.sales_transaction_dt,
      T.sales_transaction_dt,
      pos.sales_transaction_dt),
      'MON')
      AS sales_transaction_dt,
      T.postpaid_totalqty,
      T.postpaid_totaldollars,
      T.prepaid_totalqty,
      T.prepaid_totaldollars,
      T.gosmart_totalqty,
      T.gosmart_totaldollars,
      T.unknown_sub_totalqty,
      T.unknown_sub_totaldollars,
      T.postpaidfeature_totalqty,
      T.postpaidfeature_totaldollar,
      T.prepaidfeature_totalqty,
      T.prepaidfeature_totaldollars,
      T.gosmartfeature_totalqty,
      T.gosmartfeature_totaldollars,
      T.unknown_feat_totalqty,
      T.unknown_Feat_totaldollars,
      pos.accessory_totalqty,
      pos.accessory_totaldollars,
      pos.handset_totalqty,
      pos.handset_totaldollars
      FROM (SELECT NVL2 (sub.ntlogin, sub.ntlogin, prod.ntlogin)
      AS ntlogin,
      NVL2 (sub.first_name,
      sub.first_name,
      prod.first_name)
      AS first_name,
      NVL2 (sub.last_name,
      sub.last_name,
      prod.last_name)
      AS last_name,
      NVL2 (sub.job_title,
      sub.job_title,
      prod.job_title)
      AS job_title,
      NVL2 (sub.store_id, sub.store_id, prod.store_id)
      AS store_id,
      NVL2 (sub.store_name,
      sub.store_name,
      prod.store_name)
      AS store_name,
      NVL2 (sub.sap_number,
      sub.sap_number,
      prod.sap_number)
      AS sap_number,
      NVL2 (sub.district, sub.district, prod.district)
      AS District,
      NVL2 (sub.region, sub.region, prod.region)
      AS region,
      NVL2 (sub.division, sub.division, prod.division)
      AS division,
      NVL2 (sub.sales_transaction_dt,
      sub.sales_transaction_dt,
      prod.sales_transaction_dt)
      AS sales_transaction_dt,
      postpaid_totalqty,
      postpaid_totaldollars,
      prepaid_totalqty,
      prepaid_totaldollars,
      gosmart_totalqty,
      gosmart_totaldollars,
      sub.unknown_sub_totalqty,
      sub.unknown_sub_totaldollars,
      postpaidfeature_totalqty,
      postpaidfeature_totaldollar,
      prepaidfeature_totalqty,
      prepaidfeature_totaldollars,
      gosmartfeature_totalqty,
      gosmartfeature_totaldollars,
      prod.unknown_feat_totalqty,
      prod.unknown_feat_totaldollars
      FROM (SELECT *
      FROM reg_comm_mob_sub_info
      WHERE ntlogin = p_ntlogin_id) sub
      FULL OUTER JOIN
      (SELECT *
      FROM reg_comm_mob_prod_info
      WHERE ntlogin = p_ntlogin_id) prod
      ON (sub.ntlogin = prod.ntlogin
      AND sub.region = prod.region)) t
      FULL OUTER JOIN
      (SELECT pos.*
      FROM REG_COMM_MOB_POS_INFO POS
      WHERE ntlogin = p_ntlogin_id) pos
      ON (t.ntlogin = pos.ntlogin AND t.region = pos.region);


      CURSOR c_division_data (
      p_region IN VARCHAR2)
      IS
      SELECT NVL2 (T.ntlogin, T.ntlogin, pos.ntlogin) AS ntlogin,
      NVL2 (T.first_name, T.first_name, pos.first_name)
      AS first_name,
      NVL2 (T.last_name, T.last_name, pos.last_name) AS last_name,
      NVL2 (T.job_title, T.job_title, pos.job_title) AS job_title,
      NVL2 (T.store_id, T.store_id, pos.store_id) AS store_id,
      NVL2 (T.store_name, T.store_name, pos.store_name)
      AS store_name,
      NVL2 (T.sap_number, T.sap_number, pos.sap_number)
      AS sap_number,
      NVL2 (T.district, T.district, pos.district) AS District,
      NVL2 (T.region, T.region, pos.region) AS region,
      NVL2 (T.division, T.division, pos.division) AS division,
      T.postpaid_totalqty,
      T.postpaid_totaldollars,
      T.prepaid_totalqty,
      T.prepaid_totaldollars,
      T.gosmart_totalqty,
      T.gosmart_totaldollars,
      T.unknown_sub_totalqty,
      T.unknown_sub_totaldollars,
      T.postpaidfeature_totalqty,
      T.postpaidfeature_totaldollar,
      T.prepaidfeature_totalqty,
      T.prepaidfeature_totaldollars,
      T.gosmartfeature_totalqty,
      T.gosmartfeature_totaldollars,
      T.unknown_feat_totalqty,
      T.unknown_feat_totaldollars,
      pos.accessory_totalqty,
      pos.accessory_totaldollars,
      pos.handset_totalqty,
      pos.handset_totaldollars
      FROM (SELECT NVL2 (sub.ntlogin, sub.ntlogin, prod.ntlogin)
      AS ntlogin,
      NVL2 (sub.first_name,
      sub.first_name,
      prod.first_name)
      AS first_name,
      NVL2 (sub.last_name,
      sub.last_name,
      prod.last_name)
      AS last_name,
      NVL2 (sub.job_title,
      sub.job_title,
      prod.job_title)
      AS job_title,
      NVL2 (sub.store_id, sub.store_id, prod.store_id)
      AS store_id,
      NVL2 (sub.store_name,
      sub.store_name,
      prod.store_name)
      AS store_name,
      NVL2 (sub.sap_number,
      sub.sap_number,
      prod.sap_number)
      AS sap_number,
      NVL2 (sub.district, sub.district, prod.district)
      AS District,
      NVL2 (sub.region, sub.region, prod.region)
      AS region,
      NVL2 (sub.division, sub.division, prod.division)
      AS division,
      postpaid_totalqty,
      postpaid_totaldollars,
      prepaid_totalqty,
      prepaid_totaldollars,
      gosmart_totalqty,
      gosmart_totaldollars,
      sub.unknown_sub_totalqty,
      sub.unknown_sub_totaldollars,
      postpaidfeature_totalqty,
      postpaidfeature_totaldollar,
      prepaidfeature_totalqty,
      prepaidfeature_totaldollars,
      gosmartfeature_totalqty,
      gosmartfeature_totaldollars,
      prod.unknown_feat_totalqty,
      prod.unknown_feat_totaldollars
      FROM (SELECT *
      FROM DIV_COMM_MOB_SUB_INFO
      WHERE ntlogin = p_ntlogin_id
      AND region = p_region) sub
      FULL OUTER JOIN
      (SELECT *
      FROM DIV_COMM_MOB_PROD_INFO
      WHERE ntlogin = p_ntlogin_id
      AND region = p_region) prod
      ON (sub.ntlogin = prod.ntlogin
      AND sub.division = prod.division)) t
      FULL OUTER JOIN
      (SELECT pos.*
      FROM DIV_COMM_MOB_POS_INFO POS
      WHERE ntlogin = p_ntlogin_id AND region = p_region) pos
      ON (t.ntlogin = pos.ntlogin AND t.division = pos.division);


      CURSOR c_district_data (
      p_division IN VARCHAR2)
      IS
      SELECT NVL2 (T.ntlogin, T.ntlogin, pos.ntlogin) AS ntlogin,
      NVL2 (T.first_name, T.first_name, pos.first_name)
      AS first_name,
      NVL2 (T.last_name, T.last_name, pos.last_name) AS last_name,
      NVL2 (T.job_title, T.job_title, pos.job_title) AS job_title,
      NVL2 (T.store_id, T.store_id, pos.store_id) AS store_id,
      NVL2 (T.store_name, T.store_name, pos.store_name)
      AS store_name,
      NVL2 (T.sap_number, T.sap_number, pos.sap_number)
      AS sap_number,
      NVL2 (T.district, T.district, pos.district) AS District,
      NVL2 (T.region, T.region, pos.region) AS region,
      NVL2 (T.division, T.division, pos.division) AS division,
      T.postpaid_totalqty,
      T.postpaid_totaldollars,
      T.prepaid_totalqty,
      T.prepaid_totaldollars,
      T.gosmart_totalqty,
      T.gosmart_totaldollars,
      T.unknown_sub_totalqty,
      T.unknown_sub_totaldollars,
      T.postpaidfeature_totalqty,
      T.postpaidfeature_totaldollar,
      T.prepaidfeature_totalqty,
      T.prepaidfeature_totaldollars,
      T.gosmartfeature_totalqty,
      T.gosmartfeature_totaldollars,
      T.unknown_feat_totalqty,
      T.unknown_Feat_totaldollars,
      pos.accessory_totalqty,
      pos.accessory_totaldollars,
      pos.handset_totalqty,
      pos.handset_totaldollars
      FROM (SELECT NVL2 (sub.ntlogin, sub.ntlogin, prod.ntlogin)
      AS ntlogin,
      NVL2 (sub.first_name,
      sub.first_name,
      prod.first_name)
      AS first_name,
      NVL2 (sub.last_name,
      sub.last_name,
      prod.last_name)
      AS last_name,
      NVL2 (sub.job_title,
      sub.job_title,
      prod.job_title)
      AS job_title,
      NVL2 (sub.store_id, sub.store_id, prod.store_id)
      AS store_id,
      NVL2 (sub.store_name,
      sub.store_name,
      prod.store_name)
      AS store_name,
      NVL2 (sub.sap_number,
      sub.sap_number,
      prod.sap_number)
      AS sap_number,
      NVL2 (sub.district, sub.district, prod.district)
      AS District,
      NVL2 (sub.region, sub.region, prod.region)
      AS region,
      NVL2 (sub.division, sub.division, prod.division)
      AS division,
      postpaid_totalqty,
      postpaid_totaldollars,
      prepaid_totalqty,
      prepaid_totaldollars,
      gosmart_totalqty,
      gosmart_totaldollars,
      sub.unknown_sub_totalqty,
      sub.unknown_sub_totaldollars,
      postpaidfeature_totalqty,
      postpaidfeature_totaldollar,
      prepaidfeature_totalqty,
      prepaidfeature_totaldollars,
      gosmartfeature_totalqty,
      gosmartfeature_totaldollars,
      prod.unknown_feat_totalqty,
      prod.unknown_feat_totaldollars
      FROM (SELECT *
      FROM DIST_COMM_MOB_SUB_INFO
      WHERE ntlogin = p_ntlogin_id
      AND division = p_division) sub
      FULL OUTER JOIN
      (SELECT *
      FROM DIST_COMM_MOB_PROD_INFO
      WHERE ntlogin = p_ntlogin_id
      AND division = p_division) prod
      ON (sub.ntlogin = prod.ntlogin
      AND sub.district = prod.district)) t
      FULL OUTER JOIN
      (SELECT pos.*
      FROM DIST_COMM_MOB_POS_INFO POS
      WHERE ntlogin = p_ntlogin_id AND division = p_division) pos
      ON (t.ntlogin = pos.ntlogin AND t.district = pos.district);



      CURSOR c_stores_data (
      p_district IN VARCHAR2)
      IS
      SELECT NVL2 (T.ntlogin, T.ntlogin, pos.ntlogin) AS ntlogin,
      NVL2 (T.first_name, T.first_name, pos.first_name)
      AS first_name,
      NVL2 (T.last_name, T.last_name, pos.last_name) AS last_name,
      NVL2 (T.job_title, T.job_title, pos.job_title) AS job_title,
      NVL2 (T.store_id, T.store_id, pos.store_id) AS store_id,
      NVL2 (T.store_name, T.store_name, pos.store_name)
      AS store_name,
      NVL2 (T.sap_number, T.sap_number, pos.sap_number)
      AS sap_number,
      NVL2 (T.district, T.district, pos.district) AS District,
      NVL2 (T.region, T.region, pos.region) AS region,
      NVL2 (T.division, T.division, pos.division) AS division,
      T.postpaid_totalqty,
      T.postpaid_totaldollars,
      T.prepaid_totalqty,
      T.prepaid_totaldollars,
      T.gosmart_totalqty,
      T.gosmart_totaldollars,
      T.unknown_sub_totalqty,
      T.unknown_sub_totaldollars,
      T.postpaidfeature_totalqty,
      T.postpaidfeature_totaldollar,
      T.prepaidfeature_totalqty,
      T.prepaidfeature_totaldollars,
      T.gosmartfeature_totalqty,
      T.gosmartfeature_totaldollars,
      T.unknown_Feat_totalqty,
      T.unknown_feat_totaldollars,
      pos.accessory_totalqty,
      pos.accessory_totaldollars,
      pos.handset_totalqty,
      pos.handset_totaldollars
      FROM (SELECT NVL2 (sub.ntlogin, sub.ntlogin, prod.ntlogin)
      AS ntlogin,
      NVL2 (sub.first_name,
      sub.first_name,
      prod.first_name)
      AS first_name,
      NVL2 (sub.last_name,
      sub.last_name,
      prod.last_name)
      AS last_name,
      NVL2 (sub.job_title,
      sub.job_title,
      prod.job_title)
      AS job_title,
      NVL2 (sub.store_id, sub.store_id, prod.store_id)
      AS store_id,
      NVL2 (sub.store_name,
      sub.store_name,
      prod.store_name)
      AS store_name,
      NVL2 (sub.sap_number,
      sub.sap_number,
      prod.sap_number)
      AS sap_number,
      NVL2 (sub.district, sub.district, prod.district)
      AS District,
      NVL2 (sub.region, sub.region, prod.region)
      AS region,
      NVL2 (sub.division, sub.division, prod.division)
      AS division,
      postpaid_totalqty,
      postpaid_totaldollars,
      prepaid_totalqty,
      prepaid_totaldollars,
      gosmart_totalqty,
      gosmart_totaldollars,
      sub.unknown_sub_totalqty,
      sub.unknown_sub_totaldollars,
      postpaidfeature_totalqty,
      postpaidfeature_totaldollar,
      prepaidfeature_totalqty,
      prepaidfeature_totaldollars,
      gosmartfeature_totalqty,
      gosmartfeature_totaldollars,
      prod.unknown_feat_totalqty,
      prod.unknown_feat_totaldollars
      FROM (SELECT *
      FROM STORES_COMM_MOB_SUB_INFO
      WHERE ntlogin = p_ntlogin_id
      AND district = p_district) sub
      FULL OUTER JOIN
      (SELECT *
      FROM STORES_COMM_MOB_PROD_INFO
      WHERE ntlogin = p_ntlogin_id
      AND district = p_district) prod
      ON (sub.ntlogin = prod.ntlogin
      AND sub.store_id = prod.store_id)) t
      FULL OUTER JOIN
      (SELECT pos.*
      FROM STORES_COMM_MOB_POS_INFO POS
      WHERE ntlogin = p_ntlogin_id AND district = p_district) pos
      ON (t.ntlogin = pos.ntlogin AND t.store_Id = pos.store_id);


      lv_xml_data CLOB;
                lv_xml_data1 CLOB;
                lv_xml_data2 CLOB;
                lv_xml_data3 CLOB;
                lv_xml_data4 CLOB;
                lv_xml_data5 CLOB;
                lv_xml_data6 CLOB;
                lv_xml_data7 CLOB;
                
      BEGIN
           
      lv_xml_data := lv_xml_data || '<Region>';

      FOR m_region IN c_region_data
      LOOP
      lv_xml_data :=
      lv_xml_data
      || '<Region_Data>'
      || '<Region_Name>'
      || '<![CDATA['||m_region.store_name||']]>'
      || '</Region_Name>'
      || '<EmployeeFullName>'
      || m_region.first_name
      || ' '
      || m_region.last_name
      || '</EmployeeFullName>'
      || '<EmployeeAlias>'
      || m_region.ntlogin
      || '</EmployeeAlias>'
      || '<EmployeeRole>'
      || m_region.job_title
      || '</EmployeeRole>'
      || '<SAPNumber>'
      || m_region.sap_number
      || '</SAPNumber>'
      || '<Day>'
      || m_region.sales_transaction_dt
      || '</Day>'
      || '<RegionName>'
      || m_region.region
      || '</RegionName>'
      || '<PostpaidCount>'
      || m_region.postpaid_totalqty
      || '</PostpaidCount>'
      || '<PostpaidAmount>'
      || m_region.postpaid_totaldollars
      || '</PostpaidAmount>'
      || '<PostpaidFeatureCount>'
      || m_region.postpaidfeature_totalqty
      || '</PostpaidFeatureCount>'
      || '<PostpaidFeatureAmount>'
      || m_region.postpaidfeature_totaldollar
      || '</PostpaidFeatureAmount>'
      || '<PrepaidCount>'
      || m_region.prepaid_totalqty
      || '</PrepaidCount>'
      || '<PrepaidAmount>'
      || m_region.prepaid_totaldollars
      || '</PrepaidAmount>'
      || '<PrepaidFeatureCount>'
      || m_region.prepaidfeature_totalqty
      || '</PrepaidFeatureCount>'
      || '<PrepaidFeatureAmount>'
      || m_region.prepaidfeature_totaldollars
      || '</PrepaidFeatureAmount>'
      || '<AccessoriesCount>'
      || m_region.accessory_totalqty
      || '</AccessoriesCount>'
      || '<AccessoriesAmount>'
      || m_region.accessory_totaldollars
      || '</AccessoriesAmount>'
      || '<HandsetsCount>'
      || m_region.handset_totalqty
      || '</HandsetsCount>'
      || '<HandsetsAmount>'
      || m_region.handset_totaldollars
      || '</HandsetsAmount>';

      lv_xml_data := lv_xml_data || '<Division_Data>';

      FOR m_division IN c_division_data (m_region.region)
      LOOP
      lv_xml_data :=
      lv_xml_data
      || '<Division>'
      || '<DivisonName>'
      || m_division.division
      || '</DivisonName>'
      || '<PostpaidCount>'
      || m_division.postpaid_totalqty
      || '</PostpaidCount>'
      || '<PostpaidAmount>'
      || m_division.postpaid_totaldollars
      || '</PostpaidAmount>'
      || '<PostpaidFeatureCount>'
      || m_division.postpaidfeature_totalqty
      || '</PostpaidFeatureCount>'
      || '<PostpaidFeatureAmount>'
      || m_division.postpaidfeature_totaldollar
      || '</PostpaidFeatureAmount>'
      || '<PrepaidCount>'
      || m_division.prepaid_totalqty
      || '</PrepaidCount>'
      || '<PrepaidAmount>'
      || m_division.prepaid_totaldollars
      || '</PrepaidAmount>'
      || '<PrepaidFeatureCount>'
      || m_division.prepaidfeature_totalqty
      || '</PrepaidFeatureCount>'
      || '<PrepaidFeatureAmount>'
      || m_division.prepaidfeature_totaldollars
      || '</PrepaidFeatureAmount>'
      || '<AccessoriesCount>'
      || m_division.accessory_totalqty
      || '</AccessoriesCount>'
      || '<AccessoriesAmount>'
      || m_division.accessory_totaldollars
      || '</AccessoriesAmount>'
      || '<HandsetsCount>'
      || m_division.handset_totalqty
      || '</HandsetsCount>'
      || '<HandsetsAmount>'
      || m_division.handset_totaldollars
      || '</HandsetsAmount>'
      ;

      lv_xml_data := lv_xml_data || '<District_Data>';

      FOR m_district IN c_district_data (m_division.division)
      LOOP
      lv_xml_data :=
      lv_xml_data
      || '<District>'
      || '<DistrictName>'
      || m_district.district
      || '</DistrictName>'
      || '<PostpaidCount>'
      || m_district.postpaid_totalqty
      || '</PostpaidCount>'
      || '<PostpaidAmount>'
      || m_district.postpaid_totaldollars
      || '</PostpaidAmount>'
      || '<PostpaidFeatureCount>'
      || m_district.postpaidfeature_totalqty
      || '</PostpaidFeatureCount>'
      || '<PostpaidFeatureAmount>'
      || m_district.postpaidfeature_totaldollar
      || '</PostpaidFeatureAmount>'
      || '<PrepaidCount>'
      || m_district.prepaid_totalqty
      || '</PrepaidCount>'
      || '<PrepaidAmount>'
      || m_district.prepaid_totaldollars
      || '</PrepaidAmount>'
      || '<PrepaidFeatureCount>'
      || m_district.prepaidfeature_totalqty
      || '</PrepaidFeatureCount>'
      || '<PrepaidFeatureAmount>'
      || m_district.prepaidfeature_totaldollars
      || '</PrepaidFeatureAmount>'
      || '<AccessoriesCount>'
      || m_district.accessory_totalqty
      || '</AccessoriesCount>'
      || '<AccessoriesAmount>'
      || m_district.accessory_totaldollars
      || '</AccessoriesAmount>'
      || '<HandsetsCount>'
      || m_district.handset_totalqty
      || '</HandsetsCount>'
      || '<HandsetsAmount>'
      || m_district.handset_totaldollars
      || '</HandsetsAmount>'
      ;

      lv_xml_data := lv_xml_data || '<Store_Data>';

      FOR m_stores IN c_stores_data (m_district.district)
      LOOP
      lv_xml_data :=
      lv_xml_data
      || '<Store>'
      || '<StoreNumber>'
      || m_stores.store_id
      || '</StoreNumber>'
      || '<StoreLocation>'
      || '<![CDATA['||m_stores.store_name||']]>'
      || '</StoreLocation>'
      || '<PostpaidAmount>'
      || m_stores.postpaid_totaldollars
      || '</PostpaidAmount>'
      || '<PostpaidFeatureCount>'
      || m_stores.postpaidfeature_totalqty
      || '</PostpaidFeatureCount>'
      || '<PostpaidFeatureAmount>'
      || m_stores.postpaidfeature_totaldollar
      || '</PostpaidFeatureAmount>'
      || '<PrepaidCount>'
      || m_stores.prepaid_totalqty
      || '</PrepaidCount>'
      || '<PrepaidAmount>'
      || m_stores.prepaid_totaldollars
      || '</PrepaidAmount>'
      || '<PrepaidFeatureCount>'
      || m_stores.prepaidfeature_totalqty
      || '</PrepaidFeatureCount>'
      || '<PrepaidFeatureAmount>'
      || m_stores.prepaidfeature_totaldollars
      || '</PrepaidFeatureAmount>'
      || '<AccessoriesCoun
        • 1. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
          BluShadow
          Maddy wrote:
          Hello All,

          I am trying to build an XML document in a CLOB PLSQL variable.
          But when I am reaching more than 32767 bytes my code is failing.

          Is there anyway we can store more than 32767 bytes of data in a PLSQL variable of type CLOB.
          Yes. CLOB's can hold more than 32767 bytes. You would also be better to store your XML in an XMLTYPE as well, as that's designed for XML.
          The fact that your code is failing is obviously an issue with your code. But without seeing the code we cannot tell you where you've gone wrong.
          • 2. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
            816802
            Hello,

            The below is the code which I am using to build the XML File.

            I have 4 cursors using which I am building the XML. The cursors are huge and hence I am avoiding pasting them. But I am pasting the code which will does the rest.

            FYI: The database we are using is Oracle 11gr2.

            I am not using XMLTYPE datatype as I tried to build XML using Oracle SQL/XML functions and there is a limitation of 4000bytes for XML Elements in 11GR2 and my code is failing, Hence I rewrite all the code now to accomodate the data into a CLOB And building the XML using Cursors as given below.

            But the code is failing if the size of the data is growing more than 32767 bytes.


            I was going through one of the asktom sites and he has mentioned that there is length limitation of 32767 bytes for CLOBS in PLSQL.

            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4084920819312

            Is this the case in Oracle 11gr2 also.


            PROCEDURE GET_HANDSET_DATA_PRC IS

            lv_xml_data CLOB;
                      
            BEGIN
                 
            lv_xml_data := lv_xml_data || '<Region>';

            FOR m_region IN c_region_data
            LOOP
            lv_xml_data :=
            lv_xml_data
            || '<Region_Data>'
            || '<Region_Name>'
            || '<![CDATA['||m_region.store_name||']]>'
            || '</Region_Name>'
            || '<EmployeeFullName>'
            || m_region.first_name
            || ' '
            || m_region.last_name
            || '</EmployeeFullName>'
            || '<EmployeeAlias>'
            || m_region.ntlogin
            || '</EmployeeAlias>'
            || '<EmployeeRole>'
            || m_region.job_title
            || '</EmployeeRole>'
            || '<SAPNumber>'
            || m_region.sap_number
            || '</SAPNumber>'
            || '<Day>'
            || m_region.sales_transaction_dt
            || '</Day>'
            || '<RegionName>'
            || m_region.region
            || '</RegionName>'
            || '<PostpaidCount>'
            || m_region.postpaid_totalqty
            || '</PostpaidCount>'
            || '<PostpaidAmount>'
            || m_region.postpaid_totaldollars
            || '</PostpaidAmount>'
            || '<PostpaidFeatureCount>'
            || m_region.postpaidfeature_totalqty
            || '</PostpaidFeatureCount>'
            || '<PostpaidFeatureAmount>'
            || m_region.postpaidfeature_totaldollar
            || '</PostpaidFeatureAmount>'
            || '<PrepaidCount>'
            || m_region.prepaid_totalqty
            || '</PrepaidCount>'
            || '<PrepaidAmount>'
            || m_region.prepaid_totaldollars
            || '</PrepaidAmount>'
            || '<PrepaidFeatureCount>'
            || m_region.prepaidfeature_totalqty
            || '</PrepaidFeatureCount>'
            || '<PrepaidFeatureAmount>'
            || m_region.prepaidfeature_totaldollars
            || '</PrepaidFeatureAmount>'
            || '<AccessoriesCount>'
            || m_region.accessory_totalqty
            || '</AccessoriesCount>'
            || '<AccessoriesAmount>'
            || m_region.accessory_totaldollars
            || '</AccessoriesAmount>'
            || '<HandsetsCount>'
            || m_region.handset_totalqty
            || '</HandsetsCount>'
            || '<HandsetsAmount>'
            || m_region.handset_totaldollars
            || '</HandsetsAmount>';

            lv_xml_data := lv_xml_data || '<Division_Data>';

            FOR m_division IN c_division_data (m_region.region)
            LOOP
            lv_xml_data :=
            lv_xml_data
            || '<Division>'
            || '<DivisonName>'
            || m_division.division
            || '</DivisonName>'
            || '<PostpaidCount>'
            || m_division.postpaid_totalqty
            || '</PostpaidCount>'
            || '<PostpaidAmount>'
            || m_division.postpaid_totaldollars
            || '</PostpaidAmount>'
            || '<PostpaidFeatureCount>'
            || m_division.postpaidfeature_totalqty
            || '</PostpaidFeatureCount>'
            || '<PostpaidFeatureAmount>'
            || m_division.postpaidfeature_totaldollar
            || '</PostpaidFeatureAmount>'
            || '<PrepaidCount>'
            || m_division.prepaid_totalqty
            || '</PrepaidCount>'
            || '<PrepaidAmount>'
            || m_division.prepaid_totaldollars
            || '</PrepaidAmount>'
            || '<PrepaidFeatureCount>'
            || m_division.prepaidfeature_totalqty
            || '</PrepaidFeatureCount>'
            || '<PrepaidFeatureAmount>'
            || m_division.prepaidfeature_totaldollars
            || '</PrepaidFeatureAmount>'
            || '<AccessoriesCount>'
            || m_division.accessory_totalqty
            || '</AccessoriesCount>'
            || '<AccessoriesAmount>'
            || m_division.accessory_totaldollars
            || '</AccessoriesAmount>'
            || '<HandsetsCount>'
            || m_division.handset_totalqty
            || '</HandsetsCount>'
            || '<HandsetsAmount>'
            || m_division.handset_totaldollars
            || '</HandsetsAmount>'
            ;

            lv_xml_data := lv_xml_data || '<District_Data>';

            FOR m_district IN c_district_data (m_division.division)
            LOOP
            lv_xml_data :=
            lv_xml_data
            || '<District>'
            || '<DistrictName>'
            || m_district.district
            || '</DistrictName>'
            || '<PostpaidCount>'
            || m_district.postpaid_totalqty
            || '</PostpaidCount>'
            || '<PostpaidAmount>'
            || m_district.postpaid_totaldollars
            || '</PostpaidAmount>'
            || '<PostpaidFeatureCount>'
            || m_district.postpaidfeature_totalqty
            || '</PostpaidFeatureCount>'
            || '<PostpaidFeatureAmount>'
            || m_district.postpaidfeature_totaldollar
            || '</PostpaidFeatureAmount>'
            || '<PrepaidCount>'
            || m_district.prepaid_totalqty
            || '</PrepaidCount>'
            || '<PrepaidAmount>'
            || m_district.prepaid_totaldollars
            || '</PrepaidAmount>'
            || '<PrepaidFeatureCount>'
            || m_district.prepaidfeature_totalqty
            || '</PrepaidFeatureCount>'
            || '<PrepaidFeatureAmount>'
            || m_district.prepaidfeature_totaldollars
            || '</PrepaidFeatureAmount>'
            || '<AccessoriesCount>'
            || m_district.accessory_totalqty
            || '</AccessoriesCount>'
            || '<AccessoriesAmount>'
            || m_district.accessory_totaldollars
            || '</AccessoriesAmount>'
            || '<HandsetsCount>'
            || m_district.handset_totalqty
            || '</HandsetsCount>'
            || '<HandsetsAmount>'
            || m_district.handset_totaldollars
            || '</HandsetsAmount>'
            ;

            lv_xml_data := lv_xml_data || '<Store_Data>';

            FOR m_stores IN c_stores_data (m_district.district)
            LOOP
            lv_xml_data :=
            lv_xml_data
            || '<Store>'
            || '<StoreNumber>'
            || m_stores.store_id
            || '</StoreNumber>'
            || '<StoreLocation>'
            || '<![CDATA['||m_stores.store_name||']]>'
            || '</StoreLocation>'
            || '<PostpaidAmount>'
            || m_stores.postpaid_totaldollars
            || '</PostpaidAmount>'
            || '<PostpaidFeatureCount>'
            || m_stores.postpaidfeature_totalqty
            || '</PostpaidFeatureCount>'
            || '<PostpaidFeatureAmount>'
            || m_stores.postpaidfeature_totaldollar
            || '</PostpaidFeatureAmount>'
            || '<PrepaidCount>'
            || m_stores.prepaid_totalqty
            || '</PrepaidCount>'
            || '<PrepaidAmount>'
            || m_stores.prepaid_totaldollars
            || '</PrepaidAmount>'
            || '<PrepaidFeatureCount>'
            || m_stores.prepaidfeature_totalqty
            || '</PrepaidFeatureCount>'
            || '<PrepaidFeatureAmount>'
            || m_stores.prepaidfeature_totaldollars
            || '</PrepaidFeatureAmount>'
            || '<AccessoriesCount>'
            || m_stores.accessory_totalqty
            || '</AccessoriesCount>'
            || '<AccessoriesAmount>'
            || m_stores.accessory_totaldollars
            || '</AccessoriesAmount>'
            || '<HandsetsCount>'
            || m_stores.handset_totalqty
            || '</HandsetsCount>'
            || '<HandsetsAmount>'
            || m_stores.handset_totaldollars
            || '</HandsetsAmount>'
            || '</Store>';
            END LOOP;

            lv_xml_data := lv_xml_data || '</Store_Data>';
                                     lv_xml_data := lv_xml_data || '</District>';
                                     
            END LOOP;

            lv_xml_data := lv_xml_data || '</District_Data>';
                                lv_xml_data := lv_xml_data || '</Division>';
                                
            END LOOP;

            lv_xml_data := lv_xml_data || '</Division_Data>';
            lv_xml_data := lv_xml_data || '</Region_Data>';
                           
            END LOOP;

            lv_xml_data := lv_xml_data || '</Region>';

            p_handset_data := lv_xml_data;
                      
            EXCEPTION
            WHEN OTHERS
            THEN
            DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace||' AND '||LENGTH(lv_xml_data));
            END GET_HANDSET_DATA_PRC;

            Edited by: Maddy on May 7, 2013 3:22 AM
            • 3. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
              Billy~Verreynne
              Maddy wrote:

              Is there anyway we can store more than 32767 bytes of data in a PLSQL variable of type CLOB.
              Fact. CLOBs handle GB's of data.
              I am adding my code also here for further clarification
              Ugly code... This is not how one should create XML objects in Oracle. Or write exception handlers...

              The reason for your error - you are creating strings and assigning these to a CLOB. A string cannot be larger than 32KB. Your code attempts to create strings larger than that.

              I suggest you scrap this code all together. And have a look at how to use implicit cursors with XML functions to return a single XML object.
              • 4. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
                816802
                Hi Billy,

                I tried the approach of creating an XML document using Oracle SQL/XML Functions, but there is a limitation in 11gr2 for building XML Elements having more than 4000 bytes.

                http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/appjspec.htm

                I wrote the below code to generate the XML Files and it is working fine, but when the data for certain inner elements are growing, the XML creation is failing.

                The below is the code I used.

                SELECT XMLSERIALIZE (DOCUMENT DATA AS CLOB) AS DATA
                FROM ( SELECT XMLELEMENT (
                "Region_Data",
                XMLAGG (
                XMLFOREST (
                r.region AS "Region_Name",
                r.first_name || r.last_name AS "EmployeeFullName",
                r.ntlogin AS "EmployeeAlias",
                r.job_title AS "EmployeeRole",
                r.sap_number AS "SAPNumber",
                r.sales_transaction_dt AS "Day",
                r.region AS "RegionName",
                r.postpaid_totalqty AS "PostpaidCount",
                r.postpaid_totaldollars AS "PostpaidAmount",
                r.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                r.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                r.prepaid_totalqty AS "PrepaidCount",
                r.prepaid_totaldollars AS "PrepaidAmount",
                r.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                r.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                r.accessory_totalqty AS "AccessoriesCount",
                r.accessory_totaldollars AS "AccessoriesAmount",
                r.handset_totalqty AS "HandsetsCount",
                r.handset_totaldollars AS "HandsetsAmount",
                (SELECT XMLAGG (
                XMLELEMENT (
                "Division",
                XMLFOREST (
                di.division AS "DivisonName",
                di.postpaid_totalqty AS "PostpaidCount",
                di.postpaid_totaldollars AS "PostpaidAmount",
                di.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                di.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                di.prepaid_totalqty AS "PrepaidCount",
                di.prepaid_totaldollars AS "PrepaidAmount",
                di.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                di.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                di.accessory_totalqty AS "AccessoriesCount",
                di.accessory_totaldollars AS "AccessoriesAmount",
                di.handset_totalqty AS "HandsetsCount",
                di.handset_totaldollars AS "HandsetsAmount",
                (SELECT XMLAGG (
                XMLELEMENT (
                "District",
                XMLFOREST (
                dis.district AS "DistrictName",
                dis.postpaid_totalqty AS "PostpaidCount",
                dis.postpaid_totaldollars AS "PostpaidAmount",
                dis.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                dis.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                dis.prepaid_totalqty AS "PrepaidCount",
                dis.prepaid_totaldollars AS "PrepaidAmount",
                dis.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                dis.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                dis.accessory_totalqty AS "AccessoriesCount",
                dis.accessory_totaldollars AS "AccessoriesAmount",
                dis.handset_totalqty AS "HandsetsCount",
                dis.handset_totaldollars AS "HandsetsAmount",
                (SELECT XMLAGG (
                XMLELEMENT (
                "Store",
                XMLFOREST (
                mst.store_id AS "StoreNumber",
                mst.store_name AS "StoreLocation",
                mst.postpaid_totaldollars AS "PostpaidAmount",
                mst.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                mst.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                mst.prepaid_totalqty AS "PrepaidCount",
                mst.prepaid_totaldollars AS "PrepaidAmount",
                mst.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                mst.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                mst.accessory_totalqty AS "AccessoriesCount",
                mst.accessory_totaldollars AS "AccessoriesAmount",
                mst.handset_totalqty AS "HandsetsCount",
                mst.handset_totaldollars AS "HandsetsAmount")))
                FROM stores_comm_mobility_info_vw mst
                WHERE mst.district =
                dis.district) "Store_Data")))
                FROM diST_comm_mobility_info_vw dis
                WHERE dis.division =
                di.division) "District_Data")))
                FROM div_comm_mobility_info_vw di
                WHERE di.region = r.region) AS "Division_Data")))
                AS DATA
                FROM reg_comm_mobility_info_vw r
                GROUP BY region);


                As this code is failing, I started to write the code using PLSQL and the exception block I am just using here is for testing and not yet standardized.
                The code using PLSQL is still under construction and I am struggling now to write the XML using PLSQL using regular cursors.

                I have declared the lv_xml_data as a CLOB data type.
                Am I missing something here. I am under the assumption that the variable is a CLOB datatype and when I concat the data it should be concatenated to a CLOB variable and should not fail.

                Appreciate your response.

                Thanks,
                MK.
                • 5. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
                  odie_63
                  Maddy wrote:
                  I tried the approach of creating an XML document using Oracle SQL/XML Functions, but there is a limitation in 11gr2 for building XML Elements having more than 4000 bytes.

                  http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/appjspec.htm
                  No.
                  You've misread the doc. The limitation is about the XML identifier i.e. the name of the element or attribute, not its value.

                  I wrote the below code to generate the XML Files and it is working fine, but when the data for certain inner elements are growing, the XML creation is failing.
                  You probably see my reply in your latest XML DB thread : {message:id=11003916}
                  What if you try nested GROUP-BY subqueries instead of scalar subqueries ?

                  How much data are we talking about here ?
                  • 6. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
                    BluShadow
                    Maddy wrote:
                    Hi Billy,

                    I tried the approach of creating an XML document using Oracle SQL/XML Functions, but there is a limitation in 11gr2 for building XML Elements having more than 4000 bytes.

                    http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/appjspec.htm
                    You mean this...

                    >
                    XML Identifier Length Limit – Oracle XML DB supports only XML identifiers that are 4000 characters long, or shorter.
                    >

                    Are you seriously creating identifiers that are longer than 4000 characters?
                    • 7. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
                      816802
                      Hi Blueshadow/Odie_63,

                      I am using the below SQL/XML statement and it is failing when I am trying to generate lots of data and it is prompting the below error message.

                      ORA-22813: operand value exceeds system limits
                      ORA-06512: at "SCMSA_HIST.SCMSA_POC_HANDSET_MOBILITY_PKG", line 138
                      ORA-06512: at line 4

                      ERROR:
                      ORA-22813: operand value exceeds system limits

                      I do not know what the above error message means. So I am trying different approaches of writing the code in PLSQL.

                      SELECT *
                      FROM ( SELECT XMLELEMENT (
                      "Region_Data",
                      XMLAGG (
                      XMLFOREST (
                      r.region AS "Region_Name",
                      r.first_name || r.last_name AS "EmployeeFullName",
                      r.ntlogin AS "EmployeeAlias",
                      r.job_title AS "EmployeeRole",
                      r.sap_number AS "SAPNumber",
                      r.sales_transaction_dt AS "Day",
                      r.region AS "RegionName",
                      r.postpaid_totalqty AS "PostpaidCount",
                      r.postpaid_totaldollars AS "PostpaidAmount",
                      r.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                      r.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                      r.prepaid_totalqty AS "PrepaidCount",
                      r.prepaid_totaldollars AS "PrepaidAmount",
                      r.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                      r.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                      r.accessory_totalqty AS "AccessoriesCount",
                      r.accessory_totaldollars AS "AccessoriesAmount",
                      r.handset_totalqty AS "HandsetsCount",
                      r.handset_totaldollars AS "HandsetsAmount",
                      (SELECT XMLAGG (
                      XMLELEMENT (
                      "Division",
                      XMLFOREST (
                      di.division AS "DivisonName",
                      di.postpaid_totalqty AS "PostpaidCount",
                      di.postpaid_totaldollars AS "PostpaidAmount",
                      di.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                      di.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                      di.prepaid_totalqty AS "PrepaidCount",
                      di.prepaid_totaldollars AS "PrepaidAmount",
                      di.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                      di.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                      di.accessory_totalqty AS "AccessoriesCount",
                      di.accessory_totaldollars AS "AccessoriesAmount",
                      di.handset_totalqty AS "HandsetsCount",
                      di.handset_totaldollars AS "HandsetsAmount",
                      (SELECT XMLAGG (
                      XMLELEMENT (
                      "District",
                      XMLFOREST (
                      dis.district AS "DistrictName",
                      dis.postpaid_totalqty AS "PostpaidCount",
                      dis.postpaid_totaldollars AS "PostpaidAmount",
                      dis.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                      dis.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                      dis.prepaid_totalqty AS "PrepaidCount",
                      dis.prepaid_totaldollars AS "PrepaidAmount",
                      dis.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                      dis.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                      dis.accessory_totalqty AS "AccessoriesCount",
                      dis.accessory_totaldollars AS "AccessoriesAmount",
                      dis.handset_totalqty AS "HandsetsCount",
                      dis.handset_totaldollars AS "HandsetsAmount",
                      (SELECT XMLAGG (
                      XMLELEMENT (
                      "Store",
                      XMLFOREST (
                      mst.store_id AS "StoreNumber",
                      mst.store_name AS "StoreLocation",
                      mst.postpaid_totaldollars AS "PostpaidAmount",
                      mst.postpaidfeature_totalqty AS "PostpaidFeatureCount",
                      mst.postpaidfeature_totaldollar AS "PostpaidFeatureAmount",
                      mst.prepaid_totalqty AS "PrepaidCount",
                      mst.prepaid_totaldollars AS "PrepaidAmount",
                      mst.prepaidfeature_totalqty AS "PrepaidFeatureCount",
                      mst.prepaidfeature_totaldollars AS "PrepaidFeatureAmount",
                      mst.accessory_totalqty AS "AccessoriesCount",
                      mst.accessory_totaldollars AS "AccessoriesAmount",
                      mst.handset_totalqty AS "HandsetsCount",
                      mst.handset_totaldollars AS "HandsetsAmount")))
                      FROM stores_comm_mobility_info_vw mst
                      WHERE mst.district =
                      dis.district) "Store_Data")))
                      FROM diST_comm_mobility_info_vw dis
                      WHERE dis.division =
                      di.division) "District_Data")))
                      FROM div_comm_mobility_info_vw di
                      WHERE di.region = r.region) AS "Division_Data")))
                      AS DATA
                      FROM reg_comm_mobility_info_vw r
                      GROUP BY region);
                      • 8. Re: How to store more than 32767 bytes in a CLOB PLSQL variable
                        816802
                        Hi,

                        When I am avoiding the GROUP BY clause, the SQL/XML function is also working fine.

                        I googled for the error code and found the below link which said something about the ordering of data in memory and I thought I am building XMLData and have group by clause in it. So I removed the GROUP BY Clause and the query is working fine now.

                        http://psoug.org/oraerror/ORA-22813.htm


                        Thanks,
                        MK.