5 Using Oracle Application Express Utilities

5.1.1 About the Data Load and Unload Wizards

The Data Load and Data Unload wizards in Oracle Application Express enable you to easily load and unload delimited text data to and from the database. The step-by-step wizards have the following features:

• Load or unload XML files or delimited-field text files (such as comma-delimited (.csv) or tab-delimited files).

• Load by copying and pasting from a spreadsheet.

• Omit (skip) columns when loading or unloading.

• Load into an existing table or create a new table from the loaded data.

• When loading into a new table, the primary key can be taken from the data or generated from a new or existing Oracle sequence.

• When loading into a new table, column names can be taken from the loaded data.

• Each time that you load from a file, file details are saved in a Text Data Load Repository. You can access these files from within the repository at any time.

Limitations include the following:

• The wizards load and unload table data only. They do not load or unload other kinds of schema objects.

• You can load and unload to and from your own schema only. This is also true for users with administrator privileges.

• You can load or unload only a single table at a time.

• There are no data type limitations for unloading to text or XML files, or for loading from XML files. However, when loading from spreadsheets (through copy and paste) or from text files, only the following data types are supported: NUMBER, DATE, VARCHAR2, CLOB, BINARY_FLOAT, and BINARY_DOUBLE.

Supported unload formats include:

• Text such as comma-delimited or tab-delimited data

• XML documents

5.1.2 About Importing, Exporting, Loading, and Unloading Data

You have several options when copying data between Oracle databases or between an Oracle database and external files. Data copying is accomplished by exporting and importing data, and by unloading and loading data. The following table defines these terms.

You can export data from any Oracle Database edition (Express Edition, Standard Edition, and Enterprise Edition) into any other edition.

5.1.3 Import/Export/Load/Unload Options

The Oracle Database and Oracle Application Express provide several powerful options for importing, exporting, loading, and unloading data. Table 5-1 provides a summary of these options.

Table 5-2 provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.

5.1.4 Accessing the Data Load/Unload Page

To access the Data Load/Unload page:

1. On the Workspace home page, click the SQL Workshop icon.

2. Click Utilities.

3. Click Data Workshop.

   The Data workshop page appears.

4. Click the appropriate icon to load data, unload data, or view the repository.

5.1.5 Loading Data

The Load Data Wizard is used to copy and paste or upload data.

• About Loading Data

• Loading a Text File or Spreadsheet Data

• Loading an XML Document

5.1.6 Unloading Data

The Unload page is used to export the contents of a table to a text file or XML document.

• Unloading a Text File

• Unloading to an XML Document

5.1.7 Using the Repository

Loaded text data files and spreadsheets are stored in the Repository.

To access the different repositories:

1. On the Workspace home page, click the Utilities icon.

2. Click Data Workshop.

3. Under Repository, select one of the following:

   • Import Repository - Displays the status of loaded Text data. Details include file name, imported by, imported on, schema, table, bytes, and the number of rows that were loaded successfully and the number were not.

   • Spreadsheet Imports - Displays the status of loaded spreadsheet data. Details include file name, imported by, imported on, schema, table, and the number of rows that were loaded successfully and the number were not.

4. To filter the display, make a selection from the Show list and click Go.

5. To view information about a specific file, click the View icon.

6. To delete an imported file, select it and click Delete Checked.

5.4.1 Viewing Table Reports

Use the Table reports to view specific details about the tables within your database.

To view the Table reports:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Object Reports.

   The Object Reports page appears.

4. Under Table Reports, select a report to view:

   • Table Columns

   • Table Comments

   • Table Constraints

   • Table Statistics

   • Table Storage Sizes

5. To filter a report, enter search criteria in the field provided and click Go.

6. To view reports for a different schema, select another schema from the Schema list.

5.4.2 Viewing PL/SQL Reports

Use the PL/SQL reports to view program unit arguments or unit line counts and also to search PL/SQL source code.

• Viewing Package Input and Output Parameters

• Viewing the Number of Lines of Code for an Object

• Searching PL/SQL Source Code

5.4.3 Viewing Exception Reports

Use the Exception Reports to view unindexed foreign keys and tables without primary keys, indexes, or triggers.

To view Exception reports:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Object Reports.

   The Object Reports page appears.

4. In the Exception Reports section, click the report you want to view.

   • Tables without Primary Keys

   • Tables without Indexes

   • Unindexed Foreign Keys

   • Tables without Triggers

5. To filter the report, enter a table name and click Go.

6. To view reports for a different schema, select another schema from the Schema list.

5.4.4 Viewing Security Reports

Use the Security reports to view object or column privileges granted on database objects owned by other schemas. You can also use these reports to view database role and system privileges.

To view the Security reports:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Object Reports.

   The Object Reports page appears.

4. Under Security Reports, click the report you want to view:

   • Object Grants - View the privileges for an existing schema and also understand what privileges have been granted from the selected schema to other schemas.

   • Column Privileges - View the privileges for columns within your schema and also understand what privileges have been granted from the selected schema to other schemas.

   • Role Privileges - View the database roles that have been granted to a selected schema. Roles are collections of various privileges.

   • System Privileges - View the database privileges that have been granted to a selected schema.

5. To filter a report, enter search criteria in the field provided and click Go.

6. To view reports for a different schema, select another schema from the Schema list.

5.4.5 Viewing All Object Reports

Use the All Object reports to view objects for the selected schema.

To view the All Object reports:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Object Reports.

   The Object Reports page appears.

4. Under All Object Reports, select a report to view:

   • All Objects - List all objects in the current schema. Displays creation date and when the last DDL (data definition language) was performed.

   • Invalid Objects - View all invalid objects in the database by object type.

   • Object Creation Calendar - View all objects in a calendar format based on the date each database object was created.

   • Object Counts by Type - View the number of database objects by type for the selected schema.

   • Data Dictionary - View the data dictionary for this database.

     An Oracle data dictionary is a set of tables and views used as a read-only reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database.

     A data dictionary also stores information about valid Oracle database users, integrity constraints for tables in the database, and the amount of space allocated for a schema object and the amount being used.

5. To filter a report, enter search criteria in the field provided and click Go.

6. To view reports for a different schema, select another schema from the Schema list.

5.6.1 Query Builder Home Page

The Query Builder page is divided into three sections:

• Object Selection pane displays on the left side of the page and contains a list objects from which you can build queries. Only objects in the current schema display.

• Design pane displays to the right of the Object Selection pane and above the Conditions, SQL, Results, and Saved SQL tabs. When you select an object from the Object Selection pane, it appears in the Design pane.

• Output pane displays below the Design pane. Once you select objects and columns, you can create conditions, view the generated SQL, or view query results.

5.6.2 Accessing Query Builder

To access Query Builder:

1. On the Workspace home page, click SQL Workshop.

2. Click Utilities.

3. Click Query Builder.

The Query Builder home page appears.

5.6.3 Understanding the Query Building Process

To build a query in Query Builder, you perform the following steps:

1. Select objects from the Object Selection pane. See "Using the Object Selection Pane".

2. Add objects to the Design pane and select columns. See "Selecting Objects".

3. Optional: Establish relationships between objects. See "Creating Relationships Between Objects".

4. Optional: Create query conditions. See "Specifying Query Conditions".

5. Execute the query and view results. See "Viewing Query Results".

5.6.4 Using the Object Selection Pane

This section describes how to use the Object Selection pane.

• Searching and Filtering Objects

• Hiding and Restoring the Object Selection Pane

• Selecting Objects

5.6.5 Using the Design Pane

This section describes how to use the Design pane.

• About Supported Column Types

• Adding an Object to the Design Pane

• Resizing the Design and Results Panes

• Removing or Hiding Objects in the Design Pane

5.6.5.4 Removing or Hiding Objects in the Design Pane

You remove or hide objects in the Design pane by selecting controls at the top of the object. To remove an object, select the Remove icon in the upper right corner. To temporarily hide the columns within an object, click the Show/Hide Columns icon.

Description of the illustration query_builder_remove.png

5.6.6 Specifying Query Conditions

Conditions enable you to filter and identify the data you want to work with. As you select columns within an object, you can specify conditions on the Conditions tab. You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions.

To specify query conditions:

1. On the Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane.

3. Select the columns to be included in your query by clicking the box to the left of the column name.

   When you select a column, you are indicating you want to include it in your query. As you select each column, it appears as a separate row in the Conditions view. Table 5-3 describes the attributes available on the Conditions tab.

   Table 5-3 Conditions Tab

   Condition Attribute	Description
   Up and Down Arrows	Controls the order that the columns to be displayed in the resulting query. Click the arrow buttons to move columns up and down. See Also: "Viewing Query Results"
   Column	Displays the column name.
   Alias	Specify an optional column alias. An alias is an alternative column name. Aliases make a column name more descriptive, shorten the column name, or prevent possible ambiguous references.
   Object	Specifies table or view name.
   Condition	Specify a condition for the column. The condition you enter modifies the query's WHERE clause. When specifying a column condition, you must include the appropriate operator and operand. Consider the following examples: >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15
   Sort Type	Select a sort type. Options include: Ascending (Asc) Descending (Desc)
   Sort Order	Enter a number (1, 2, 3, and so on) to specify the order in which selected columns should display.
   Show	Select this check box to include the column in your query results. It is not necessary to select Show to add a column to the query for filtering only. For example, suppose you want to create following query: SELECT ename FROM emp WHERE deptno = 10 To create this query in Query Builder: From the Object list, select emp. In the Design Pane, select ename and deptno. For the deptno column, in Condition enter =10 and deselect the Show check box.
   Function	Select an argument function. Available functions are dependent on the column type and include: NUMBER columns - COUNT, COUNT DISTINCT, AVG, MAX, MIN, SUM VARCHAR2, CHAR columns - COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER DATE, TIMESTAMP columns - COUNT, COUNT DISTINCT, TO_CHAR YEAR, TO_CHAR QUARTER, TO_CHAR MONTH, TO_CHAR DAY, TO_CHAR DAY OF YEAR, TO_CHAR WEEK
   Group By	Specify columns to be used for grouping when an aggregate function is used. Only applicable for columns included in output.
   Delete	Deselect the column, excluding it from the query.

   
   

   As you select columns and define conditions, Query Builder writes the SQL for you.

4. To view the underlying SQL, click the SQL tab.

5.6.7 Creating Relationships Between Objects

You can create relationships between objects by creating a join. A join identifies a relationship between two or more tables, views, or materialized views.

• About Join Conditions

• Joining Objects Manually

• Joining Objects Automatically

5.6.8 Working with Saved Queries

As you create queries, you can save them by clicking the Save button in the Design pane. Once you save a query, you can access it later in the Saved SQL view.

• Saving a Query

• Editing a Saved Query

• Deleting a Saved Query

5.6.9 Viewing Generated SQL

The SQL view presents a read-only, formatted representation of the SQL generated by Query Builder. You can copy the SQL code that appears in the SQL View for use in other tools such as SQL Command Processor or Application Builder.

To access the SQL view:

1. On the Workspace home page, click SQL Workshop and then Query Builder.

   Query Builder appears.

2. Select an object from the Object Selection pane.

   The selected object appears in the Design Pane.

3. Select the columns to be included in your query.

4. Click the SQL tab.

   The SQL code generated by Query Builder appears.

5.6.10 Viewing Query Results

Once you select objects and determine what columns to include in your query, you execute a query by:

• Clicking the Run button (or pressing Ctrl+Enter)

• Selecting the Results tab

The Results view appears, displaying formatted query results. To export the report as a comma-delimited file (.csv) file, click the Download link at the bottom of the page.

5.7.1 About User Interface Defaults

User Interface Defaults enable you to assign default user interface properties for regions and items. This wizard enables you to specify whether you want to use User Interface Defaults if they exist. When you create a form or report using a wizard, the wizard uses this information to create default values for region and item properties. Utilizing user interface defaults can save valuable development time and has the added benefit of providing consistency across multiple pages in an application. User Interface Defaults are divided into two categories, the Table Dictionary and the Attribute Dictionary.

• The Table Dictionary enables you to specify defaults for tables and columns that are initialized from the database definition.

• The Attribute Dictionary enables you to create defaults based on attribute or column names, thus being usable across all tables. Attribute definitions can also have synonyms, allowing more than one attribute to share a common definition.

When you use User Interface Defaults during creation of pages and regions, the Table Dictionary takes priority over the Attribute Dictionary. If a table and column combination exists, that is used rather than an attribute definition of the same name. This can be useful, for example, when you want to have a specific label or Help text for the CREATED_BY column in the emp table but then use more generic defaults for CREATED_BY in another table.

5.7.2 Creating User Interface Defaults for a Table

If no defaults have been created for a table, use the Table Dictionary Create Defaults wizard to automatically generate defaults based on the database table and column definitions. After the initial defaults are created, you can modify the individual default values.

To create the initial user interface defaults for tables:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click User Interface Defaults.

   The Dashboard page for User Interface Defaults appears.

4. Click Table Dictionary.

   The Table Dictionary page displays a report of all objects in your workspace that includes a Defaults Exist column indicating whether defaults have been created for each object.

5. Click the object name for which you want to create defaults.

   The Create Table Dictionary Defaults page appears.

6. Click Create Defaults.

   The Table Dictionary page displays a report showing that defaults exist for the object you selected.

   Note:

   Synchronizing defaults with the database data dictionary creates table based defaults for any tables in the current schema that do not have defaults yet and adjusts those of tables that do to match the database definition.

5.7.3 Modifying Table User Interface Defaults

To modify existing table defaults:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click User Interface Defaults.

   The Dashboard page for User Interface Defaults appears.

4. Click Table Dictionary.

   The Table Dictionary page displays a report of all objects in your workspace. The Defaults Exist column indicates whether defaults have been created for an object.

5. Click the object name you want to modify.

   The Table and Column Properties page displays a report of column defaults.

6. Click the column name you want to modify.

   The Column Defaults page displays.

7. Make modifications and click Apply Changes.

   See field-level help for a description of the individual settings on this page.

   Note:

   A column can be removed from the Table Dictionary, thus allowing the Attribute Dictionary defaults to be used during the creation process. For example, when you have auditing columns where you may want the exact same labels and help across every instance of CREATED_BY and CREATED_ON, regardless of which table they come from, you could simply remove the column defaults from each table for those columns.

5.7.4 Creating User Interface Attributes

Use the Attribute Dictionary Create wizard to define and add new attributes.

To create an attribute:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click User Interface Defaults.

   The Dashboard page for User Interface Defaults appears.

4. Click Attribute Dictionary.

   The Attribute Dictionary page displays a report of all attributes in your workspace.

5. Click Create.

   The Column Details page appears.

6. Enter attribute specifications and click Create.

   See field-level help for a description of the individual settings on this page.

5.7.5 Modifying User Interface Attributes

To modify an existing attribute:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click User Interface Defaults.

   The Dashboard page for User Interface Defaults appears.

   The current schema displays to the right of the breadcrumb menu.

4. Click Attribute Dictionary.

   The Attribute Dictionary page displays a report of all attributes in your workspace.

5. To edit select attributes for all displayed columns:

   1. Click Grid Edit.

   2. Edit the appropriate attributes and click Apply Changes.

6. To edit a specific column, click the edit icon for the attribute you want to modify.

   The Column Details page displays.

   Note:

   If you select to edit a synonym, identified by having the Synonym of column populated, you are taken to the definition for the base column, because the base column and all synonyms share one definition. To delete the synonym, select the synonym within the Synonyms region. Deleting the base column deletes the column and all synonyms.

7. Make modifications and click Apply Changes.

   See field-level help for a description of the individual settings on this page.

5.7.6 Exporting and Importing User Interface Defaults

You export user interface defaults in the same way you export any related application file. Exporting user interface defaults from one development instance to another involves the following steps:

1. Export the user interface defaults using the Export User Interface Defaults utility. See "Exporting User Interface Defaults" in Oracle Application Express Application Builder User's Guide.

2. Import the exported file into the target Oracle Application Express instance. See "Importing User Interface Defaults" in Oracle Application Express Application Builder User's Guide.

3. Install the exported file from Export Repository. See "Installing Export Files" in Oracle Application Express Application Builder User's Guide.

When you export user interface defaults, all user interface defaults for the selected schema are exported to a single script. The file contains an API call to create table hints by making calls to the application PL/SQL API. You can use this file to import user interface defaults to another database and Oracle Application Express instance.

5.9.1 Accessing Session Page Reports

A session is the connection of a user to an Oracle database instance. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

• Accessing Reports on the Session Page

• Creating Sessions Reports

• Controlling the Number of Rows for Locks Reports

• Creating Waits Reports

• Creating I/O Reports

• Creating SQL Reports

• Creating Open Cursors Report

5.9.2 System Statistics

The System Statistics page displays statistics for:

• Physical I/O. A physical I/O is an I/O that requires disk access. This report displays disk access statistics for physical reads and writes.

• Logical I/O. A logical I/O is an I/O that is satisfied in memory or disk. Displays the sum of buffer reads which might be consistent gets or current mode gets. Redo is the buffer in the SGA that contains information about changes.

• Memory Statistics. Displays memory consumption of the database.

• Time Statistics. Shows various times consumed by the database.

• SQL Cursor Statistics. Displays statistics about the cursors in the Oracle database.

• Transaction Statistics. Shows the number of transactions performed.

5.9.3 Viewing System Statistics

To view the System Statistics page:

1. On the Workspace home page, click SQL Workshop.

2. Click Utilities.

3. Click Database Monitor.

4. Click System Statistics.

5. If prompted, enter the appropriate administrator user name and password and click Login.

   The System Statistics page appears.

Additional controls on the System Statistics page include:

• Refresh Report - Refreshes the System Statistics report.

• Save Statistics - Saves the current report.

• Show delta between current and saved values - Click this check box to display actual statistic values, or display deltas between a saved value and the current value.

  See Also:

  Oracle Database Performance Tuning Guide

5.9.4 About Top SQL Page

The top SQL statements represent the SQL statements that are executed most often, that use more system resources than other SQL statements, or that use system resources more frequently than other SQL statements. Use the Top SQL page to identify poorly performing SQL.

5.9.5 Viewing Top SQL Page

Use the Top SQL page to identify poorly performing SQL.

To view the Top SQL page:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Database Monitor.

4. Click Top SQL.

5. If prompted, enter the appropriate administrator user name and password and click Login.

   The Top SQL page appears. Use the search fields and lists and the top of the page and click Go to narrow the display. For details on each field or list, click the label.

6. To access the SQL Plan page, click the View icon.

   The SQL Plan page appears, containing the following sections:

   • Query Plan - Contains a color coded explain plan. Note that unindexed columns display in red.

   • SQL Text - Displays the full text of the SQL statement.

   • Indexes - Displays all indexes on the table in the query. There is a checkmark when that index is used in the query.

   • Table Columns - Shows all columns on all tables or views in the query.

5.9.6 About Long Operations Page

The Long Operations page displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

5.9.7 Viewing the Long Operations Page

To view the Long Operations page:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Database Monitor.

4. Click Long Operations.

5. If prompted, enter the appropriate administrator user name and password and click Login.

6. See Also:

   "V$SESSION_LONGOPS" in Oracle Database Reference

5.10.1 Managing Objects in the Recycle Bin

You can view objects in the Recycle Bin on the Dropped Objects page. Once you select an object and view the Object Details page, you can choose to purge the object or restore the object by clicking the appropriate button.

To view objects in the Recycle Bin:

1. On the Workspace home page, click the SQL Workshop.

2. Click Utilities.

3. Click Recycle Bin.

4. Click Dropped Objects.

   The Dropped Objects page appears.

5. To filter the report, select an object type, enter the object name in the Original Name field, and click Go.

6. To view object details, click the object name.

   The Object Details page appears.

7. To restore the current object, click Restore Object.

8. To permanently delete the current object, click Purge Object.

5.10.2 Emptying the Recycle Bin Without Viewing the Objects

To empty the Recycle Bin without viewing the objects:

1. On the Workspace home page, click SQL Workshop.

2. Click Utilities.

3. Click Recycle Bin.

4. Click Purge Recycle Bin.

   The Purge Recycle Bin page appears.

5. Confirm your request by clicking Purge Recycle Bin again.