Re: Return table set in stored procedure

"Nils Magnus Englund" <nils.magnus.englund_at_orkfin.no> wrote in message news:<WlWod.553$72.180214272_at_news.telia.no>...
> Hi!
>
> I'm quite new to Oracle, coming from a background of dabbling a bit with
> Microsoft SQL Server (although I'm not particularly experienced with SQL
> Server either).
>
> What is Oracles equivalent of a stored procedure with parameters, returning
> a result set?
>
> e.g. in MSSQL I could do this:
>
> CREATE PROCEDURE SelectMoreThan (@Value INT) AS
> BEGIN
> SELECT * FROM table WHERE column > @Value
> END
>
> And running "EXEC SelectMoreThan 5" would return to me all rows with a
> Column value exceeding 5. How would I accomplish the same in Oracle?
>
>
>
> Thanks!
>
>
>
> Sincerely,
> Nils Magnus Englund

This is a FAQ.
There are several things people exposed to Sqlserver need to do for a start

- forget everything about sqlserver
- don't 'port' any bad habit from sqlserver to Oracle
- learn to read manuals, especially the Concepts Manual
- Buy the two books of Thomas Kyte and treat them as your gospel.

You would need to look up 'REF CURSOR' in your oracle doco.

The equivalent would be

CREATE or replace PROCEDURE SelectMoreThan (refc out REF CURSOR, p_Value number) AS
 BEGIN
   open refc for "SELECT * FROM table WHERE column > :p_value" using p_value;
 END;
/

Note: as this is dynamic sql (as opposed to static sql), it will be (soft-)parsed everytime you call it, consequently you are going to have an application with limited scalability.

Regards

--
Sybrand Bakker
Senior Oracle DBA