Re: Dynamic SQL
From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Fri, 22 Oct 2010 22:03:17 +0200
Message-ID: <i9sqm2$9u$1_at_online.de>
Am 22.10.2010 16:25, schrieb The Magnet:
>
> Hi, I'm looking for an answer but also thought I can take a shortcut
> here.
>
> We have a query with a ton of calculations using DECODE and NULLIF and
> more and more. So, we created a function in which we will pass a pair
> of values in a string (number,'operator'):
>
> CALC_FORMULA(x,'-',y,'+',z,'*')
>
> My question is, with all that dynamic stuff, can I perform the
> calculation? I mean, the operators are variables. So, is it as
> simple as constructing a string and using like EXECUTE IMMEDIATE?
>
Date: Fri, 22 Oct 2010 22:03:17 +0200
Message-ID: <i9sqm2$9u$1_at_online.de>
Am 22.10.2010 16:25, schrieb The Magnet:
>
> Hi, I'm looking for an answer but also thought I can take a shortcut
> here.
>
> We have a query with a ton of calculations using DECODE and NULLIF and
> more and more. So, we created a function in which we will pass a pair
> of values in a string (number,'operator'):
>
> CALC_FORMULA(x,'-',y,'+',z,'*')
>
> My question is, with all that dynamic stuff, can I perform the
> calculation? I mean, the operators are variables. So, is it as
> simple as constructing a string and using like EXECUTE IMMEDIATE?
>
You could assemble an expression and, using EXECUTE IMMEDIATE, SELECT this expression from DUAL.
Or you assemble an anonymous PL/SQL block with a host variable as assignment target for that expression, and EXECUTE IMMEDIATE with OUT variable for the result.
Regards
Peter
-- The only way to keep your health is to eat what you don't want, drink what you don't like, and do what you'd rather not. -- Mark TwainReceived on Fri Oct 22 2010 - 15:03:17 CDT