Re: ref_cursor was (Sybase vs Oracle)?

>> The benefit's of PL/SQLs tight coupling to the
>> database are very valuable indeed.

I'm having a long hard look at PL/SQL and I'm finding it too losely coupled to the database. At least when comparing to what Sybase does with temporary tables. One can emulate such tables in PL/SQL as a set or paraller arrays, but then these arrays cannot be used in further joins.

The memory space of PL/SQL and Oracle/SQL are somewhat separated, and there is no set-based traffic between them. This may be an advantage in allowing the control loop to be executed from the client, but represents a less tight coupling than temp tables in Sybase.

Andrew Schonberger

On Thu, 03 Dec 1998 23:46:13 -0500, Adrian Hands <AHands_at_sprynet.com> wrote:

>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 Fri Dec 04 1998 - 00:00:00 CST