RE: Bind variables, library cache and SQL sharing

PL/SQL pipelined function, in 9i and up. Before 9i, it's a bit more complicated.

Go to http://www.asktom.oracle.com/ and do a search on "variable in list". Lots of hits, lots of examples.

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ramick Sent: Monday, April 24, 2006 9:37 PM
To: oracle-l_at_freelists.org
Subject: Bind variables, library cache and SQL sharing

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 24 2006 - 20:43:35 CDT