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 =3D "select * from emp
>>> where SUBSTR(ENAME,1,LENGTH(:who)) =3D :who AND SAL > :paid order by :h=
ow"
>>> cursor =3D connex.parse(request)
>>>
>>> # Bind the parameters.
>>> cursor.bind_param(':who', 'A') =A0 =A0 =A0# This work.
>>> cursor.bind_param(':paid', 1000) =A0 =A0# This work.
>>> cursor.bind_param(':how', 'ENAME') =A0# !! This doesn't work ???
>>> cursor.exec()
>>
>> You cannot give the column name as a bind parameter. =A0You 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. =A0There 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-manu=
al.pdf

Qute from the document:

$s =3D 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

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