Question about prepare() versus prepare_cached()

Does prepare_cached() only operate on the current DB handle or will it
cause Oracle to cache the sql in shared memory as well? For example, I
run the following sql from Perl CGI programs about 30,000 time a day:

$script =
"select a.statid, b.webstatdesc, a.pendid, c.penddesc
from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
where a.statid = b.statid
and a.pendid = c.pendid (+)
and tranid = 1
and certno = ?";

If I execute it some thing like:

$dbh = DBI->connect($dbsource,$user,$pass,\%attr);

$sth = $dbh->prepare_cached($script);

$rv = $sth->execute($certno);

undef $sth;
undef $dbh;

will the cached script be available for the next DB handle I open?

--
Stephen Carville
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602

Re: Question about prepare() versus prepare_cached()

Forget I said anythng. The DBA explained to me that Oracle take care of
caching scripts in shared memory allon its own.

Stephen Carville wrote:
> Does prepare_cached() only operate on the current DB handle or will it
> cause Oracle to cache the sql in shared memory as well? For example, I
> run the following sql from Perl CGI programs about 30,000 time a day:
>
> $script =
> "select a.statid, b.webstatdesc, a.pendid, c.penddesc
> from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
> where a.statid = b.statid
> and a.pendid = c.pendid (+)
> and tranid = 1
> and certno = ?";
>
> If I execute it some thing like:
>
> $dbh = DBI->connect($dbsource,$user,$pass,\%attr);
>
> $sth = $dbh->prepare_cached($script);
>
> $rv = $sth->execute($certno);
>
> undef $sth;
> undef $dbh;
>
> will the cached script be available for the next DB handle I open?
>


--
Stephen Carville
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602