Oracle Application Express 18: Quick SQL (2 of 2) - Creating a Script

Before You Begin

This 10 minute tutorial shows you how to create a Quick SQL Shorthand, configure the SQL script, save, and download the Quick SQL script.

This is the last tutorial in Oracle Application Express 18: Quick SQL series. Read the tutorials in sequence.

• Oracle Application Express 18: Quick SQL (1 of 2) - Accessing Samples
• Oracle Application Express 18: Quick SQL (2 of 2) - Creating a Script

Background

Oracle Application Express is a rapid web application development tool for the Oracle Database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in the Oracle Cloud.

Quick SQL in Oracle Application Express Release 18 is a quick way to develop a script for simple tables and views. You can quickly generate the SQL required to create a relational data model from an indented text document. Quick SQL reduces your time and effort required to create SQL tables, triggers, and index structures.

What Do You Need?

• Complete the preceding tutorial in this series.
• Access to Oracle Application Express Release 18.1 or later.

Create a SQL Script

You can create simple SQL scripts using the Quick SQL syntax. To create a Quick SQL Shorthand, follow the steps below:

1. Sign in to your development environment.
2. From the Oracle Application Express home page, click SQL Workshop.

   

3. Click Utilities.
4. Click Quick SQL. The Quick SQL page appears.

   

5. Enter the following SQL statement in the Quick SQL Shorthand pane:

   Events /insert 10
     name
     start_date
     duration /values HALF DAY, FULL DAY, TWO DAY, ONE WEEK
     location
     Attendees /insert 100
       name
       company
       email
       speaker_yn vc1 /values Y, N

   

   Observe the Quick SQL Shorthand. In this code, you are creating two tables: Events and Attendees. The Events table has the following columns:

   • name
   • start_date
   • duration

     The duration column has a list of allowable values.

   • location

   The Attendees table has the following columns:

   • name
   • company
   • email
   • speaker_yn

     The speaker_yn column has Y and N values.

6. Click Generate SQL. The generated SQL appears in Oracle SQL Output pane. Compare the Quick SQL script with the corresponding Quick SQL Shorthand. Observe the Events table and Attendees table. The generated script corresponds to the Quick SQL Shorthand values.

   

Configure Your Quick SQL Script Settings

Configure the Quick SQL settings to explicitly set SQL syntax generation options. To configure Quick SQL settings, follow the steps below:

1. On the Quick SQL page, click Settings.

   

2. To prefix all databse object names in the script with the provided value, in the Settings dialog, in the Table region, enter MY in the Object Prefix field. Review other configuration settings by clicking on the Help icon.

   

3. To add additional columns to automatically add to your database table, scroll down to the Additional Columns region and select Audit Columns.

   

4. Click Save Changes. Notice the changes.

   

Save and Download Your Quick SQL Script

You can now save and download the generated Quick SQL script. To save and download your Quick SQL script, follow the steps below:

1. Click Save SQL Script.
2. In the Save Script dialog, enter Eventplanning for the Script Name.

   

3. Click Save Script. When you click the button, the output is saved as a SQL Script within SQL Workshop. This allows you to run the script into your development environment in a later step.
4. You can download your Quick SQL script on your local system. To download the Quick SQL script, click Download.

   

5. To review and run your SQL script, click Review and Run. The Script Editor window appears.

   

6. Review the SQL script and click Run. A Run Script dialog appears.

   

7. Click Run Now to proceed. The SQL Scripts Results page appears with the result.

   

8. To see your Quick SQL script, click the SQL Scripts breadcrumb.

   

9. On the SQL Scripts page, find your script.

   

Want to Learn More?

• Using Quick SQL