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

Discussion in 'Ruby' started by B. Randy, Mar 13, 2009.

  1. B. Randy

    B. Randy Guest

    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/.
     
    B. Randy, Mar 13, 2009
    #1
    1. Advertising

  2. 2009/3/13 B. Randy <>:

    > # Request with 3 parameters.
    > request =3D "select * from emp
    > where SUBSTR(ENAME,1,LENGTH:)who)) =3D :who AND SAL > :paid order by :how=

    "
    > cursor =3D connex.parse(request)
    >
    > # Bind the parameters.
    > cursor.bind_param(':who', 'A') =A0 =A0 =A0# This work.
    > cursor.bind_param(':paid', 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
     
    Robert Klemme, Mar 13, 2009
    #2
    1. Advertising

  3. Hi,

    On Sat, Mar 14, 2009 at 12:28 AM, B. Randy <> 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 > :paid order by :how=

    "
    > cursor =3D connex.parse(request)
    >
    > # Bind the parameters.
    > cursor.bind_param(':who', 'A') =A0 =A0 =A0# This work.
    > cursor.bind_param(':paid', 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 ':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.
     
    KUBO Takehiro, Mar 13, 2009
    #3
  4. B. Randy

    B. Randy Guest

    B. Randy, Mar 13, 2009
    #4
  5. 2009/3/31 Chris Jones <>:
    > Robert Klemme <> writes:
    >
    >> 2009/3/13 B. Randy <>:
    >>
    >>> # Request with 3 parameters.
    >>> request =3D "select * from emp
    >>> where SUBSTR(ENAME,1,LENGTH:)who)) =3D :who AND SAL > :paid 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(':paid', 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 2008) of
    > http://www.oracle.com/technology/tech/php/pdf/underground-php-oracle-manu=

    al.pdf

    Qute from the document:

    $s =3D oci_parse($c, "select first_name, last_name
    from employees
    order by
    case :eek:b
    when 'FIRST_NAME' then first_name
    else last_name
    end");
    oci_bind_by_name($s, ":eek: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
     
    Robert Klemme, Apr 1, 2009
    #5
  6. B. Randy

    B. Randy Guest

    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 :eek:b
    > when 'FIRST_NAME' then first_name
    > else last_name
    > end");
    > oci_bind_by_name($s, ":eek: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/.
     
    B. Randy, Apr 21, 2009
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. RTJ
    Replies:
    2
    Views:
    4,065
    Sanjay Rana
    Sep 27, 2006
  2. Dima
    Replies:
    0
    Views:
    322
  3. RTJ
    Replies:
    4
    Views:
    729
  4. Krivenok Dmitry
    Replies:
    3
    Views:
    753
  5. Nicolas Couturier

    oci adpater and Oracle

    Nicolas Couturier, Nov 13, 2007, in forum: Ruby
    Replies:
    4
    Views:
    202
    Nicolas Couturier
    Nov 16, 2007
Loading...

Share This Page