Introducing to key development components

The primary purpose of SQL Workshop is to manage database objects directly from the browser. This is especially useful when working in hosted environments or when developers does not have direct access to the underlying database and cannot use tools like SQL Developer or SQL Plus for database management.

You can access SQL Workshop from the APEX home page in two ways: either by clicking the SQL Workshop icon on the Workspace home page and selecting the desired component, or by using the dropdown arrow next to SQL Workshop in the Navigation tab and choosing the component you want to access.

A schema serves as a logical container for database objects. On the SQL Workshop homepage, you can select the default database schema for your session using the schema list on the right side. Only schemas assigned to your workspace will be displayed in the list.

Each SQL Workshop component serves a specific purpose to help you effectively interact with and manage your database objects. Based on the task, the following components can be used:

Object Browser: The Object Browser allows developers to browse, create, and edit database objects. On the Object Browser homepage, you can create various types of database objects, such as table, views, packages, procedures and functions.

The Object Browser page is divided into two panes:

• Object Selection Pane (Left): Displays a list of database objects of a selected type within the current schema. You can narrow down the results by filtering based on object names. To create a new object, click the plus icon at the top-left corner of the pane and select the desired object type.

• Detail Pane (Right): Shows detailed information about the selected object. For example, selecting a table will display details like columns, data, indexes, constraints, grants, statistics, triggers, dependencies, DDL, and sample queries. You can also modify the table structure, by adding a new column, or you can delete (drop) the table entirely.

SQL Commands: The SQL Commands component allows you to create, edit, view, run and delete database objects. Commands can include both SQL statements or PL/SQL blocks.

The SQL Commands interface in Oracle APEX is divided into two main sections:

1. Command Editor – for executing SQL commands.

2. Display Pane – for viewing output, saved command lists, and history lists.

Here is a sample SQL query to display all orders from the ’Orders’ table, with a status of ‘Complete.’ The query results are displayed under the Results tab within the display pane. Additionally, you can export the query results to a spreadsheet by clicking the Download option.

Oracle APEX also offers Query Builder with a graphical interface, allowing database developers to construct SQL queries visually, without needing to write SQL code manually. You can access Query Builder from a code editor in Oracle APEX by clicking the Query Builder icon while editing a SQL attribute.

With Query Builder, users can:

• Search for and filter database objects.
• Select specific objects and columns.
• Create relationships between objects.
• View query results in a formatted layout and save queries, even with minimal SQL expertise.

Queries created in Query Builder are accessible from SQL Commands. Furthermore, you can launch Query Builder through the SQL Workshop Utilities menu.

SQL Scripts: SQL Scripts are collections of SQL commands stored as files within the SQL Scripts section. These scripts can include one or more sequential SQL statements or PL/SQL blocks. They are used to manage script files by enabling you to create, edit, run, view, and delete them.

To create and execute a SQL script in Oracle APEX, start by navigating to SQL Workshop and selecting SQL Scripts from the home page. Click Create to begin creating a new script. Assign a name to your script, then enter your SQL statements or PL/SQL code in the editor provided. Once you’ve finished, click the Create button in the upper-right corner to save the script. The SQL Scripts page will then display your newly saved script. To execute it, click the Run icon. For detailed output, select the Detail radio button and click Go to view the results.

If you already have a script saved locally, you can upload it directly from your local file system. To do so, go to SQL Workshop > SQL Scripts and click the Upload button. In the Upload Script dialog box, click Choose File to select the script from your device. Optionally, specify a script name before clicking Upload. The script will be listed on the SQL Scripts page, and you can execute it in the same way as a newly created script.

Utilities: The Oracle APEX Utilities feature offers a variety of tools to streamline database management. You can build SQL queries, load and unload data, generate DDL, view database object reports, manage user interface defaults, compare schemas, restore dropped objects, monitor the database, and view detailed database information.

RESTful Services: This component allows you to define, modify, and manage RESTful services declaratively. RESTful services operate with Oracle REST Data Services (ORDS) to provide database access for external applications. If the APEX instance administrator has disabled RESTful services, this feature will not be available, and the corresponding icon will not appear in SQL Workshop.

Loading and Exporting Data with Data Workshop

The Data Workshop page in Oracle APEX allows you to manage data by importing it into your workspace or exporting it. You can load data from file formats such as XLSX, CSV, XML, and JSON, or export your data in text or XML format.

To load data, use the Load Data Wizard, and for transferring data from your workspace, use the Unload Data Wizard.

Steps to access the data load or unload page:

1. From the APEX Workspace homepage, click the down arrow next to SQL Workshop.
2. Under Utilities, select Data Workshop to open the page.
3. From there, you can choose Load Data to import files into your workspace or Unload Data to export data from it.

Alternatively, you can access the Data Workshop page by clicking the SQL Workshop icon on the workspace homepage. Then, navigate to Utilities > Data Workshop.

Sample Datasets

Sample Datasets provide a convenient way to create sample applications in Oracle APEX. These datasets include enough data to design pages using various components and can be easily managed within your workspace.

You can install, refresh, or replace sample datasets in any schema associated with your workspace. Each dataset contains a variety of database objects and sample data, which can be effortlessly removed if needed. While the sample data is available in multiple languages, only one language can be loaded into the sample database objects at a time.

Accessing Sample Datasets:

1. From the APEX Workspace homepage, navigate to SQL Workshop > Utilities > Sample Datasets.
2. On the Sample Datasets page, you’ll find a list of available datasets.
3. Click Install next to any dataset to add it to your workspace.

Quick SQL

Quick SQL simplifies the process of designing and prototyping data models using a markdown-like shorthand syntax that expands into standards-compliant Oracle SQL. This tool is ideal for quickly creating robust data structures while learning and leveraging SQL syntax.

Key Use Cases of Quick SQL:

• Rapidly generate and refine data models.
• Save shorthand syntax as templates for future use or to iterate through design changes.
• Generate random data for testing purposes.
• Explore SQL syntax for creating tables, selecting data, inserting values, creating indexes and triggers, and working with PL/SQL packages and views using built-in examples.

While Quick SQL significantly reduces the effort required to design SQL tables, triggers, and indexes, it is not intended to replace comprehensive data modeling. Once SQL is generated, you can further tweak and enhance it to meet your specific requirements.

Accessing Quick SQL:

From the APEX Workspace homepage, click the down arrow next to SQL Workshop, select Utilities, and then choose Quick SQL. Alternatively, click the SQL Workshop icon on the homepage, navigate to Utilities, and select Quick SQL.

You can explore Quick SQL samples to better understand the shorthand syntax and how it translates into SQL. To access these samples, click the Load button to view a selection of templates. Once you load a sample, the shorthand syntax will appear on the left, and the corresponding generated SQL will be displayed in the right-hand pane.
Similarly, if you click the Help button and navigate to the Samples tab, you can load examples from the list provided.