Expert: Suchitra Joshi - 5/13/2009

Question
Hi,

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
Hi Uma,

I am not sure if the following will work, but you can try it.

1) Pass formula p_formula as IN paramter and p_val as OUT parameter to a procedure
2) Declare v_sql varchar2(100); v_val number;
3) v_sql := 'select ' || p_formula || ' from dual';
4) Use execute immediate command to evaluate the formula.
  EXECUTE IMMEDIATE v_sql INTO p_val;

I hope this will return p_val as the executed formula.

Hope this helps. Please let me know if any errors occur or you are not able to process this way.

Regards

Suchitra