100% dynamic froms

Collected from: http://sheikyerbouti.developpez.com/dynamic/dynamic.htm

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 rows 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 row, 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 rows from the query    **

**   s - Select a particular row         **

**   i - Insert a row                    **

**   u - Update a particular row         **

**   d - Delete a particular row         **

**   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 row

**

** 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 row, 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 rows corresponding to the query are displayed on the screen

Ø      List all rows for the current query

Display the current query corresponding rows

Ø      Select a particular row

Enter an option letter (default : g) : s

*

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

** SELECT a ROW FROM the TABLE **

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

*

Enter a row 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 row

Enter an option letter (default : g) : i

*

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

**  INSERT a new row  **

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

*

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

PL/SQL procedure successfully completed.

Ø      Update a particular row

Enter an option letter (default : g) : u

*

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

**  UPDATE a particular row  **

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

*

Enter a row 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.

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 row

Enter an option letter (default : g) : d

*

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

** DELETE a row from the TABLE **

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

*

Enter a row 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