Re: ref_cursor was (Sybase vs Oracle)?

Thomas Kyte wrote:

> secondly - since most processing that needs to be done in fact can be done by
> pl/sql (as opposed to some other language), why use ref cursors to return result

I pushed to get PL/SQL utilized for development in our office and since then it's really taken off.
MAJOR portions of our code are now in PL/SQL. The benefit's of PL/SQLs tight coupling to the database are very valuable indeed.

...however...

my current feeling is that (with Oracle 7, at least) we'd do better to use a little less PL/SQL.
At least take those procedures that run for more than a few minutes and move the outer loop to the client/host language so that the client is making shorter duration calls to Oracle. This is because PL/SQL can be really difficult to debug. When a procedure takes too long it's very difficult to determine exactly WHERE it's bogged down, it's difficult to kill a wayward procedure, it's difficult to determine which procedure belongs to who, the Oracle error messages are frequently WRONG when they give you the line # for the error...etc.

One way to break-up a procedure is to migrate it into a package where cursors and variables can maintain state across multiple requests. However the downside of this "statefullness" is that it makes it impossible to pool that database connection, so I'm reluctant to charge forth with that type of design.

Any comments ?

BTW, I really appreciate Oracle's presence on this ng ! Received on Wed Dec 02 1998 - 00:00:00 CST