Use a character string of arithmetic logic, selecting against the DUAL table to return a number value. : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial






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>








31.26.DBMS_SQL
31.26.1.dbms_sql.number_table
31.26.2.Close a cursor
31.26.3.Assign date with DBMS_SQL package
31.26.4.Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'
31.26.5.DBMS_SQL.PARSE
31.26.6.An example of using DBMS_SQL.OPEN_CURSOR
31.26.7.Package for running dynamic sql based on DBMS_SQL package
31.26.8.Wrap dbms_sql package
31.26.9.Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
31.26.10.DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
31.26.11.DBMS_SQL.EXECUTE an update statement
31.26.12.Use DBMS_SQL to update a table and get the number of rows updated
31.26.13.DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
31.26.14.Use dynamic SQL to check the business logic
31.26.15.Use DBMS_SQL package to parse math expression
31.26.16.Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
31.26.17.DBMS_SQL.LAST_ERROR_POSITION
31.26.18.Dump query with dbms_sql
31.26.19.Use dbms_sql.describe_columns
31.26.20.Print table with dynamic query