Bind variables, library cache and SQL sharing

From: ramick <ramick_at_dotster.com>
Date: Mon, 24 Apr 2006 18:37:21 -0700
Message-Id: <20060425013725.541A8300784@turing.freelists.org>

Oracle 9205 RAC server, PHP4 client.

One of my developers posed a very good question that I don't have an answer for:
Is there any way to do dynamic query binding?

Let's say we have a variable list of things that we want for an in-list; we end up with:

SELECT blah FROM blah WHERE blah in (:BV0);
SELECT blah FROM blah WHERE blah in (:BV0, :BV1);
SELECT blah FROM blah WHERE blah in (:BV0, :BV1, :BV2);
SELECT blah FROM blah WHERE blah in (:BV0, :BV1, :BV2, :BV3);

...
SELECT blah FROM blah WHERE blah in (:BV0, ..., :BV499);

These won't be shared as they are different; we end up with 500 cursors in the library cache.

Is there anything like:
do_query_bind(

   "SELECT blah FROM blah WHERE blah in :BINDLIST",    array('BINDLIST' => $biglistofmanythings));

The bindlist array could be a dynamic table in PL/SQL, but what in PHP?

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 24 2006 - 20:37:21 CDT