By Joel Kallman
Oracle Application Express (Oracle APEX) is the low-code development framework of Oracle Database. Oracle APEX enables the easy creation of modern and responsive web apps with no code. It is ideal for creating new user interfaces on top of existing data models or application logic in the database, but it is also widely used for creating opportunistic applications: brand-new applications that have loosely defined requirements and need to be up and running as soon as possible.
Every application (regardless of technology) is only as good as its data model, and a poor data model will result in a poor application. Quick SQL, a new feature in Oracle APEX Release 18, is the ideal tool for modeling data and creating a physical data model in Oracle Database. This article focuses on how to use the Quick SQL feature embedded in Oracle APEX 18, but even if you’re not an Oracle APEX user, you can still use Quick SQL online for free, via Oracle Live SQL, the free site where you can learn, practice, and share examples of SQL and PL/SQL.
Now suppose you’ve been tasked to quickly build an application to manage the employees of your organization. In this Oracle Magazine article, you’re going to build a new data model to manage these employees and then build an Oracle APEX application on top of this new data model. Employees have jobs, and they also belong to a department, so you will need to model these entities as well.
This article’s sample is built in Oracle APEX 18.1. If you’re not already running Oracle APEX 18.1 or later, you can request a free workspace at apex.oracle.com. Alternatively, you can go to the Oracle Technology Network to download the Database App Development Virtual Machine, which includes a preconfigured Oracle Database 18c Enterprise Edition database, Oracle APEX 18.2, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler.
Begin your exploration of Quick SQL by modeling the departments table.
The Quick SQL shorthand you entered should result in this:
departments name cost center
By indenting two spaces under departments, you are using the shorthand syntax of Quick SQL to specify the column names of the departments table. As you press Enter after each new line, Quick SQL will dynamically update the database definition language (DDL) of your data model in the right-hand text area (Oracle SQL Output). With three simple lines in Quick SQL, you now have DDL for the departments table, a primary key for the departments table, and a trigger to populate the primary key column with a globally unique ID (GUID). (At livesql.oracle.com, the default settings are different, so your DDL may not match this description exactly.)
Now update the data types and lengths for the name and cost center columns:
The Quick SQL shorthand you entered should now result in this:
departments name vc100 /nn cost center num
To complete the departments table, you will want to normalize the values in the name column to all uppercase, apply a unique constraint (to ensure that the name values of all rows in the table are unique), and supply the domain of values for the name column.
The Quick SQL shorthand you entered should now result in this:
departments name vc100 /nn /upper /unique /check hr, development, legal, sales, operations cost center num
With this little bit of Quick SQL shorthand, you have a departments table with a named primary key, unique and check constraints, and a database trigger to generate the primary key value and normalize the name column to uppercase. Pretty easy. But it gets better.
About Oracle Application ExpressOracle Application Express (Oracle APEX) is a high-productivity, low-code platform for creating modern, responsive, and accessible web applications. A no-cost feature of Oracle Database, it is a compelling application development platform available in all Oracle Database Cloud services. |
One of the hallmarks of relational databases is the ability to declaratively express relationships between tables. These are typically expressed via referential integrity constraints and are used to ensure that the data represented in associated tables is consistent. To put this into practical terms, you will now define a table for employees, and the employees table will be a child of the parent departments table. You will not be able to insert a row into the employees table and reference a department that does not exist.
Defining a child table in Quick SQL is easy. All you need to do is indent the lines of the child table directly underneath the parent table.
The Quick SQL shorthand you entered should now result in this:
departments name vc100 /nn /upper /unique /check hr, development, legal, sales, operations cost center num employees first_name last_name email /lower hire_date city country
Review the generated DDL in the Oracle SQL Output text area. You now have a second table for employees; a department_id column with a named foreign key constraint to the employees table; and, following best practices, an index on the column of the foreign key constraint (department_id).
As you just saw, you can represent the relationship between a parent table and a child table with simple indentation in Quick SQL. But what if there are multiple relationships to the same table? You can easily represent those relationships via the /references column modifier, which is used to explicitly define a relationship between tables.
Each employee has a job. Now define the jobs in a separate table.
jobs name /upper /unique /check developer, analyst, manager, assistant
job id / references jobs.
The Quick SQL shorthand you entered should result in this:
jobs name /upper /unique /check developer, analyst, manager, assistant departments name vc100 /nn /upper /unique /check hr, development, legal, sales, operations cost center num employees first_name last_name email /lower hire_date city country job id /references jobs
The employees table now has an additional named foreign key constraint referencing the new jobs table.
There are several settings you can use to control the generation of the DDL and also enable additional features. You can, for example, specify a prefix to use for all object names, change the primary key value generation to use identity data types, and even generate a PL/SQL API.
Now enable the Audit Columns feature:
Selection of the Audit Columns option means that every table now includes four additional columns to track who created or updated the row and when. The database triggers for the tables have also been updated to automatically populate the audit columns. Setting APEX Enabled to Yes affects the Audit Columns logic so that the authenticated username of an Oracle APEX application is correctly referenced in the database trigger code.
It’s far easier to prototype an application when you can see it with data, even if it’s dummy data. Fortunately, Quick SQL has an easy facility for generating sample data with the /insert table directive. After every table, you can append /insert NN, where NN is the number of rows of data to generate.
Now use Quick SQL to generate some sample data for the tables:
The Quick SQL shorthand you entered should now result in this:
jobs /insert 4 name /upper /unique /check developer, analyst, manager, assistant departments /insert 5 name vc100 /nn /upper /unique /check hr, development, legal, sales, operations cost center num employees /insert 100 first_name last_name email /lower hire_date city country job id /references jobs
And there you have it. With 14 lines of Quick SQL shorthand, you have now generated a well-formed and properly named data model—shown in Figure 1—with three tables, indexed foreign-key constraints, named check constraints and unique constraints, database triggers to audit changes made to the tables, and sample data. More than 2,000 lines of SQL script. Amazing!
Figure 1: The completed data model and SQL script
If you’re using Quick SQL within Oracle APEX, you can save and execute the statements of your generated SQL script to quickly create an Oracle APEX application on this data model.
After the tables and triggers have been created and the sample data inserted, it’s time to create your application.
You can navigate among the different pages of your application, with reports and forms on your new tables. Note that on the form for editing employees, the job and department fields are controlled via a select list, constraining the values to those defined in the related tables. With 14 lines of Quick SQL shorthand and no code, you now have a 28-page responsive web application, shown in Figure 2, complete with user feedback, activity monitoring, and access control!
Figure 2: The Oracle APEX application on the new data model
An application is only as good as its data model. Although Quick SQL is not a replacement for the sophisticated data modeling capabilities of Oracle SQL Developer Data Modeler, rapid and concise data model development with Quick SQL is ideal for many opportunistic applications. Available as a service for all (via livesql.oracle.com) and tightly integrated with Oracle APEX, Quick SQL enables you to quickly generate complete and accurate data models, along with sample data. Using the combination of the low-code rapid application development of Oracle APEX and Quick SQL is an excellent way to design and develop your next opportunistic application.
DOWNLOAD Oracle Application Express 18.2.
LEARN more about Oracle APEX.
TRY
Oracle Database Cloud services.
Oracle Live SQL.
REQUEST a free Oracle APEX workspace.
Illustration by Wes Rowell