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.
B. R. (Guest)
on 2009-03-13 17: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
Robert K. (Guest)
on 2009-03-13 18:04
(Received via mailing list)
2009/3/13 B. Randy <removed_email_address@domain.invalid>:

> # 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
Takehiro K. (Guest)
on 2009-03-13 18:28
(Received via mailing list)
Hi,

On Sat, Mar 14, 2009 at 12:28 AM, B. Randy 
<removed_email_address@domain.invalid>
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.
B. R. (Guest)
on 2009-03-13 19:22
Hi,

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

Randy
Robert K. (Guest)
on 2009-04-02 02:41
(Received via mailing list)
2009/3/31 Chris J. <removed_email_address@domain.invalid>:
>>> 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
B. R. (Guest)
on 2009-04-21 18: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 K. 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.