[DBI-users] an explain plan for Oracle queries with placeholders

Instead of a ? as a place holder, try using numeric place holders (eg.
:1, :2...etc)
Example:
Select * from sfile where id = :1

-----Original Message-----
From: E R
Sent: Monday, April 20, 2009 12:25 PM
To: [email protected]
Subject: an explain plan for Oracle queries with placeholders

I'd like to perform an EXPLAIN PLAN on a query that has question mark
placeholders.

I've seen perl scripts which perform EXPLAIN PLAN commands, but the
queries they operate on don't have placeholders.

Any pointers on how I would go about doing this?

Thanks,
ER

Perhaps I should have divulged more of what I am already trying.

I am getting this error:

ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName)
[for Statement "EXPLAIN PLAN SET STATEMENT_ID = '21623' FOR SELECT *
FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = ?" with ParamValues:
:p1='v8799']

when I run this code:

my $prefix = "EXPLAIN PLAN SET STATEMENT_ID = '$$' FOR ";
my $sql = "SELECT * FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = ?";
my @params = qw(v8799);
...
my $esth = $dbh->prepare("$prefix$sql");
unless ($esth) {
die "prepare of EXPLAIN failed";
}

unless ($esth->execute(@params)) {
die "execute of EXPLAIN failed";
}

On Mon, Apr 20, 2009 at 2:42 PM, Nelson, Erick [HDS]
wrote:

Try using :val instead of ?

Oracle does not understand that notation.

HTH
Hannes

E R wrote:

--
--
Johannes Gritsch

_____________________________________________________________

GNC Akademie GmbH

Nussdorfer Laende 23
1190 Wien
Austria - Europe

email    [email protected]
web      http://www.gnc.at

# Phone 0-810-820-462 zum Ortstarif innerhalb Österreichs
# Phone +43-1-3709787 from all countries
# Fax 0-810-820-GNC-99 zum Ortstarif innerhalb Österreichs
# Fax +43-1-3709787-99 from all countries
_____________________________________________________________

company details http://www.gnc.at/gnc3
registered office Austria, 1190 Vienna, Nussdorfer Laende 23
registration number 222339w
vat registration number ATU56000204
court of commercial registration Handelsgericht Wien
legal form Gesellschaft mit beschraenkter Haftung (Ltd)

On Mon, Apr 20, 2009 at 4:43 PM, Mike Nhan wrote:
If you are only interested in an explain plan, why are you passing the value
of the bind_parameter to the explain plan.  Its not necessary.

instead of dbh->do("$prefix$sql", undef, @params);

just run:

$dbh->do("$prefix$sql");

explain plan do not need to have the value of the bind_parameter bound.

Regards,

Michael
--
---//---
Time flies like the wind. Fruit flies like bananas.
--- Groucho Marx

Either write something worth reading or do something worth writing.
--- Benjamin Franklin

A meeting is an event at which the minutes are kept and the hours are lost