Re: Query in a Stored Procedure

I appreciate that the answer you got does appear to 'bite your head off'. However this question is asked almost daily, usually by people from an MS background such as yourself. For what its worth searching comp.databases.oracle.server for stored procedure resultset yields the following url as result 1. This will answer your question complete with code snippets.

http://groups.google.com/groups?q=stored+procedure+resultset&hl=en&group=com p.databases.oracle.server&safe=off&rnum=1&selm=7drq8h%24au9%241%40news.inet. tele.dk

As a side note It would perhaps be nice if the Oracle documentation could refer to resultsets as well as ref cursors, if only to cut down on the amount of hassle different terminology causes.

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Roman Sochan" <rsochan_at_removeme.ix.netcom.com> wrote in message
news:9mlets$pct$1_at_slb6.atl.mindspring.net...


> Thanks to all who posted a response.

>

> Jim:

>

> I searched the newsgroup (my ISP's news server only downloaded the last

1900


> messages) on "stored procedure" and I only found one other request similar

> to mine.  The answer in that thread was "Of course [it can be done]", but

it


> didn't say how.

>

> I spent an entire day trying to find the answer in the Oracle

documentation


> (version 7.3.4, aka "unsupported").  I attempted to find the answer in the

> sections on PL/SQL, Stored Procedures and Functions, Triggers, etc.

>

> Alas, I still don't have my answer, and now I've had my head bit off in

this


> newsgroup.

>

> I will go back and read up on "ref cursor"...

>

> Best regards,

> Roman

>

> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message

> news:dDqj7.443347$p33.8505576_at_news1.sttls1.wa.home.com...

> > Search the newsgroup.  This question is asked almost daily.  Have you

> > attempted to even read the manual?  Look up ref cursor.

> > Jim

> > "Roman Sochan" <rsochan_at_removeme.ix.netcom.com> wrote in message

> > news:9mlchf$8qh$1_at_slb2.atl.mindspring.net...

> > > I'm new to Oracle stored procedures (my experience is with MS SQL

Server


> > > stored procedures) and I can't find a way to store a SELECT sql

> statement

> > > that will return the result of a query.

> > >

> > > In SQL Server, it's okay to have a stored procedure in the form:

> > >

> > > CREATE PROCEDURE qryGetDirections

> > > @JobNum as varchar(20)

> > > As

> > > SELECT job, directions_desc

> > > FROM tblDirections

> > > WHERE job = @JobNum

> > >

> > > When it is run, it simply returns a different result set based on the

> > value

> > > of JobNum.  In Oracle, a similar procedure would give me an error

saying


> > > that I'm missing an INTO clause.

> > >

> > > How would I convert the above into PL/SQL and store it as a procedure,

> or

> > am

> > > I not able to store queries in an Oracle stored procedure?

> > >

> > >

> >

> >

>

>