Re: Dynamic SQL

> 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?
>

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