5 Managing Database Objects with SQL Workshop

About Transaction Support

Oracle HTML DB is a browser-based development environment which communicates over HTTP. Because HTTP is a stateless protocol, any command you issue using SQL Workshop is automatically followed by a database COMMIT. There is no support for transactions that span multiple pages in the SQL Workshop. For example, you cannot issue an UPDATE statement on one page in the SQL Workshop and then revert it on a subsequent page using a ROLLBACK command.

Since the commands COMMIT, ROLLBACK and SAVEPOINT are executed as one transaction, you can include these commands in SQL Workshop by using scripts.

About Unsupported SQL*Plus Commands

SQL Workshop does not support SQL*Plus commands. If you attempt to enter a SQL*Plus command in SQL Workshop an error message displays. The following are examples of unsupported SQL*Plus commands:

SET ECHO OFF
SET ECHO ON
SET VERIFY ON
SET LONG 600
COLUMN dummy NOPRINT
COLUMN name FORMAT A20
DEFINE
ACCEPT
PROMPT
REMARK
SHOW

Using the SQL Command Processor

You use the SQL Command Processor to run SQL commands and SQL scripts on any Oracle database schema for which you have privileges.

To access the SQL Command Processor:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, click SQL Command Processor.

   The SQL Command Processor appears.

3. Select a schema from the list and follow the on-screen instructions.

4. To run entered commands, click Run SQL.

   For example, to describe a database object you would type DESC MY_OBJECT. For example:

   DESC DEMO_ABOUT
   
   

   Alternatively, if you are using Internet Explorer, you can also press CTRL+Enter to execute your SQL Statement.

5. To save entered commands, click Save.

INSERT INTO emp
      (50,'John Doe','Developer',10,SYSDATE,1000,10);

INSERT INTO emp
      (50,'John Doe','Developer',10,SYSDATE,1000,10)
/

Using Explain Plan

Use Explain Plan to view the plan the Oracle Optimizer uses to run your SQL Command.

To view the Explain Plan:

1. Click SQL Workshop on the Workspace home page.

2. From the Tasks list on right side of the page, select Explain Plan.

   Explain Plan appears.

3. Enter a command in the field provided and click Explain Plan.

Viewing Database Objects

You can use the Database Browser to view database objects. To find a database object, select the schema you would like to view. The values available in the schema depend upon your resource privileges.

To browse database objects:

1. Click SQL Workshop on the Workspace home page.

2. Under Database Browser, select the type of database object you would like to view.

3. To search for an object, select a schema, an object type, type a search string in the Search field, and click Go. Searches are case insensitive and no wildcards or quotes are necessary.

4. To view object details, click the View icon adjacent to the appropriate name.

5. Optionally, select a task from the Tasks list on the right side of the page.

Creating Database Objects

You can create new database objects using the Create Database Object Wizard.

To create new database objects in SQL Workshop:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, select Create Object.

   The Create Database Object Wizard appears.

3. Select the type of database object you would like to create.

4. Follow the on-screen instructions.

Dropping Database Objects

You can drop database objects using the Drop Database Object Wizard. When you drop a table using this wizard, you also remove all related triggers and indexes.

To drop a database object:

1. Click SQL Workshop on the Workspace home page.

2. From the Tasks list on the right side of the page, select Drop Database Object.

   The Drop Database Object Wizard appears.

3. Select a schema and then an object type.

4. Follow the on-screen instructions.

Restoring Dropped Database Objects

If you are running Oracle HTML DB with Oracle Database 10g release 1 (10.1), 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 where it can be recovered at a later time.

To use the Recycle Bin:

1. Click SQL Workshop on the Workspace home page.

2. From the Tasks list on the right side of the page, select Manage Recycle Bin.

   The Recycle Bin appears.

3. To search for an object, select a schema, an object type, type a search string in the Search field, and click Go.

4. To view object details, click the View icon adjacent to the appropriate name.

   On the Object Summary page, you can:

   • Click Restore Object to restore the current object

   • Click Purge to permanently delete the current object

To empty the Recycle Bin without viewing the objects:

1. Click SQL Workshop on the Workspace home page.

2. From the Tasks list on the right side of the page, select Manage Recycle Bin.

   The Recycle Bin appears.

3. From the Tasks list on the right side of the page, select Purge Recycle Bin.

Using the SQL Script Repository

You can use the SQL Script Repository to view, edit, and run uploaded script files. For example, you can upload new script files as well as create and edit your create table, create index, and create PL/SQL package scripts.

Topics in this section include:

• Managing Script Files in the SQL Script Repository

• Uploading and Creating Script Files

• About Script Termination

• Using Parameters in a Script

• Including SQL Queries in a Script

• Exporting a Script File

INSERT INTO emp values(10,'aaa')
/
INSERT INTO emp values(20,'bbb')
/
INSERT INTO emp  values(30,'ccc')
/

CREATE TABLE #OWNER#.xyz (X INT)
/
CREATE TABLE #OWNER#.abc (Y NUMBER)
/

CREATE TABLE &OWNER.xyz (X INT)
/
CREATE TABLE &OWNER.abc (Y NUMBER)
/

Accessing Saved Commands in the SQL Archive

When you click Save in SQL Command Processor, SQL Workshop saves entered commands and scripts to the SQL Archives.

The SQL Archives is different from the SQL Script Repository. By saving frequently used SQL commands in the SQL Archives, you can run the commands again without retyping. When you save a SQL command to the SQL Archives, the saved command does not appear in the Script Repository.

To view the SQL Archives:

1. Click SQL Workshop on the Workspace home page.

2. From the Tasks list on the right side of the page, select Manage SQL Archive.

   SQL Archives appears. Scripts are stored based on your workspace username.

3. Follow the on-screen instructions.

Accessing the SQL Command History

SQL Command History displays the 200 most recent commands and scripts run in the SQL Command Processor.

To view the SQL Command History:

1. Click SQL Workshop on the Workspace home page.

2. From the Tasks list on the right side of the page, select View SQL History.

   SQL Command History appears.

3. To run a command, click the appropriate SQL link.

   The selected SQL command or the script displays in the SQL Command Processor.

Generating DDL

If you are running Oracle HTML DB with Oracle Database 10g release 1 (10.1), you can use DDL statements to create, alter, and drop schema objects when they are no longer needed. You can also use DDL statements to grant and revoke privileges and roles, analyze table, index, or cluster information, establish auditing options, or add comments to the data dictionary.

To generate DDL statement in SQL Workshop:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Scripts, click Generate DDL.

   The Generate DDL Wizard appears.

3. Follow the on-screen instructions.

Managing Control Files

Control files enable you to run a series of scripts in a predefined order. From the Control Files Repository, you can create, edit, delete, or run control files.

To access the Control Files Repository:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Scripts, click Script Control.

   Control Files Repository appears.

3. To search for a script, select a username from the Show list, enter a search string in the Find field (optional), and click Go.

   While in the Control Files Repository you can:

   • Reorder a list by clicking the column heading

   • Edit a file by clicking the Edit icon

   • Run a file by clicking Run

   • Delete a script by selecting it and clicking Delete Checked

To create a control file:

1. In the Control Files Repository, click Create.

   The Control File Create page appears.

2. Enter a name for the control file, select the script files you would like to include, and click Create.

3. Specify the execution order and click Done.

To edit a control file:

1. In the Control Files Repository, click the Edit icon.

   The Edit Control File page appears.

2. On the Edit Control File page you can:

   • Change the order in which files are executed by clicking Edit Execution Order

   • Add additional script files by clicking Add More Files

   • Delete a script by selecting it and clicking Delete Checked

To run a control file in the Control Files Repository:

1. In the Action Column, click Run.

   The Run File page appears.

2. Select an Oracle Schema from the Parse As list.

   If your script files include parameters you must define them. You can define up to ten different parameters in each script.

3. Enter a parameter name and value in the fields provided.

   If you wish to run the control file in the background, select Run in Background. Running a control file in the background means that Oracle HTML DB submits it as a job. The advantage of this approach is that you do not have to wait for it to finish to continue using SQL Workshop.

4. Click Run File.

   The Run Results page appears displaying the number of success, failures, and the elapsed time. RED indicates that errors occurred while executing the file.

5. To run the file again, select Run File again

Managing Tables

You can also use SQL Workshop to create new tables or edit existing tables.

To create a new table:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, click Create Object.

   The Create Database Object Wizard appears.

3. Select Table and click Next.

4. Follow the on-screen instructions.

To edit an existing table:

1. Click SQL Workshop on the Workspace home page.

2. Under Database Browser, select Tables.

3. To search for a table, select a schema, table type, type a search string in the Search field, and click Go.

4. To view table details, click the View icon.

   The Table Definition appears.

5. To edit the table or generate a report, make selection from the Tasks list. Table 5-2 describes the options available on the Tasks list.

   Table 5-2 Table Tasks

   Task	Description
   Count Rows	Counts all rows in the currently selected table.
   Create Lookup Table	Creates a lookup table for the column you select. The selected column becomes a foreign key for the lookup table.
   Drop this object	Drops the current table.
   Generate Create Table Script	Generates a create table script.
   Insert Row	Inserts a row into the current table.
   Manage Table	Displays a wizard for performing a variety of tasks on the current table, including: table (copy, drop, rename, analyze, truncate) columns (add, drop, modify, and rename) constraints (add, disable, drop) trigger (create, disable, drop, enable) index (create and drop
   Popup Table Columns	Displays a popup window that lists the columns in the current table.
   Query by Example	Displays a form so you can create a report and view the data in selected columns.
   Report Dependent Objects	Displays a report that describes database objects that have dependencies on the selected table.
   View Data Model	Displays a report that shows the referential integrity constraints for the current table.

   
   

6. To view user interface defaults, click the Manage User Interface Defaults button.

Viewing Tables Utilizing User Interface Defaults

To view tables that utilize user interface defaults:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, select User Interface Defaults.

   A list of tables for which user interface defaults are defined displays.

3. Select a specific table by clicking the Edit icon adjacent to the table name.

Editing a Column Attributes

You define user interface defaults for a specific column by editing column attributes on the Column Definition page.

To edit column attributes:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, select User Interface Defaults.

   A list of tables for which user interface defaults are defined displays.

3. Select a specific table by clicking the Edit icon adjacent to the table name.

   The View Details page appears. The following information displays at the top of the page:

   • Schema is the schema that owns the table.

   • Table/View Name is the name of the selected table or view.

   • Report Region Title and Form Region Title becomes the default title for all report or form regions. Both of these names a modified versions of Table/View Name in which the first letter is capitalized and any underscores are replaced with spaces.

   Column-level User Interface Defaults appear next. You can edit select attributes for all displayed columns, by clicking Grid Edit.

4. To select a specific column, click the Edit icon adjacent to the column name.

Applying User Interface Defaults to a Table or View

You can view a listing of tables without user interface defaults on the Tables without Defaults page.

To apply user interface defaults to a table or view:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, select User Interface Defaults.

3. From the Tasks list, select Tables without User Interface Defaults.

4. To apply user interface defaults to a table or view, select the Default link to the left of the table or view name.

Comparing User Interface Defaults Across Applications

Use the Compare Defaults report to monitor consistency in user interface design across all pages in a single application or multiple applications in the current workspace. Running the Compare Defaults report compares currently defined user interface defaults (or column attributes) against the column attributes set for forms, reports, and tabular forms.

To run the Compare Defaults report:

1. Click SQL Workshop on the Workspace home page.

2. Under SQL Workshop, select User Interface Defaults.

3. From the Tasks list, select Compare User Interface Defaults.

   The Compare Defaults page appears.

4. Select a schema, table name, and column.

5. From Display, select the attributes you want to compare.

6. Optionally select an application.

7. Click Go.

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 Oracle HTML DB development instance to another involves the following steps:

• Export the user interface defaults using the Export User Interface Defaults utility

• Import the exported file into the target Oracle HTML DB instance

• Install the exported file from Export Repository

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 HTML DB instance.