MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

APEX SQL Query

without comments

The following walks through how you sign on to a STUDENT Workspace with Oracle’s APEX product and write and run free-form SQL statements. You can find instructions on how to create your own STUDENT Workspace.

While this blog introduces several concepts and features of Oracle APEX, it only focuses on how to write and run free-form SQL statements. Overall, Oracle APEX is a valuable tool to learn and master.

UseStudentAPEX01

  1. You start the process by accessing the Oracle Database 11g APEX, which you can access at http://localhost:8080/apex by default on the server. If you’ve got a static IP address for your instance, you can replace localhost with the IP address or hostname for the IP address.

    • Workspace: STUDENT
    • Username:  ADMIN
    • Password:  STUDENT

UseStudentAPEX02

  1. After you login to the STUDENT workspace, you have four options. They are the: Application Builder, SQL Workshop, Team Development, and Administration. You start the process by accessing the Oracle Database 11g APEX, which you can access at http://localhost:8080/apex by default on the server. If you’ve got a static IP address for your instance, you can replace localhost with the IP address or hostname for the IP address. Click on the SQL Workshop icon to proceed.

    • Application Builder: Let’s you build custom APEX applications.
    • SQL Workshop: Let’s you work with custom SQL, and APEX provides you with the following utilities:
      • Object Browser: Lets you create tables, views, and other objects.
      • SQL Commands: Lets you run individual SQL statements inside a browser window and returns results in the bottom pane.
      • SQL Scripts: Lets you create, upload, delete, and run scripts from the browser.
      • Query Builder: Lets you create free form queries that include joins between tables, but limits you to primary to foreign key table relationships. That means you can’t write range joins with a cross join and the BETWEEN operator and you can’t write self-joins.
      • Utilities: Lets you work with the Data Workshop (imports and exports data), Object Reports (a SQL report writer tool), Generate DDL (a tool that creates structures in the database), User Interface Defaults (coordinate data dictionary), Schema Comparison (a tool to compare similarities between schemas, About Database (the ability to connect as the database administrator), and Recycle Bin (dropped and purged structures).
    • Team Development: A project management tool.
    • Administration: Lets you manage database services, users and groups, monitor activities, and dashboards. You should note that the SQL query doesn’t have a semicolon like it would in a SQL*Plus environment. The Run button acts as the execution operator and effectively replaces the role of the semicolon, which traditionally executes a statement.

UseStudentAPEX02A

  1. Clicking the SQL Workshop icon takes you to the second level menu. You click the SQL Commands icon to enter a free-form SQL statement. Click on the SQL Commands icon to proceed.

UseStudentAPEX03

  1. The first text panel lets you enter free-form queries. The Autocommit checkbox is enabled, which means the result of INSERT and UPDATE statements are immediate and don’t require a COMMIT statement. The second text panel displays results from a query or acknowledgment of statement completion.

UseStudentAPEX04

  1. This screen shot shows a query in the first panel and the results of the query in the second panel.

As always, I hope this helps those looking to learn new things and approaches.

Written by maclochlainn

April 5th, 2015 at 4:36 pm