Expert: Elliot Mak - 5/13/2009

Question
Hi Mak

I have a requierement as below.
- I have a formula (Ex: (120/(120+20))*100). I need to pass this formula as a parameter to PL/SQL procedure or function and get the result.
- I cannot write this formula with in the function because, i have plenty of formulas and need a piece of code to reuse
- I tried to pass the formula as a varchar2, it was ok but datatype conversion is the problem here

Please provide me the solution to work on this

Thanks for the time
Uma

Answer
Good day Uma

When you are passing PL/SQL a formula, as you said in your email, PL/SQL interprets it as a variable and not an instruction to calculate.  Therefore, it will be impossible to have Oracle Pl/SQL to calculate based on a formula you passed.

There may be a way to work around it.  I don't recommend that but it is possible.

"execute immediate" allows you to construct the entire DML (update/delete/insert) and DCL using a variable and "execute immediate" can invoke that command as per the variable.  

Let's use the following example
Formula is (120/(120+20))*100)
Function name is calc_proc(in_var varchar2)
The in_var is '(120/(120+20))*100)'

When you invoke the function, it will look something like this
CALC_PROC('(120/(120+20))*100)')

You will also need a global temporary table, let's called that as CALC_RESULT_GTT

In your code, you can

Create Function ...
Begin
....

 l_stmt := 'insert into calc_result_gtt values ' || in_var;

 execute immediate l_stmt;

 select *
 into   l_results
 from   calc_result_gtt;

...

End

Try it out

Cheers
Elliot