Mega Code Archive
Use a character string of arithmetic logic, selecting against the DUAL table to return a number value
<![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 := 'BEGIN ' ||' :lv_value_num := ' || p_statement_txt || ';' ||'END;';
11 DBMS_SQL.PARSE(lv_cursor_id_num, lv_statement_txt,DBMS_SQL.NATIVE);
12 DBMS_SQL.BIND_VARIABLE(lv_cursor_id_num, ':lv_value_num', lv_return_value_num);
13
14 lv_rowcount_num := DBMS_SQL.EXECUTE(lv_cursor_id_num);
15
16 DBMS_SQL.VARIABLE_VALUE(lv_cursor_id_num, ':lv_value_num',
17 lv_return_value_num);
18 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
19 RETURN ROUND(lv_return_value_num, p_precision_num);
20 EXCEPTION
21 WHEN OTHERS THEN
22 IF DBMS_SQL.IS_OPEN(lv_cursor_id_num) THEN
23 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
24 END IF;
25 RAISE_APPLICATION_ERROR(-20101, 'Error processing SQL ' ||
26 'statement in MATH_CALC procedure', FALSE);
27 END math_calc;
28 /
Function created.
SQL>
SQL> DECLARE
2 lv_return_num NUMBER;
3 lv_value_num_1 NUMBER;
4 lv_value_num_2 NUMBER;
5 BEGIN
6 lv_value_num_1 := 100.002;
7 lv_value_num_2 := 3.02;
8 lv_return_num := math_calc(TO_CHAR(lv_value_num_1) ||' * ' || TO_CHAR(lv_value_num_2), 5);
9 DBMS_OUTPUT.PUT_LINE('Value: ' || lv_return_num);
10 END;
11 /
Value: 302.00604
PL/SQL procedure successfully completed.
SQL>
]]>