| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ref_cursor was (Sybase vs Oracle)?
A copy of this was sent to Adrian Hands <AHands_at_sprynet.com>
(if that email address didn't require changing)
On Wed, 02 Dec 1998 22:10:49 -0500, you wrote:
>
> 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.
i would disagree -- i do 99.9 of my stuff in plsql, resorting to C only when i have to...
> 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.
If you instrument your code with calls to dbms_application_info, you can sort of monitor it from a distance by looking at one of 3 columns in v$session (check out the spec for dbms-applicatoin-info, it lets you set three columns in your row in v$session. great for letting your pl/sql routine let the outside world know what its doing).
Also, we've written a 'debug' package. I instrument my code with printf like statments. so, my code is littered with:
debug.f( 'I am doing %s for %s', some_variable, some_other_variable );
the debug package normally is a no-op, does nothing. but, if I insert a name of a MODULE (pl/sql package or procedure or function) into a table and a directory to write to, it will start using utl_file to write those messages to a trace file. that way, if I believe a long running routine is going bad -- I can just turn on debug in another session and see my trace and then turn it off again. Be glad to share that with you if you have 7.3.3 or up (need UTL_FILE -- if you don't have it, it won't work)
We've used this when writing an interface to NNTP, IMAP and SMTP in the database (using extprocs in 8.x). My NNTP stuff runs forever -- never stops. debug.f maintains my sanity -- lets me see what its doing. dbms_application_info is great for this as well.
> 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.
>
the only time i've seen a wrong line number is when it reports the LAST line in a LOOP when it really meant the FIRST line in the loop. check that out next time -- if you get sent to the last line of a loop and it doesn't make sense, goto the top of the loop and check there.
> 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.
>
pool the database connection? what exactly do you mean by that?
> Any comments ?
>
> BTW, I really appreciate Oracle's presence on this ng !
>
its my presence really, i happen to work for oracle but oracle is not doing this..
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 02 1998 - 22:03:13 CST
 |
 |