PL/SQL Using input param. to set ORDER By in a cursor ?

From: Magnus S. Petersen <advlaw_at_post.olivant.fo>
Date: 1998/03/28
Message-ID: <01bd581b$91940480$8debb6c2@magnuspe>#1/1

Working with Delphi3 C/S and Personal Oracle. I have constructed some packages on the server with cursors,procedures functions etc.
My problem is to figure out how i can use IN parameters in procedures to set the sorting ORDER BY in a cursor. It compiles fine but does not seem to work.
I have a TDBGRID on a form which is connected to a DataSource which is connected
to a TStoredProc which executes the stored procedure TimeTrack_pkg.GetSagTimeTrack.
I receive the result set which is displayed in the grid but the sorting is not
according to the input parameter.
Here is an extract:

CREATE PACKAGE TIMETRACK_PKG
IS

   TYPE SagTimeTrack_curtype IS REF CURSOR;

   PROCEDURE GETSAGTIMETRACK(Sag_id_in IN Sag.Sag_id%TYPE,

                             SortColumn_in IN VARCHAR2,
                             Cursor_out IN OUT SagTimeTrack_curtype);

END TIMETRACK_PKG; CREATE PACKAGE BODY TIMETRACK_PKG
IS

   PROCEDURE GETSAGTIMETRACK(Sag_id_in IN Sag.Sag_id%TYPE,

   			     SortColumn_in IN VARCHAR2,
                             Cursor_out IN OUT SagTimeTrack_curtype)

   IS
   BEGIN

      OPEN Cursor_out
      FOR
         SELECT
t.Timetrack_id,t.employee_id,t.StartDay,t.StartTime,t.TimeSpent,
                t.IsBillable,t.IsBilled,round(t.TimeSpent * s.Salary/60)
CalcSalary,
                t.Billedkr,t.SubTaskName
           FROM TimeTrack t, SubTask s
          WHERE sag_id = sag_id_in
            AND t.subtask_id = s.subtask_id
            AND s.salary is not null
          ORDER BY SortColum_in;

   END GetSagTimeTrack;

END TimeTrack_pkg;

When i hardcode the colum in ORDER BY the sorting is ok, but using the parameter
does not have any effect. Am i doing something wrong

Regards
Magnus Received on Sat Mar 28 1998 - 00:00:00 CST