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)
>
