0

I have a table with below structure:

create table CUST_MODERN_CHANNEL_BRANCH
(
  customer_Num      NUMBER,
  channel_Code  NUMBER(5)
)

Some example data :

   Customer_Num       Channel_Code
  =============     ==============
       1                 5
       1                 4
       1                 6
       2                 3
       3                 5

I wrote a procedure which you can see the code below :

create or replace procedure query_test(res          out sys_refcursor,
                                       order_by     number,
                                       customer_num number) is
  qry varchar2(1000) := ' select * from cust_modern_channel_branch ';
begin

  if customer_num is not null then
    qry := qry || ' where customer_num = :cust ';
  end if;

  if order_by is not null then
    qry := qry || ' order by :ord ';
  end if;
    
        --******* Generate Result 

  if customer_num is not null then
    if order_by is not null then
      open res for qry
        using customer_num, order_by;
    else
      open res for qry
        using order_by;
    end if;
  end if;
end;

I'm using bind variable in my procedure to protect it from injection and make it better in terms of performance. The problem is that when I TEST my procedure with input parameters customer_num=1 / Order_by=2 the final result is not sorted on the second column and it is like this:

  Customer_Num       Channel_Code
  =============     ==============
       1                 5
       1                 4
       1                 6

But when I write a query with order by, it gives me the output below:

select *
from CUST_MODERN_CHANNEL_BRANCH t
where t.customer_num = 1
order by 2

  Customer_Num       Channel_Code
  =============     ==============
       1                 4
       1                 5
       1                 6

Is that something wrong with my procedure? Or the way I used bind variables? Thanks in advance

2

order by 2 does not equal to order by :ord where :ord = 2.

With order by 2, 2 represents a position, meaning the result must be sorted always by 2nd column in select list.

With order by :ord, :ord is an expression, which will be evaluated to a value, and sorting will be done by that value, not a column.

If you use order by :ord where :ord = 2, that is handled like like order by '2' (logically, ignore the type difference for now).

Do not bind the order by position.

2
  • Thanks for you answer . My question is that why "order by :ord" is handled like order by '2' ? I mean WHY exactly does Oracle engine do that?? Why does not it understand that it should be treated like custumer_num=:cust ? – Pantea Dec 21 '20 at 11:51
  • 1
    Because you can provide a variety of valid values for :ord and they may affect the optimizer to produce different execution plans based on the column used for sorting and it was not prepared for this case, not even in 19c. – Balazs Papp Dec 21 '20 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.