Thanks - I didn't realize that bind parameters are ignored/not
necessary for EXPLAIN PLAN.
On Mon, Apr 20, 2009 at 4:43 PM, Mike Nhan <mn...@watson.wustl.edu> 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
>
> On Mon, 20 Apr 2009, E R wrote:
>
>> Date: Mon, 20 Apr 2009 16:26:06 -0500 From: E R <pc88m...@gmail.com> To:
>> Johannes Gritsch <johannes.grit...@gnc.at> Cc: "dbi-users@perl.org"
>> <dbi-users@perl.org> 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
>> <johannes.grit...@gnc.at> 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]
>>>> <erick.nel...@hdsupply.com> 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:pc88m...@gmail.com]
>>>>> Sent: Monday, April 20, 2009 12:25 PM
>>>>> To: dbi-users@perl.org
>>>>> 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 johannes.grit...@gnc.at
>>> 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
