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
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 :
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.
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.
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.
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.
$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.
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.
This solve my problem.