ORA-04031 - too many parameters?

[MattWoberts]

Hi all,

We are experiencing an ORA-04031 error when we try to run a SQL statement that contains 3 IN statements - e.g. "where X in (1,2,3,4,5..)". The numbers that are in my "IN" are all parameterized, so theres quite a few parameters (about 260). This seems to cause oracle to fail and run out of shared memory

Is there a way to make this SQL statement run? I am aware that I can change the init.ora file and increase the shared pool, but it never seems to have enough...

Any help appreciated!

SELECT
    "PERFORMANCE_CACHE"."TARGET_ID" AS "Target_ID",
    "PERFORMANCE_CACHE"."LASTRECACHE_DATE" AS "LastReCache_Date",
    "PERFORMANCE_CACHE"."SITE_ID" AS "Site_ID",
    "PERFORMANCE_CACHE"."GROUP_ID" AS "Group_ID",
    "PERFORMANCE_CACHE"."CACHEVALUE" AS "CacheValue",
    "PERFORMANCE_CACHE"."BANDINGZONE" AS "BandingZone",
    "PERFORMANCE_CACHE"."DIRTYYN" AS "DirtyYN",
    "PERFORMANCE_CACHE"."DATASTATUSFLAG" AS "DataStatusFlag" 
FROM
    "ENVOY"."PERFORMANCE_CACHE" 
WHERE
    ( 
    ( "PERFORMANCE_CACHE"."SITE_ID" IN 
        (
** LIST OF 220 SITE ID's **
        ) Or
        "PERFORMANCE_CACHE"."GROUP_ID" IN 
        (
            :Group_ID222,
            :Group_ID223,
            :Group_ID224
        )
    ) And
"PERFORMANCE_CACHE"."TARGET_ID" IN 
(
    ** LIST OF 20 OR SO TARGET ID's **

)
)

 

[donvittorio]

Here's a couple of thoughts which may or may not be useful...

If appropriate then you could use 'between', i.e.

WHERE "PERFORMANCE_CACHE"."SITE_ID" between :lowval and :highval

Or if your site ids can be found with an sql statement then you could use a sub-select, i.e.

WHERE "PERFORMANCE_CACHE"."SITE_ID" IN
(SELECT ID FROM SITE WHERE.....)

Steve