Yeah, a common problem for MSSQL people: in T-SQL when you
SELECT in a SP and run that SP you get back the result set as if you
did that SELECT yourself (it just implicitly opens a cursor and returns
it to you). Not the case with PL/SQL. In PL/SQL, you need to declare
a cursor variable, open it for a query and then return that cursor
variable so that client app can fetch from it. For integrity reasons,
strong cursors are preferred, those with RETURN clause specifying
the type of the row this particular cursor returns. But there are also
weak cursors, those for which row type is not known at compile time
(you can call them generic cursors if you wish.) There are several
ways to declare a cursor in PL/SQL. REF CURSOR is one of them,
and it allows to declare a weak cursor that can receive any result set
from any query at run time. You still need to know the row type
to be able to fetch from it, or you need to do a "describe" operation
on that cursor before fetching so that you know which columns of
which types are there. Unfortunately, you can't describe a weak cursor
from PL/SQL (at least, I am not aware of any way to do this except
turning to server-side Java and using JDBC for that, but it's really not
a trivial excercise...)
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"George" <junk_at_ixsis.com> wrote in message news:6EjR9.426444$GR5.139286_at_rwcrnsc51.ops.asp.att.net...
> Wonderful! Thanks
>
> Now this opens a whole new can of worms for me.
>
> The first one to come to mind is the select * statement. I am assuming that
> this can be a lengthy operation if the table has many fields and modifying
> the table later would rise cause to modify all procedures that contain a
> select * from the modified table.
>
> Also how then does the procedure return records to the client, or does it
> not? I then surmize that using procedures to return records to the client is
> might not be the prefered method but rather a view?
>
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E15BA0A.EC90F173_at_exesolutions.com...
> > George wrote:
> >
> > > I am coming from a SQL Server background so be gentle...
> > >
> > > I am trying to get this simple procedure worked out and it compiles
> invalid.
> > > What am I doing wrong
> > >
> > > BEGIN
> > > SELECT SSN FROM STUDENT;
> > > END;
> >
> > You must SELECT into something.
> >
> > PL/SQL is a server language not a client language.
> >
> > So you SELECT statement slelects the value into nothingness.
> >
> > Try this:
> >
> > DECLARE
> >
> > x student.ssn%TYPE;
> >
> > BEGIN
> > SELECT ssn
> > INTO x
> > FROM student;
> > END;
> > /
> >
> > Daniel Morgan
> >
>
>
Received on Sat Jan 04 2003 - 07:17:14 CST
