Re: Ora newbi question (whenever someone finds time)

"Tom" <t.alexander_at_ads-tec.de> wrote in message news:697dbfee.0207090104.5811ed5b_at_posting.google.com...
> Thank you for the quick response, but the issue is, because the
> formula is NOT complex I'm trying to avoid writing a PL/SQL procedure
> to parse this expession if oracle can already evaluate simple string
> expressions.
>
> What would be an easy way (if any) of doing this?
>
> Thanxs
>
>
> > -----Ursprüngliche Nachricht-----
> > Von: Raffaello Galli [mailto:galli_at_molgen.mpg.de]
> > Gesendet: Dienstag, 9. Juli 2002 10:51
> > An: Thomas Alexander
> > Betreff: Re: Ora newbi question (whenever someone finds time)
> >
> >
> > Yes, it's possible.
> > You could even use a stored procedure, this solution is the
> > best if the
> > formula is complex.
> >
> > greetings
> >
> > Raffaello
> >
> > On 9 Jul 2002, Tom wrote:
> >
> > > Hello! An answer to your question
> > > " !!!!I'm new to the Oracle field !!!!
> > >
> > > Hi,
> > > Is it possible to perform simple Arithmetic Expressions in SQL?
> > > I imagine something like:
> > >
> > > > MagicBallCalculateThis '1 + 2 * (3 + 4)';
> > > >----------------------------------------------
> > > > 15
> > >
> > > Reason:
> > > An application I am using is sending a Formula in form of a
> > string of
> > > characters. In order to evaluate the Formula I would have
> > to parse the
> > > formula to validate it, break it down into each arithmetic operater
> > > and convert the numeric strings to numbers.
> > >
> > > Wish:

Tom,

When you recieve the formula in a string format, the only options is to create a stored PL/SQL function
and work with dynamic SQL or Ref Cursors. I used Ref Cursors for a problem that is quite similar to
yours. Hopelfully this example PL/SQL function makes it clear

CREATE OR REPLACE FUNCTION CALCULATE_FORMULA(formula in varchar2) return number
AS

    TYPE dynamic_cursor_type IS REF CURSOR;     dynamic_cursor dynamic_cursor_type;
    cursor_source VARCHAR2(200);
    result number := 0;
BEGIN
    cursor_source := 'select '||formula||' from dual';     OPEN dynamic_cursor FOR cursor_source;     FETCH dynamic_cursor INTO result;
    CLOSE dynamic_cursor;
    return result;
EXCEPTION
    when others
    then

        raise_application_error(-20110, 'CALCULATION ERROR. PLEASE CHECK YOUR FORMULA!', TRUE);
END; Regards,
Steven. Received on Tue Jul 09 2002 - 10:00:18 CDT