3 Using SQL Scripts

3.2.1 About the SQL Scripts Page

The SQL Scripts page displays an Interactive Report of all SQL scripts created by the current user. 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 See "Customizing Interactive Reports" in the Oracle Application Express Application Builder User's Guide. The Report view displays each script as a row in a report.

The SQL Scripts page features the following controls:

• 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. For additional customization options, see "Customizing Interactive Reports" in the Oracle Application Express Application Builder User's Guide.

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

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

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.

Available icons include:

• Object Browser. See "Managing Database Objects with Object Browser".

• SQL Commands. See "Using SQL Commands".

• SQL Scripts. See "Using SQL Scripts".

• Query Builder. See "Building SQL Queries with Query Builder".

• Utilities. See "Using Oracle Application Express Utilities".

• RESTful Services. See "Accessing RESTful Services".

• Help.

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

3.3.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. Enter a name for the script in the Script Name field.

   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 Save to save your script to the repository.

   The SQL Scripts page appears listing your newly saved script.

3.3.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. Use Choose File button to select the script you want to upload.

4. Optionally rename the script by entering the new name in the Script Name field.

   This is the name given to the script in the Script Repository.

5. Click Upload to add the script to the Script Repository.

   The SQL Scripts page appears listing your newly uploaded script.

   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 in the Script Repository, you are prompted to rename it.

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

   
   Description of the illustration sql_s_editor.gif
   
   

3. Edit the script.

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

   • Search and Replace. Click Find & Replace to display the text and JavaScript regular expression find and replace options. Click Find & Replace again to hide the options. See "Searching and Replacing Text or Regular Expressions".

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

   • Undo. Use to undo the last edit.

   • Redo. Use to reapply the last undone edit.

   You can test your script during editing by running the script to reveal errors. The Run Script dialog and the Script Results pages enable you to resume editing the script. See "Executing a SQL Script" and "Viewing SQL Script Results".

4. Click Save to save your script to the Script Repository,

   The SQL Scripts page appears.

3.4.2 Searching and Replacing Text or Regular Expressions

Clicking the Find & Replace button in the Script Editor displays the Find and Replace with fields at the top of the page. Use these fields to search for and replace text strings and JavaScript regular expressions within a script. To exit Find mode, click Find again.

To access Find mode in the Script Editor:

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

   The SQL Scripts page appears.

2. Select a script.

   The Script Editor appears.

3. Click the Find& Replace button.

   The Find and Replace fields appear.

4. In the Find field, enter the string you want to find. In Replace with, enter the new string to be added and then click the appropriate button (Find Next, Replace, or Replace All.)

   To further refine your search, select the Match Case check box.

5. To exit Find mode, click Find & Replace.

3.4.3 Summary of Script Editor Controls

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

3.5.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 Edit icon. 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. You are prompted to confirm this action before the script is deleted.

   The message "Script(s) deleted" appears above the updated list of Scripts.

3.5.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. You are prompted to confirm this action before the script is deleted.

   The SQL Scripts page appears. The message "Script(s) deleted" appears above the updated list of scripts.

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

3. The Run Script page appears.

   The Run Script 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.

   The Run Script page has three controls:

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

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

   • Run Now to submit the script for execution.

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

   The Manage Script Results page appears listing script results.

5. To view script results, click the View icon under View Results.

3.7.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. Click the Run icon for the script you want to execute. The Run icon is located on the far right side adjacent to the script name.

4. The Run Script page appears.

   The Run Script 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. The Run Script page has three controls:

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

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

   • Run Now to submit the script for execution.

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

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

6. Click the View icon for the results you want to view. The View icon is at the right end of the scripts listed in the Manage Script Results page.

3.8.1 Viewing Results from the SQL Scripts Page

To view script results from the SQL Scripts 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 Manage Results.

3. For the results you want to view, click the View Results icon.

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

   
   Description of the illustration sql_m_results.gif
   
   

4. Click the View Results icon for the results you want to view. The View icons display on the far right side of page under the heading View Results.

   The Results page appears. See "About the Results Page".

3.8.2 About the Results Page

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

On the Results page you can:

• Choose the view. Click the Detail or Summary option and click Go to specify whether to display complete or summarized information.

• Choose the number of rows to display. In Summary view, make a selection from the Display list and click Go to specify the number of rows displayed.

• Sort the statement report. In Summary view, select a column heading to sort the listed values by that column.

• Edit the script. Click Edit Script to load the script into the Script Editor. See "Using the Script Editor".

3.9.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. Click the check box for each of the scripts you want to export. The check boxes display on the left side adjacent to the script name. To select all displayed scripts for export, click the column head check box.

4. Click Add to Export to create a list of scripts to be added to the export script.

   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.

3.9.1.1 About the Scripts Pane

Description of the illustration sql_s_pane.gif

In the Scripts pane you can:

• Search for a script. Enter a script name or partial name in the Find field and click Go. To view all scripts, leave the Find field blank, select - All Users - from the Owner list and click Go. You control how many rows display by making a selection from the Rows list.

• Cancel the export. Click Cancel to return to the SQL Scripts page without exporting any scripts, or to return to the SQL Scripts page after saving an export script.

• Selecting scripts to export. Click Add to Export to add scripts to the export script. Scripts added to the export script are no longer listed in the Script pane, but appear in the Scripts to Export pane.

• Sort scripts. Click a column heading to sort the listed scripts by that column.

3.9.1.2 About the Scripts to Export Pane

Description of the illustration sql_s_export.gif

In the Scripts to Export pane you can:

• Rename the export script. Enter a name for the export script in the File Name field or leave the default script name.

• Remove scripts. Click the check box adjacent to the scripts you want to remove f and click Remove Checked. Scripts removed are no longer listed in the Scripts to Export pane, but appear in the Scripts pane.

• Save the export script. Click Export All to save the export script to your local file system. You are prompted to enter the directory where you want to save the export script.

3.9.2 Importing Scripts from an Export Script

To import scripts from 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 Import.

   The Import Scripts pane appears. See "About the Import Scripts Pane".

3. Enter the name and path to the export script you want to import to the Script Repository, or click Browse to locate the export script you want to import.

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

   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.

3.9.2.1 About the Import Scripts Pane

Description of the illustration sql_s_import.gif

In the Import Scripts pane you can:

• Enter the export script. Enter the name and path of the script to import in the Import file field, or click Browse to locate the script.

• Cancel the import. Click Cancel to return to the SQL Scripts page without importing scripts.

• Proceed with the import. Click Next to import the scripts in the specified export script. You can review the listed scripts to import.

• Choose another export file. Click Previous to return to the Import Scripts file selection page to choose a different export script.