FAQ
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

Search Discussions

  • E R at Apr 20, 2009 at 8:12 pm
    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:
    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: 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 at Apr 20, 2009 at 8:16 pm
    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]
    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
    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.gritsch@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)
  • E R at Apr 20, 2009 at 9:26 pm
    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
    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]
    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
    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.gritsch@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)
  • Mike Nhan at Apr 20, 2009 at 9:43 pm
  • E R at Apr 20, 2009 at 9:48 pm
    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 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 <pc88mxer@gmail.com> To:
    Johannes Gritsch <Johannes.Gritsch@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
    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]
    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
    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.gritsch@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
  • Steve Baldwin at Apr 20, 2009 at 9:20 pm
    Have you tried replacing the question marks with named placeholders?
    DBD::Oracle supports both and I'm pretty sure you can do an explain
    plan with named placeholders.

    Steve
    On 21/04/2009, at 6:12 AM, 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]
    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
    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

    This email is intended solely for the use of the addressee and may
    contain information that is confidential, proprietary, or both.
    If you receive this email in error please immediately notify the
    sender and delete the email.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedApr 20, '09 at 7:25p
activeApr 20, '09 at 9:48p
posts7
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase