top of page

Underused features of SQL Workshop in Oracle APEX

Writer: Sam G
Sam G
Oct 13
3 min read

With many widely used, essential features in Oracle APEX’s SQL Workshop such as SQL Command and the Object Browser, there are as many if not more underused features especially in the Utilities section of the workshop. 


The ones I will be covering in this blog will be the Data Generator, Query Builder and Quick SQL.


Data generator


Utilities page in Oracle APEX showing grid of tools like Data Workshop, Data Generator, Query Builder, and Quick SQL on a white dashboard.

Data Generator is a useful tool when you need to produce a large amount of randomly generated test data for tables in your apps. They work by creating a blueprint for data generation which can be reused if you need more test data with the same formats. 


When creating a blueprint for an existing table, the wizard maps all of the table’s columns to the columns in the blueprint editor. From here you can check each of the parameters of each individual column to check they match your requirements. Parameters you can change include:

  • Maximum length (for strings) or maximum/minimum value (for integers)

  • Format mask

  • Data source (this has built-in options but there are others)


Note: You should preview how the data will fill your table(s) and go back to make any adjustments needed before inserting any data into your database. 


After you are happy with the data it will generate, you can click the generate data button to proceed. On the dialog that comes up you can choose from 2 generation options - file export (JSON, CSV or SQL Insert) and insert into database. Now when you click either generate data or insert data, depending on whether you are exporting a file or inserting to a database, your data will be randomly generated. This can sometimes take a while depending on how many rows you are creating and how many columns you have.


Generating test data for a table can be done using for loops, however, by using a data generator you gain many advantages such as previewing the data before it is inserted and it being quicker to generate data for large tables.


Query Builder


Oracle APEX Utilities page showing tiles for Data Workshop, Data Generator, Query Builder, Quick SQL, and other tools.

The query builder generates sql commands based on the diagram made by you in the editor. These diagrams look similar to entity relationship diagrams as shown below:


Database schema with HR_EMPLOYEE and HR_DEPARTMENT tables; FIRST_NAME, LAST_NAME, DEPARTMENT_NAME, and SHORT_CODE are checked.

The tick boxes on the left hand side of the table should be ticked when you want that item to be selected in the query. The right hand side of the table is used when joining to other tables. From these 2 main functionalities, the query builder will write an SQL query which you would be able to copy and paste into your app. For example, the diagram above would generate the code below:


SELECT  hr_employee.first_name AS first_name ,hr_employee.last_name AS last_name ,hr_department.department_name AS department_name ,hr_department.short_code AS short_codeFROM  hr_department hr_department ,hr_employee hr_employeeWHERE hr_employee.department_id = hr_department.id

Note: The generated code can be run immediately from the query builder 


Whilst this is useful as a low-code tool for generating queries, it does have some disadvantages.

  1. The formatting Oracle uses most likely will not be how you would format it meaning you would need to change the code manually after copying it into your app.

  2. The connection line between the tables doesn’t make it clear which columns are connected between the tables which.


Quick SQL


Oracle APEX Utilities dashboard showing tiles like Data Workshop, Data Generator, Query Builder and Quick SQL on a white interface.

The Quick SQL section will convert QuickSQL into normal SQL which can save time when you need to create either lots of tables or tables with a large number of columns. It can be used to automatically set up industry best practice features such as audit columns, PL/SQL APIs and triggers. 


Like the data generator, Quick SQL can be used to insert large amounts of sample data quickly with a short command like ‘/insert 20’ which would insert 20 rows of randomised data. However, this isn’t as versatile as the data generator as it won’t format email addresses correctly or have a format mask for a date. Below is an example of QuickSQL and its normal SQL equivalent made by the editor:


SQL schema screenshot for users table showing id, username, email, created_at, primary key, unique email, and default timestamp

Whilst it is a lot shorter than writing SQL out normally, it does come with some disadvantages.

  1. Rigid naming conventions for APIs and triggers which may cause some issues if the conventions you follow are different than Oracle’s.

  2. Limited in the fact it can only create tables not alter or delete and also struggles with advanced, multi-column constraints, complex check constraints, or conditional foreign keys so isn’t useful for complex tables.



Final notes


Using these lesser known tools can help you to use Oracle APEX to its fullest and build apps faster and more efficiently so be sure to check out these features and experiment with them.


Also don’t forget to check the RADAPEX blog page and RADAPEX on LinkedIn to read more of our blogs and success stories.


 
 
bottom of page