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


#1

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


#2

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


#3

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.


#4

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/underground-php-oracle-manual.pdf

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


#5

Hello Robert,

I’ve been long to reply, I’m working on other things. But I’ve
tested your solution with success :slight_smile: 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


#6

Hi,

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

Randy