Hi all,

I have a query which is failing with ORA-22813: operand value exceeds system limits. I have found out that this is due to a bug with 9i. Oracle have suggested an upgrade to 10g which is not feaisable. The problem is the volume of data in one of the tables which is accessed via a dblink. I am just wondering if there a way to re-write the query so that it fetches chunks to data rather the whole dataset? Is this possible via pl/sql cursors? if so, how?

The failing query
=============

SELECT housenumbername, postcode, null, 'N', null, businessind, blockpromo FROM (SELECT PKG_OSAP_SUPPORT.getAddressString(a.o
rganisation_name, a.department, a.po_box_number, a.sub_building_name, a.building_name, a.building_number, a.dep_thoroughfare,
a.thoroughfare, a.dbl_dep_locality, a.dep_locality) || ',' || a.post_town as housenumbername, a.postcode, a2.businessind, a2
.blockpromo
FROM TARGETAREA@EAAPLink t0 ,ADDRESS_POINT_001 a, ADDRESS@EAAPLink a2
WHERE a2.housenumbername(+) = PKG_OSAP_SUPPORT.getAddressString(a.organisation_name,
a.department,
a.po_box_number,
a.sub_building_name,
a.building_name,
a.building_number,
a.dep_thoroughfare,
a.thoroughfare,
a.dbl_dep_locality, a.dep_locality) || ',' || a.post_town
AND a2.postcode(+) = a.postcode
AND t0.TARGETAREAID = 'f33997b10a051e22004686eaf0bbfbdc'
AND sdo_relate (a.GEOMETRY, t0.SHAPE, 'mask=anyinteract querytype=window') = 'TRUE' MINUS
(SELECT a.HOUSENUMBERNAME, a.POSTCODE, a.BLOCKPROMO, a.BUSINESSIND
FROM PROPERTY@EAAPLink p, ADDRESS@EAAPLink a, RECIPIENT@EAAPLink r , PROPERTYTARGETAREA@EAAPLink pt0
WHERE r.RECIPIENTID = p.RECIPIENTID AND p.ISREMOVED = 'N' AND r.STATUS = 'active' AND a.ADDRESSID = p.ADDRESSID AND pt0.TARGE
TAREAID = 'f33997b10a051e22004686eaf0bbfbdc' AND p.PROPERTYID=pt0.PROPERTYID))
ORDER BY postcode


Thanks in advance,
Chucks