Skip Headers
Oracle® Application Express SQL Workshop Guide
Release 4.2 for Oracle Database 12c

E17964-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Using Oracle Application Express Utilities

This section describes how to use Oracle Application Express 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.

Topics:

4.1 Building SQL Queries with Query Builder

Query Builder's graphical user interface enables database developers to build SQL queries without the need for manual SQL coding. Using Query Builder, you can search and filter database objects, select objects and columns, create relationships between objects, view formatted query results, and save queries with little or no SQL knowledge.

Topics:

4.1.1 About Query Builder

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.

Description of query_builder.gif follows
Description of the illustration query_builder.gif

Selecting a Schema

A schema is a logical container for database objects. To access objects in another schema, make a selection from the Schema list in the SQL Workshop home page. Only schemas assigned to your workspace are listed.

Switching to Another SQL Workshop Component

You can navigate to another SQL Workshop component by making a selection from the Component list located on the upper right side of the page.

Description of obj_b_icons.gif follows
Description of the illustration obj_b_icons.gif

Available icons include:

Topics:

4.1.1.1 Accessing Query Builder

To access Query Builder:

  1. Log in to the Workspace home page.

  2. Click SQL Workshop.

  3. Click Utilities.

  4. Click Query Builder.

The Query Builder home page appears.

4.1.1.2 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".

4.1.2 Using the Object Selection Pane

The Object Selection pane displays on the left side of the Query Builder page and lists tables, views, and materialized views within the current schema.

Topics:

4.1.2.1 Searching and Filtering Objects

Use the Object Selection pane to search for and view tables, views, and materialized views within the current schema.

To search or filter objects:

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

    Query Builder appears.

  2. In the search field at the top of the pane, enter a case insensitive query.

  3. To view all tables or views within the currently selected schema, leave the search field blank.

Description of q_b_search.gif follows
Description of the illustration q_b_search.gif

4.1.2.2 Hiding the Object Selection Pane

You can hide the Object Selection pane by selecting the Hide Table or Views control. By hiding the Object Selection pane, you can increase the size of the Design and Result panes.

The Hide Table or Views control displays on the right side of the Object Selection pane. If the Object list appears, selecting this control hides it. Similarly, if the Object list is hidden, selecting this control causes the pane to reappear.

4.1.3 Selecting Objects

The Design pane displays to the right of the Object Selection pane. When you select an object from the Object Selection pane, it appears in the Design pane. You use the Object Selection pane to select objects (that is, tables, views, and materialized views) and the Design pane to identify how those selected objects are used in a query.

Topics:

4.1.3.1 About Supported Column Types

Columns of all types available in Oracle Database 10g Release (10.2) or later display as objects in the Design pane. Note the following column restrictions:

  • You may only select a maximum of 60 columns for each query.

  • The following column types are not selectable and cannot be included in a generated query:

    • BLOB

    • NCLOB

    • RAW

    • LONG

    • LONG RAW

    • XMLType

    • Any other nonscalar column types

4.1.3.2 Adding an Object to the Design Pane

You add an object to the Design pane by selecting it from the Object Selection pane.

To add an object to the Design pane:

  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. Note that a graphical representation of the datatype displays to the right of the column name.

    Description of q_b_add.gif follows
    Description of the illustration q_b_add.gif

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

    When you select a column you are indicating it is included in the query. As you select a column, it appears on the Conditions tab. Note that the Show check box on the Conditions tab controls whether a column is included in query results. Be default, this check box is selected.

    To select the first twenty columns, click the Table Actions icon in the upper left corner of the object. The Actions window appears. Select Check All.

  4. To execute the query and view results, click Run (CTRL + ENTER).

    The Results pane displays the query results.

4.1.3.2.1 Resizing the Design and Results Panes

As you select objects, you can resize Design and Results panes by selecting the gray horizontal rule in the center of the page. Moving the rule up, shrinks the Design pane. Moving the rule down expands the Design pane.

4.1.3.3 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 q_b_remove.gif follows
Description of the illustration q_b_remove.gif

4.1.4 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 4-1 describes the attributes available on the Conditions tab.

    Table 4-1 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:

    1. From the Object list, select emp.

    2. In the Design Pane, select ename and deptno.

    3. For the deptno column, in Condition enter =10 and uncheck 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.

4.1.5 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.

Topics:

4.1.5.1 About Join Conditions

When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition. A join condition determines how the rows from one object combine with the rows from another object.

Query Builder supports inner, outer, left, and right joins. An inner join (also called a simple join) returns the rows that satisfy the join condition. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

See Also:

Oracle Database SQL Language Reference for information about join conditions

4.1.5.2 Joining Objects Manually

You can create a join manually by selecting the Join column in the Design pane.

To join two objects manually:

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

    Query Builder appears.

  2. From the Object Selection pane, select the objects you want to join.

    The objects display in the Design pane.

  3. Identify the columns you want to join.

    You create a join by selecting the Join column adjacent to the column name. The Join column displays to the right of the datatype, beneath the Remove icon. When your cursor is in the appropriate position, the following help tip displays:

    Click here to select column for join
    
  4. Select the appropriate Join column for the first object.

    When selected, the Join column displays as a dark gray. To deselect a Join column, simply select it again or press ESC.

  5. Select the appropriate Join column for the second object.

    Tip:

    You can also join two objects by dragging and dropping. Select a column in the first table and then drag and drop it onto a column in another table.
    Description of q_b_join.gif follows
    Description of the illustration q_b_join.gif

    When joined, a line connects the two columns.

  6. Select the columns to be included in your query. You can view the SQL statement resulting from the join by positioning the cursor over the line.

  7. Click Run to execute the query.

    The Results pane displays the query results.

4.1.5.3 Joining Objects Automatically

When you join objects automatically, the Query Builder suggests logical, existing parent and child relationships between existing columns.

To join objects automatically:

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

    Query Builder appears.

  2. From the Object Selection pane, select an object.

    The object displays in the Design pane.

  3. Click the small icon in the upper left corner of the object. Depending upon the selected object, the icon label displays as Table Actions or View Actions.

    The Actions window appears. Use the Actions window to select all columns within the current object or objects related to the current object.

  4. In the Actions window, select the appropriate options:

    • Check All - Select this option to select the first twenty columns in the current object.

    • Add Parent - Displays tables that are referenced as a foreign key to the current object.

    • Add Child - Displays tables that reference the current object in a foreign key.

    If using Add Parent or Add child, the selected object appears and a line connects the foreign key columns.

  5. Select additional columns to be included in your query.

    You can view the SQL statement resulting from the join by positioning the cursor over the green line.

  6. Click Run to execute the query.

    The Results pane displays the query results.

4.1.6 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.

Topics:

4.1.6.1 Saving a Query

To save a query:

  1. Build a query:

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

      Query Builder appears.

    2. Select objects from the Object Selection pane.

    3. Add objects to the Design pane and select columns.

    4. Execute the query.

  2. Click Save.

  3. Enter a name and description and click Save.

    The saved query displays in the Saved SQL view.

Description of q_b_save.gif follows
Description of the illustration q_b_save.gif

Note that Query Builder does not support duplicate query names. If you open an existing query, keep the existing name, and save it again, Query Builder over-writes the existing query. If you change the name of an existing query and save it again, Query Builder saves the query again under the new name.

4.1.6.2 Editing a Saved Query

Once you save a query, you can access it in the Saved SQL view.

To edit a Saved SQL query:

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

    Query Builder appears.

  2. Select the Saved SQL tab.

  3. To filter the display, you can:

    • Make a selection from the Owner list and click Go.

    • Enter a search query in the Name field and click Go.

  4. To edit a query, select the appropriate name.

    The saved query appears. The selected objects display in the Design pane and the Conditions view appears.

4.1.6.3 Deleting a Saved Query

To delete a Saved SQL query:

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

    Query Builder appears.

  2. Select the Saved SQL tab.

  3. Select the queries to be deleted and click Delete Checked.

4.1.7 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.

4.1.8 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.

Description of qry_bldr_results.gif follows
Description of the illustration qry_bldr_results.gif

4.2 Using the Data Workshop to Manage Data

The Data Load/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:

Limitations include the following:

Supported unload formats include:

Topics:

4.2.1 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.

Term Definition
Exporting Copying database data to external files for import into another Oracle database only. The files are in a proprietary binary format.
Importing Copying data into the database from external files that were created by exporting from another Oracle database.
Unloading Copying database data to external text files for consumption by another Oracle database or another application (such as a spreadsheet application). The text files are in an industry-standard format such as tab-delimited or comma-delimited (CSV).
Loading Copying data into the database from external text files that are in either a standard delimited format or in any of the formats that are supported by the Oracle SQL*Loader utility.

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

Topics:

4.2.1.1 Choosing the Right Import/Export/Load/Unload Option

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

Table 4-2 Summary of Oracle Application Express Import/Export Options

Feature or Utility Description

Data Load/Unload wizards in Oracle Application Express

  • Easy to use graphical interface

  • Loads/unloads from and to external text files (delimited fields) or XML files

  • Loads/unloads tables only, one table at a time

  • Access only to schema of logged-in user

  • No data filtering on upload

SQL*Loader utility

  • Command-line interface, invoked with sqlldr command

  • Bulk-loads data into the database from external files

  • Supports numerous input formats, including delimited, fixed record, variable record, and stream

  • Loads multiple tables simultaneously

  • Powerful data filtering capabilities

Data Pump Export and Data Pump Import utilities

  • Command-line interface, invoked with expdp and impdp commands

  • Exports and imports from one Oracle database to another (proprietary binary format)

  • Imports/exports all schema object types

  • Imports/exports entire database, entire schema, multiple schemas, multiple tablespaces, or multiple tables

  • Powerful data filtering capabilities

  • High speed

Export and Import utilities

  • Command-line interface, invoked with exp and imp commands

  • Exports and imports from one Oracle database to another (proprietary binary format)

  • Supports XMLType data

  • Does not support the FLOAT and DOUBLE data types

  • Capabilities similar to Data Pump; Data Pump is preferred


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

Table 4-3 Import/Export Scenarios and Recommended Options

Import/Export Scenario Recommended Option

You have fewer than 10 tables to load, the data is in spreadsheets or tab-delimited or comma-delimited text files, and there are no complex data types (such as objects or multivalued fields).

Data Load/Unload wizards in Oracle Application Express

You have to load data that is not delimited. The records are fixed length, and field definitions depend on column positions.

SQL*Loader

You have tab-delimited text data to load, and there are more than 10 tables.

SQL*Loader

You have text data to load, and you want to load only records that meet certain selection criteria (for example, only records for employees in department number 3001).

SQL*Loader

You want to import or export an entire schema from or to another Oracle database. There is no XMLType data in any of the data.

Data Pump Export and Data Pump Import

You want to import or export data from or to another Oracle database. The data contains XMLType data and contains no FLOAT or DOUBLE data types.

Import (imp) and Export (exp)


See Also:

Oracle Database Utilities for more information on Data Pump, the Import and Export utilities, and SQL*Loader

4.2.2 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.

4.2.3 Loading Data

You can load data into the Oracle Application Express database in the following ways:

  • Copy and paste data from a spreadsheet.

  • Upload a spreadsheet file in a delimited format (such as comma-delimited (.csv) or tab-delimited).

  • Upload a text file containing comma-delimited or tab-delimited data.

  • Upload an XML document.

Topics:

4.2.3.1 Loading a Text File or Spreadsheet Data

You can upload a Text file or copy and paste tab-delimited data directly into the Load Data Wizard.

To load data from a text file:

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

  2. Click Utilities.

  3. Click Data Workshop.

  4. Under Data Load, click Text Data.

    The Load Data Wizard appears.

  5. Under Load To, select either Existing table or New table.

  6. Under Load From, select either Upload file or Copy and paste.

    • Upload file - Select to specify an existing text file to upload.

    • Copy and paste - Select to create a text file to upload by performing a copy and paste.

  7. Click Next.

  8. If you selected Upload file, under Load Data, make the following changes:

    • Text File - Browse for or enter the name of the Text File to load.

    • Separator - Enter the column separator character. See Item Help for more details.

    • Optionally Enclosed By - If your data contains a delimiter character, enter it here. See Item Help for more details.

    • First row contains column names - Check this box if the first row of your data contains column names.

    • File Character Set - Select the character set in which your text file is encoded. See Item Help for more details.

  9. If you selected Copy and paste, under Data make these changes:

    • Data - Enter the text file to upload.

    • First row contains column names - Check this box if the first row of your data contains column names.

  10. Under Globalization, make the following changes:

    • Currency Symbol - If your data contains an international currency symbol, enter it here. See Item Help for more details.

    • Group Separator - If your data contains a character that separates integer groups, for example to show thousands and millions, enter it here. See item Help for more details.

    • Decimal Character - If your data contains a character that separates the integer and decimal parts of a number, enter it here. See item Help for more details.

  11. Click Next.

    The table Properties page appears. Before performing the data upload, this page allows you to optionally customize the data.

  12. For Table Properties, make the following changes and click Next:

    • Schema - Select the schema containing the table to load.

    • Table Name - Identify the table name you want to create. By default, all table names are converted to upper case. Select Preserve Case to override this default behavior.

    • Column Names - Enter the names of the columns.

    • Data Type - Select the data type for this column.

    • Column Length - Enter the length of the data in this column.

    • Upload - Select Yes to upload data for this column. Select No to not upload data for this column and skip to the next column.

  13. For Primary Key, make the following changes:

    • Primary Key From - Define the primary key column by choosing either an existing column or creating a new column. Options include:

      • Use an existing column - If you choose an existing column you must select the column from the columns being loaded.

      • Create new column - If you choose to create a new column, you must specify the name of the new column.

    • Constraint Name - The constraint name defaults to the table name appended with _PK. You can update the name if desired.

    • Primary Key Population - You must decide how your primary key is populated. You either generate it from a new sequence, from a new sequence or not at all. Options include:

      • Generated from a new sequence - If you choose a new sequence, you can use the sequence name given or update it as necessary. The sequence is created for you.

      • Generated from an existing sequence - If you choose an existing sequence, you must select the sequence from those currently defined in your schema.

        If you generate the primary key from a sequence, either new or existing, the wizard creates a trigger on the table to retrieve the next sequence value and populate the primary key when a record is inserted.

      • Not generated

  14. Click Load Data.

4.2.3.2 Loading an XML Document

Oracle Application Express supports XML documents in Oracle's canonical XML format.

In Oracle's canonical XML format, each element represents a column value, each element is named after the column, all elements that are part of the same row are children of a <ROW> element, and all <ROW> elements are children of a <ROWSET> element.

To load an XML document:

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

  2. Click Utilities.

  3. Click Data Workshop.

  4. Click XML Data.

    The Load XML Data Wizard appears.

  5. Select the appropriate schema for your data and click Next.

  6. Select the table to load the data into and click Next.

  7. Follow the on-screen instructions.

4.2.4 Unloading Data

You can use the Unload page to export the contents of a table to a text file or XML document.

Topics:

4.2.4.1 Unloading a Text File

Use the Unload to Text Wizard to export the contents of a table to a text file. For example, you could export an entire table to a comma-delimited file (.csv).

To unload a table to a text file:

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

  2. Click Utilities.

  3. Click Data Workshop.

  4. Under Data Unload, click To Text.

    The Unload to Text Wizard appears.

  5. Select the appropriate schema and click Next.

  6. Select the table to unload and click Next.

  7. Highlight the columns to include, optionally enter a Where Clause to limit the records unloaded and click Next.

  8. For Options, enter the following:

    • Separator - Enter the type of separator used to separate column values.

    • Optionally Enclosed By - If your data contains a delimiter character, enter it here. See item Help for more details.

    • Include Column Names - Check this box to include the name of each column in the text file. See item Help for more details.

    • File Format - Select the appropriate file format. See item Help for more details.

    • File Character Set - Select the character set in which your text file is encoded. See item Help for more details.

  9. Click Unload Data.

    The File Download window appears.

  10. Click Save to download the file.

4.2.4.2 Unloading to an XML Document

Use the Unload to XML Wizard to export the contents of a table to an XML document adhering to the Canonical XML specification.

To unload a table to an XML document:

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

  2. Click Data Workshop.

  3. Under data Unload, click to XML.

    The Unload to XML Wizard appears.

  4. Select the appropriate schema and click Next.

  5. Select the table to unload and click Next.

  6. Highlight the columns to include.

  7. Check Export as File to save the output directly to a file.

    If you do not check Export as File, the XML document is displayed in your browser and can be saved to a file or cut and pasted as necessary.

  8. Optionally enter a Where Clause to limit the records unloaded, and click Unload Data.

    A browser displays the XML data.

4.2.5 Using 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. Make one of the following selections:

    • 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.

      By clicking the details magnifying glass you can review the failed rows.

    • 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.

      By clicking the details magnifying glass you can review the failed rows.

  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.

4.3 Generating DDL

With Oracle Application Express, you can generate data definition language (DDL) statements from the Oracle data dictionary. You can use these scripts to create or re-create database schema objects. The scripts can be generated to display inline or saved as a script file. You can generate the create scripts for all objects for a specific schema, specific object types, or specific objects.

To generate a DDL statement:

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

  2. Click Utilities.

  3. Click Generate DDL.

    The Generate DDL page appears.

  4. Click Create Script.

    The Generate DDL Wizard appears.

  5. Select a database schema and click Next.

  6. Define the object type:

    1. Output - Specify an output format. Select either Display Inline or Save As Script File.

    2. Check All - Select this option to include all object types for which to generate DDL.

    3. Object Type - Select the object types for which to generate DDL.

    4. To select object names for the selected object types, click Next and follow the on-screen instructions.

  7. Click Generate DDL.

See Also:

4.4 Managing Methods on Tables

The Managing Methods on Tables utility enables you to create an application programming interface, or package, based on a specified table.

To create a package:

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

  2. Click Utilities.

  3. Click Methods on Tables.

    The Create Table API page appears.

  4. Enter a Package name. To have the final package name match the case entered in the Package Name field, click Preserve Case and click Next.

  5. Select the table(s) for which you want to generate the PL/SQL package-based application program interface (API) and click Next.

  6. A confirmation page appears, which displays the subprogram(s) and their description. You have the option to show or download the specification or body. To confirm, click Create Package.

4.5 Managing 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.

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.

Topics:

4.5.1 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. See "Modifying Table User Interface Defaults".

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.

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

  4. Click Manage 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 you want to create defaults for.

    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.

4.5.2 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.

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

  4. Click Manage 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 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 Item 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.

4.5.3 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.

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

  4. Click Manage 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 Item Help for a description of the individual settings on this page.

4.5.4 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 Manage 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 Item Help for a description of the individual settings on this page.

4.5.5 About 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.

4.6 Viewing Database Details

You can view details about your database on the About Database page.

To access details about your database:

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

  2. Click Utilities.

  3. Under Additional Utilities, click About Database.

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

    The About Database page appears.

  5. To view additional information about installed options, currently used features, or National Language Support, select one of the following check boxes and click Go:

    • Version

    • Settings

    • Options

    • National Language Support

    • CGI Environment

    • Parameters

4.7 Comparing Schemas

You can run a report that compares database objects in two schemas, displaying differences between them. You can compare all objects in the schemas or limit your report to specific objects. To compare two schemas, both must be available to your workspace.

Examples:

To compare schemas:

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

  2. Click Utilities.

  3. Under additional Utilities, click Schema Comparison.

  4. On the Schema Comparison page, make the appropriate selections to run the comparison:

    • Schema 1 and Schema 2 - Select the schemas to compare.

    • Compare - Restrict the report to show one object type or select All to show all database objects.

    • Search - Enter a case insensitive query for the object name.

    • Rows - To change the number of rows that appear in the report, make a selection from the Display list.

    • Compare - Click Go to find the results matching your selections.

    • Show Differences Only or Show Details - Select the type of information you want to review.

4.8 Viewing Object Reports

Utilities includes a variety of object reports to help you better manage the objects in your database.

Topics:

4.8.1 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. In the Table Reports section, select the report you want to view:

    • Table Columns

    • Table Comments

    • Table Constraints

    • Table Statistics

    • Table Storage Sizes

  5. To filter a report, enter search criteria in the fields provided or make selections from the lists, and click Go. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details.

  6. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

4.8.2 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. In the Security Reports section, 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 fields provided or make selections from the lists, and click Go. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details.

  6. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

4.8.3 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.

Topics:

4.8.3.1 Program Unit Arguments

Use the Program Unit Arguments report to view package input and output parameters.

To view the PL/SQL Unit Arguments report:

  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 PL/SQL Reports section, click Program Unit Arguments.

  5. To filter a report, enter search criteria in the fields provided or make selections from the lists, and click Go. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details.

  6. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

4.8.3.2 Unit Line Counts

Use the Unit Line Counts report to view the number of lines of code for each object. Use this report to identify larger PL/SQL program units.

To view the Unit Line Counts report:

  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 PL/SQL Reports section, click Unit Line Counts.

  5. To filter the report, enter an object name and click Go. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details.

  6. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

4.8.3.3 Search PL/SQL Source Code

Use the Search PL/SQL Source code page to search the text within your PL/SQL code. Use this report to find references to tables or functions you might be thinking of deleting. You can also use this page to locate code when you can only recall a code snippet.

To search for PL/SQL source code:

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

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the PL/SQL Reports section, click Search PL/SQL Source Code.

  4. To filter the report:

    1. In From/To Line, enter the range of lines you want to search.

    2. Click Set.

    3. To filter the report, enter an object name and click Go. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details

  5. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

4.8.4 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. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details.

  6. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

4.8.5 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. In the All Object Reports section, select the report you want 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. For All reports, you can filter the report:

    1. Select an object type.

    2. Enter an object name.

    3. Click Go. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details

  6. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

See Also:

Oracle Database Concepts for information about the data dictionary

4.9 Monitoring the Database

The reports available on the Database Monitor page provide a database-wide view of the database sessions, system statistics, SQL statements, and longer operations. You can use these reports to identify poorly performing SQL and to better understand the workload of the database.

To access any of the icons on the Database Monitor page, you must have an account that has been granted a database administrator role.

Topics:

4.9.1 Sessions

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.

You must have database administrator privileges to access the Sessions page.

To access reports on the Sessions page:

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

  2. Click Utilities.

  3. Click Database Monitor.

  4. Click Sessions.

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

    The Sessions page appears.

  6. To view a report, select one of the following tabs at the top of the page:

    • Sessions

    • Locks

    • Waits

    • I/O

    • SQL

    • Open Cursors

    The sections that follow describe each report.

4.9.1.1 Sessions Report

The Sessions Report displays information about the current sessions in the database. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Show. Select how many columns to display and click Go.

  • Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, navigate to the Session Details page and click Kill Session.

4.9.1.2 Locks Report

The Locks report displays a report of sessions which have locks that are blocking other session(s). To control the number of rows that appear, make a selection from the Rows list and click Go.

4.9.1.3 Waits Report

The Waits report displays the wait events for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Show. Select how many columns to display and click Go.

  • Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

4.9.1.4 I/O Report

The I/0 report displays details about the I/O for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

4.9.1.5 SQL Report

The SQL report displays details about the current or last SQL statement executed for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Show. Select how many columns to display and click Go.

  • Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

4.9.1.6 Open Cursors

The Open Cursors report displays details about the number of open cursors for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Rows. Select the number of rows to appear in the report and click Go.

To view details about a specific open cursor count, click the numeric link under the Open Cursor Count column.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

4.9.2 About 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.

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.

4.9.3 About Top SQL

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.

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.

    Description of view_icon.gif follows
    Description of the illustration view_icon.gif

    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.

4.9.4 About Long Operations

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.

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

4.10 Using the Recycle Bin to View and Restore Dropped Objects

You can use the Recycle Bin to view and restore dropped database objects. When you drop a table, the space associated with the table is not immediately removed. The Oracle database renames the table and places it and any associated objects in the Recycle Bin. You can recover objects in the Recycle Bin at a later time.

Topics:

Note:

The Recycle Bin feature is only available if you run with an Oracle 10g or later database.

See Also:

"Performing Backup and Recovery" in Oracle Database 2 Day DBA

4.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. See Customizing Interactive Reports in the Oracle Application Express Application Builder User's Guide for further details.

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

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

    The Object Details page appears.

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

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

4.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.