This discussion is archived
1 Reply Latest reply: Jul 29, 2012 8:13 AM by odie_63 RSS

ORA-22813: operand value exceeds system limits

952608 Newbie
Currently Being Moderated
Hi All,

I have created stored procedure to extract data from XML and to insert into tables.
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE procedure test_xml_parse
( i_action IN VARCHAR2, i_Merchant_ID IN VARCHAR2, i_add_update_xml IN XMLTYPE,
i_delete_xml IN XMLTYPE,
o_OutputStatus OUT NUMBER,
o_OutputMessage OUT VARCHAR2)
as

v_ErrorFlag number;
v_xml CLOB;

TYPE XML_TYPE IS RECORD
(
TAB VARCHAR2(20) ,
ENABLED varchar2(2),
PRODUCTCODE varchar2(20),
ITEMCOMMODITYCODE varchar2(20),
PRODUCTNAME varchar2(100),
PRODUCTDESCRIPTION varchar2(500),
PRODUCTCATEGORY varchar2(30),
DISCOUNTELGIBILITY varchar2(20),
SIZEWEIGHT varchar2(10),
TAXCATEGORY varchar2(50),
TAXRATE varchar2(5),
MEASUREMENTUNIT varchar2(20),
STOCKKEEPINGUNIT varchar2(20),
URL varchar2(100),
PRICE varchar2(10), --MERCHANT_PRODUCT.PRICE%TYPE,
CUSTOMFIELDNAME varchar2(30),
CUSTOMFIELDTYPE varchar2(30),
CUSTOMFIELDVALUE varchar2(50),
UPC VARCHAR2(12) ,
PARENTUPC VARCHAR2(12) ,
EAN VARCHAR2(13) ,
PARENTEAN VARCHAR2(13),
UPCPRODUCTNAME varchar2(50),
UPCPRODUCTDESCRIPTION varchar2(100),
UPCPRODUCTCATEGORY varchar2(50),
UPCMANUFACTURER varchar2(50),
UPCBRAND varchar2(50),
UPCPRODUCTSIZE VARCHAR2(10),
UPCUNITOFMEASUREMENT varchar2(50),
UPCPACKAGE varchar2(50),
UPCCOLOR varchar2(50),
UPCIMAGE1 CLOB,
UPCIMAGE2 CLOB,
UPCIMAGE3 CLOB,
UPCIMAGE4 CLOB,
UPCIMAGE5 CLOB
);

TYPE XML_TAB_TYPE IS TABLE OF XML_TYPE;
XML_PROD_TYPE XML_TAB_TYPE;

cursor cur_xml is
with t as
(select xmltype(to_clob(i_add_update_xml)) xml
from dual)
select 'MER_PRODUCT' tab,'Y' enabled,
(extractvalue (t2.column_value, 'Product_Info/productCode')) productCode,
(extractvalue (t2.column_value, 'Product_Info/itemCommodityCode')) itemCommodityCode,
(extractvalue (t2.column_value, 'Product_Info/productName')) productName,
(extractvalue (t2.column_value, 'Product_Info/productDescription')) productDescription,
(extractvalue (t2.column_value, 'Product_Info/productCategory')) productCategory,
(extractvalue (t2.column_value, 'Product_Info/discountElgibility')) discountElgibility,
(extractvalue (t2.column_value, 'Product_Info/sizeWeight')) sizeWeight,
(extractvalue (t2.column_value, 'Product_Info/taxCategory')) taxcategory,
(extractvalue (t2.column_value, 'Product_Info/taxRate')) taxRate,
(extractvalue (t2.column_value, 'Product_Info/measurementUnit')) measurementUnit,
(extractvalue (t2.column_value, 'Product_Info/stockKeepingUnit')) stockKeepingUnit,
(extractvalue (t2.column_value, 'Product_Info/url')) url,
(extractvalue (t2.column_value, 'Product_Info/price')) price,
NULL customFieldName,
NULL customFieldType,
NULL customFieldValue,
NULL upc,
NULL parentUPC,
(select (CASE WHEN (extractvalue (t2.column_value, 'UPC_Field_Information/ean')) IS NULL THEN LPAD(((extractvalue (t2.column_value, 'UPC_Field_Information/upc'))),13,0 ) ELSE (extractvalue (t2.column_value, 'UPC_Field_Information/ean')) END)
from t t, table (xmlsequence (t.xml.extract ('Product_Information/Product_Info/UPC_Field_Information'))) t2) ean,
NULL parentEAN,
NULL upcProductName,
NULL upcProductDescription,
NULL upcProductCategory,
NULL upcManufacturer,
NULL upcBrand,
NULL upcProductSize,
NULL upcUnitOfMeasurement,
NULL upcPackage,
NULL upcColor,
NULL upcImage1,
NULL upcImage2,
NULL upcImage3,
NULL upcImage4,
NULL upcImage5
from t t, table (xmlsequence (t.xml.extract ('Product_Information/Product_Info'))) t2
union all
select 'MER_CUSTOM_DETAILS', NULL ,
(select (extractvalue (t2.column_value, 'Product_Info/productCode'))
from t t, table (xmlsequence (t.xml.extract ('Product_Information/Product_Info'))) t2) ,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
(extractvalue (t2.column_value, 'Custom_Field_Information/customFieldName')) ,
(extractvalue (t2.column_value, 'Custom_Field_Information/customFieldType')) ,
(extractvalue (t2.column_value, 'Custom_Field_Information/customFieldValue')) ,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
from t t,
table (xmlsequence (t.xml.extract ('Product_Information/Product_Info/Custom_Field_Information'))) t2
union all
select 'GLOBAL_PRODUCTS',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
(extractvalue (t2.column_value, 'UPC_Field_Information/upc')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/parentUPC')) ,
(CASE WHEN (extractvalue (t2.column_value, 'UPC_Field_Information/ean')) IS NULL THEN LPAD(((extractvalue (t2.column_value, 'UPC_Field_Information/upc'))),13,0 ) ELSE (extractvalue (t2.column_value, 'UPC_Field_Information/ean')) END) ,
(CASE WHEN (extractvalue (t2.column_value, 'UPC_Field_Information/parentEAN')) IS NULL THEN LPAD(((extractvalue (t2.column_value, 'UPC_Field_Information/parentUPC'))),13,0 ) ELSE (extractvalue (t2.column_value, 'UPC_Field_Information/parentEAN')) END) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcProductName')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcProductDescription')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcProductCategory')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcManufacturer')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcBrand')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcProductSize')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcUnitOfMeasurement')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcPackage')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/upcColor')) ,
NULL,
NULL,
NULL,
NULL,
NULL
from t t,
table (xmlsequence (t.xml.extract ('Product_Information/Product_Info/UPC_Field_Information'))) t2
union all
select 'GLOBAL_IMAGES',
NULL ,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
(extractvalue (t2.column_value, 'UPC_Field_Information/upc')) ,
(extractvalue (t2.column_value, 'UPC_Field_Information/parentUPC')) ,
(CASE WHEN (extractvalue (t2.column_value, 'UPC_Field_Information/ean')) IS NULL THEN LPAD(((extractvalue (t2.column_value, 'UPC_Field_Information/upc'))),13,0 ) ELSE (extractvalue (t2.column_value, 'UPC_Field_Information/ean')) END) ,
(CASE WHEN (extractvalue (t2.column_value, 'UPC_Field_Information/parentEAN')) IS NULL THEN LPAD(((extractvalue (t2.column_value, 'UPC_Field_Information/parentUPC'))),13,0 ) ELSE (extractvalue (t2.column_value, 'UPC_Field_Information/parentEAN')) END) ,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
to_clob(extractvalue (t2.column_value, 'UPC_Field_Information/upcImage1')) ,
to_clob(extractvalue (t2.column_value, 'UPC_Field_Information/upcImage2')) ,
to_clob(extractvalue (t2.column_value, 'UPC_Field_Information/upcImage3')) ,
to_clob(extractvalue (t2.column_value, 'UPC_Field_Information/upcImage4')) ,
to_clob(extractvalue (t2.column_value, 'UPC_Field_Information/upcImage5'))
from t t,
table (xmlsequence (t.xml.extract ('Product_Information/Product_Info/UPC_Field_Information'))) t2 ;

l_offset number := 1;
begin
OPEN cur_xml;

v_ErrorFlag := 1.1;

FETCH cur_xml bulk collect INTO XML_PROD_TYPE limit 100;

v_ErrorFlag := 1.2;

for i in 1..XML_PROD_TYPE.count
loop

v_ErrorFlag := 2;

IF i_action = 'ADD' THEN
IF xml_prod_type(i).tab = 'MER_PRODUCT' THEN
INSERT INTO MERCHANT_PRODUCT(MERCHANT_ID, ITEM_NAME, ITEM_CODE, ITEM_COMMODITY_CODE, MEASUREMENT_UNIT, PRICE, TAX_RATE, TAX_TYPE, STOCK_KEEPING_UNIT, URL, DISCOUNT_ELIGIBILITY, PRODUCT_DESCRIPTION, PRODUCT_SIZE_WEIGHT, PRODUCT_CATEGORY,ENABLED, EAN_CODE)
VALUES (i_Merchant_ID, XML_PROD_TYPE(i).PRODUCTNAME, XML_PROD_TYPE(i).PRODUCTCODE, XML_PROD_TYPE(i).ITEMCOMMODITYCODE, XML_PROD_TYPE(i).MEASUREMENTUNIT, XML_PROD_TYPE(i).PRICE, XML_PROD_TYPE(i).TAXRATE, XML_PROD_TYPE(i).TAXCATEGORY, XML_PROD_TYPE(i).STOCKKEEPINGUNIT, XML_PROD_TYPE(i).URL, XML_PROD_TYPE(i).DISCOUNTELGIBILITY, XML_PROD_TYPE(i).PRODUCTDESCRIPTION, XML_PROD_TYPE(i).SIZEWEIGHT, XML_PROD_TYPE(i).PRODUCTCATEGORY, XML_PROD_TYPE(i).ENABLED, XML_PROD_TYPE(i).EAN );
ELSIF xml_prod_type(i).tab = 'MER_CUSTOM_DETAILS' THEN
INSERT INTO MER_CUSTOM_PRODUCT_DETAILS (MERCHANT_ID, ITEM_CODE, FIELD_NAME, FIELD_TYPE, FIELD_VALUE, DATE_CREATED, DATE_MODIFIED)
values (i_Merchant_ID, XML_PROD_TYPE(i).PRODUCTCODE, XML_PROD_TYPE(i).CUSTOMFIELDNAME, XML_PROD_TYPE(i).CUSTOMFIELDTYPE, XML_PROD_TYPE(i).CUSTOMFIELDVALUE, SYSDATE, SYSDATE );
ELSIF xml_prod_type(i).tab = 'GLOBAL_PRODUCTS' THEN
INSERT INTO GLOBAL_PRODUCTS (EAN_CODE, PARENT_EAN_CODE, UPC_PRODUCT_NAME, UPC_PRODUCT_DESC, UPC_BRAND, UPC_MANUFACTURER, UPC_SIZE, UPC_MEASUREMENT_UNIT, UPC_PACKAGE, UPC_COLOR, PRODUCT_CATEGORY, DATE_CREATED, DATE_MODIFIED )
VALUES (XML_PROD_TYPE(i).EAN, XML_PROD_TYPE(i).PARENTEAN, XML_PROD_TYPE(i).UPCPRODUCTNAME, XML_PROD_TYPE(i).UPCPRODUCTDESCRIPTION, XML_PROD_TYPE(i).UPCBRAND, XML_PROD_TYPE(i).UPCMANUFACTURER, XML_PROD_TYPE(i).UPCPRODUCTSIZE, XML_PROD_TYPE(i).UPCUNITOFMEASUREMENT, XML_PROD_TYPE(i).UPCPACKAGE, XML_PROD_TYPE(i).UPCCOLOR, XML_PROD_TYPE(i).UPCPRODUCTCATEGORY, SYSDATE, SYSDATE );
ELSIF xml_prod_type(i).tab = 'GLOBAL_IMAGES' THEN
INSERT INTO GLOBAL_PRODUCT_IMAGES_1 (EAN_CODE, UPC_IMAGE1, UPC_IMAGE2, UPC_IMAGE3, UPC_IMAGE4, UPC_IMAGE5)
VALUES(XML_PROD_TYPE(i).EAN, XML_PROD_TYPE(i).UPCIMAGE1, XML_PROD_TYPE(i).UPCIMAGE2, XML_PROD_TYPE(i).UPCIMAGE3, XML_PROD_TYPE(i).UPCIMAGE4, XML_PROD_TYPE(i).UPCIMAGE5);
END IF;

COMMIT;

v_ErrorFlag := 3;

END IF;

END LOOP;

CLOSE cur_xml;

EXCEPTION
WHEN OTHERS
THEN
'Procedure test_xml_parse Failed at step No = '
|| v_ErrorFlag
|| ' SQL Error :- '
|| SUBSTR (SQLERRM, 1, 100);
END;
/

------------------------------------------------------------------------------------------------------------------------------------------------------
I am getting below error at, ---FETCH cur_xml bulk collect INTO XML_PROD_TYPE limit 100; (while fetching)

ORA-22813: operand value exceeds system limits.


Oracle version: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0


Please help me to resolve this issue.
  • 1. Re: ORA-22813: operand value exceeds system limits
    odie_63 Guru
    Currently Being Moderated
    Hi,
    Please help me to resolve this issue.
    Apart from the error you're receiving, there are a lot of wrong things in your procedure :

    - cursor row-by-row processing : it doesn't scale
    - commiting in a loop
    - unnecessary conversion from xmltype to clob then to xmltype again : what's the point of doing that? consuming as much PGA memory as possible?
    - extractvalue() and xmlsequence() functions should be replaced by xmltable()

    From what I understand of the procedure, it could be simplified down to 4 INSERTs only, no cursor, no transient PL/SQL types, and even (maybe) to a single multitable INSERT.

    I can show you how to do it, but I'd require a sample input XML to test.


    BTW, please use
     tags to format your code on the forum.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Legend

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