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

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-forum.com/.

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

···

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

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

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

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.

···

On Sat, Mar 14, 2009 at 12:28 AM, B. Randy <randy11@numericable.fr> wrote:

Hi,

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

Randy

···

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

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

···

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

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

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

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 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-forum.com/\.