100% dynamic

Introduction

Sometimes I asked myself some strange questions. One of them is : “What can I do with a single query ?”

What can I do with a single “Select * From EMP” ?

Of course, I can get the corresponding raws in the table, but I can also get the data structure, and if I can get data and their structure, I can handle these data by providing INSERT, UPDATE and DELETE orders.

I can define control rules to validate them and also use functions to apply other controls on them...

Actually, I can do a lot of things with a single query !

The problem

What about an application with 70 simple tables of parameters?

The programer will probably build 70 different screens, each of them based on a different table, but what about further modifications of the table structure or the implementation of a new control rule ? He has to open each screen, apply the modifications, provide the corresponding tests and re-deliver the screen to the production environment.

In fact, this kind of job is too simple (boring ?) for a programer and could be done by a non-programer.

The solution

The solution is called “dynamic SQL” and Oracle provides 2 mechanisms to resolve this sort of problem

q The DBMS_SQL package

q The EXECUTE IMMEDIATE function

Which are available only on the server side (Oracle Forms provides a client side equivalent : the EXEC_SQL internal package)

Implementation

In this article, I provide two examples using dynamic SQL

The first example shows a single dialog able to handle any data from any table.

The second example shows how to handle data with Sql*Plus when the starting point is a single SELECT order.

Example 1 : the Oracle Forms 100% dynamic dialog

Generaly, an Oracle Forms dialog is based on a table, a view, or a strored procedure.

This one is only based on the SELECT order provided through a parameter.

DYNAMIC_FORM.fmb

It can handle data provided by any select order (actually, this sample module contains only 20 char items, 10 number items and 5 date items)

You can INSERT, UPDATE and DELETE any raw, you can use the ENTER-QUERY mode, enter any search criterion in any queryable field:

> 1000

BETWEEN 1000 AND 2000

IN(‘CLERCK’,’ANALYST’)

Etc...

The validation rules are stored in a special table and are dynamically read and executed.

A second sample screen is provided to handle these validation rules

DYNAMIC_BUILD.fmb

It allows to store dialog specifications.

In the top part of the screen, you can create as many dialog specifications as you need.

Each dialog is based on a table name.

You can define the title of the dialog and the rights that are granted (Insert, Update, Delete).

You can run the test by clicking on the Run... button

The bottom part of the screen shows 2 tabs to define the column specifications and the validation rules applied on them.

The “Columns” tab is automatically populated when you choose a table name in the first block.

You can define some properties for each column.

Ø The display prompt

Ø The initial value

Ø Field is, or is not, required

Ø Field is queryable

Ø The case which can be U (upper), L (lower) or M (mixt)

Ø The format mask

Ø Item is, or is not, displayed

The second tab “Validation” allows to enter validation rules on items

Each column supports as many rules as required.

The rule text can contain any valid SQL test.

For the case of the integrity constraint, I use a special syntax which is:

EXISTS IN TABLE=the FK table name COLUMN=the FK column name

You can provide an error message, displayed to the end user if the control failed.

Example 2 : The Oracle Sql*Plus 100% dynamic demonstration

Some Sql*Plus scripts are shipped with this article to show how to handle data provided by a single SELECT order.

To start the main menu, run Sql*Plus, connect to the user which holds the stored function.

Assume the scripts are located in the d:\dynamic\scripts folder, issue the following Sql*Plus command :

SQL> @d:\dynamic\scripts\m

*******************************************

** 100% DYNAMIC **

** M A I N M E N U **

** -------------------------------- **

** Option list : **

** **

** g - Generate a new query **

** l - List all raws from the query **

** s - Select a particular raw **

** i - Insert a raw **

** u - Update a particular raw **

** d - Delete a particular raw **

** c - Commit r - Rollback **

** **

** e - End **

*******************************************

Enter an option letter (default : g) :

The menu allows to test the following actions:

Ø Generate a new query by providing a SELECT order

************************************************************

** Show data AND data structure FOR ANY SELECT ORDER **

************************************************************

** !!! Warning !!!!

** You must include the ROWID in the query

** to be able to Lock, Update or Delete a raw

** DEFAULT SELECT ORDER = SELECT ROWNUM, t.*, t.ROWID FROM EMP t

************************************************************

Enter a valid Select order :

Enter a Select order.

By default, the select order is : ‘SELECT ROWNUM, t.*, t.ROWID FROM EMP t’

Caution

If you want to be able to SELECT, INSERT, UPDATE or DELETE a particular raw, you have to include the ROWNUM and ROWID pseudo-columns in your Select order.

Just type the “Enter” key to test the default order

PL/SQL procedure successfully completed.

ROWNUM EMPN ENAME JOB MGR HIREDATE SAL COMM DE RO

-------------------------------------------------------------------------------------

1 9999 FZAPPA PRESIDENT 30/10/05 10000 10 AA

2 7369 SMITH CLERK 7902 17/12/80 900 20 AA

3 7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30 AA

4 7521 WARD SALESMAN 7698 22/02/81 1250 500 30 AA

5 7566 JONES MANAGER 7839 02/04/81 2975 20 AA

6 7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30 AA

7 7698 BLAKE MANAGER 7839 01/05/81 2850 30 AA

8 7782 CLARK MANAGER 7839 09/06/81 2450 10 AA

9 7788 SCOTT ANALYST 7566 09/12/82 3000 20 AA

10 7839 KING PRESIDENT 17/12/80 5000 10 AA

11 7844 TURNER SALESMAN 7698 08/09/81 1500 0 30 AA

12 7876 ADAMS CLERK 7788 12/01/83 1100 20 AA

13 7900 JAMES CLERK 7698 03/12/81 950 30 AA

14 7902 FORD ANALYST 7566 03/12/81 3000 20 AA

15 7934 MILLER CLERK 7782 09/12/82 1301 10 AA

The raws corresponding to the query are displayed on the screen

Ø List all raws for the current query

Display the current query corresponding raws

Ø Select a particular raw

Enter an option letter (default : g) : s

*********************************

** SELECT a RAW FROM the TABLE **

*********************************

Enter a raw number (default 1) : 9

PL/SQL procedure successfully completed.

ROWNUM EMPN ENAME JOB MGR HIREDATE SAL COMM DE RO

-------------------------------------------------------------------------------------

9 7788 SCOTT ANALYST 7566 09/12/82 3000 20 AA

-------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Ø Insert a raw

Enter an option letter (default : g) : i

************************

** INSERT a new raw **

************************

Enter the data to insert : 8888,''XXX'',''CLERK'',null,sysdate,2000,null,10

PL/SQL procedure successfully completed.

Ø Update a particular raw

Enter an option letter (default : g) : u

*******************************

** UPDATE a particular raw **

*******************************

Enter a raw number (default 1) : 16

PL/SQL procedure successfully completed.

Enter the column and value to update (e.g. COMM=500) : ename=''BURGER''

PL/SQL procedure successfully completed.

Raw updated

PL/SQL procedure successfully completed.

ROWNUM EMPN ENAME JOB MGR HIREDATE SAL COMM DE RO

-------------------------------------------------------------------------------------

16 8888 BURGER CLERK 01/11/05 2000 10 AA

-------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Ø Delete a particular raw

Enter an option letter (default : g) : d

*********************************

** DELETE a raw from the TABLE **

*********************************

Enter a raw number (default 1) : 16

PL/SQL procedure successfully completed.

Ø Commit or Rollback

Download the examples

This article is shipped with examples.

Ø Download the dynamic.zip file

Ø Unzip the dynamic.zip file

Ø Create a new Oracle user

Ø Run the install.sql script to build the demo tables and the package

Ø Open the <dynamic_directory>\forms folder

Ø Open and compile the DYNAMIC_BUILD.fmb and DYNAMIC_FORM.fmb modules (Oracle Forms 10.1.2 version)

Ø Run the DYNAMIC_BUILD module