Re: ref_cursor was (Sybase vs Oracle)?

Thomas Kyte wrote:

> A copy of this was sent to Joe & Anne Buhl <jabuhl_at_worldnet.att.net>
> (if that email address didn't require changing)
> On 2 Dec 1998 04:36:56 GMT, you wrote:
>
> >First I called 5 experienced Oracle DBA's and only one had ever used Ref Cursors.
> >She only described them to me as "not pretty".
> >
>
> well, most dba's I know do not write application code -- why would they know
> about it?
>

That would depend on your definition of DBA. If you're talking about a DBA who manages tables ... yep, they wouldn't need to know about ref cursors. But if you're talking about a DBA who works in development, coding on the server (triggers, stored procs), doing P&T, and training/overseeing developer access to the database ... then the DBA *would* need to know about ref cursors. (Not an argument ... just an example.)

> elaborate please? what was hard about them? I mean, to write a simple routine
> and use this in sqlplus to return a result set, all it takes is:
>
> SQL> create procedure show_emps( p_cur in out types.refCur )
> 2 as
> 3 begin
> 4 open p_cur for select * from emp;
> 5 end;
> 6 /
>
> Procedure created.
>
> SQL> variable x refcursor
> SQL> exec show_emps( :x );
>
> PL/SQL procedure successfully completed.
>
> SQL> print x;

OK, now compare your coding to the following:

sql> create procedure show_emps
sql> as
sql> select * from em;
sql> return
sql> go
sql> exec show_emps
sql> go

Every time you want to run your stored proc you now have to setup the refcursor variable, run the proc, and print the variable. All I have to do is run the proc and I'm done.

In this case Sybase is easier to code and execute.

Of course, you can find places where Oracle features are easier to code than in Sybase, e.g., exception/error handling.

--
Mark A. Parsons
Iron Horse, Inc.