Re: ORA-06502 when moving from 8.0.5 to 8.1.7

nimesh_myanger_at_hotmail.com (NM) wrote in message news:<bdae89b.0112182335.624a2fc7_at_posting.google.com>...
> 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 --

An inherited system? Been there, and have had to live with how it had been constructed.

The routine should be a function and called as in my example or in pl/sql:
v_variable := buy_sell(v_other_variable) ;

I ran my example on an 8.1.7.2 system if you are still having a problem you probably need to post exactly what you are executing.

• Mark D Powell --