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

This is related to binding issues in PL/SQL. You will have to use dynamic SQL for accomplishing what you need.

Rangarajan

Magnus S. Petersen wrote:

> 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