Re: Question on How to call a Select stmt in a Stored Procedure?

Bill Ford (wwford_at_ingr.com) wrote:
: In article <34qagn$ov4_at_bmerha64.bnr.ca>, Jason Lisenchuk <hris_at_nt.com>
: says:
: >
: >Question on How to call a Select stmt in a Stored Procedure ?
: >
: >Please keep in mind that Oracle 7.0.x stored procedures cannot return a
: >cursor; instead return a scalar value (like NUMBER or VARCHAR or LONG).
: >If the SELECT contained within the procedure retrieves a small amount of
: >data, you could consider packing this into a LONG (with appropriate
: >delimiters) and parsing via C at the client. Alternatively, you could
: >use the SELECT to summarize data and INSERT it into another table, which
: >you SELECT and FETCH via C at the client.
 

: This is a situation that has been plaguing me as well. Even though I
: wouldn't consider using a stored procedure for security on a select
: instead of using a VIEW or something else, this is what a customer wants.
 

: Now, in Sybase Open Client, I can execute a stored procedure that
: does a select from my (written in C) client, and then in the client
: access the results. As you have mentioned, and as I've painfully
: discovered, there is no way for me to do something like this in embedded
: Oracle.
 

: Other than your two suggestions (pack and return the results, or use a
: temporary table) can ANYONE think of a way to do execute a select-stored
: procedure from a client program?
 

: wwford_at_ingr.com

Why not use dbms_output? Just loop through your cursor formatting your result set into dbms_output.put_line. This should handle formatted result sets up to 1 million bytes. Above that, I would be dubious as to whether it's an appropriate technique anyhow.

If you don't want to output your results, then use dbms_pipe instead.

It's tacky, I agree. I think the system should make this communication transparent. I'm not really familiar with the Sybase technique, but I seem to remember being unimpressed by a thread in c.d.sybase or c.s.powerbuilder which implied that there was a potential for Sybase to throw gigabytes of results at the client without the client being able to say "Aaargh! No! Stop! I'm out of RAM!". Maybe it was a PowerBuilder issue, I'm not very familiar with either.

The Informix technique of returning after each row seems to me to be the neatest of the ones I've seen so far.

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.