Re: Stored Procedures

I think the question is how to get a result set back from an oracle stored procedure which takes in static parameters.

OK, even if we have dynamic query and use DBMS_SQL, then how do we get a result set from such a stored proc.

What I have done in the past is create a 2 dimensional PL/SQL table,populate my result set into it and then pass back the PL/SQL table. I have also heard from other people that using a ref cursor can do it but dont know how to do that.

-anil

In article <939331189.854374_at_news1.ucsd.edu>,   "Corey Lawson" <clawson_at_bogusucsd.edu> wrote:
> do you want to execute a dynamic query from a proc where the user
passes in
> some parameter
> values (check out the DBMS_SQL package), or just a proc that spits out
an
> SQL?
>
> Can a LONG be used as an OUT parameter for a proc? If so, then you
should
> be able to do it that way, no?
>
> --
> --------------------------------------------------
> Corey Lawson
> clawson_at_bogusucsd.edu
> (remove the 'bogus', as it's my feeble attempt
> to defeat spammer address suckers)
> Marvin wrote in message <7tiufb$7s9$1_at_nnrp1.deja.com>...
> >In ORACLE can a stored procedure be created such that
> >it returns a query. That is, basically instead of a stored procedure
> >that does INSERTS, UPDATES or DELETES , What I want is really a
stored
> >query, a SELECT with a really ugly WHERE caluse that I can pass
> >parameters into. What I don't know is how to get a hold of a query
> >result and pass it back to the caller. Is a stored function that
> >returns a cursor a possibility? Even then how do I throw the cursor
> >back to the issuer of the query?
> >
> >Help me SQL gods, your my only hope
> >
> >--
> >Marvin
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

--
Anil Kamath
SmartOnline Inc. (www.smartonline.com)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 07 1999 - 18:20:13 CDT