4 Using SQL Scripts

This section provides information on how to use SQL Scripts to create, edit, view, run, and delete script files.

4.1 What is SQL Scripts?

A SQL script is a set of SQL commands saved as a file in SQL Scripts. A SQL script can contain one or more SQL statements or PL/SQL blocks. You can use SQL Scripts to create, edit, view, run, and delete database objects.

When using SQL Scripts, remember the following:

  • SQL*Plus commands in a SQL script are ignored at runtime.

  • There is no interaction between SQL Commands and SQL Scripts.

  • You can cut and paste a SQL command from the SQL Script editor to run it in SQL Commands.

  • SQL Scripts does not support bind variables.

See Also:

"Using SQL Scripts," "Using SQL Commands," and "About Long Operations Page"

4.2 Accessing SQL Scripts

To access SQL Scripts:

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

    Description of sql_script.png follows
    Description of the illustration sql_script.png

    The SQL Scripts page appears.

    Note:

    If the instance administrator has disabled RESTful Services for this Application Express instance, RESTful Services are not available for this instance and the RESTful Services icon does not display. See "Configuring SQL Workshop" in Oracle Application Express Administration Guide.

See Also:

"About the SQL Scripts Page," "Using SQL Commands," and "About Long Operations Page"

4.3 About the SQL Scripts Page

The SQL Scripts page displays an interactive report of all SQL scripts created by the current user. If you are a workspace administrator, this page displays all SQL scripts created by workspace users. You can alter the layout of report data by choosing the columns you are interested in, applying filters, highlighting, and sorting. You can also define breaks, aggregations, different charts, group bys, and add your own computations.

Description of sql_s_home.png follows
Description of the illustration sql_s_home.png

A search bar displays at the top of every interactive report and includes the following features:

  • Select Columns to Search - This icon resembles a magnifying glass. Click this icon to narrow your search to specific columns. To search all columns, select All Columns.

  • View Icons. Displays each script as an icon identified by the script name.

  • View Report. Displays each script as a line in a report. Each line includes a check box to enable the selection of scripts for deletion, an edit icon to enable the script to be loaded into the script editor, the script name, the script owner, when the script was last updated and by who, the size in bytes, the number of times the script has been run linked to the run results, and an icon to enable the script to be run.

    Reports view offers the following additional controls:

    • Delete Checked. In Reports view, select the check box associated with the script you want to delete and click Delete Checked. See "Deleting a SQL Script."

    • Sort. In Reports view, click a column heading to sort the listed scripts by that column.

  • Upload. Click Upload to upload a script from your local file system into SQL Scripts. See "Uploading a SQL Script."

  • Create. Click Create to create a script in the Script Editor. See "Creating a SQL Script."

See Also:

"Selecting a Schema" and "Customizing Interactive Reports" in the Oracle Application Express Application Builder User's Guide

4.4 About the Tasks List

A Tasks list displays on the right side of the SQL Scripts page.

The Task list contains the following links:

  • Manage Results enables you to view, search, and display results. See "Viewing SQL Script Results."

  • Show Quotas displays the Script Quotas page. The Script Quotas page shows the maximum size of a single result, the maximum size of all results, the quota used and the quota free. It also shows the maximum size of a SQL Script.

  • Export enables you to export multiple scripts from the current SQL Script Repository for import into SQL Scripts in a different workspace. The scripts you select to export are encoded in a single export script written to your local file system. The export script is named workspace_name_script.sql by default. See "Exporting and Importing SQL Scripts."

  • Import enables you to import a script exported by this workspace, or a different workspace. Import only imports scripts encoded in an export script created using Export. The export script to import must be accessible on your local file system. See "Exporting and Importing SQL Scripts."

See Also:

"Accessing SQL Scripts"

4.5 Creating a SQL Script

You can create a script in the Script Repository by:

4.5.1 Creating a SQL Script in the Script Editor

To create an SQL script in the Script Editor:

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

    The SQL Scripts page appears.

  2. Click the Create button.

    The Script Editor appears.

  3. In Script Name, enter a name for the script. Script name extensions are optional.

  4. Enter the SQL statements, PL/SQL blocks you want to include in your script.

    Remember that SQL*Plus commands are ignored at runtime.

  5. Click Create.

    The SQL Scripts page appears listing your newly saved script.

4.5.2 Uploading a SQL Script

To upload a script from your local file system:

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

    The SQL Scripts page appears.

  2. Click the Upload button.

    The Upload Script dialog appears.

  3. For Upload Script:

    1. File - Browse to locate the script you want to upload.

    2. Script Name - Optionally enter a new name in the Script Name field. This name displays in the Script Repository.

    3. File Character Set - Select the character set in which the script is encoded.

    4. Click Upload.

    The SQL Scripts page appears listing your newly uploaded script.

Tip:

The script is parsed during runtime. When parsed, ignored statements such as SQL*PLUS commands are listed. Any invalid SQL is identified only in results. If a script of the same name exists, you are prompted to rename it.

See Also:

"Creating a SQL Script in the Script Editor"

4.6 Using the Script Editor

You use the Script Editor to add content to a new script, to edit existing scripts, and to run and delete scripts in the script repository.

4.6.1 Editing an Existing Script

To edit a SQL script:

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

    The SQL Scripts page appears.

  2. You can load a script into the editor as follows:

    • In Icons view, click the script icon.

    • In Report view, click the Edit icon.

    The Script Editor appears.

  3. Edit the script.

    Note that new lines are automatically indented to the previous line start column. Other features of the Script Editor include:

    • Undo. Use to undo the last edit.

    • Redo. Use to reapply the last undone edit.

    • Find. Click the Find icon (which resembles a magnifying glass) to perform a basic search.

    • Replace. Click the Replace icon (which resembles a two-sided arrow) to replace existing code.

    • Cut, Copy, and Paste. Use standard edit controls to cut, copy and paste content in the Script Editor.

  4. Click Apply Changes to save your script.

    The SQL Scripts page appears.

You can test your script during editing by running the script to reveal errors.

See Also:

"Executing a SQL Script" and "Viewing SQL Script Results"

4.6.2 Script Editor Controls

Table 4-1describes the buttons and controls available within the Script Editor

Table 4-1 Buttons and Controls within the Script Editor

Button Descriptions

Cancel

Cancel the editing session and exit the Script Editor without saving changes made since the last save.

Download

Saves a copy of the current script to your local file system. Enter a name for the script on your local file system and a directory path.

Delete

Removes the current script from the Script Repository.

See Also: "Deleting a SQL Script"

Save

Saves the currently script. Save is disabled if you edit the Script Name. Instead use Apply Changes.

Apply Changes

Saves the script and returns to the SQL Scripts report.

Run

Submits the script for execution.

See Also: "Executing a SQL Script"

Undo (Ctrl+Z)

Removes, or undoes, the most recent line edit made in the Script Editor.

Redo (Ctrl+Y)

Repeats the most recent line edit made in the Script Editor.

Find (Ctrl+F)

Click the Find icon (which resembles a magnifying glass) to perform a basic search.

Replace (Ctrl+Shift+F)

Click the Replace icon (which resembles a two-sided arrow) to replace existing code.

4.7 Deleting a SQL Script

You can delete scripts from the Script Repository by deleting selected scripts from the SQL Scripts page, or deleting the current script in the Script Editor.

4.7.1 Deleting Scripts from the SQL Scripts Page

To delete scripts from the SQL Scripts page:

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

    The SQL Scripts page appears.

  2. Click the View Report icon.

    Reports view appears.

  3. Select the scripts to be deleted.

    To select individual scripts, click the check box to the left of the script name. To select all scripts visible in the current page, click the check box in the column heading.

  4. Click Delete Checked to permanently remove the selected scripts from the Script Repository.

  5. When prompted to confirm the delete action, click OK.

4.7.2 Deleting a Script in the Script Editor

To delete a script in the Script Editor:

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

    The SQL Scripts page appears.

  2. Open the script you want to delete in the Script Editor.

  3. Click Delete to permanently remove the script from the Script Repository.

  4. When prompted to confirm the delete action, click OK.

4.8 Copying a SQL Script

You can copy a script in the Script Repository by saving it with a new name.

To copy a script:

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

    The SQL Scripts page appears.

  2. Open the script into the Script Editor.

  3. In Script Name, enter a new script name.

  4. Click Apply Changes.

    The SQL Scripts page appears listing the new script.

4.9 Executing a SQL Script

You can execute scripts stored in the Script Repository. You can submit a script for execution either from the Script Editor or from the SQL Scripts page.

When you submit a script for execution, the Run Script page appears. It displays the script name, when it was created and by whom, when it was last updated and by whom, the number of statements it contains, and its size in bytes. It also lists unknown statements such as SQL*Plus commands that are ignored during execution.

If a script does not contain any runnable statements, the Run control does not display.

See Also:

"About Long Operations Page"

4.9.1 Executing a SQL Script in the Script Editor

To execute a script in the Script Editor:

  1. Open the script you want to execute in the Script Editor. See "Using the Script Editor."

  2. Click Run in the Script Editor.

    The Run Script page appears. This page displays information about the script and lists statements in error preventing execution, or statements such as SQL*Plus commands that are ignored when the script is executed.

    Available options on the Run Script page include:

    • Cancel returns you to the SQL Scripts page without executing the script.

    • Run Now to submit the script for execution.

    • Run in Background runs the script using DBMS_JOB rather than submitting for execution. This option is useful for long running scripts.

  3. Click Run Now to submit the script for execution.

    The Manage Script Results page appears listing script results.

  4. To view script results, click View Results.

See Also:

"Viewing SQL Script Results"

4.9.2 Executing a SQL Script from the SQL Scripts Page

To execute a script from the SQL Scripts page:

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

    The SQL Scripts page appears.

  2. Click the View Report icon.

    Reports view appears.

  3. In the far right column, click the Run icon for the script you want to execute.

    Tip:

    If the script does not contain any runnable statements, an error icon displays.

    The Run Script page appears. This page displays information about the script and lists statements in error preventing execution, or statements such as SQL*Plus commands that are ignored when the script is executed.

    Available options on the Run Script page include:

    • Cancel returns you to the SQL Scripts page without executing the script.

    • Run Now to submit the script for execution.

    • Run in Background runs the script using DBMS_JOB rather than submitting for execution. This option is useful for long running scripts.

  4. Click Run Now to submit the script for execution.

    The Manage Script Results page appears listing available results for the script.

  5. Click the View Results icon in the far right column.

See Also:

"Viewing SQL Script Results"

4.10 Viewing SQL Script Results

You use the Manage Script Results page to view and delete script results.

To view script results:

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

    The SQL Scripts page appears.

  2. On the Tasks list, click Manage Results.

    The Manage Script Results page appears, listing available results for the script.

  3. Click the View Results icon in the far right column.

    The Results page appears.

    The Results page displays the script name and status (Complete, Canceled, Executing or Submitted), and lists the statements executed.

  4. On the Results page, select a View option and click Go:

    • Detail. Displays the Results in a report.

    • Summary. Displays a Summary page. From the Rows list, select the number of Rows to display. Select a column heading to sort the listed values by that column.

  5. Click Edit Script to load the script into the Script Editor.

See Also:

"Using the Script Editor."

4.11 Exporting and Importing SQL Scripts

You can transfer scripts from your current Script Repository to a Script Repository in a different workspace by using the Export and Import tasks. Exported scripts are encoded in a single file on your local file system. Once exported, you then log in to another workspace and import the file. During import, the file is run to re-create the scripts in the current Script Repository.

By default, the Export SQL Scripts page lists all scripts in the Script Repository created by the current user. There are two panes on the Export SQL Scripts page, the Scripts pane and the Scripts to Export pane. You use the Scripts pane to select scripts to export. Then, you use the Scripts to Export pane to finalize the scripts to export, to choose a name for the export script, and to save the selected scripts in the export script on your local file system. You use the Import Scripts pane to select the export script containing the scripts to import.

4.11.1 Copying Scripts to an Export Script

To copy scripts to an export script:

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

    The SQL Scripts page appears.

  2. On the Tasks list, click Export.

    The Export SQL Scripts page appears.

  3. Select the scripts you want to export. To select all displayed scripts, click the column head check box.

    Description of sql_s_pane.png follows
    Description of the illustration sql_s_pane.png

  4. Click Add to Export.

    The selected scripts are added to the list of scripts in the Scripts to Export pane.

  5. Enter a name for the export script in the File Name field.

    The default script name is workspace_name_script.sql.

  6. Click Export All to export the scripts to the export script.

    You are prompted to enter the directory where you want to save the export script.

4.11.2 Importing Scripts

To import scripts:

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

    The SQL Scripts page appears.

  2. On the Tasks list, click Import.

    The Import Scripts pane appears.

  3. Browse to export script you want to import to the Script Repository.

  4. Click Next to list the scripts in the export script.

    The Import Scripts page appears. The Action column indicates whether the imported script is new, or whether it replaces an existing script of the same name.

  5. Click Import Script(s) to import the listed scripts into the current Script Repository.

    The SQL Scripts page appears listing the imported scripts.

4.12 Viewing Script and Result Quotas

You can view the script limits in the current workspace on the Script Quotas page.

To view the Script Quotas page:

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

    The SQL Scripts page appears.

  2. On the Tasks list, click Show Quotas.

    The Script Quotas page appears and displays the following limits:

    • Result Quota in Bytes:

      • Maximum Script Result Size. The maximum size in bytes of a single script result. This size is set by an Oracle Application Express instance administrator and cannot be changed from within the workspace.

      • Quota for All Script Results. The maximum size in bytes of all results in this workspace. This size is set by an Oracle Application Express instance administrator and cannot be changed from within the workspace.

      • Used. The number of bytes currently used in this workspace.

      • Free. The number of bytes currently free in this workspace.

      • Quota. A usage bar illustrating the percentage of quota currently used.

    • Script Quota in Bytes:

      • Maximum Script Size. The maximum size in bytes of a single script. The size is set by the Oracle Application Express instance administrator and cannot be changed within the Workspace.

  3. Click Cancel to return to the SQL Scripts page.