ORA-22992 / ORA-06502+ORA-06512 errors

Question

I'm using this part of code in large one :

SELECT DISTINCT 
    P.SKU, SUBSTR(X.ATTRIBUTENAME, 14, 3) ATTRIBUTECODE, X.ATTRIBUTEVALUE
FROM 
    PRODUCT@ISPSTAG2 P,
    XMLTABLE('/attrs/attr'
    PASSING XMLTYPE(REGEXP_REPLACE(P.ATTRIBUTES_DE_AT, '<attr name="longDescription">.*?<\/attr>'))
    COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH '@name',
            ATTRIBUTEVALUE VARCHAR2(4000) PATH '/string'
) X
WHERE X.ATTRIBUTENAME LIKE 'Z_CA%'
AND DN(DOMAINID) = 'AT'
AND SKU NOT LIKE 'OFF_%' AND SKU NOT LIKE 'PDT%'
AND ATTRIBUTES_DE_AT IS NOT NULL;

And it throws an ORA-22992 error.

I've made some research and a tip can to use the dual

SELECT DISTINCT P.SKU, SUBSTR(X.ATTRIBUTENAME, 14, 3) ATTRIBUTECODE, X.ATTRIBUTEVALUE
FROM PRODUCT@ISPSTAG2 P,
XMLTABLE('/attrs/attr'
    PASSING XMLTYPE(REGEXP_REPLACE(P.ATTRIBUTES_DE_AT, '<attr name="longDescription">.*?<\/attr>'))
    COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH '@name',
            ATTRIBUTEVALUE VARCHAR2(4000) PATH '/string'
) X
WHERE X.ATTRIBUTENAME LIKE 'Z_CA%'
AND DN(DOMAINID) = 'AT'
AND SKU NOT LIKE 'OFF_%' AND SKU NOT LIKE 'PDT%'
AND (SELECT ATTRIBUTES_DE_AT FROM DUAL) IS NOT NULL;

But now I get ORA-06502/ORA-06512 errors :

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à "SYS.XMLTYPE", ligne 272
ORA-06512: à ligne 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

But, I executed the first one on ISPSTAG2 and it works, but the second one on ISPSTAG2 returns me the same ORA-06502/ORA-06512 errors so the issue is with the dual subquery.

I also tried to create a view on ISPSTAG2 using DBMS_LOB.SUBSTR with 4000 characters but same error.

Any ideas ? Thank you.

Well when I remove the DN(DOMAINID) oracle does not throw any errors: dbfiddle.uk/… what is DN ? — VBoka
DN() is just an inner join function which helps me to filter with the country. You can replace it with : ``` AND P.DOMAINID = 'SG0K_clgVhkAAAFSNGUf5PTo' ``` I also tried to select just ATTRIBUTES_DE_AT and no error, but when I use any function like XMLISVALID, XMLTABLE, etc it returns me an error, so I guess a XML error inside the LOB, but how can I find it ? Using ROWNUM < 10 the request is good, but < 100 it returns the error — Dimitri
And when you remove that line of code does the second code work ? — VBoka

Dimitri Dimitri · Accepted Answer · 2019-11-15T14:39:33

If forgot to use the (SELECT ATTRIBUTES_DE_AT FROM DUAL) subquery inside the XMLTYPE...

SELECT DISTINCT P.SKU, SUBSTR(X.ATTRIBUTENAME, 14, 3) ATTRIBUTECODE, X.ATTRIBUTEVALUE
FROM PRODUCT@ISPSTAG2 P,
XMLTABLE('/attrs/attr'
    PASSING XMLTYPE(REGEXP_REPLACE(**(SELECT ATTRIBUTES_DE_AT FROM DUAL)**, '<attr name="longDescription">.*?<\/attr>'))
    COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH '@name',
            ATTRIBUTEVALUE VARCHAR2(4000) PATH '/string'
) X
WHERE X.ATTRIBUTENAME LIKE 'Z_CA%'
AND DN(DOMAINID) = 'AT'
AND SKU NOT LIKE 'OFF_%' AND SKU NOT LIKE 'PDT%'
AND (SELECT ATTRIBUTES_DE_AT FROM DUAL) IS NOT NULL;

The thing I don't understand is that when don't use the subquery in the IS NOT NULL filter I have the the ORA-22992 error (using distant LOB), so why I have a different error not using the dual subquery, which is the same distant LOB ?

Anyway for you for your time/help :)

ORA-22992 / ORA-06502+ORA-06512 errors

1 Answers