Re: ORA-06502 when moving from 8.0.5 to 8.1.7

From: Mark D Powell <mark.powell_at_eds.com>
Date: 18 Dec 2001 06:33:56 -0800
Message-ID: <178d2795.0112180633.326e400d@posting.google.com>

nimesh_myanger_at_hotmail.com (NM) wrote in message news:<bdae89b.0112172351.3f9f01a9_at_posting.google.com>...
> Hi,
>
> I have a simple stored procedure that works in 8.0.5 but not in 8.1.7
> - anyone have an idea why this does not work?
>
> Code:
>
> BUY_SELL (buy IN CHAR, sell IN CHAR )
> RETURN CHAR IS
>
> BEGIN
> IF buy IS NOT NULL THEN
> RETURN buy;
> ELSE
> RETURN sell;
> END IF;
> END buy_sell;
>
> Any input will be greatly appreciated.
>
> Thanks,
> NM

Oracle stored procedures do not return values in this manner, but functions do:
UT1> create or replace function
  2 BUY_SELL (buy IN CHAR, sell IN CHAR )   3 RETURN CHAR IS
  4
  5 BEGIN

  6     IF buy IS NOT NULL THEN
  7        RETURN buy;
  8     ELSE
  9        RETURN sell;
 10     END IF;

11 END buy_sell;
12 /

Function created.

UT1> select buy_sell(NULL,'S') from dual;

BUY_SELL(NULL,'S')

UT1> select buy_sell('B','S') from dual;

BUY_SELL('B','S')

I would hope you realize that you could return the same result using Oracle provided functions like decode or the SQL case statement rather than write a function to do this.

Mark D Powell --

Received on Tue Dec 18 2001 - 08:33:56 CST