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

B. Randy

3/13/2009 3:28:00 PM

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
--
Posted via http://www.ruby-....

5 Answers

Robert Klemme

3/13/2009 4:01:00 PM

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 :how=
"
> 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. 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

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

KUBO Takehiro

3/13/2009 4:26:00 PM

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 =3D OCI8.new('scott', 'tiger', 'My_Base_Oracle')
>
> # Request with 3 parameters.
> request =3D "select * from emp
> where SUBSTR(ENAME,1,LENGTH(:who)) =3D :who AND SAL > :paid order by :how=
"
> 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()

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. Randy

3/13/2009 5:21:00 PM

Hi,

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

Randy
--
Posted via http://www.ruby-....

Robert Klemme

4/1/2009 5:39:00 AM

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-or...
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

B. Randy

4/21/2009 2:22:00 PM

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

--
Posted via http://www.ruby-....

comp.lang.ruby

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

B. Randy

Robert Klemme

KUBO Takehiro

B. Randy

Robert Klemme

B. Randy

x Login to ForumsZone