ORA-22813: operand value exceeds system limits
I've got a SQL statement that gives the following error when I execute it:
ERROR:
ORA-22813: operand value exceeds system limits
Here's the SQL statement:
SELECT
/* = /Tar ================================================================== */
XMLElement("Tar",
XMLAttributes(tc.tar_number as "tar_number"),
/* = /Tar/Config =========================================================== */
XMLAgg(XMLElement("Config",
XMLAttributes(decode(tc.target_type,'oracle_database', 'DB Config',
'supp_ias', 'iAS Config', 'supp_apps_system','Ebiz Config',
'supp_apps_forms','Form Server Config',
'supp_apps_cmanager','Concurrent Manager Config',
'supp_apps_apache', 'WebServer Config', tc.target_type) as "data_type",
tc.target_type as "member_target_type",
tc.target_name as "member_target_name",
TO_CHAR((nvl(t.last_load_time,t.load_timestamp)),
'DD-MON-YYYY HH24:MI') as "collection_timestamp"),
/* = /Tar/Config/(Database)/InitParams====================================== */
(SELECT XMLAgg(XMLElement("init_params",
XMLAttributes(ip.name as "name",
ip.value as "value",
ip.target_guid as "target_guid")))
FROM ct$mgmt_db_init_params ip
WHERE ip.target_guid = tc.target_guid AND
ip.name != 'filesystemio_options'),
/* = /Tar/Config/(Ebusiness)/Modules ======================================= */
(SELECT XMLAgg(XMLElement("MODULES",
XMLAttributes(m.target_guid as "target_guid",
m.collection_timestamp as "collection_timestamp",
m.application_name as "application_name",
m.install_status as "install_status",
m.product_version as "product_version",
m.patch as "patch",
m.last_update as "last_update")))
FROM ct$supp_apps_modules m
WHERE m.target_guid = tc.target_guid)
))) as Tar
from
ct$supp_tar_configs tc, mgmt_targets t
where
tc.target_guid = t.target_guid and
tc.target_type not in ('oracle_gtcr_file')
group by
tc.tar_number
The following section of the SQL statement seems to be the problem because when I remove it the SQL executes just fine:
SELECT XMLAgg(XMLElement("MODULES",
XMLAttributes(m.target_guid as "target_guid",
m.collection_timestamp as "collection_timestamp",
m.application_name as "application_name",
m.install_status as "install_status",
m.product_version as "product_version",
m.patch as "patch",
m.last_update as "last_update")))
FROM ct$supp_apps_modules m
Taking out that section yields the following SQL:
SELECT
/* = /Tar ================================================================== */
XMLElement("Tar",
XMLAttributes(tc.tar_number as "tar_number"),
/* = /Tar/Config =========================================================== */
XMLAgg(XMLElement("Config",
XMLAttributes(decode(tc.target_type,'oracle_database', 'DB Config',
'supp_ias', 'iAS Config', 'supp_apps_system','Ebiz Config',
'supp_apps_forms','Form Server Config',
'supp_apps_cmanager','Concurrent Manager Config',
'supp_apps_apache', 'WebServer Config', tc.target_type) as "data_type",
tc.target_type as "member_target_type",
tc.target_name as "member_target_name",
TO_CHAR((nvl(t.last_load_time,t.load_timestamp)),
'DD-MON-YYYY HH24:MI') as "collection_timestamp"),
/* = /Tar/Config/(Database)/InitParams====================================== */
(SELECT XMLAgg(XMLElement("init_params",
XMLAttributes(ip.name as "name",
ip.value as "value",
ip.target_guid as "target_guid")))
FROM ct$mgmt_db_init_params ip
WHERE ip.target_guid = tc.target_guid AND
ip.name != 'filesystemio_options')
))) as Tar
from
ct$supp_tar_configs tc, mgmt_targets t
where
tc.target_guid = t.target_guid and
tc.target_type not in ('oracle_gtcr_file')
group by
tc.tar_number
However, that section I cut out above, if you execute in isolation, it executes just fine.
Can anyone tell me what is wrong with my SQL statement?
Thanks,
Steve