DDL for evaluate expression function

The sole purpose a this function is to evaluate mathematical expressions like "1+2*4" into the numerical value 9.

CREATE or REPLACE
    function evaluate_expression
            ( p_expression in varchar2)
    return number
    is
        l_rowsprocessed number default 0;
        l_retval number;
        g_theCursor integer default dbms_sql.open_cursor;
    begin
        dbms_sql.parse(g_theCursor,
        'begin :ret_val := ' || p_expression ||
        '; end;',
        dbms_sql.native );
    
        dbms_sql.bind_variable ( g_theCursor,':ret_val',l_retval );
       l_rowsprocessed := dbms_sql.execute(g_theCursor);
       dbms_sql.variable_value ( g_theCursor, ':ret_val', l_retval );
    
    return l_retval;
    end evaluate_expression; 

DDL for Package Specification POLYMORPHIC_PKG

To create a polymorphic table function (PTF), you need to create a package with a describe function. I will use this to define a new column RESULT in the output. I will use the Fetch_rows procedure to set values for new column.

create or replace PACKAGE POLYMORPHIC_PKG AS

  FUNCTION describe (tab IN OUT DBMS_TF.table_t)
    RETURN DBMS_TF.describe_t;

  PROCEDURE fetch_rows;
  
END POLYMORPHIC_PKG;

DDL for Package body

Describe uses the col_names to define how many columns to add to the output and their name.
Fetch_rows then extracts the values from the source string to the new columns.
This part of the code is where the heavy lifting takes place:

• line 30: The rows are processed one by one
• line 31: The columns are processed one by one
• line 32: First column must be "EXPRESSION", store expression value
• line 35: Replace in the "EXPRESSION" the variable matching the column name with the value from that column
• line 38: Store in the column "RESULT" the evaluated expression

create or replace PACKAGE BODY POLYMORPHIC_PKG 
AS
  FUNCTION describe (tab IN OUT DBMS_TF.table_t)
    RETURN DBMS_TF.describe_t
  AS
  BEGIN
    -- Make sure the for_read flag for each real column is set, or get_row_set has no values.
    FOR i IN 1 .. tab.column.count LOOP 
      CONTINUE WHEN NOT DBMS_TF.supported_type(tab.column(i).description.TYPE);
      tab.column(i).for_read := TRUE;
    END LOOP;

    -- Add the new RESULT column.
    RETURN DBMS_TF.describe_t(
             new_columns => DBMS_TF.columns_new_t(1 => DBMS_TF.column_metadata_t(name =>'RESULT'))
           );   
  END;

  PROCEDURE fetch_rows AS 
    l_row_set    DBMS_TF.row_set_t;
    l_new_col    DBMS_TF.tab_varchar2_t;
    l_row_count  PLS_INTEGER;
    l_putcolcount pls_integer;
    v_temp_var   varchar2(500) := 'A';
    env          dbms_tf.env_t := dbms_tf.get_env();
  BEGIN
    DBMS_TF.get_row_set(l_row_set, l_row_count, l_putcolcount);
    -- Populate the new column with the evaluated expression of the associated row.
    FOR row_num IN 1 .. l_row_count LOOP
      for colindx in 1 .. env.get_columns.count loop
        IF env.get_columns ( colindx ).name = '"EXPRESSION"' then
            v_temp_var := nvl(l_row_set(colindx).tab_varchar2 (row_num), 'empty');
        else
            v_temp_var := REPLACE(v_temp_var, REPLACE(env.get_columns ( colindx ).name, '"', ''), nvl(l_row_set(colindx).tab_number (row_num), '0'));
        end if;
      end loop;
      l_new_col(row_num) := evaluate_expression(v_temp_var);
    END LOOP;

    -- Associate the new values with the new column.
    DBMS_TF.put_col(1, l_new_col);
    EXCEPTION
      WHEN OTHERS THEN
        RAISE;
  END; 

END POLYMORPHIC_PKG;

DDL for Function MY_POLYMORPHIC_TABLE

This function is the interface to the implementation package. The argument is the name of the table that is the source data set.
The source data set can be:

• a table
• a view
• an inline query

CREATE OR REPLACE FUNCTION MY_POLYMORPHIC_TABLE (tab IN TABLE)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC 
USING POLYMORPHIC_PKG;

Create demo table

Create table with demo data. You can update the script with custom values.
Is mandatory for the "EXPRESSION" column to be first.

begin
 execute immediate 'drop table demo_ptf_table';
 dbms_output.put_line('Table dropped successfully!');
exception when others then null;
end;
/

create table demo_ptf_table
as 
    select 'VAR1+VAR2*VAR3/TEST1' expression, 24 var1, 30 var2, 15 var3, 45*10 test1 from dual
    union all
    select 'VAR1+VAR2/VAR3+SQRT(TEST1)' expression, 24 var1, 30 var2, 15 var3, 1 test1 from dual
    union all
    select 'VAR1+POWER(VAR2,VAR3)+SQRT(TEST1)' expression, 2 var1, 2 var2, 2 var3, 27/3 test1 from dual
    union all
    select '1/(VAR1/POWER(VAR2,VAR3)+ROUND(SIN(TEST1),3))' expression, 4 var1, 2 var2, 2 var3, 3*3 test1 from dual;

select *
from demo_ptf_table;

Demo using table

The polymorphic function will dynamically append a new column named "RESULT" at the end of the result set.

SELECT * FROM MY_POLYMORPHIC_TABLE(demo_ptf_table);

Create demo view

Based on the table I have created a view to show that the Polymorphic Functions have the same capabilities.

create or replace view demo_ptf_view
as
select * from demo_ptf_table;

select *
from demo_ptf_view;

Demo using view

Again, the polymorphic function will dynamically append a new column named "RESULT" at the end of the result set.

 
SELECT * 
FROM MY_POLYMORPHIC_TABLE(demo_ptf_view); 

Demo using an ad-hoc result set

Please feel free to change the dataset!

with my_set as (
            select 'VAR1+VAR2*VAR3/TEST1' expression, 24 var1, 30 var2, 15 var3, 45*10 test1 from dual
            union all
            select 'VAR1+VAR2/VAR3+SQRT(TEST1)' expression, 24 var1, 30 var2, 15 var3, 1 test1 from dual
            union all
            select 'VAR1+POWER(VAR2,VAR3)+SQRT(TEST1)' expression, 2 var1, 2 var2, 2 var3, 27/3 test1 from dual
            union all
            select '1/(VAR1/POWER(VAR2,VAR3)+ROUND(SIN(TEST1),3))' expression, 4 var1, 2 var2, 2 var3, 3*3 test1 from dual
            )
SELECT * FROM MY_POLYMORPHIC_TABLE(my_set);