Re: Ora newbi question (whenever someone finds time)

Steve, Galen, Ed,

THANK YOU! That was EXACTLY what I was looking for.

I appreciate everybody's help !!! This saved me a lot of time!

Tom

"Steven Janssens" <steven.janssens_at_yucom.be> wrote in message news:<3d2afa88$0$12221$4d4efb8e_at_news.be.uu.net>...
> "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 Wed Jul 10 2002 - 02:57:22 CDT