Re: Dynamic "Orde by" parameter in Oracle Stored procedure

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 17 Aug 2010 05:44:22 -0700 (PDT)
Message-ID: <3b7ec277-d7a5-4869-bd6b-46e08b9b3392_at_f6g2000yqa.googlegroups.com>



On Aug 16, 3:07 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Amritha wrote:
> > Hi,
>
> > I would like to execute a SELECT * from table1 order by<XYZ>
> > and get the results into IN OUT cursor.
>
> > I want to pass XYZ as input parameter to stored procedure.
> > It is treating it as string.
>
> > For example: if XYZ value is field1, it is executing the SQL statement
> > as
> > SELECT * from table1 order by 'field1'
> > instead of
> > SELECT * from table1 order by field1
>
> > Please help.
>
> I suppose you could use a ref_cursor, but what kind of design requires such construction?- Hide quoted text -
>
> - Show quoted text -

If there are a limited number of desired order by clauses then this can be done using a weakly typed Reference Cursor. Based on the input just assign the correct SQL statement to being the Reference Cursor value.

Depending on the problem trying to be solved another approach might be to define a view that performs the base SQL statement and just specify the desired order by on the query against the join.

Another alternate might be the use of a pipelined function.

Only as a last resort would I use string concatenation and execute immediate to solve the problem using dynamic SQL.

HTH -- Mark D Powell -- Received on Tue Aug 17 2010 - 07:44:22 CDT

Original text of this message