1

Oracle Application Express Users Guide Contents 1. 2.

3. 4. 5. 6. 7. 8.

9. 10.

Introduction Logging in to Oracle Application Express Username Oracle Application Express Components Using SQL Commands from the SQL Workshop Component General Overview SQL Command Window Saving a SQL Statement Accessing the Saved SQL Statement Using the History Option Using the Explain Option Using Object Browser tool from the SQL Workshop Component Using SQL Scripts tools from the SQL Workshop Component Viewing Scripts Running Scripts Creating Scripts Upload Scripts Using Query Builder tools from the SQL Workshop Component Contacting the Academy

2 2 2 3 3 4 6 7 8 9 10 10 11 11 13 14 15 15

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

2 1. Introduction This document will help Oracle Academy students and instructors become familiar with how to use Oracle Application Express and each of its components from an end user’s perspective. If you would like to learn more about the teacher capabilities of Oracle Application Express, please refer to the Oracle Application Express – Teacher Guide.

2. Logging in to Oracle Application Express Oracle Academy students and instructors can log in to Oracle Application Express by going to http://iacademy.oracle.com.

1.

2.

3.

4.

Enter your School, Username, and Password information. (This information was given to Academy instructors via email. If you did not receive this information, please contact the Academy as described on the contents page of this document. You must be a registered and paid instructor.) Students obtain their account information from their instructor.

Username The Username cannot be changed, and is broken down into 4 parts: 1. 2. 3. 4.

Country and State. Example: USVA for a school in the US in Virginia School naming convention given to you by Academy. The SQL section (SQL01) – if you teach more than one class you may have students in SQL02, SQL03, etc, Student number (S01).

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

3 Oracle Application Express Components Once you log into Oracle Application Express you will see the Oracle Application Express home page. This page displays all of the components of Oracle Application Express: Application Builder, SQL Workshop and Administration.

• • •

Application Builder: Allows you to create, view or monitor applications. SQL Workshop: You can create, manage, and view the database objects from a Web browser using SQL Workshop. Administration: Monitor Activity and Dashboards.

SQL Workshop is the main component that is used with the Database Design and Programming with SQL curriculum. Note that the tabs at the top of the page provide quick access to these components.

3. Using SQL Commands from the SQL Workshop Component Click on the SQL Workshop icon. On the SQL Workshop home page you will see the four tools available from SQL Workshop: • Object Browser • SQL Commands • SQL Scripts • Utilities

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

4

SQL Commands is where you will be spending most of your time entering and practicing SQL coding in the Database Design and Programming with SQL course. You can use the SQL Command tool to run SQL statements on any Oracle database schema that you have access privileges.

General Overview SQL Command Window The following two graphics will point out a few general things to note about the SQL Command window: 1. 2. 3. 4.

Schema: The drop down menu only displays the schemas that you have been granted access. Statement window: Type in your SQL commands in this window. Run SQL button: Click this button to execute the SQL statement. Save button: You have the ability to run your SQL statement or save it for future use. You may find yourself typing the same SQL statement over and over. Rather than retyping the statement every time, you can save the statement by clicking on the Save button. 5. After a SQL statement is executed, the results are displayed in the Results window. Or an error message displayed if there is a problem with the SQL command.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

5

Graphic 2 on General Overview on the SQL Command Window: 6. Autocommit: Be sure to uncheck the autocommit box (if available), until you are sure you are ready to commit your commands. 7. Display: The display drop down menu lets you select the number of rows you would wish displayed. 8. Tabs: The tabs can quickly take you back to any of the 3 main components of the Oracle Application Express. The Home tab will take you back to the main Home page. 9. Saved SQL: Click on this button to display your list of saved SQL commands. 10. History: Displays a list of the recently executed SQL commands

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

6

There are additional features to note about the Results window (see Graphic below): 1.

2. 3. 4.

If you want to output the results to a file: a. Click on the “Download” link b. A Pop-up window will appear and ask if you want to “open or save this file” c. If you select “open” then it will open the results in a Microsoft Excel spreadsheet. From Microsoft Excel you can then do a “save as” to save the file in this format. d. If you select “saved” then it will save it as a .csv (comma separated value) file. A pop-up will allow you to select the saved filename and location. You can control the number of rows displayed in the Results window by using the “Rows” drop down menu. Click on the “eraser” to clear the Statement Window. Click on the “flashlight” to see a list of table names.

4. Saving a SQL Statement You may find yourself typing the same SQL statement over and over. Rather than retyping the statement every time, you can save the statement in the SQL Archives for quick access. To save the SQL commands: 1. Click on the ‘Save’ button in the SQL command window. 2. When you click the Save button, a pop-up window will appear where you can enter the name and description of the file. Click the Save button when done.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

7

Accessing the Saved SQL Statement The saved SQL commands can be accessed, executed, modified and deleted (See Graphic Below). 1. 2. 3.

4. 5.

To display the list of saved SQL commands: click on the “Saved SQL” option. To execute a saved SQL command: click on the “Name” of the saved SQL command. You will see it displayed in the statement window. You can now click on the “Run” button to execute these commands. To edit a saved SQL command: click on the “Name” of the saved SQL command. You will see it displayed in the statement window. Edit the command, as needed then click on the “Save” button. The pop-up window will contain the original information. You can either keep it or edit the information or save to a new filename. To delete a saved SQL command: click on the box in front of the command you wish to delete, and then click on the “Delete Checked” button. To search for a SQL command: enter a key word in the “Find” box, then click on the “Go” Button.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

8

5. Using the History Option A list of the latest run SQL commands is kept in History. By default they are listed the most recently run commands. There are different options to note in the History window. 1. Click on the “History” option. 2. To search for a previously used command: Enter a key search word in the “Find” box the click on the “Go” button. 3. To re-execute the SQL command: Click on the SQL you wish to execute. You will see it displayed in the Statement Window. Click on the “Run” button to execute the SQL commands. 4. To change the display order: Click on the “Time” column to change the order that the SQL commands are displayed.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

9

6. Using the Explain Option Either type in a SQL command in the “Statement” window or select a command from “History” or “Saved SQL”, then click on the “Explain” option to see a graphic explanation of SQL command in the “Results” window.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

10 7. Using the Object Browser tool from the SQL Workshop Component What if you needed to know the columns in a table? Or the object types of the columns in a table? Or whether the columns are NULL or NOT NULL? The answers to these questions plus more can be answered by using the Object Browser tool. The Object Browser can be used to create or browse objects in your schema.

8. Using SQL Scripts tools from the SQL Workshop Component The SQL Scripts can be used to view, create or upload SQL scripts. A SQL script is one or more SQL statements that are executed sequentially. Each statement must have a semi-colon at the end of the statement.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

11 Viewing Scripts To view scripts loaded into the SQL Script tool, either click on the SQL Script icon or select “View” from the SQL Script drop down menu. A detailed view of the scripts is displayed including script owner, script name and etc.

Running Scripts 1. To run a script, click the “Run” icon next to the script you wish to execute. Once a script has been executed it is no longer displayed in this list.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

12

2. You will get a pop-up confirmation window to either cancel the request, Run Now, or Run in Background. Click the “Run in Background” button. To see the results, click on “View Results.”

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

13 The following is a sample of the type of detailed results information you might see.

Creating Scripts To access the script editor window you can either: •

Select “Create” from the pull down menu of the SQL Script Tool

•

Or click on the “Create” button from the SQL Script tool.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

14 To create a new script: 1. Give your script a name 2. Enter in the SQL commands 3. Click “Save” button to save your script in the SQL Script Tool 4. Or Click “Run” button to execute a test run of your script The script will remain in the SQL Script Tool list until you execute the script or you delete it from the list.

Upload Scripts Before you upload a script into the Script Repository, you must first create it in a text editor on your desktop. When saving your script, make sure to save it as a .sql file. Sometimes programs will add an extension to your .sql and cause problems. For example, cre_dept.sql may sometimes be saved as cre_dept.sql.doc. If this is a problem, place double quotes around the title of the script when saving – “cre_dept.sql”. You can access the upload window by selecting “Upload” from the SQL Scripts drop down menu. You will be prompted for the path to your file and to enter a script name.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

15

9. Using Query Builder tools from the SQL Workshop Component The Query Builder tool assists in creating or viewing existing SQL queries. No further information will be provided in this documentation on using this tool.

10. Contacting the Academy If you have any questions about how to use Oracle Application Express or about the Academy in general, please go to https://academy.oracle.com and click on the “Contact Us” link at the bottom of the webpage.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.