Home » SQL & PL/SQL » SQL & PL/SQL » "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data (Oracle 11g)
| "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652605] |
Tue, 14 June 2016 09:59  |
 |
Nikita_01
Messages: 5
Registered: June 2016
|
Junior Member |
|
|
Hi All,
I am facing this issue with below query that fetches data from various table and creates an XML. The query returns data in the format that conforms to XSD file so I cannot modify the query in the way that changes the format of XML nodes. The query is:
select TO_CLOB(XMLElement("xml",
(
select XMLAGG(XMLElement("lob", XMLAttributes(V1.LOB_ID AS "lobValue"),
(
select XMLAGG(XMLElement("language",XMLAttributes(V2.LANG_ID AS "languageValue"),
(
select XMLAGG(XMLElement("siteId",XMLAttributes(V3.SITE_ID AS "site_id",V3.ICOMS_ID AS "icoms_id"),
(
select XMLAGG(XMLElement("exitPoint",
XMLAttributes(V4.REASON_NAME AS "ivr_reason",V4.CPR_ELIGIBLE AS "cpr_eligible",V4.CALLER_TYPE AS "caller_type", V4.ROUTING_VDN AS "routing_vdn",V4.EXITPOINT_ID AS "exit_point")))
from (select EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V4
where V3.SITE_ID = V4.SITE_ID and V3.ICOMS_ID = V4.ICOMS_ID and V4.LANG_ID = V3.LANG_ID and V4.LOB_ID=V3.LOB_ID
)
))
from (select SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V3 where V3.LANG_ID=V2.LANG_ID and V3.LOB_ID=V2.LOB_ID
)
) )
from (select LOB_ID, LANG_ID from V_VDN_MAP group by LOB_ID, LANG_ID) V2 where V2.LOB_ID=V1.LOB_ID
)
))
from V_VDN_MAP V1
group by V1.LOB_ID) ) )
AS "RESULT"
from V_VDN_MAP;
I cannot share the data as it is of client. This query is supposed to return a single row. I understand that the issue is because of the size of XMLTYPE data exceeding the system limits. If I remove one level of aggregation then the query runs fine and returns 2 rows. But I need to run this query.
Can anyone share some workaround?
|
|
|
|
| Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652607 is a reply to message #652605] |
Tue, 14 June 2016 11:09  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
You can use SQL Formatter to format your queries.
ORA-22813: operand value exceeds system limits
*Cause: Object or Collection value was too large. The size of the value
might have exceeded 30k in a SORT context, or the size might be
too big for available memory.
*Action: Choose another value and retry the operation.
Quote:I cannot share the data as it is of client.
Just change the characters to make them meaningless but it is important we can reproduce what you have to see if we have the same error and search for a workaround. So please post CREATE TABLE and INSERT statements for a [[test case]) that reproduces the error and we can execute.
But the first thing I'd say, is rewrite the query to remove all SELECT in SELECT clause of external query.
[Updated on: Wed, 15 June 2016 00:17] Report message to a moderator |
|
|
|
| Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652620 is a reply to message #652605] |
Tue, 14 June 2016 21:28 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What happens if you remove the TO_CLOB? Do you still get the error? That might help narrow down the problem area.
I don't know if it will make a difference or not, but you might try using
XMLELEMENT(...).GETCLOBVAL()
instead of
TO_CLOB(XMLELEMENT(...))
They both return the same results, same datatype as shown below, but GETCLOBVAL is intended for use with XML, so there may not be as many restrictions.
-- TO_CLOB:
SCOTT@orcl_12.1.0.2.0> SELECT TO_CLOB
2 (XMLELEMENT
3 ("Department",
4 XMLAGG
5 (XMLELEMENT
6 ("Employee",
7 e.job||' '||e.ename)
8 ORDER BY ename))) as "Dept_list"
9 FROM emp e
10 WHERE e.deptno = 30
11 /
Dept_list
--------------------------------------------------------------------------------
<Department><Employee>SALESMAN ALLEN</Employee><Employee>MANAGER BLAKE</Employee
><Employee>CLERK JAMES</Employee><Employee>SALESMAN MARTIN</Employee><Employee>S
ALESMAN TURNER</Employee><Employee>SALESMAN WARD</Employee></Department>
-- GETCLOBVAL():
SCOTT@orcl_12.1.0.2.0> SELECT XMLELEMENT
2 ("Department",
3 XMLAGG
4 (XMLELEMENT
5 ("Employee",
6 e.job||' '||e.ename)
7 ORDER BY ename)).GETCLOBVAL() as "Dept_list"
8 FROM emp e
9 WHERE e.deptno = 30
10 /
Dept_list
--------------------------------------------------------------------------------
<Department><Employee>SALESMAN ALLEN</Employee><Employee>MANAGER BLAKE</Employee
><Employee>CLERK JAMES</Employee><Employee>SALESMAN MARTIN</Employee><Employee>S
ALESMAN TURNER</Employee><Employee>SALESMAN WARD</Employee></Department>
1 row selected.
[Updated on: Tue, 14 June 2016 21:31] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652631 is a reply to message #652630] |
Wed, 15 June 2016 04:14 |
|
Nikita_01
Messages: 5
Registered: June 2016
|
Junior Member |
|
|
There is only one table used in the query as you can see and here is the create table query.
CREATE TABLE v_vdn_map
(
site_id NUMBER NOT NULL ENABLE,
icoms_id NUMBER NOT NULL ENABLE,
lob_id NUMBER NOT NULL ENABLE,
lang_id NUMBER NOT NULL ENABLE,
exitpoint_id NUMBER NOT NULL ENABLE,
routing_vdn NUMBER NOT NULL ENABLE,
caller_type VARCHAR2(32),
cpr_eligible CHAR(1),
reason_name NUMBER,
last_change DATE NOT NULL ENABLE
)
|
|
|
|
| Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652632 is a reply to message #652628] |
Wed, 15 June 2016 04:16 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Nikita_01 wrote on Wed, 15 June 2016 10:51The size of data is 480919 characters inside one XML tag.
The size limit of XMLType is 2GB or 4GB, depending on the database character set. So I don't think you run into THAT limit, since the data is only 481kB.
You run into some other size limit, I suspect the 4k varchar2 limit in SQL. But at which point in the query exactly is pretty impossible to tell without having the actual database objects.
|
|
|
|
|
|
| Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652636 is a reply to message #652632] |
Wed, 15 June 2016 05:25 |
|
Nikita_01
Messages: 5
Registered: June 2016
|
Junior Member |
|
|
ThomasG,
The query runs fine if I remove the outermost aggregation and run like this -
select XMLElement("lob", XMLAttributes(V1.LOB_ID AS "lobValue"),
(
select XMLAGG(XMLElement("language",XMLAttributes(V2.LANG_ID AS "languageValue"),
(
select XMLAGG(XMLElement("siteId",XMLAttributes(V3.SITE_ID AS "site_id",V3.ICOMS_ID AS "icoms_id"),
(
select XMLAGG(XMLElement("exitPoint",
XMLAttributes(V4.REASON_NAME AS "ivr_reason",V4.CPR_ELIGIBLE AS "cpr_eligible",V4.CALLER_TYPE AS "caller_type", V4.ROUTING_VDN AS "routing_vdn",V4.EXITPOINT_ID AS "exit_point")))
from (select EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V4
where V3.SITE_ID = V4.SITE_ID and V3.ICOMS_ID = V4.ICOMS_ID and V4.LANG_ID = V3.LANG_ID and V4.LOB_ID=V3.LOB_ID
)
))
from (select SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V3 where V3.LANG_ID=V2.LANG_ID and V3.LOB_ID=V2.LOB_ID
)
) )
from (select LOB_ID, LANG_ID from V_VDN_MAP group by LOB_ID, LANG_ID) V2 where V2.LOB_ID=V1.LOB_ID
)
)
AS RESULT from V_VDN_MAP V1
group by V1.LOB_ID;
This returns me 2 rows of XMLTYPE data
|
|
|
|
| Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data [message #652656 is a reply to message #652636] |
Wed, 15 June 2016 12:55 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If I enter 2 rows of test data, then run your original query, I get "ORA-01427: single-row subquery returns more than one row". If I run your query that returns 2 rows, then I also get 2 rows. So, it sounds like it is not just a limit problem. You need to figure out where your query is causing 2 rows where it is expecting 1. If I comment out the "group by ..." the original query runs without error. You might try that and see whether that eliminates your error or not. Please see my tests below.
-- table you provided:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE v_vdn_map
2 (
3 site_id NUMBER NOT NULL ENABLE,
4 icoms_id NUMBER NOT NULL ENABLE,
5 lob_id NUMBER NOT NULL ENABLE,
6 lang_id NUMBER NOT NULL ENABLE,
7 exitpoint_id NUMBER NOT NULL ENABLE,
8 routing_vdn NUMBER NOT NULL ENABLE,
9 caller_type VARCHAR2(32),
10 cpr_eligible CHAR(1),
11 reason_name NUMBER,
12 last_change DATE NOT NULL ENABLE
13 )
14 /
Table created.
-- 2 rows of simple test data:
SCOTT@orcl_12.1.0.2.0> insert into v_vdn_map values (1, 2, 3, 4, 5, 6, 'caller_type1', 'y', 7, sysdate)
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into v_vdn_map values (8, 9, 10, 11, 12, 13, 'caller_type2', 'n', 14, sysdate)
2 /
1 row created.
-- original query produces error:
SCOTT@orcl_12.1.0.2.0> select TO_CLOB(XMLElement("xml",
2 (
3 select XMLAGG(XMLElement("lob", XMLAttributes(V1.LOB_ID AS "lobValue"),
4 (
5 select XMLAGG(XMLElement("language",XMLAttributes(V2.LANG_ID AS "languageValue"),
6 (
7 select XMLAGG(XMLElement("siteId",XMLAttributes(V3.SITE_ID AS "site_id",V3.ICOMS_ID AS "icoms_id"),
8 (
9 select XMLAGG(XMLElement("exitPoint",
10 XMLAttributes(V4.REASON_NAME AS "ivr_reason",V4.CPR_ELIGIBLE AS "cpr_eligible",V4.CALLER_TYPE AS "caller_type", V4.ROUTING_VDN AS "routing_vdn",V4.EXITPOINT_ID AS "exit_point")))
11 from (select EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V4
12 where V3.SITE_ID = V4.SITE_ID and V3.ICOMS_ID = V4.ICOMS_ID and V4.LANG_ID = V3.LANG_ID and V4.LOB_ID=V3.LOB_ID
13 )
14 ))
15 from (select SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V3 where V3.LANG_ID=V2.LANG_ID and V3.LOB_ID=V2.LOB_ID
16 )
17 ) )
18 from (select LOB_ID, LANG_ID from V_VDN_MAP group by LOB_ID, LANG_ID) V2 where V2.LOB_ID=V1.LOB_ID
19 )
20 ))
21 from V_VDN_MAP V1
22 group by V1.LOB_ID) ) )
23 AS "RESULT"
24 from V_VDN_MAP;
ERROR:
ORA-01427: single-row subquery returns more than one row
no rows selected
-- query that returns 2 rows for you also returns 2 rows for me:
SCOTT@orcl_12.1.0.2.0> select XMLElement("lob", XMLAttributes(V1.LOB_ID AS "lobValue"),
2 (
3 select XMLAGG(XMLElement("language",XMLAttributes(V2.LANG_ID AS "languageValue"),
4 (
5 select XMLAGG(XMLElement("siteId",XMLAttributes(V3.SITE_ID AS "site_id",V3.ICOMS_ID AS "icoms_id"),
6 (
7 select XMLAGG(XMLElement("exitPoint",
8 XMLAttributes(V4.REASON_NAME AS "ivr_reason",V4.CPR_ELIGIBLE AS "cpr_eligible",V4.CALLER_TYPE AS "caller_type", V4.ROUTING_VDN AS "routing_vdn",V4.EXITPOINT_ID AS "exit_point")))
9 from (select EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V4
10 where V3.SITE_ID = V4.SITE_ID and V3.ICOMS_ID = V4.ICOMS_ID and V4.LANG_ID = V3.LANG_ID and V4.LOB_ID=V3.LOB_ID
11 )
12 ))
13 from (select SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V3 where V3.LANG_ID=V2.LANG_ID and V3.LOB_ID=V2.LOB_ID
14 )
15 ) )
16 from (select LOB_ID, LANG_ID from V_VDN_MAP group by LOB_ID, LANG_ID) V2 where V2.LOB_ID=V1.LOB_ID
17 )
18 )
19 AS RESULT from V_VDN_MAP V1
20 group by V1.LOB_ID;
RESULT
--------------------------------------------------------------------------------
<lob lobValue="3"><language languageValue="4"><siteId site_id="1" icoms_id="2"><
exitPoint ivr_reason="7" cpr_eligible="y" caller_type="caller_type1" routing_vdn
="6" exit_point="5"></exitPoint></siteId></language></lob>
<lob lobValue="10"><language languageValue="11"><siteId site_id="8" icoms_id="9"
><exitPoint ivr_reason="14" cpr_eligible="n" caller_type="caller_type2" routing_
vdn="13" exit_point="12"></exitPoint></siteId></language></lob>
2 rows selected.
-- original query without group by:
SCOTT@orcl_12.1.0.2.0> select TO_CLOB(XMLElement("xml",
2 (
3 select XMLAGG(XMLElement("lob", XMLAttributes(V1.LOB_ID AS "lobValue"),
4 (
5 select XMLAGG(XMLElement("language",XMLAttributes(V2.LANG_ID AS "languageValue"),
6 (
7 select XMLAGG(XMLElement("siteId",XMLAttributes(V3.SITE_ID AS "site_id",V3.ICOMS_ID AS "icoms_id"),
8 (
9 select XMLAGG(XMLElement("exitPoint",
10 XMLAttributes(V4.REASON_NAME AS "ivr_reason",V4.CPR_ELIGIBLE AS "cpr_eligible",V4.CALLER_TYPE AS "caller_type", V4.ROUTING_VDN AS "routing_vdn",V4.EXITPOINT_ID AS "exit_point")))
11 from (select EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by EXITPOINT_ID,ROUTING_VDN,CALLER_TYPE,CPR_ELIGIBLE,REASON_NAME,SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V4
12 where V3.SITE_ID = V4.SITE_ID and V3.ICOMS_ID = V4.ICOMS_ID and V4.LANG_ID = V3.LANG_ID and V4.LOB_ID=V3.LOB_ID
13 )
14 ))
15 from (select SITE_ID,ICOMS_ID,LANG_ID,LOB_ID from V_VDN_MAP group by SITE_ID,ICOMS_ID,LANG_ID,LOB_ID) V3 where V3.LANG_ID=V2.LANG_ID and V3.LOB_ID=V2.LOB_ID
16 )
17 ) )
18 from (select LOB_ID, LANG_ID from V_VDN_MAP group by LOB_ID, LANG_ID) V2 where V2.LOB_ID=V1.LOB_ID
19 )
20 ))
21 from V_VDN_MAP V1
22 --group by V1.LOB_ID
23 ) ) )
24 AS "RESULT"
25 from V_VDN_MAP
26 /
RESULT
--------------------------------------------------------------------------------
<xml><lob lobValue="3"><language languageValue="4"><siteId site_id="1" icoms_id=
"2"><exitPoint ivr_reason="7" cpr_eligible="y" caller_type="caller_type1" routin
g_vdn="6" exit_point="5"></exitPoint></siteId></language></lob><lob lobValue="10
"><language languageValue="11"><siteId site_id="8" icoms_id="9"><exitPoint ivr_r
eason="14" cpr_eligible="n" caller_type="caller_type2" routing_vdn="13" exit_poi
nt="12"></exitPoint></siteId></language></lob></xml>
<xml><lob lobValue="3"><language languageValue="4"><siteId site_id="1" icoms_id=
"2"><exitPoint ivr_reason="7" cpr_eligible="y" caller_type="caller_type1" routin
g_vdn="6" exit_point="5"></exitPoint></siteId></language></lob><lob lobValue="10
"><language languageValue="11"><siteId site_id="8" icoms_id="9"><exitPoint ivr_r
eason="14" cpr_eligible="n" caller_type="caller_type2" routing_vdn="13" exit_poi
nt="12"></exitPoint></siteId></language></lob></xml>
2 rows selected.
[Updated on: Wed, 15 June 2016 13:06] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 13:52:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
