From: KUBO Takehiro <kubo@...>
Date: 2009-03-14T01:25:43+09:00
Subject: Re: OCI-8, Oracle : 'ORDER BY' doesn't work with 'bind_param'

Hi,

On Sat, Mar 14, 2009 at 12:28 AM, B. Randy <randy11@numericable.fr> wrote:
> I use 'bind_param' with success for some fields but not with the 'ORDER
> BY' clause used with 'bind_param'. If I make a call with 'ORDER BY
> ENAME' the result is good. My test code :
> ---------------
> require 'rubygems'
> require 'oci8'
>
> # Connect to the table.
> connex = OCI8.new('scott', 'tiger', 'My_Base_Oracle')
>
> # 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()

What you want is "ORDER BY ENAME." But it is equivalent to "ORDER BY 'ENAME'."

> The output, the selection by ':who' and ':paid' works but the ':how' is
> ignored.

The output is not ordered by the contents in the ENAME column, but by the
string constant 'ENAME.' The order is undefined.