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

[B. Randy]

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,LENGTHwho)) = :who AND SAL > aid order by :how"
cursor = connex.parse(request)

# Bind the parameters.
cursor.bind_param(':who', 'A') # This work.
cursor.bind_param('aid', 1000) # This work.
cursor.bind_param(':how', 'ENAME') # !! This doesn't work ???
cursor.exec()

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

 

[Robert Klemme]

# Request with 3 parameters.
request =3D "select * from emp
where SUBSTR(ENAME,1,LENGTHwho)) =3D :who AND SAL > aid order by :how= "
cursor =3D connex.parse(request)

# Bind the parameters.
cursor.bind_param(':who', 'A') =A0 =A0 =A0# This work.
cursor.bind_param('aid', 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]

Hi,

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,LENGTHwho)) =3D :who AND SAL > aid order by :how= "
cursor =3D connex.parse(request)

# Bind the parameters.
cursor.bind_param(':who', 'A') =A0 =A0 =A0# This work.
cursor.bind_param('aid', 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 'aid' 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]

Hi,

Thanks to you Robert and Takehiro for your fast replies

Randy

 

[Robert Klemme]

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 b
when 'FIRST_NAME' then first_name
else last_name
end");
oci_bind_by_name($s, "b", $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]

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.