This course walks you through how to use SQL Workshop in Oracle APEX.
- 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
- 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 get started and use the SQL Workshop components. My name is Apoorva and I am a Senior Product Manager for Oracle APEX. Let's get started. In this lesson, you are introduced to SQL Workshop and its components. We also covered how to interact with the Oracle database using the Object Browser in SQL Workshop. What is SQL Workshop?
Oracle APEX includes the SQL Workshop for application developers to maintain database objects from the browser. This feature is particularly important when developing in hosted environments or where the application developer does not have access to the underlying database and cannot use tools such as SQL Developer or SQL+ to manage the database objects. You can access SQL Workshop in one of the following two ways from the APEX homepage. You can either click on the 'SQL Workshop' icon from the workspace homepage and select the component you want to access, or you can click the dropdown arrow next to SQL Workshop on the navigation tab, and then select the component you want to access, that is, Object Browser, SQL Commands, SQL Scripts, Utilities, and RESTful services.
A schema is a logical container for database objects. Use the schema list on the right side of the SQL Workshop homepage to select the default database schema for your SQL Workshop session. Only schemas assigned to your workspace appear in the schema list. The SQL Workshop provides tools that enable you to view and manage database objects. Based on the task, you use the following components of SQL Workshop: Object Browser. This component enables developers to browse, create, and edit objects in a database. SQL commands: use SQL Commands to create, edit, view, run, and delete database objects. A SQL Command can contain SQL statements or PL/SQL blocks. SQL Scripts: A SQL Script is a set of SQL Commands saved as a file in the SQL Scripts. A SQL Script can contain one or more SQL statements or PL/ SQL blocks.
You use SQL Scripts to create, edit, view, run, and delete database objects. Utilities: Use Oracle APEX 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. Lastly, RESTful services. RESTful services enable the declarative specification of RESTful services used to access the database. These services work with the Oracle REST Data Services, also known as ORDS, to enable the consumption of the services. This component helps you create, edit, and delete RESTful services. Note that if the instance administrator has disabled RESTful services for this APEX instance, then the RESTful services is not available for this instance and the icon does not appear in SQL Workshop. Object Browser enables you to browse, create, and edit objects in a database. The Object Browser page is divided into two panes: the object selection pane.
This pane appears on the left side of the Object Browser page. It list the database objects of a selected type within the current schema. You can further narrow the results by filtering on the object name. The detail pane: This pane appears on the right of the page. It displays detailed information about the selected object. In this example shown on the screen, the OEHR_ORDERS table is selected on the left pane and the detailed information of this table is displayed in the detailed pane on the right. To access Object Browser in your APEX environment, in your workspace homepage, click on 'SQL Workshop' and then select 'Object Browser'. You can also navigate using the SQL Workshop navigation menu at the top. To view object details, select an object in the object selection pane. Here I select OEHR_ORDERS. The details are displayed in the detail pane. Click the tabs at the top of the detail pane to view additional details about the current object. To edit an object, select an appropriate button, such as to add, modify, rename, drop a column, or dropping a table, and so on.
Now, let us see how to create a table in the Object Browser. You create database objects by using the Create Database Object wizard. To create an object, click the 'Create' icon located in the upper right corner of the detail pane. From the list of object types, select the type of object you want to create. For example, if I choose table, you get a wizard to create a new object. Now you can follow the on-screen instructions to create the new object. In this case, you need to enter a table name and the column names for the table, and in the next few steps, you will be entering the primary key and foreign keys and any other constraints if applicable and finish the creation of the table. In this section, you learned what is SQL Workshop and its components. We also covered Object Browser and how to create a database object like table. I hope you learned something useful. Thank you for watching.
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.