Mega Code Archive
Use DBMS_SQL package to parse math expression
<![CDATA[
SQL>
SQL> CREATE OR REPLACE FUNCTION math_calc(p_statement_txt VARCHAR2,
2 p_precision_num PLS_INTEGER := 2)
3 RETURN NUMBER IS
4 lv_cursor_id_num PLS_INTEGER;
5 lv_statement_txt VARCHAR2(500);
6 lv_rowcount_num PLS_INTEGER;
7 lv_return_value_num NUMBER;
8 BEGIN
9 lv_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
10 lv_statement_txt :=
11 'BEGIN ' ||
12 ' :lv_value_num := ' || p_statement_txt || ';' ||
13 'END;';
14
15 DBMS_SQL.PARSE(lv_cursor_id_num, lv_statement_txt,DBMS_SQL.NATIVE);
16
17 DBMS_SQL.BIND_VARIABLE(lv_cursor_id_num, ':lv_value_num', lv_return_value_num);
18
19 lv_rowcount_num := DBMS_SQL.EXECUTE(lv_cursor_id_num);
20
21 DBMS_SQL.VARIABLE_VALUE(lv_cursor_id_num, ':lv_value_num',lv_return_value_num);
22 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
23 RETURN ROUND(lv_return_value_num, p_precision_num);
24 EXCEPTION
25 WHEN OTHERS THEN
26 IF DBMS_SQL.IS_OPEN(lv_cursor_id_num) THEN
27 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
28 END IF;
29 RAISE_APPLICATION_ERROR(-20101, 'Error processing SQL ' ||
30 'statement in MATH_CALC procedure', FALSE);
31 END math_calc;
32 /
Function created.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
2 lv_return_num NUMBER;
3 BEGIN
4 lv_return_num := math_calc('4*5', 2);
5 DBMS_OUTPUT.PUT_LINE('Value: ' || lv_return_num);
6 END;
7 /
Value: 20
PL/SQL procedure successfully completed.
SQL>
]]>