18 Managing Database Objects Using Object Browser

Object Browser enables developers to browse, create, and edit objects in multiple schemas in a single database.

This section contains the following topics:

• About Object Browser

• Searching For and Browsing Database Objects

• About Creating New Database Objects

• Managing Tables

• Managing Views

• Managing Indexes

• Managing Sequences

• Managing Types

• Managing Packages

• Managing Procedures

• Managing Functions

• Managing Triggers

• Managing Database Links

• Managing Materialized Views

• Managing Synonyms

About Object Browser

The Object Browser page is divided into two sections:

• Object Selection pane displays on the left side of the Object Browser page and lists database objects of a selected type within the current schema. You can further narrow the results by filtering on the object name.

• Detail pane displays to the right of the page and displays detailed information about the selected object. To view object details, select an object in the Object Selection pane. Click the tabs at the top of the Detail pane to view additional details about the current object. To edit an object, click the appropriate button.

Figure 18-1 Object Browser

Description of the illustration o_brws.gif

Accessing Object Browser

To access Object Browser:

1. Log in to Oracle HTML DB.

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

   The SQL Workshop home page appears.

3. To view Object Browser you can either:

   • Click the Object Browser icon.

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

   Figure 18-2 Object Browser Pull-down Menu

   
   Description of the illustration o_brws_menu.gif
   
   
   
   

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

   
   

Searching For and Browsing Database Objects

The Object Selection pane displays on the left side of the Object Browser page and lists database objects by type with the current schema. You can filter the view by selecting an object type or entering a case insensitive search term.

Topics in this section include:

• Searching and Filtering Database Objects

• Hiding the Object Selection pane

• Selecting a Database Object

Searching and Filtering Database Objects

To search or filter objects in the Object Selection pane:

1. Navigate to Query Builder:

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

   2. Click Object Browser.

      Object Browser appears.

2. Select a schema from the Schema list on the right side of the page.

   Only objects in the current schema display. Remember that the values available in the schema depend upon your workspace privileges.

3. Select an object type from the Object list.

   The list of objects that appears depends upon the available objects in the current schema. Note that any object having a red bar adjacent to it is invalid.

4. To search for an object name, enter a case insensitive search term in the search field.

5. To view all objects, leave the search field blank.

Figure 18-3 Object Browser Search Field

Description of the illustration o_brws_srch.gif

Hiding the Object Selection pane

You can hide the Object Selection pane by selecting the Hide Objects control. This control displays on the right side of the Object Selection pane. If the Object Selection pane appears, selecting this control hides it. Similarly, if the Object Selection pane is hidden, selecting this control causes the pane to reappear.

Figure 18-4 Hide Object Control

Description of the illustration o_brws_hide.gif

Selecting a Database Object

Once you locate the database object you want to view, simply select it. The selected object displays in the Detail pane. If no object is selected, the Detail pane is blank.

About Creating New Database Objects

You can create new database objects using the Create Database Object Wizard. Once you select an object, a set of tabs and buttons appears at the top of the Detail pane. Use the tabs to view different aspects of the current items (for example, a tables indexes). Use the buttons to modify the current object.

To create a new object:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create, located in the upper right corner of the Detail pane.

3. From the list of object types, select the type of object you want to create.

4. Follow the on-screen instructions.

Managing Tables

A table is a unit of data storage in an Oracle database, containing rows and columns. When you view a table in Object Browser, a table description appears that describes each column in the table.

Topics in this section include:

• Creating a Table

• Browsing a Table

• Editing a Table

Creating a Table

To create a new table:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Table.

4. Enter a table name.

   Table names must conform to Oracle naming conventions and not contain spaces or start with a number or underscore.

5. To have the final table name match the case entered in the Table Name field, click Preserve Case.

6. Enter details for each column. For each column:

   1. Enter the column name.

   2. Select the column type. Available types include NUMBER, VARCHAR2, DATE, TIMESTAMP, CHAR, CLOB, BLOB, NVARCHAR2, BINARY_FLOAT, and BINARY_DOUBLE

   3. Enter the following additional information as appropriate:

      • Precision

      • Scale

   4. To specify a column should not be NULL, select the check box in the Not Null column.

   To change the order of previously entered columns, click the Up and Down arrows in the Move column. To add additional columns, click Add Column.

   Next, define the primary key for this table (optional). A primary key is a single field or combination of fields that uniquely identifies a record.

7. For Primary Key, select one of the following and click Next:

   • No primary key - No primary key is created.

   • Generated from a new sequence - Creates a primary key and creates a trigger and a new sequence. The new sequence is used in the trigger to populated the selected primary key column. The primary key can only be a single column.

   • Generated from an existing sequence - Creates a primary key and creates a trigger. The selected sequence is used in the trigger to populate the selected primary key column. The primary key can only be a single column.

   • Not generated - Defines a primary key but does not have the value automatically populated with a sequence within a trigger. You can also select this option to define a composite primary key (that is, a primary key made up of more than one column).

   Next, add foreign keys (optional). A foreign key establishes a relationship between a column (or columns) in one table and a primary or unique key in another table.

8. To add a foreign key:

   1. Name - Enter a name of the foreign key constraint that you are defining.

   2. Select Key Column(s) - Select the columns that are part of the foreign key. Once selected, click the Add icon to move them to Key Column(s).

   3. References Table - Select the table which will be referenced by this foreign key. Then, select the columns to be referenced by this foreign key. Once selected, click the Add icon to move the selected columns to Referenced Column(s).

   4. Select one of the following:

      • Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in this table.

      • Select Cascade Delete - Deletes the dependent rows from this table when the corresponding parent table row is deleted.

      • Set to Null on Delete - Sets the foreign key column values in this table to null when the corresponding parent table row is deleted.

   5. Click Add.

   6. Click Next.

   Next, add a constraint (optional). You can create multiple constraints, but you must add each constraint separately.

9. To add a constraint:

   1. Specify the type of constraint (Check or Unique).

      A check constraint is a validation check on one or more columns within the table. No records can be inserted or updated in a table which violates an enabled check constraint. A unique constraint designates a column or a combination of columns as a unique key. To satisfy a unique constraint, no two rows in the table can have the same values for the specified columns.

   2. Enter the constraint in the field provided. For unique constraints, select the column(s) that are to be unique. For check constraints, enter the expression that should be checked such as, flag in ('Y','N').

   3. Click Add.

10. Click Finish.

    A confirmation page appears. To view the SQL used to create the table, click SQL Syntax.

11. Click Create.

    Note that you do not need to follow the steps for creating a table in the order described in the previous procedure. Instead of navigating through the wizard by clicking the Next and Previous button, you can also access a specific step by selecting it in the progress indicator on the left side of the page.

See Also: Oracle Database Concepts for information about tables

Browsing a Table

When you view a table in Object Browser, the table description appears. While viewing this description, you can add a column, modify a column, rename a column, drop a column, rename the table, copy the table, drop the table, truncate the table, or create a lookup table based upon a column in the current table. Additionally, you have access other reports that offer related information including the table data, indexes, data model, constraints, grants, statistics, user interface defaults, triggers, dependencies, and SQL to produce the selected table.

To view a table description:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Tables.

3. From the Object Selection pane, select a table.

   The table description appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the table. Table 18-1 describes all available views.

Table 18-1 Available Views for Tables

View	Description
Table	While viewing table details you can add, modify, delete, or rename a column. Additionally, you can drop, rename, copy, or truncate the table as long as the referencing table has no records well as create a lookup table. See Also: "Editing a Table"
Data	Displays a report of the data in the current table. Actions you can perform include: Query - Enables you to sort by column. To restrict specific rows, enter a condition in the Column Condition field. Use the percent sign (%) for wildcards. From Order by, select the columns you want to review and click Query. Count Rows - Displays a report of the number of rows in the current table. Insert Row - Enables you to insert a new row into the table. Download - Exports all data in the table to a spreadsheet. Click the download link at the bottom of the page to export all data in the selected table.
Indexes	Displays indexes associated with this table. Actions you can perform include Create and Drop. See Also: "Managing Indexes"
Model	Displays a graphical representation of the selected table along with all related tables. Related tables are those that reference the current table in a foreign key and those tables referenced by foreign keys within the current table. You can position the cursor over an underlined table name to view the relationship between that table and the current table. Click an underlined table name to view the model of the related table.
Constraints	Displays a list of constraints for the current table. Actions you can perform include Create, Drop, Enable, and Disable.
Grants	Displays a list of grants on the current table, including the grantee, the privilege, and grant options. Actions you can perform in this view include Grant and Revoke.
Statistics	Displays collected statistics about the current table, including the number of rows and blocks, the average row length, sample size, when the data was last analyzed, and the compression status (enabled or disabled). Click Analyze to access the Analyze Table Wizard.
UI Defaults	Displays user interface defaults for forms and reports. User interface defaults enable developers to assign default user interface properties to a table, column, or view within a specified schema. Click Edit to edit defined user interface defaults. Click Create to initialize user interface defaults for tables that do not currently have user interface defaults defined. See Also: "Managing User Interface Defaults"
Triggers	Displays a list of triggers associated with the current table. Actions you can perform include Create, Drop, Enable, and Disable. To view trigger details, click the trigger name. See Also: "Managing Triggers"
Dependencies	Displays report showing objects referenced by this table, objects this table references, and synonyms for this table.
SQL	Displays the SQL necessary to re-create this table, including keys, indexes, triggers and table definition.

Editing a Table

While viewing a table description, you can edit it by clicking the buttons above the table description.

To edit a table:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Tables.

3. From the Object Selection pane, select a table.

   The table description appears.

4. Click the appropriate button described in Table 18-2.

   Table 18-2 Edit Table Buttons

   Button	Description
   Add Column	Adds a new column to the table. Enter a column name and select a type. Depending upon the column type, specify whether the column requires a value as well as the column length, precision, and scale.
   Modify Column	Modifies the selected column.
   Rename Column	Renames the selected column.
   Drop Column	Drops the selected column.
   Rename	Renames the selected table.
   Copy	Copies the selected table.
   Drop	Drops the selected table. See Also: "Using the Recycle Bin to View and Restore Dropped Objects"
   Truncate	Removes all rows from the selected table. Truncating a table can be more efficient than dropping and re-creating a table. Dropping and re-creating a table may invalidate dependent objects, requiring you to regrant object privileges or re-create indexes, integrity constraints, and triggers.
   Create Lookup Table	Creates a lookup table based on the column you select. That column becomes a foreign key to the lookup table.

   
   

Managing Views

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.

Topics in this section include:

• Creating a View

• Browsing a View

• Editing a View

• Compiling a View

• Dropping a View

See Also: Oracle Database Administrator's Guide for information about views

Creating a View

To create a new view:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select View.

4. Define the view:

   • View Name - Enter a name for the View.

   • Query - Specify a query to define the view.

     To access Query Builder or SQL Command Processor, click the appropriate link at the bottom of the page. The selected tool appears in a pop-up window. Once you create the appropriate SQL, click Return to automatically close the popup window and return to the wizard with the SQL.

5. Click Next.

   A confirmation page appears. To view the SQL used to create the view, click SQL.

6. Click Create.

See Also: "Building Queries with Query Builder" and "Using SQL Command Processor"

Browsing a View

When you access a view in Object Browser, the Detail pane displays a report listing the columns in that view.

To browse a view:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Views.

3. From the Object Selection pane, select a view.

   The view definition appears displaying the appropriate columns.

Summary of Available Views

Click the tabs at the top of the page to view different reports. Table 18-3 describes all available views.

Table 18-3 Available Views for Views

View	Description
View	(Default) Displays the columns in the current view. Actions you can perform include: Compile Drop See Also: "Editing a View", "Compiling a View", and "Dropping a View"
Data	Displays a report of the data in the columns in the view. Actions you can perform include: Query - Enables you to sort by column. To restrict specific rows, enter a condition in the Column Condition field. Use the percent sign (%) for wildcards. From Order by, select the columns you want to review and click Query. Count Rows - Enables you to insert a new row into the table. Insert Row - Enables you to insert a new row into the table.
Grants	Displays a list of grants associated with the columns in the view. Grant details include grantee, privilege, and grant options. Actions you can perform include Grant and Revoke.
UI Defaults	Displays user interface defaults for forms and reports. User interface defaults enable developers to assign default user interface properties to a table, column, or view within a specified schema. Click Edit to edit existing user interface defaults. Click Create to initialize user interface defaults for views that do not currently have user interface defaults defined. See Also: "Managing User Interface Defaults"
Dependencies	Displays a report showing objects referenced by this view, objects this view references, and synonyms for this view.
SQL	Displays the SQL necessary to re-create this view.

Editing a View

When you edit a view you can edit the code manually, perform a search and replace, and compile the view. Additionally, you can save the view as a file or drop it.

Editing a View Manually

To edit a view manually:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Views.

3. From the Object Selection pane, select a view.

4. Select the Code tab.

5. Click Edit to activate manual edit mode.

   If you edit and make changes to a view, you need to compile.

Note: You can expand the Edit pane by clicking the Full Screen icon in the upper right of the pane, beneath the Create button.

Using Find and Replace

Click Find to perform a basic search and replace.

Downloading a View

Click Download to save the current view as a file.

Compiling a View

If you edit and make changes to a view, you need to compile in order to save your changes. Note that there is no save function since this is just a view of the object within the database.

Click Compile to re-create the current view.

Dropping a View

Click Drop to delete the current view.

Managing Indexes

An index is an optional structure associated with tables and clusters. You can create indexes on one or more columns of a table to speed access to data on that table.

When you view an index in Object Browser, the Detail pane displays a report containing the index name, index type, table owner, table type, and a listing of the indexed columns.

Topics in this section include:

• Creating an Index

• Browsing an Index

See Also: Oracle Database Concepts for information about indexes

Creating an Index

To create an view:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Index.

4. Select a table and select the type of index you want to create. Available index types include:

   • Normal - Indexes one or more scalar typed object attributes of a table

   • Text - Creates a text index (Oracle Text)

5. Click Next.

6. Create the index definition. Specify an index name, select one or more columns to be indexed, and click Next.

   A confirmation page appears. To view the SQL used to create the index, click SQL.

7. Click Finish.

Browsing an Index

To browse an index:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Indexes.

3. From the Object Selection pane, select an index.

   The index appears displaying the index name, type, table owner, and table type as well as a listing of indexed columns.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the index. Table 18-4 describes all available views.

Table 18-4 Available Views for Indexes

View	Description
Object Details	(Default) Displays the index name, index type, table owner, and table type as well as a listing of the indexed columns. Actions you can perform while viewing Object Details include: Disable - Disables the current index Drop - Drops the current index.x Rebuild - Rebuilds the current index
Statistics	Displays collected statistics about the current view, including the number of rows, sample size, when the data was last analyzed, and the compression status (enabled or disabled). Click Analyze to refresh the displayed statistics.
SQL	Displays the SQL necessary to re-create this index.

Managing Sequences

A sequence generates a serial list of unique numbers for numeric columns of a database table. Database sequences are generally used to populate table primary keys.

Topics in this section include:

• Creating a Sequence

• Browsing a Sequence

See Also: Oracle Database Administrator's Guide for information about sequences

Creating a Sequence

To create a sequence:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Sequence.

4. Define the sequence, specify a sequence name, and click Next.

   A confirmation page appears. To view the SQL used to create the sequence, click Show SQL.

5. Click Create.

Browsing a Sequence

To browse a sequence:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Sequences.

3. From the Object Selection pane, select a sequence.

   The Object Details view appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the sequence. Table 18-5 describes all available views.

Table 18-5 Available Views for Sequences

View	Description
Object Details	(Default) Displays details about the current sequence. Actions you can perform in this view include Alter and Drop.
Grant	Displays a list of grants associated with the sequence. Grant details include grantee, privilege, and grant options. Actions you can perform include Grant and Revoke.
Dependencies	Displays a list of objects that use (or depend) upon this sequence.
SQL	Displays the SQL necessary to re-create this sequence.

Managing Types

A type is a user-specified object or collection definition. Oracle HTML DB currently only supports collection definitions. There are two categories of Oracle collections (SQL collections):

• Variable-length arrays (VARRAY types)

• Nested tables (TABLE types)

VARRAY types are used for one-dimensional arrays, while nested table types are used for single-column tables within an outer table.

Topics in this section include:

• Creating a Type

• Browsing a Type

See Also: Oracle Database Concepts and SQL*Plus User's Guide and Reference for information about collection types

Creating a Type

To create a collection type:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Type.

4. Specify a name and click Next.

5. Select a type, data type, limit, and click Next.

   A confirmation page appears. To view the SQL used to create the collection type, click Show SQL.

6. Click Finish.

Browsing a Type

To browse a collection type:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Type.

3. From the Object Selection pane, select a type.

   The Object Details view appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports. Table 18-6 describes all available views.

Table 18-6 Available Views for Types

View	Description
Object Details	(Default) Displays details about the selected type. To drop a type, click Drop.
Synonyms	Displays a list of synonyms for the current type.
Grants	Displays a list of grants associated with the type. Grant details include grantee, privilege, and grant options. Actions you can perform include Grant and Revoke.
SQL	Displays the SQL necessary to re-create this type.

Managing Packages

A package is a database object that groups logically related PL/SQL types, items, functions and procedures. Packages usually have two parts, a specification and a body. The specification is the interface to your application. The body implements the specification.

Topics in this section include:

• Creating a Package

• Viewing a Package

• Editing a Package

• Compiling a Package

• Dropping a Package

• Dropping a Package

See Also: SQL*Plus User's Guide and Reference for information about PL/SQL packages

Creating a Package

To create a package:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Package.

4. Select the type of package you want to create:

   • Specification

   • Body

   • Package with methods on database tables

5. If you select Specification:

   1. Enter a name and click Next.

      The wizard creates a dummy package specification and displays it for editing.

   2. Edit the specification and click Finish.

6. If you select Body:

   1. Select the package you want to create the body for and click Next

      The wizard creates a package body with stubbed out calls identified in the specification and displays it for editing.

   2. Edit the package body and click Finish.

7. If you select Package with methods on database tables:

   1. Enter a name and click Next.

   2. Select up to ten tables and click Next.

      The wizard creates a specification and body with insert, update, delete, and GET APIs for the selected tables. Note that you have the option to show or download the specification or body.

   3. Click Finish.

Viewing a Package

When you access a package in Object Browser the specification appears.

To view a specification:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Packages.

3. From the Object Selection pane, select a package.

   The Specification appears. You can copy the code in this view for use in other tools.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the package. Table 18-7 describes all available views.

Table 18-7 Available Views for Packages

View	Description
Specification	(Default) Displays the package specification. This define the interface to your application. Actions you can perform include: Edit Compile Download Drop Find
Body	Displays the package body, if one exists, for the selected package. Actions you can perform include: Edit Compile Download Drop Find
Dependencies	Displays objects that use (or depend on) on the current package and objects the package depends on.
Errors	Displays errors related to the current package.
Grants	Lists details of grants for the current package, including privilege, grantee, grantable, grantor, and object name.

Editing a Package

When you edit a package, you can edit the code manually, perform a search and replace, and compile the package.

Editing a Package Manually

To edit a package manually:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Packages.

3. From the Object Selection pane, select a package.

   The Specification appears. You can copy the code in this view for use in other tools. Note you can edit both the specification and the body fromObject Browser.

4. Click Edit to activate edit mode.

5. Click Find to perform a basic search and replace.

Note: You can expand the Edit pane by clicking the Full Screen icon in the upper right of the pane, beneath the Create button.

Compiling a Package

If you edit and make changes to a package, you need to compile in order to save your changes. There is no save function because this is just a view of the object within the database.

Click Compile to compile the current package. Compiling re-creates the object in the database. If the compile fails, an error message display above the code.

Downloading a Package

Click Download to save the current package as a file.

Dropping a Package

Click Drop to delete the current package.

Managing Procedures

A procedure is a subprogram that performs a specific action. You can use Object Browser to view, create, edit, download, and drop procedures.

Topics in this section include:

• Creating a Procedure

• Browsing a Procedure

• Editing a Procedure

• Compiling a Procedure

• Downloading a Procedure

• Dropping a Procedure

See Also: SQL*Plus User's Guide and Reference for information about PL/SQL procedures

Creating a Procedure

To create a procedure:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Procedures.

4. Enter a procedure name and click Next.

5. Define the arguments by specifying the following information (optional):

   • Argument Name

   • In/Out (the parameter mode)

   • Argument Type (datatype)

   • Default (value)

   To add additional arguments, click Add Argument.

6. Click Next.

7. Enter PL/SQL block you want to use as the procedure body and click Next.

   To view the previously defined arguments, click Defined Arguments.

   A confirmation page appears. To view the SQL used to create the procedure, click Show SQL.

8. Click Finish.

Browsing a Procedure

To browse a procedure:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Procedures.

3. From the Object Selection pane, select a procedure.

   The Code view appears, displaying the source code for the procedure. You can copy the code in this view for use in other tools.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the procedure. Table 18-8 describes all available views.

Table 18-8 Available Views for Procedures

View	Description
Code	(Default) Displays the source code for the procedure. You can copy the code in this view for use in other tools. Actions you can perform in this view include: Edit Compile Download Drop Find See Also: "Editing a Procedure", "Compiling a Procedure", "Downloading a Procedure", and "Dropping a Procedure"
Dependencies	Displays objects that use (or depend) on the current procedure and objects the procedure depends on.
Errors	Lists errors related to the current procedure.
Grants	Lists details of grants for the current procedure, including privilege, grantee, grantable, grantor, and object name.

Editing a Procedure

When you edit a procedure you can edit the code manually or perform a search and replace.

Editing a Procedure Manually

To edit a procedure manually:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Procedures.

3. From the Object Selection pane, select a procedure.

   The Code view appears. Be default, you can copy the code in this view for use in other tools.

4. Click Edit to activate edit mode.

5. Click Find to perform a basic search and replace.

Note: You can expand the Edit pane by clicking the Full Screen icon in the upper right of the pane, beneath the Create button.

Compiling a Procedure

If you edit and make changes to a procedure, you need to compile in order to save your changes. There is no save function because this is just a view of the object within the database.

Click Compile to compile the current procedure. Compiling re-creates the object in the database. If the compile fails, an error message display above the code.

Downloading a Procedure

Click Download to save the current procedure as a file.

Dropping a Procedure

Click Drop to delete the current procedure.

Managing Functions

A function is a subprogram that can take parameters and return a single value.

Topics in this section include:

• Creating a Function

• Browsing a Function

• Editing a Function

• Compiling a Function

• Downloading a Function

• Dropping a Function

See Also: SQL*Plus User's Guide and Reference for information about PL/SQL functions

Creating a Function

To create a function:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Functions.

4. Enter a function name, specify the return datatype, and click Next.

5. Define the arguments and click Next (optional):

   • Argument Name

   • Argument Type (datatype)

   • Default (value)

   To add additional arguments, click Add Argument.

6. Enter P/LSQL block you want to use as the function body and click Next.

   To link to the SQL Command Processor, click Command Processor. To view the previously defined arguments, click Defined Arguments.

   A confirmation page appears. To view the SQL used to create the function, click Show SQL.

7. Click Finish.

Browsing a Function

To view a function in Object Browser:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Functions.

3. From the Object Selection pane, select a function.

   The Code view appears. You can copy the code in this view for use in other tools.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the function. Table 18-9 describes all available views.

Table 18-9 Available Views for Functions

View	Description
Code	(Default) Displays the source code for the function. You can copy the code in this view for use in other tools. Actions you can perform in this view include: Edit Compile Download Drop Find See Also: "Editing a Function", "Compiling a Function", "Downloading a Function", and "Dropping a Function"
Dependencies	Displays objects that use (or depend) on the current function and objects the function depends on.
Errors	Displays errors related to the current function.
Grants	Lists details of grants for the current function, including privilege, grantee, grantable, grantor, and object name.

Editing a Function

When you edit a function you can edit the code manually, perform a search and replace, and compile the function.

Editing a Function Manually

To edit a function manually:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Functions.

3. From the Object Selection pane, select a function.

   The Code view appears. Be default, you can copy code from this view for use in other tools.

4. Click Edit to activate manual edit mode.

5. Click Find to perform a basic search and replace.

Note: You can expand the Edit pane by clicking the Full Screen icon in the upper right of the pane, beneath the Create button.

Compiling a Function

If you edit and make changes to a function, you need to compile in order to save your changes. There is no save function because this is just a view of the object within the database.

Click Compile to compile the current function. Compiling re-creates the object in the database. If the compile fails, an error message display above the code.

Downloading a Function

Click Download to save the current function as a file.

Dropping a Function

Click Drop to delete the current function.

Managing Triggers

A database trigger is a stored subprogram associated with a database table, view, or event. The trigger can be called once, for example when an event occurs, or many times, for example for each row affected by an INSERT, UPDATE, or DELETE statement.

Topics in this section include:

• Creating Triggers

• Browsing a Trigger

• Editing a Trigger

• Compiling a Trigger

• Downloading a Trigger

• Dropping a Trigger

See Also: Oracle Database Concepts and SQL*Plus User's Guide and Reference for information about triggers

Creating Triggers

To create a trigger:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Trigger.

4. Select a table name and click Next.

5. Select the appropriate trigger attributes, enter the trigger body, and click Next.

   A confirmation page appears. To view the SQL used to create the trigger, click SQL.

6. Click Finish.

Browsing a Trigger

To browse a trigger in Object Browser:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Triggers.

3. From the Object Selection pane, select a trigger.

   The Details view appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the trigger. Table 18-10 describes all available views.

Table 18-10 Available Views for Triggers

View	Description
Object Details	(Default) Lists of the details about the current trigger. Actions you can perform include: Compile Disable Download Drop Code See Also: "Editing a Trigger", "Compiling a Trigger", "Downloading a Trigger", and "Dropping a Trigger"
Errors	Displays errors related to the current trigger.
SQL	Displays the SQL necessary to re-create the trigger.

Editing a Trigger

When you edit a trigger you can edit the code manually, perform a search and replace, and compile the trigger.

Editing a Trigger Manually

To edit a trigger manually:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Triggers.

3. From the Object Selection pane, select a trigger.

4. Select the Code tab and then click Edit to activate manual edit mode.

5. Click Find to perform a basic search and replace.

Note: You can expand the Edit pane by clicking the Full Screen icon in the upper right of the pane, beneath the Create button.

Compiling a Trigger

If you edit and make changes to a function, you need to compile in order to save your changes. There is no save function because this is just a view of the object within the database.

Click Compile to compile the current trigger. Compiling re-creates the object in the database. If the compile fails, an error message display above the code.

Downloading a Trigger

Click Download to save the current trigger as a file.

Dropping a Trigger

Click Drop to delete the current trigger.

Managing Database Links

A database link is a schema object in one database that enables you to access objects in another database. Once you create a database link, you can access the remote objects by appending @dblink to the table or view name, where dblink is the name of the database link.

Topics in this section include:

• Creating a Database Link

• Browsing a Database Link

Creating a Database Link

To create a database link:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Database Link.

4. Specify the following information and click Next.

   • Database Link Name

   • Connect To Schema

   • Password

   • Remote Hostname or IP

   • Remove Host Port

   • SID or Service Name

   A confirmation page appears.

5. To view the SQL used to create the database link, click Show SQL.

6. Click Create.

Browsing a Database Link

To browse a database link:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Database Links.

3. From the Object Selection pane, select a database link.

   The Object Details View appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the database link. Table 18-11 describes all available views.

Table 18-11 Available Views for Database Links

View	Description
Object Details	(Default) Displays details about the database link. Actions you can perform include: Drop - Deletes the database link Test - Tests the database link
Dependencies	Displays a list of objects that use (or depend) upon this database link.
SQL	Displays the SQL necessary to re-create this database link.

Managing Materialized Views

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base tables or in a different database.

Topics in this section include:

• Creating a Materialized View

• Browsing a Materialized View

See Also: Oracle Database Concepts for information about materialized views

Creating a Materialized View

To create a materialized view:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Materialized View.

4. Define the materialized view:

   1. Materialized View Name - Enter a name.

   2. Query - Specify a query to define the view.

      To access Query Builder or SQL Command Processor, click the appropriate link at the bottom of the page. The selected tool appears in a pop-up window. Once you generate the appropriate SQL, click Return to automatically close the popup window and return to the wizard with the SQL.

   3. Click Next.

      A confirmation page appears. To view the SQL used to create the materialized view, click SQL.

5. Click Create.

See Also: "Building Queries with Query Builder" and "Using SQL Command Processor"

Browsing a Materialized View

To view a materialized view:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Materialized Views.

3. From the Object Selection pane, select a view.

   The Materialized View appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the materialized view. Table 18-12 describes all available views.

Table 18-12 Available Views for Materialized View

View	Description
Materialized View	(Default) Displays details about the columns in the materialized view, including: Column Name Data type Nullable flag Default value Primary key Click Drop to delete the current materialized view.
Data	Displays a report of the data in the columns. Actions you can perform include: Query - Enables you to sort by column. To restrict specific rows, enter a condition in the Column Condition field. Use the percent sign (%) for wildcards. From Order by, select the columns you want to review and click Query. Count Rows - Displays a report of the data in the current table.
Details	Displays object details stored in DBA_SNAPSHOTS such as updatable and status.
Grants	Displays a list of grants on the current view, including grantee, privilege, and grant options. Actions you can perform in this view include Grant and Revoke.
Dependencies	Displays a list of objects that use (or depend) upon this materialized view.
SQL	Displays the SQL necessary to re-create this materialized view.

Managing Synonyms

A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.

Topics in this section include:

• Creating Synonyms

• Viewing a Synonym

• Dropping a Synonym

See Also: Oracle Database Administrator's Guide for information about synonyms

Creating Synonyms

To create a synonym:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. Click Create.

3. From the list of object types, select Synonym.

4. Define the synonym:

   1. Synonym Name - Enter a name.

   2. Public or Private - Specify whether the synonym should be public or private.

   3. Schema - Select the database schema (or username) which owns the object upon which you want to create your synonym.

   4. Object - Enter the name of the object upon which you want to create a synonym.

   5. Database Link - Enter the name of the database link to use if the synonym is to be create on a remote object.

   6. Click Next.

   A confirmation page appears. To view the SQL used to create the synonym, click Show SQL.

5. Click Finish.

See Also: "Managing Synonyms"

Viewing a Synonym

To view a synonym:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Synonyms.

3. From the Object Selection pane, select a synonym.

   The Object Details view appears displaying the following:

   • Synonym owner

   • Synonym name

   • Object owner

   • Object Name

   • Object Status

   • Status

Dropping a Synonym

To view drop a synonym:

1. Navigate to Object Browser:

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

   2. Click Object Browser.

      Object Browser appears.

2. From the Object list, select Synonyms.

3. From the Object Selection pane, select a synonym.

4. Click Drop.