Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > OCI-8, Oracle : 'ORDER BY' doesn't work with 'bind_param'

Reply
Thread Tools

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

 
 
B. Randy
Guest
Posts: n/a
 
      03-13-2009
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 > 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
---------------

The output, the selection by ':who' and 'aid' 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/.

 
Reply With Quote
 
 
 
 
Robert Klemme
Guest
Posts: n/a
 
      03-13-2009
2009/3/13 B. Randy <(E-Mail Removed)>:

> # Request with 3 parameters.
> request =3D "select * from emp
> where SUBSTR(ENAME,1,LENGTH(:who)) =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

 
Reply With Quote
 
 
 
 
KUBO Takehiro
Guest
Posts: n/a
 
      03-13-2009
Hi,

On Sat, Mar 14, 2009 at 12:28 AM, B. Randy <(E-Mail Removed)> 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 > 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.

 
Reply With Quote
 
B. Randy
Guest
Posts: n/a
 
      03-13-2009
Hi,

Thanks to you Robert and Takehiro for your fast replies

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

 
Reply With Quote
 
Robert Klemme
Guest
Posts: n/a
 
      04-01-2009
2009/3/31 Chris Jones <(E-Mail Removed)>:
> Robert Klemme <(E-Mail Removed)> writes:
>
>> 2009/3/13 B. Randy <(E-Mail Removed)>:
>>
>>> # Request with 3 parameters.
>>> request =3D "select * from emp
>>> where SUBSTR(ENAME,1,LENGTH(:who)) =3D :who AND SAL > aid 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('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. =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 200 of
> http://www.oracle.com/technology/tec...p-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

 
Reply With Quote
 
B. Randy
Guest
Posts: n/a
 
      04-21-2009
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 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


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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified procedure could not be found at C:/Perl/lib/DynaLoader.pm line 230. Feyruz Perl Misc 4 10-14-2005 06:47 PM
Exception thrown while accessing Oracle through oracle client =?Utf-8?B?TmVv?= ASP .Net 2 07-12-2005 06:59 PM
Oracle adapter don't work with Oracle 10.1.0.2 v. Client vaidas gudas ASP .Net 1 10-11-2004 01:11 PM
Re: Error Connecting to Oracle after an Oracle shutdown Juha Laiho Java 0 07-30-2003 10:22 AM



Advertisments