From: Robert Klemme <shortcutter@...>
Date: 2009-04-01T14:38:52+09:00
Subject: Re: OCI-8, Oracle : 'ORDER BY' doesn't work with 'bind_param'

2009/3/31 Chris Jones <christopher.jones@oracle.com>:
> Robert Klemme <shortcutter@googlemail.com> writes:
>
>> 2009/3/13 B. Randy <randy11@numericable.fr>:
>>
>>> # Request with 3 parameters.
>>> request = "select * from emp
>>> where SUBSTR(ENAME,1,LENGTH(:who)) = :who AND SAL > :paid order by :how"
>>> cursor = connex.parse(request)
>>>
>>> # Bind the parameters.
>>> cursor.bind_param(':who', 'A')      # This work.
>>> cursor.bind_param(':paid', 1000)    # This work.
>>> cursor.bind_param(':how', 'ENAME')  # !! This doesn't work ???
>>> cursor.exec()
>>
>> You cannot give the column name as a bind parameter.  You either have
>> to insert it when constructing the statement or you have to have
>> several statements.
>>
>> Btw, I doubt that *any* RDBMS will allow to select a column used for
>> ordering with a bind parameter because that changes semantics of the
>> SQL statement. This would make a recompile of the SQL statement
>> necessary because the execution plan will change every time you invoke
>> it rendering bind parameters useless.
>
> There are various workarounds for binding in an ORDER BY: one is to
> use CASE.  There is a PHP example in "Binding in an ORDER BY Clause"
> on p148 of the current version (Dec 2008) of
> http://www.oracle.com/technology/tech/php/pdf/underground-php-oracle-manual.pdf

Qute from the document:

$s = oci_parse($c, "select first_name, last_name
from employees
order by
case :ob
when 'FIRST_NAME' then first_name
else last_name
end");
oci_bind_by_name($s, ":ob", $vs);
oci_execute($s);

That's a bad hack and is likely to screw execution plans.  Using
multiple SQL statements is superior since Oracle's CBO can then handle
this much easier.  The DBA will also have a hard time optimizing this
because he sees just a single statement.  Whereas with different
statements of which some are slow he immediately sees the proper SQL.
Also, you get better statistical evaluations.

Kind regards

robert

-- 
remember.guy do |as, often| as.you_can - without end