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

Thanks for your answer. I am unfamiliar with dynamic SQL, so apparently i will have to do some study on that topic. As you will se from my code example i have one input parameter sag_id_in which is the primary key and this parameter is used in the WHERE clause < WHERE sag_id = sag_id_in> and that gives no problems. Are you sure of this.
It seems strange that i can use an input parameter there but not in the ORDER BY clause.
Regards
Magnus

Rangarajan Radhakrishnan <ranx_at_worldnet.att.net> skrev i artiklen <6fjg7l$298_at_bgtnsc02.worldnet.att.net>...
> 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 Sun Mar 29 1998 - 00:00:00 CST