What is the scope of Oracle Application Express

Calculations, simulations and more - in APEX: SQL MODEL clause
Release monthAPEX versionDatabase version
September 2015allfrom 10.1

Many APEX components are based on an SQL query. Whether it is a classic or interactive report, a calendar or a chart; an SQL query is always the data source. This also means that the full range of SQL functions can be used in these components - and that is a lot ...

  • Analytical SQL functions
  • Linguistic SQL functions
  • Full text search
  • Spatial queries (geodata)
  • XML and JSON functions
  • SQL Pattern Matching (from 12.1)
  • SQL MODEL clause

This tip is about the SQL MODEL clause, which, in short, allows spreadsheet formulas to be used in an SQL query. This makes it possible to address individual columns and rows in an SQL result set like in an Excel formula and, if necessary, to change them. This is best explained using an example - the starting point is a simple SQL query on the EMP table.

select * from emp

The syntax of the SQL MODEL clause takes getting used to and requires some training time - but you are rewarded with completely new possibilities that would otherwise not be possible with pure SQL queries. First a very simple example - which is explained below.

select * from emp MODEL DIMENSION BY (empno) MEASURES (ename, job, sal, comm, hiredate, deptno, mgr) RULES (job [7844] = 'APEXDEV', sal [7902] = 9876, comm [7839] = sum (comm) [ANY], ename [9000] = 'NOBODY'); EMPNO ENAME JOB SAL COMM HIREDATE DEPTNO MGR ----- ---------- --------- ----- ----- -------- ----------- ------ ----- 7369 SMITH CLERK 800 12/17/1980 00:00:00 20 7902 7499 ALLEN SALESMAN 1600 300 02/20/1981 00:00:00 30 7698 7521 WARD SALESMAN 1250 500 02/22/1981 00:00:00 30 7698 7566 JONES MANAGER 2975 04/02/1981 00:00:00 20 7839 7654 MARTIN SALESMAN 1250 1400 09/28/1981 00:00:00 30 7698 7698 BLAKE MANAGER 2850 05/01 .1981 00:00:00 30 7839 7782 CLARK MANAGER 2450 06/09/1981 00:00:00 10 7839 7788 SCOTT ANALYST 3000 04/19/1987 00:00:00 20 7566 7839 KING PRESIDENT 5000 2200 11/17/1981 00:00:00 10 7844 TURNER APEXDEV 1500 0 09/08/1981 00:00:00 30 7698 7876 ADAMS CLERK 1100 05/23/1987 00:00:00 20 7788 7900 JAMES CLERK 950 12/03/1981 00:00:00 30 769 8 7902 FORD ANALYST 9876 12/3/1981 00:00:00 20 7566 7934 MILLER CLERK 1300 01/23/1982 00:00:00 10 7782 9000 NOBODY
  • The MODEL keyword introduces the SQL MODEL clause
  • The columns labeled DIMENSIONS in the tables mentioned above are used to refer to "cells" in the result set. For the EMP table, the EMPNO is sufficient; other tables may require multiple columns. The primary key columns are typically used here.
  • MEASURES are the columns that can be addressed and changed by the rules of the MODEL clause. The SELECT * of the SQL query also refers to the columns specified here: Only the MEASURES columns are returned.
  • Finally, the spreadsheet formulas are stored as RULES. The examples show very clearly how this works. As you will see in the following, the syntax of the formulas is very powerful. But this simple example also shows the assignment of a sum in addition to the simple assignments.

Such a query can be used as a data source for an interactive or classic APEX report (Figure 1). This means that the developer can use the possibilities of the MODEL clause - time-consuming PL / SQL code for the final preparation of query results can sometimes be omitted. For the end user, however, there would be a static report and the familiar options of the interactive report.

Figure 1: Use of the SQL model clause in an APEX report

If you want to make the possibilities of the SQL MODEL clause available to the end user, you need a report with dynamic SQL. This is easy with a classic report - select Classic Report (based on function) as the region type and simply use a PL / SQL block that returns an SQL query as the region source. APEX form elements can be used to enter the rules. With such very dynamic query options, however, pay attention to application security (SQL Injection) - in the end, you give your user a lot of freedom to formulate even complex (and possibly unwanted) SQL queries.

In the following we describe a very simple example that would also allow the end user to use the SQL MODEL clause. It is based on the fact that the user enters the formulas of the SQL model clause in a text area and these are then integrated into the SQL query. As I said, you use a Classic Report (based on function) as a report. Enter the following SQL query as the region source of the static report.

declare l_sql varchar2(32767) :='select * from emp MODEL DIMENSION BY (empno) MEASURES (ename, job, sal, comm, hiredate, deptno, mgr) RULES (# RULES #)'; beginreturnreplace (l_sql, '# RULES #', v ('P2_RULES')); end;

Then create a second region with a text area element called P2_RULES. Then create a button; When you click, the report region is to be updated via Dynamic Action (Refresh). Don't forget to enter P2_RULES in the reporting region under Page Items to Submit. Your page should look something like Figure 2.

Figure 2: SQL MODEL clause in APEX: The end user can enter formulas

The following formulas were entered in Figure 2.

ename [9000] = 'SMITH', sal [ANY] = sal [cv ()] * 2, ename [ANY] = 'APEX'

The second formula in Figure 2, sal [ANY] = sal [cv ()] * 2, shows that the MODEL clause also allows the relative addressing of values: SAL [ANY] addresses all rows of the result set - is assigned the content of the same line sal [cv ()], multiplied by 2. This allows you to change many values ​​at once with a single formula.

Referencing can also be a little more complex and "jump to other lines". Try the following formula - all CLERK, SALESMAN and ANALYSTS get their managers 'salaries, the managers' and KING's salaries remain unchanged. You can see how the salary is drawn from the line of the respective manager with sal [mgr [cv ()]]. The entire SQL language range can be used in the assignments.

sal [ANY] = case when job [cv ()] in ('CLERK', 'ANALYST', 'SALESMAN') then sal [mgr [cv ()]] else sal [cv ()] end

In order for this formula to work, the keyword AUTOMATIC ORDER must be added to the SQL query in the report definition. This determines that the database determines the dependencies between the formulas and executes the rules in the correct order. Alternatively, SEQUENTIAL ORDER (default) executes the rules in the order in which they are specified.

declare l_sql varchar2(32767) :='select * from emp MODEL DIMENSION BY (empno) MEASURES (ename, job, sal, comm, hiredate, deptno, mgr) RULES AUTOMATIC ORDER (# RULES #)'; beginreturnreplace (l_sql, '# RULES #', v ('P2_RULES')); end;

With the ITERATE keyword, formulas can also be executed multiple times; either a fixed number of iterations can be stored or the formulas are executed until a certain condition is met. This will again be shown using an example. Create the same APEX application page again (ideally as a copy of your existing page) and then make the following changes.

  • Add two new input fields below the text area P3_RULES: P3_ITERATE and P3_ITERATE_UNTIL.
  • Change the SQL query of your report as follows (as soon as iterations come into play, the aforementioned AUTOMATIC ORDER is usually out of the question - use SEQUENTIAL ORDER):
declare l_sql varchar2(32767) :='select row, a, b, c, d, e, f from dual MODEL DIMENSION BY (rownum as row) MEASURES (0 as a, 0 as b, 0 as c, 0 as d, 0 as e, 0 as f ) RULES SEQUENTIAL ORDER ITERATE (# ITERATE #) UNTIL (# ITERATE_UNTIL #) (# RULES #) '; beginreturnreplace (replace (replace (l_sql, '# RULES #', v ('P3_RULES')), '# ITERATE #', nvl (v ('P3_ITERATE'), 1)), '# ITERATE_UNTIL #', nvl (v ('P3_ITERATE_UNTIL'), '1 = 0')); end;
  • Enter the new elements P3_ITERATE and P3_ITERATE_UNTIL in the report region under Page Items to Submit. Of course, P3_RULES must also be there.

Then when you launch the page it should look something like Figure 3.

Figure 3: APEX page for SQL MODEL clause with iterations

You can either enter a specific number in the Iterations input field, then the formulas will be executed exactly as often. The variable ITERATION_NUMBER tells you how many iterations you are in (starts at zero). Alternatively, you can enter a condition - if this is met, the iterations are canceled. A first test: Enter the following formula in Rules and take 10 iterations.


The result should look something like Figure 4.

Figure 4: First, simple test of the ITERATE clause

On this basis, simulations can be calculated - with APEX and an SQL report. The following rules implement a repayment plan for an installment loan. The formulas would be programmed in the same way in a spreadsheet.

a [0] = 200000, b [ITERATION_NUMBER] = a [cv (line)] * 0.06 / 12, d [ITERATION_NUMBER] = least (2000, a [cv (line)] + b [cv (line)]), c [ITERATION_NUMBER] = d [cv (line)] - b [cv (line)], a [ITERATION_NUMBER + 1] = a [cv (line) -1] - c [cv (line) - 1]

Specify a maximum of 400 iterations and a [ITERATION_NUMBER + 1] <= 0 as the termination condition. Figure 5 shows the result.

Figure 5: Amortization plan calculating with the SQL Model clause

You can refine this calculation - such a repayment plan is just one example of any number of simulations that you can now calculate in an APEX report. The following rules extend the example: After 24 iterations, the interest rate increases from 6 to 7 percent and a "special repayment" takes place at the 36th iteration.

a [0] = 200000, e [line <24] = 6, e [line> 23] = 7, f [36] = 20000, b [ITERATION_NUMBER] = a [cv (line)] * e [cv (line )] / 1200, d [ITERATION_NUMBER] = least (2000, a [cv (line)] + b [cv (line)]), c [ITERATION_NUMBER] = d [cv (line)] - b [cv (line) ], a [ITERATION_NUMBER + 1] = a [cv (line) -1] - c [cv (line) - 1] - nvl (f [cv (line) - 1], 0)

Chance can also be simulated. The function DBMS_RANDOM.VALUE, which supplies a random number, can also be used here. If you carry out 25 iterations with the following formula, you are essentially simulating a "game of roulette" ...

a [ITERATION_NUMBER] = round (dbms_random.value (0.36))

Figure 6: Random simulation with the SQL MODEL clause

This makes "Monte Carlo" simulations possible - the "professionalism" can be supplemented with further rules - all in an APEX application with an APEX report. Here is also an example (without any claim to technical correctness). An ATM is to be simulated.

  • In 20% of the cases, 100 € will be withdrawn
  • In 30% of the cases, € 200 will be withdrawn
  • In 20% of the cases, € 300 will be withdrawn
  • In 20% of the cases, € 500 will be withdrawn
  • In 10% of the cases, € 50 will be withdrawn

A single withdrawal can be simulated with the following formula.

a [1] = case round (dbms_random.value (1,10)) when 1 then 100 when 2 then 100 when 3 then 200 when 4 then 200 when 5 then 200 when 6 then 300 when 7 then 300 when 8 then 400 when 9 then 500 when 10 then 50 end

Figure 7: One-time simulation: ATM

If you set 1,000 iterations and add up the value in each case; this results in the simulation of 1,000 withdrawals.

a [1] = nvl (a [1], 0) + case round (dbms_random.value (1,10)) when 1 then 100 when 2 then 100 when 3 then 200 when 4 then 200 when 5 then 200 when 6 then 300 when 7 then 300 when 8 then 400 when 9 then 500 when 10 then 50 end

Figure 8: Simulation: 1000 ATM withdrawals

Now the task is to carry out such a 1000 simulation several times. It is true that iterations cannot be nested, but the MODEL clause offers us an additional feature: A FOR loop can be specified on the left side of a formula. Then this formula - within an iteration - is executed once for each loop pass - and several cells can be changed with a formula. We want to take advantage of that. The 1000 simulation should be run 20 times - this works as follows:

a [for line from 1 to 20 increment 1] = nvl (a [cv (line)], 0) + case round (dbms_random.value (1,10)) when 1 then 100 when 2 then 100 when 3 then 200 when 4 then 200 when 5 then 200 when 6 then 300 when 7 then 300 when 8 then 400 when 9 then 500 when 10 then 50 end

Figure 9: Simulation "1000 ATM withdrawals" - 20 times

The last example takes this scenario even further. Suppose you also want to know how often the individual amounts were withdrawn - to do this, the respective occurrences of the random number must be counted. The following rules implement this - but you now need one more iteration - because with a FOR loop, all 20,000 values ​​are first stored in "cells" and then the "evaluation" takes place. All of this takes place as part of the SQL execution.

a [for line from 100001 to 120000 increment 1] = case round (dbms_random.

Figure 10: Simulation - with a detailed list of the amounts that have been withdrawn

In the end, you will find the results of the 20 simulations in the 20 result lines; this was realized with just one SQL query - the SQL MODEL clause really offers the user incredible possibilities.

In practice, very few applications will look like this example - simply transferring user input into an SQL query involves a not inconsiderable SQL injection risk: the user input must definitely be validated. One approach to this could look like this.

createorreplacefunction validate_modelclause_rules (p_rules_string invarchar2 ) returnvarchar2is l_rules_array apex_application_global.vc_arr2; l_valid_rules_array apex_application_global.vc_arr2; l_b1 pls_integer:=0; l_b2 pls_integer:=0; l_q pls_integer:=0; l_ok boolean:=false; l_valid_rules_cnt pls_integer:=0; begin l_rules_array := apex_util.string_to_table (p_rules_string, '##'); if l_rules_array.count!=0thenfor i in l_rules_array.first..l_rules_array.last loop l_b1 :=0; l_b2 :=0; l_q :=0; l_ok :=true; for p in1..length (l_rules_array (i)) loopifsubstr (l_rules_array (i), p, 1) ='('then l_b1 := l_b1 +1; elsifsubstr (l_rules_array (i), p, 1) =')'then l_b1 := l_b1 -1; elsifsubstr (l_rules_array (i), p, 1) ='['then l_b2 := l_b2 +1; elsifsubstr (l_rules_array (i), p, 1) =']'then l_b2 := l_b2 -1; elsifsubstr (l_rules_array (i), p, 1) =''''then l_q := l_q +1; end if; if l_b1 <0or l_b2 <0then l_ok :=false; end if; end loop; if l_b1 !=0or l_b2 !=0or remainder (l_q, 2) !=0then l_ok :=false; end if; if l_ok then l_valid_rules_cnt := l_valid_rules_cnt +1; l_valid_rules_array (l_valid_rules_cnt) := l_rules_array (i); end if; end loop; return apex_util.table_to_string (l_valid_rules_array, ','); elsereturnzero; end if; end validate_modelclause_rules;

This function should now be integrated into the SQL query as follows - adapt the PL / SQL block in the report source as follows.

declare l_sql varchar2 (32767): = 'select line, a, b, c, d, e, f from dual MODEL DIMENSION BY (rownum as line) MEASURES (0 as a, 0 as b, 0 as c, 0 as d , 0 as e, 0 as f) RULES UPSERT SEQUENTIAL ORDER ITERATE (# ITERATE #) UNTIL (# ITERATE_UNTIL #) (# RULES #) '; begin return replace (replace (replace (l_sql, '# RULES #', validate_modelclause_rules (v ('P3_RULES'))), '# ITERATE #', nvl (v ('P3_ITERATE'), 1)), '# ITERATE_UNTIL # ', nvl (v (' P3_ITERATE_UNTIL '),' 1 = 0 ')); end;

In the APEX application, you must now separate the individual rules with ##.

Figure 11: Slightly changed formula input after the introduction of the validation function

The rules entered are then checked to see whether each open round or square bracket is closed, whether a bracket is not closed first (and thus the MODEL clause is terminated) and whether each quote character (') is correctly masked. This is not necessarily a full validation; but covers a few cases. Nevertheless, the free input of formulas remains rather a case for (few) power users. In most cases it should be better to only inquire about the basic parameters for a simulation and otherwise to save the formulas in the application development department. All in all, the SQL MODEL clause offers very powerful ways to work with the data of an SQL query - and an APEX report can be so much more than simply presenting data.

Back to the community page