Re: ORA-06502 when moving from 8.0.5 to 8.1.7

Well, I'm rather new at this... so it's no surprise that I mistook procedures with functions. Actually, I inherited this system, and now have this problem where the function works in 8.0.5, but not in 8.1.7 (error ORA-06502, character string buffer too small). I was just wondering if I need to change a global setting somewhere, or actually change the function itself.

NM

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0112180633.326e400d_at_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')
> --------------------------------------------------------------------------------
> S
>
> UT1> select buy_sell('B','S') from dual;
>
>
> BUY_SELL('B','S')
> --------------------------------------------------------------------------------
> B
>
>
> 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 Wed Dec 19 2001 - 01:35:27 CST