PL/SQL Using variables in an Explicit Cursor statement

Question

I'm declaring an explicit cursor, however, I need to order the sql differently depending on user interaction. The column to order by is passed to the procedure as a parameter, however, I'm having difficulty incorporating it into the sql for the cursor (as near as I can tell, it is interpreting the variable name as the name of the column and thus ordering by nothing.

Is there a way to use a locally declared variable in an explicit cursor statement?

Here's a basic skeleton of what I'm attempting thus far:

v_order_by varchar2(100) := <function that grabs URL param>
c_cursor is
select...
.
.
.
order by v_order_by;

Any help is greatly appreciated. All the examples of using explicit cursors I've found so far are entirely unhelpful.

Tony Andrews · Accepted Answer · 2010-02-04 16:10:08Z

2

If the possible values of v_order_by are static, you can do this:

order by case v_order_by
            when 'EMPNO' then empno
            when 'DEPTNO then deptno
            end

Otherwise you will need to use dynamic SQL and a ref cursor:

declare
   v_refcursor sys_refcursor;
   ...
begin
   open v_refcursor for   
     'select...
     order by ' || v_order_by;

If you do that, be sure to learn how to use bind variables rather than literals in your dynamic SQL where clause.

answered Feb 4, 2010 at 16:10

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hypnotic Meat Over a year ago

Thanks for the help. Unfortunately, it seems that case statements require all the possible values to be the same type. This doesn't work for me since I have several columns that need to be sorted like numbers and others that need to be sorted like Strings. I ended up changing everything to a ref cursor. Thanks again.

Jeffrey Kemp Over a year ago

You can sort strings and numbers together, you just need to convert the numbers to strings, e.g. TO_CHAR(mynum,'fm00000000') will left-pad integers with zeroes so that they are ordered correctly when sorted alphabetically.

Collectives™ on Stack Overflow

PL/SQL Using variables in an Explicit Cursor statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related