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
On Mon, 20 Apr 2009, E R wrote:
Date: Mon, 20 Apr 2009 16:26:06 -0500 From: E R <[email protected]> To:
Johannes Gritsch <[email protected]> Cc: "[email protected]"
<[email protected]> Subject: Re: an explain plan for Oracle queries
with placeholders
Ok - here's another attempt:
my $prefix = "EXPLAIN PLAN SET STATEMENT_ID = '$$' FOR ";
my $sql = "SELECT * FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = :1";
my @params = qw(v8799);
$dbh->do("$prefix$sql", undef, @params);
which yields:
ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName)
[for Statement "EXPLAIN PLAN SET STATEMENT_ID = '25287' FOR SELECT *
FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = :1"]
On Mon, Apr 20, 2009 at 3:15 PM, Johannes Gritsch
<[email protected]> wrote:
Try using :val instead of ?
Oracle does not understand that notation.
HTH
Hannes
E R wrote:
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]
<[email protected]> wrote:
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 [mailto:[email protected]]
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
--
--
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)
--
---//---
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
