Re: Basic Proc Question

"Brian Forster" <brian.forster_at_cnhiindiana.com> wrote in message news:3a79a729$0$1510$7ea90a65_at_news.netdirect.net...
> Hi All,
>
> I realize this is a very basic question, but I come from a SQL Server
> background and I am trying to grip the following difference between SQL
 Serv
> and Oracle.
>
> Basically I want to create a procedure that is nothing more than a select
> statement that accepts a parameter.
>
> Here is what I have:
>
> create procedure Daily_Ads
> (rep_no IN varchar2)
> as
> Begin
> Select adno,unet from ad where vnoflag = 'Y' and receiver
 =
> rep_no
> and prdate =
> to_char(to_date(Sysdate,'MM/DD/YYYY'),'MM/DD/YYYY');
> End;
>
> Then call it execute Daily_Ads('A23');
>
> The procedure is marked invalid because I don't have an insert into. I
> don't want one, I just want to return records. Can I do this?
>
> Please Help
>
> TIA
>
> Brian
>
>

Two remarks: I don't think it is advisable to move to Oracle just by porting sqlserver code. Oracle behaves completely different, and pl/sql behavior is completely different from ordinary selects. So, I advise against just porting your stored procedures.
Second remark: Your question has been answered a zillion of times. You need a concept called REF CURSOR
It is defined in the PL/SQL reference manual It is defined in Steven Feuersteins book on PL/SQL , published by O'Reilly And last not but least on
http://osi.oracle.com/~tkyte, topic resultsets

and of course in this newsgroups archives at www.deja.com

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Feb 01 2001 - 13:41:47 CST