Forum: Ruby OCI-8, Oracle : 'ORDER BY' doesn't work with 'bind_param'

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
3b348bc837b5b409c6f5656fa59b5b34?d=identicon&s=25 B. Randy (randy)
on 2009-03-13 16:30
Hello,
I'm testing the gem 'ruby-oci8'/oci8 (1.0.4) under Windows with Ruby
1.8.6 patchlevel 287. I play with the user SCOTT and the table EMP of
ORACLE.

First, the table :
---------------
Schema = SCOTT, Name =EMP Columns8
EMPNO | NUMBER(4) NOT NULL
ENAME | VARCHAR2(10)
JOB | VARCHAR2(9)
MGR | NUMBER(4)
HIREDATE | DATE
SAL | NUMBER(7,2)
COMM | NUMBER(7,2)
DEPTNO | NUMBER(2) NOT NULL
---------------

The rows :
---------------
7876,ADAMS,CLERK,7788,1983/01/12 00:00:00,1100.0,,20
7499,ALLEN,SALESMAN,7698,1981/02/20 00:00:00,1600.0,300.0,30
7698,BLAKE,MANAGER,7839,1981/05/01 00:00:00,2850.0,,30
7782,CLARK,MANAGER,7839,1981/06/09 00:00:00,2450.0,,10
7902,FORD,ANALYST,7566,1981/12/03 00:00:00,3000.0,,20
7900,JAMES,CLERK,7698,1981/12/03 00:00:00,950.0,,30
7566,JONES,MANAGER,7839,1981/04/02 00:00:00,2975.0,,20
7839,KING,PRESIDENT,,1981/11/17 00:00:00,5000.0,,10
7654,MARTIN,SALESMAN,7698,1981/09/28 00:00:00,1250.0,1400.0,30
7934,MILLER,CLERK,7782,1982/01/23 00:00:00,1300.0,,10
7788,SCOTT,ANALYST,7566,1982/12/09 00:00:00,3000.0,,20
7369,SMITH,CLERK,7902,1980/12/17 00:00:00,800.0,,20
7844,TURNER,SALESMAN,7698,1981/09/08 00:00:00,1500.0,0.0,30
7521,WARD,SALESMAN,7698,1981/02/22 00:00:00,1250.0,500.0,30
---------------

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()

# Output.
while row = cursor.fetch()
  puts row.join(" ")
end
---------------

The output, the selection by ':who' and ':paid' works but the ':how' is
ignored.
---------------
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600.0 300.0 30
7876 ADAMS CLERK 7788 1983/01/12 00:00:00 1100.0  20
---------------

Thank 's for your help.
Randy11
E0d864d9677f3c1482a20152b7cac0e2?d=identicon&s=25 Robert Klemme (Guest)
on 2009-03-13 17:04
(Received via mailing list)
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.

Kind regards

robert
6ece05caebbfa91944047629101bc5ea?d=identicon&s=25 Takehiro Kubo (kubo)
on 2009-03-13 17:28
(Received via mailing list)
Hi,

On Sat, Mar 14, 2009 at 12:28 AM, B. Randy <randy11@numericable.fr>
wrote:
> # 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.
3b348bc837b5b409c6f5656fa59b5b34?d=identicon&s=25 B. Randy (randy)
on 2009-03-13 18:22
Hi,

Thanks to you Robert and Takehiro for your fast replies :)

Randy
E0d864d9677f3c1482a20152b7cac0e2?d=identicon&s=25 Robert Klemme (Guest)
on 2009-04-02 00:41
(Received via mailing list)
2009/3/31 Chris Jones <christopher.jones@oracle.com>:
>>> cursor.bind_param(':who', 'A')      # This work.
>> 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/unde...

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
3b348bc837b5b409c6f5656fa59b5b34?d=identicon&s=25 B. Randy (randy)
on 2009-04-21 16:21
Hello Robert,

I've been long to reply, I'm working on other things. But I've
tested your solution with success :) This solve my problem.

Thanks for the solution and the explanations.

Robert Klemme wrote:

>
> 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
This topic is locked and can not be replied to.