Math operands can be bind variables for a static formula. E.g.
select :1 * :2 / :3 from dual
But the operators cannot be bind variables. Thus if the formula itself is dynamic then you need to use dynamic SQL. E.g.
declare
formula varchar2(100);
res number;
begin
formula := '5 + 4 - 2';
execute immediate 'select '||formula||' from dual' into res;
dbms_output.put_line( res );
end;
This is however not a good idea as this will generate unique and non-sharable SQL. Which in turn mucks up the Shared Pool, causes fragmentation and all kinds of ugly performance knocks.
A better idea would be to use DBMS_SQL. Make the formula string include bind variables. Then use DBMS_SQL to create a dynamic SQL statement, parse it to obtain the bind variables. Bind these dynamically. Then execute the DBMS_SQL cursor and retrieve the result.
Here is such an example. Note that
TCursor in this example is a custom written o-o wrapper (using Oracle User Defined Types) for DBMS_SQL. Even so, the approach show remains the same if using DBMS_SQL directly:
SQL> create or replace package lib_h as
2
3 type TNameValue is table of number index by varchar2(30);
4 end;
5 /
Package created.
SQL>
SQL> create or replace procedure W( cLine varchar2 ) is
2 begin
3 DBMS_OUTPUT.put_line( cLine );
4 exception when OTHERS then
5 NULL;
6 end;
7 /
Procedure created.
SQL>
SQL> create or replace function Calc( cExpression varchar2, uParams lib_h.TNameValue ) return number is
2 cur TCursor; -- a custom developed ADT object class that wraps DBMS_SQL
3 name varchar2(30);
4 value number;
5 rows$ number;
6 begin
7 cur := TCursor( 'select '||cExpression||' from dual' );
8
9 -- The TCursor class parses the SQL string, looking for bind variables.
10 -- It publishes the list of bind variable names via the property VARIABLE.
11 for i in 1..cur.Variable.Count
12 loop
13 name := cur.Variable(i); -- the name of the bind variable
14 begin
15 value := uParams( name ); -- find the value in the NameValue list
16 exception when NO_DATA_FOUND then
17 W( 'Error. Bind variable '||name||' was not found in the parameter NameValue list' );
18 raise;
19 end;
20
21 -- now we bind that value to the bind variable
22 cur.Bind( name, value );
23 end loop;
24
25 -- okay, let's try and execute the SQL
26 cur.Execute;
27
28 -- fetch the first (and only) row
29 cur.Next;
30
31 -- grab the 1st column from the cursor as a number data type
32 value := cur.ColumnAsNumber(1);
33
34 -- close the cursor
35 cur.Close;
36
37 -- return the value
38 return( value );
39 end;
40 /
Function created.
SQL>
SQL> declare
2 v lib_h.TNameValue;
3 n number;
4 begin
5 v('QTY') := 1; -- 1 widgets
6 v('AMT') := 10; -- $10 per widget
7 v('PROFIT') := 10/100; -- selling at 10% profit margin (i.e. $1 profit/widget)
8
9 -- This is what we want to do dynamically:
10 -- SQL> select (:QTY * :AMT) + (:AMT * :QTY) * :PROFIT into N from dual USING 1, 20, (10/100)
11 n := Calc( '(:QTY * :AMT) + (:AMT * :QTY) * :PROFIT', v );
12 W( 'answer is '||n );
13
14 end;
15 /
answer is 11
PL/SQL procedure successfully completed.
SQL>
