[DB-SIG] How to return results from stored procedures in Oracle

Andy Todd andy47@halfcooked.com
Sun, 01 Sep 2002 12:52:56 +0100

Marcos Sánchez Provencio wrote:
> El sáb, 31-08-2002 a las 18:10, Andy Todd escribió:
>>Marcos Sánchez Provencio wrote:
[snip original question and response]
>>Of course, YMMV.
> I mean the case in which the objective of the procedure (the name
> function would be more correct, I agree) is to return the data, not to
> do any data processing at all. It is very useful to return data to
> Crystal Reports (client/server) and save bandwidth by doing _all_ the
> data processing in the server, for example. I also have two versions of
> an 'application explorer', in VB and Python, and I try to move all the
> functions into the data server to make them completely compatible.
> I will try the various options offered in the list and tell you about
> the results. Thank you.

In that case I would try a slightly different approach. Returning the 
results of a select and some calculations from a stored procedure is the 
common approach in Sybase and SQL Server but is achieved in a different 
way when you've got an Oracle back end - of course.

Oracle allows you to include *functions* in the SELECT clause of your 
SQL statement. Thus, instead of fetching your data and applying 
modifications to it in a stored code module you can simply write your 
modification code and include it in your select statement.

Suppose you want to calculate the tax payable on an invoice amount when 
you get the data from invoices table. The Oracle solution would be to 
specify a calculation function;

   FUNCTION calc_tax( p_inv_amt IN NUMBER ) IS
     /* Obviously you wouldn't define this here */
     l_tax_rate = 0.15;
     RETURN p_inv_amt * l_tax_rate;
   END calc_tax;

And then in your select statement;

   SELECT invoice_amt, calc_tax(invoice_amt) tax_amt
   FROM   invoices
   WHERE  invoice_date < sysdate - 7;

And Bob is - indeed - your Auntie's live in lover.


By the way, functions and procedures ARE different creatures in PL/SQL. 
Note the difference is that procedures don't return a value but 
functions must. Hence you can only use functions in your SQL statements.
 From the desk of Andrew J Todd esq - http://www.halfcooked.com