19 Using the SQL Script Repository

This section provides information on how to use the SQL Script Repository.

This section contains the following topics:

• Accessing the SQL Script Repository

• Creating a SQL Script

• Using the Script Editor

• Deleting a SQL Script

• Copying a SQL Script

• Executing a SQL Script

• Viewing SQL Script Results

• Transferring SQL Scripts between Workspaces

• Viewing Script and Result Quotas

About the SQL Script Repository

A SQL script is a set of SQL commands saved as a file in the SQL Script repository. It can contain one or more SQL statements or PL/SQL blocks. SQL*Plus commands in a SQL script are ignored at runtime.

You can use the SQL Script Repository to create, edit, view, run, and delete script files.

There is no interaction between the SQL Command Processor and the SQL Script Repository. You can cut and paste a SQL command from the SQL Script editor to run in the SQL Command Processor.

Bind variables are not supported.

Accessing the SQL Script Repository

To access the SQL Script Repository:

1. Log in to Oracle HTML DB.

   The Workspace home page appears.

2. To view the SQL Script repository home page you can either:

   • Click the SQL Workshop icon and then SQL Scripts to drill-down to the SQL Scripts home page.

   • Click the down arrow on the right side of the SQL Workshop icon to view a pull-down menu. Then select the SQL Scripts menu option.

   Figure 19-1 SQL Workshop Pull-down Menu

   
   Description of the illustration sqlwrkshpmenu.gif
   
   
   
   

   Note: For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.

   
   

About the SQL Scripts Home Page

By default, the SQL Scripts home page lists all SQL scripts in the SQL Script repository created by the current user.

Figure 19-2 SQL Scripts Home Page

Description of the illustration script_home.gif

On the SQL Scripts home page 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 Display list.

• Change the Page View. You can change the appearance of the home page by making a selection from the View list. Available View options include:

  • Icons displays each script as an icon identified by the script name. Click the Show Results check box to additionally display run results as icons identified by the script name.

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

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

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

• Upload a script. Click Upload to upload a script from your local file system into the SQL Script Repository. See "Editing a SQL Script".

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

About the Tasks List

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

Figure 19-3 SQL Scripts Tasks List

Description of the illustration script_tasks.gif

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. These limits are set by the HTML DB Administrator. See "Managing Environment Settings".

• Export enables you to export multiple scripts from the current SQL Script Repository for import into the SQL Script Repository 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 "Transferring SQL Scripts between Workspaces".

• Import enables you to import a script exported by this, 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 "Transferring SQL Scripts between Workspaces".

Creating a SQL Script

You can create a new script in the Script Repository by:

• Creating a new script in the Script Editor

• Uploading a script from your local file system

Topics in this section include:

• Creating a SQL Script in the Script Editor

• Uploading a SQL Script

Creating a SQL Script in the Script Editor

To create a new SQL script in the Script Editor:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. Click the Create button.

   The Script Editor appears.

4. Enter a name for the script in the Script Name field.

   Script name extensions are optional.

5. Enter the SQL statements, PL/SQL blocks and SQL*Plus commands you want to include in your script.

   SQL*Plus commands are ignored at runtime.

6. Click Save to save your script to the repository.

   The SQL Scripts home page appears listing your newly saved script.

Uploading a SQL Script

To upload a script from your local file system:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. Click the Upload button.

   The Upload Script dialog appears.

4. Enter the name and path to the script you want to upload to the Script Repository,

   or

   click Browse to locate the script you want to upload.

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

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

   The SQL Scripts home page appears listing your newly uploaded script.

   The script is parsed during upload. If it has a syntax error, an error icon appears in place of the run icon in the SQL Scripts home page Details view.

   If a script of the same name exists in the Script Repository, you are prompted to rename it.

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.

Topics in this section include:

• Editing a SQL Script

• About the Script Editor

Editing a SQL Script

To edit a SQL script:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

   The SQL Scripts home page appears listing your scripts in the repository by default. The last view selected from the View list is the new default.

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

   • In Icons view, click the script icon.

   • In Details view, click the Edit icon.

   The Script Editor appears.

   Figure 19-4 SQL Scripts Editor

   
   Description of the illustration script_editor.gif
   
   

4. Edit the script. See "About the Script Editor".

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

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

   The SQL Scripts home page appears.

About the Script Editor

You can perform the following actions in the Script Editor:

• Cancel the editing session. Click Cancel to exit the Script Editor without saving changes made since you last saved. The SQL Scripts home page appears.

• Save the script to your local file system. Click Download to save 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 the script from the Script Repository. Click Delete to remove the current script from the Script Repository. See "Deleting a SQL Script".

• Save the script to the Script Repository. Click Save to save your changes to the script to the Script Repository. The SQL Scripts home page appears.

• Execute the script. Click Run to submit the script for execution. See "Executing a SQL Script".

• Undoing/redoing edits. Click Undo (Ctrl+Z) and Redo (Ctrl+Y) to undo or redo line edits in the Script Editor.

• Searching in your script. Click Find to display the text and JavaScript regular expression find and replace options. Click Find again to hide the options.

• Selecting a line. Click the line number on the left side of the Script Editor to select the associated line of your script for copying or deleting.

• Cutting and Pasting. Use standard edit controls to cut, copy and paste content in the Script Editor.

• Auto indenting lines. New lines automatically indent to the previous line start column.

Deleting a SQL Script

You can delete scripts from the Script Repository by:

• Deleting selected scripts from the SQL Scripts home page

• Deleting the current script in the Script Editor

Topics in this section include:

• Deleting Scripts from the SQL Scripts Home Page

• Deleting a Script in the Script Editor

Deleting Scripts from the SQL Scripts Home Page

To delete scripts from the SQL Scripts home page.

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon to display the SQL Scripts home page.

3. Select Details from the View list to display the SQL Scripts home page detail view.

4. Click the check box for each of the scripts you want to delete, or click the check box in the column heading to select all scripts visible in the current page. The check boxes are at the left end of the scripts listed in the Details view.

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

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

Deleting a Script in the Script Editor

To delete a script in the Script Editor:

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

2. Click Delete to delete the current script from the script repository.

   The SQL Scripts home page appears.

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. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. Load the script to copy into the editor.

4. Enter a name for the copied script in the Script Name field.

5. Click Save to save a copy of the script in the Script Repository.

   The SQL Scripts home page appears listing the newly copied script.

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 home page.

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

Finally, it lists statements with errors. If there are errors, the Run control does not appear.

Topics in this section include:

• About the Run Script Page

• Executing a SQL Script in the Script Editor

• Executing a SQL Script from the SQL Scripts Home Page

• Viewing the Status of a Long Running Script

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.

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 will be ignored when the script is executed. The Run Script page has three controls:

   Cancel to return to the SQL Scripts home page without executing the script.

   Edit Script to load the script into the Script Editor.Edit Script appears instead of Run when a script has errors.

   Run to submit the script for execution. Run is not available if there are script errors.

4. Click Run to submit the script for execution.

   The Manage Script Results page appears listing script results.

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

See Also: "Viewing SQL Script Results"

Executing a SQL Script from the SQL Scripts Home Page

To execute a script from the SQL Scripts home page:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. Click the Run icon for the script you want to execute in the SQL Scripts home page.

   The Run icon is at the right end of the scripts listed in the Details view of the SQL Scripts home page.

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 will be ignored when the script is executed. The Run Script page has three controls:

   Cancel to return to the SQL Scripts home page without executing the script.

   Edit Script to load the script into the Script Editor. Edit Script appears instead of Run when a script has errors.

   Run to submit the script for execution. Run is not available for scripts with errors.

5. Click Run 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.

See Also: "Viewing SQL Script Results"

About the Run Script Page

On the Run Script page, you can:

• Cancel the execution. Click Cancel to exit the Run Script page without executing the script. The SQL Scripts home page appears.

• Edit the script. Edit Script appears instead of Run when a script has errors. Click Edit Script to load the script into the Script Editor to remove the lines with errors.

• Execute the script. Click Run to execute the script.

Viewing the Status of a Long Running Script

If you execute a script that takes a long time to complete, you can view its status in the Long Operations report available in the Database Monitor.

To view the status of a long running script:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click Utilities and then Database Monitor.

   Accessing the Database Monitor page requires database administrator privileges. You must have a database account that has been granted the DBA role.

3. Enter a valid database account username and password at the prompt and click Login.

4. Select Long Operations under Activity.

   The Long Operations report displays. You can see the status of your long-running script.

See Also: "Long Operations"

Viewing SQL Script Results

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

You can also select script results to view from the Icons view of the SQL Scripts home page, and from the Results column of the SQL Scripts home page Details view.

Topics in this section include:

• Viewing Script Results

• About the Manage Script Results Page

• About the Results Page

Viewing Script Results

To view script results from the SQL Scripts home page:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

   The SQL Scripts page appears.

3. You can access the Manage Script Results page in the following ways:

   • From the Tasks list, select Manage Results.

   • In Details view, click the Results number for the script you want to display.

   • In Icons view, click the appropriate icon. Results icons only appear in the Icons view if you click the Show Results check box.

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

   Figure 19-5 Manage Script Results Page

   
   Description of the illustration script_manage_results.gif
   
   

4. Click the View icon for the results you want to view. The View icon appears on the far right side of the Manage Script Results page.

About the Manage Script Results Page

On the Manage Script Results page you can:

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

• Change the Page View. You can change the appearance of the page by making a selection from the View list. Available View options include:

  • Icons displays each result as an icon identified by the script name, and time and date stamp.

  • Details displays each result as a line in a report. Each line includes a check box to enable the selection of results for deletion, the associated script name which is a link enabling it to be loaded into the Script Editor, who ran the script, when the run started, how long it took to run, whether the run is complete or not, the number of statements executed, the size in bytes, and a View icon to view the results.

• Delete a result. In Details view, select the check box associated with each result you want to delete, and click Delete Checked.

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

About the Results Page

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

Figure 19-6 Result Page

Description of the illustration script_results.gif

On the Results page you can:

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

• Choose the number of rows displayed. 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.

Transferring SQL Scripts between Workspaces

You can transfer selected scripts from your current Script Repository to a Script Repository in a different Workspace by using the Export and Import tasks. The scripts you select to export are encoded in a single file on your local file system. You can 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 SQL scripts in the SQL 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. 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.

Topics in this section include:

• Copying Scripts to an Export Script

• Importing Scripts from an Export Script

Copying Scripts to an Export Script

To copy scripts to an export script:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. From the Tasks list, select Export.

   The Export SQL Scripts page appears.

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

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

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

   The default script name is workspace_name_script.sql.

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

About the Scripts Pane

Figure 19-7 Scripts Pane

Description of the illustration script_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 Display list.

• Cancel the export. Click Cancel to return to the SQL Scripts home page without exporting any scripts, or to return to the SQL Scripts home 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.

About the Scripts to Export Pane

Figure 19-8 Scripts to Export Pane

Description of the illustration script_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 of scripts you want to remove from the export script and then 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.

Importing Scripts from an Export Script

To import scripts from an export script:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. From the Tasks list, select Import.

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

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

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

   The Action column indicates whether the imported script is new, or whether it will replace an existing script of the same name.

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

   The SQL Scripts home page appears listing the imported scripts.

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

   The default script name is workspace_name_script.sql.

About the Import Scripts Pane

Figure 19-9 Script Import Pane

Description of the illustration script_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 home 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.

• Import the scripts. Click Import Script(s) to import the scripts contained in the export script.

Viewing Script and Result Quotas

You can view the script limits in the current Workspace on the Script Quotas page. The Script Quotas page displays the following limits:

Result Quota in Bytes

• Maximum Script Result Size. The maximum size in bytes of a single script result. The size is set by the HTML DB 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. The size is set by the HTML DB 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 Workspace 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 HTML DB administrator and cannot be changed from within the Workspace.

To view the Script Quotas page:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Scripts icon.

3. Click Show Quotas in the Tasks list.

   The Script Quotas page appears.

4. Click OK to return to the SQL Scripts home page.

For further information about script quotas, see "Configuring Security Settings".