Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Return table set in stored procedure

Re: Return table set in stored procedure

From: <sybrandb_at_yahoo.com>
Date: 24 Nov 2004 04:40:09 -0800
Message-ID: <a1d154f4.0411240440.6f9e2778@posting.google.com>


"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
Received on Wed Nov 24 2004 - 06:40:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US