Re: dynamic sql in PL/SQL

Hi Jimmy,

I neglected to mention that one of the downsides of using decode is that it may have issues if the datatypes of your result columns vary. The decode function will return its result in the datatype of the first return value. If this is varchar2, then subsequent values in the decode will be implicitly converted to varchar2, and then the ordering applied to the result. This means that number columns could be sorted as varchars.

So in the example I gave, you should take this into account by explicitly converting result columns to varchars as follows:

select *
  from emp
order by
decode(:order_by_bind_variable,'NAME',ename,'ID',lpad(to_char(empno,30)))

The lpad ensures that the numbers sort correctly (i.e. numerically) even though they are varchars.

As far as performance is concerned, the dynamic statements will result in hard parses for each order by varaiation, but once parsed would be faster than the decode technique.

Mike

"jimmy gogo" <james.lawless_at_vhi.ie> wrote in message news:1111574044.473085.173340_at_o13g2000cwo.googlegroups.com...
> Hi, thanks a lot for the help. I have used the decode and it is working
> now. I am still going to try out the dynamic SQL anyway just to learn
> about it. Which might be expected to be more performant? Thanks again,
> Jimmy.
>
Received on Wed Mar 23 2005 - 05:24:40 CST