AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
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;
