Passing field name as parameter value in procedure

[Dynapen]

I need to see if you can pass a field name as a parameter value in a stored procedure.

I have several stored procedures that run my web site. The problem is that I have to call them and speficiy which of several hard coded sort orders I would like the results returned in. What I would like to do it pass the name of the field I want to sort by to the procedure as a variable.

Create procedure report1 (sort_var varchar2)
as
begin
select * from scott.emp
order by sort_var;
end;
/

This way, I can have the page dynamically insert the value for the sort field depending on what the user wants, and I don't have to hard code all the sort options into the page and the procedure together. I can cheat around it in SQL Server by doubling the variable.

Create procedure report1 @sort_var varchar
as
declare @sql
@sql = "Select * from scott.emp
order by" & @sort_var & ";"

exec @sql
GO

It works here becuase it all gets reparsed into 1 string under the variable @SQL before it get syntax checked to be executed.

Is there a way to do this in Oracle. I am fairly familiar with PL/SQL, but not enough that this isn't kicking my rear.

Thanks

 

[sem]

Try to use refcursor.

Create or replace
procedure report1 (sort_var varchar2)
is
type rc is ref cursor;
c rc;
begin
open c for 'select * from scott.emp
order by '||sort_var;
end;

 

[Dynapen]

I will try that, thanks