Using SQL Commands and SQL Scripts
Using SQL Commands and SQL Scripts

This course walks you through how to use SQL Workshop in Oracle APEX.

Learning Objectives

  • Understand the basics of SQL Workshop and its components
  • Learn how to use SQL commands and SQL scripts
  • Learn how to load and unload data using the Data Workshop
  • Understand what Quick SQL is and you'll also learn how to load some Quick SQL samples

Intended Audience

  • Anyone who wants to enhance their knowledge of Oracle APEX


We recommend that you take this course as part of our Oracle APEX: Foundations learning path.


Welcome to the Oracle APEX Foundations course. Today in this lesson on Using SQL Workshop, I will be showing you how to use SQL commands and SQL scripts. Let's get started. In this section, you are introduced to SQL commands and SQL scripts. There might be reasons where you need to examine the data or prepare for backend moves that require an understanding of this utility. Therefore, running SQL scripts is a very useful feature while managing your application environments. You can use SQL commands to create, edit, view, run, and delete database objects. A SQL command can contain SQL statements or PL/SQL blocks. The SQL commands homepage is divided into 2 sections. A command editor to execute SQL commands and the display pane to view output, saved command list, and history list. The example on the screen shows a SQL command is executed and the results are displayed in the display pane. Let us see how to execute a SQL command in the APEX workspace. In your APEX workspace homepage, select SQL Workshop.

And then select SQL Commands. Here, enter the SQL command or PL/SQL statement in the command editor. I have entered a sample SQL query to display the employees whose salary is greater than the average salary of all employees. Click the 'Run' button at the top right corner. You can view the output on the Results tab of the display pane. Optionally, you can click the download link to export the results of the query to a spreadsheet. Query Builder's graphical user interface enables database developers to build SQL queries without the need for manual SQL coding. You can access Query Builder from a code editor in many parts of the Oracle APEX by clicking the Query Builder icon while editing a SQL attribute. Using Query Builder, you can search and filter database objects, select objects and columns, create relationship between objects, view formatted query results, and save queries with little or no SQL knowledge. SQL queries created in the SQL Builder can be accessed from SQL Commands. You can also access Query Builder from SQL Workshop Utilities.

To access Query Builder, on the workspace homepage, click 'SQL Workshop', select 'Utilities', select Query Builder. The Query Builder homepage appears. A SQL script is a set of SQL commands saved as a file in SQL scripts. You use SQL scripts to create, edit, view, run, and delete script files. Now, let us see how to create and run a SQL script in APEX. To do so, from your APEX Workspace home page, navigate to SQL Workshop and SQL scripts. Click the 'Create' button to create a new script. Enter a name for the script. In the code editor, enter the SQL statements or PL/SQL block you want to include in your script. Remember, that SQL+ commands are ignored at runtime. I have entered a sample SQL query to fetch the employee name, job, and salary of those employees whose salary is greater than 2,500. Click 'Create' at the top right corner.

The SQL Scripts page appears, listing your newly saved script. You can now run this script by clicking on 'Run' icon. Click 'Run' now. Observe that the SQL statement has run, and five rows were selected. To view the detailed results returned, click on the 'Detail' radio button and click 'Go'. Here, you see the list of rows that were returned from the query by running the script. You can also upload a script from your local file system. To do so, navigate to SQL Workshop and SQL Scripts. Click the 'Upload' button. The upload script dialogue box appears. Click the 'Choose File' button and select the script you want to upload. Optionally, enter a script name, and click 'Upload'. The SQL script page appears, listing here newly uploaded script.

You can run this script in the same way that we run the script earlier. Oracle APEX provides utilities to build SQL queries, load and unload data from an Oracle database, generate DDL, view object reports, manage user interface defaults, restore dropped database objects, compare schemas, monitor the database, and view database details. To navigate to the Utilities page, on your workspace homepage, click the 'SQL Workshop', and then click 'Utilities'. The Utilities page appears with the list of actions that you can perform. The upcoming videos in this lesson cover some of these utilities. To summarize, in this section, you were introduced to SQL commands and SQL scripts. I hope you learned something useful. Thank you for watching.

About the Author
Oracle University
Training Provider
Learning Paths

Oracle University, the learning and training arm of Oracle, is a leader in cloud education, providing modern digital training, certification, and learning solutions across Oracle’s entire product portfolio that enable our customers to master their Oracle Cloud solutions and maximize their investments. Over 2 million certified professionals worldwide have placed their trust Oracle University to reach their learning goals. Learn about Oracle University.