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