# OCI-8, Oracle : 'ORDER BY' doesn't work with 'bind\_param'

**URL:** <https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770>\
**Category:** Ruby\
**Created:** [March 13, 2009, 4:30pm UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770 "2009-03-13T16:30:28Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![randy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/2acd7d/32.png) [@randy](https://www.ruby-forum.com/u/randy)\
**Post date:** [March 13, 2009, 4:30pm UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770/1 "2009-03-13T16:30:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![randy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/2acd7d/32.png) [@randy](https://www.ruby-forum.com/u/randy)\
**Post date:** [March 13, 2009, 5:04pm UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770/2 "2009-03-13T17:04:11Z")

</div>

2009/3/13 B. Randy [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![randy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/2acd7d/32.png) [@randy](https://www.ruby-forum.com/u/randy)\
**Post date:** [March 13, 2009, 5:28pm UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770/3 "2009-03-13T17:28:20Z")

</div>

Hi,

On Sat, Mar 14, 2009 at 12:28 AM, B. Randy [removed\_email\_address@domain.invalid](mailto: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.

---

<div class="post-metadata">

**Author:** ![randy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/2acd7d/32.png) [@randy](https://www.ruby-forum.com/u/randy)\
**Post date:** [April 2, 2009, 12:41am UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770/4 "2009-04-02T00:41:22Z")

</div>

2009/3/31 Chris J. [removed\_email\_address@domain.invalid](mailto: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](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

---

<div class="post-metadata">

**Author:** ![randy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/2acd7d/32.png) [@randy](https://www.ruby-forum.com/u/randy)\
**Post date:** [April 21, 2009, 4:21pm UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770/5 "2009-04-21T16:21:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![randy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/2acd7d/32.png) [@randy](https://www.ruby-forum.com/u/randy)\
**Post date:** [March 13, 2009, 6:22pm UTC](https://www.ruby-forum.com/t/oci-8-oracle-order-by-doesnt-work-with-bind-param/162770/6 "2009-03-13T18:22:48Z")

</div>

Hi,

Thanks to you Robert and Takehiro for your fast replies 🙂

Randy