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?

The Magnet wrote:
You must have a system to spare?

You'll be calling PL/SQL from your SQL, which in its turn will call SQL to parse and execute
that formula.

I've seen worse attempts to drown a machine.

On Oct 22, 7:25*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
Gerard certainly has a point with the performance implications of
switching back and forth between PL and SQL, on some systems that does
become significant. On the other hand, with the modern versions way
of handling child cursors, mutexes and such, it may be either better
or worse, so a demonstration both ways (function pounded by many
multiple sessions, and equivalent inlines) might be informative to
everybody. Also try native compilation. Sometimes it is even worth
it to write heavy calcs in another language, pipeline to a dedicated
session or other strange things.

That's the thing about generalized functions; depending on exactly
what you are doing under load, they may help, hurt, or only make
coding management difference. Scalability may be a big difference, as
might the spread of different ways apps could use such a function.
There could even be platform differences specific to how calculations
are handled.

jg
--
@home.com is bogus. "Workers of America, to the ramparts! We are on
the march for — what? Retirement at 68?
So here’s to the French. Those croissant-munching, Champagne-swilling,
Galois-puffing, pension oui-nies." - Peter Rowe

Am 22.10.2010 16:25, schrieb The Magnet:
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 Twain

The Magnet <art (AT) unsu (DOT) com> writes:

Well, yes you could do it, but no, do not do it!

execute immediate and the dynamic sql package can allow all sorts of
clever dynamic sql. However, it has a very large performance impact, can
create some difficult to identify bugs and can create significant
security holes, depending on what you do and how you do it.

The PL/SQL package system on the other hand can be used to create
overloaded procedures/functions that are selected based on what
arguments you pass to them. For example, I use a package that compares
to values and determines if value 1 is less than, equal to or greater
than value 2. Using overloading, it can handle comparisons involving
arguments of different types, handles the case where one or both
arguments are null and throws exceptions for things I've overlooked or
which should not happen.

If you cannot determine which functions/procedures to call statically,
you could use an operator 'string' in an if/elsif or case statement to
call the actual function/procedure.

The one time I've had to use execute immediate has been in an
applicaiton that allowed new procedures to be defined dynamically during
runtime. This applicaiton allowed the user to define a new block of
code, which was stored in a table. Execute immediate was used to execute
the code retrieved from the table - essentially, this was a simple
version of what Oracle now provides in the rules manager package.

Tim

--
tcross (at) rapttech dot com dot au