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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ramick
Sent: Monday, April 24, 2006 9:37 PM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: