1 Managing Database Objects with Object Browser

Accessing Object Browser

To access Object Browser:

1. Log in to the Workspace home page.

2. Click SQL Workshop.

3. To view Object Browser you can either:

   • Click SQL Workshop and then Object Browser to drill-down to the Object Browser.

   • Click the down arrow on the right side of the SQL Workshop icon to view a drop down menu. Then select the Object Browser menu option.

Searching For and Selecting Database Objects

To search for a database object in the Object Selection pane:

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

   Object Browser appears.

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

   
   Description of the illustration o_brws_srch.gif
   
   

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

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

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.

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.

Using the Find Tables Icon

The Find Tables icon resembles a flashlight. Click this icon to view tables within the currently selected schema.

To view tables within the current schema:

1. Navigate to either Object Browser or SQL Commands.

2. From the Schema list, select a schema (optional).

3. Click the Find Tables icon.

   The Table Finder appears. A search bar displaying the selected schema displays at the top of the page and contains the following controls:

   • Search. Search for a table name. Enter case insensitive keywords in the Search field and click Go. To view all tables, leave the Search field blank and click Go.

   • Rows. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the list and click Go.

   • Views. Select the Views check box and click Go to include views in the resulting report.

   The Table Finder report appears displaying the table name, the number of rows, last analyzed date, and the object type.

4. Select a table name.

   
   Description of the illustration wrkshp_view_table.gif
   
   

   A table definition appears on the right side of the page.

   This report displays the column names, data type, length, precision, scale and the SQL necessary to re-create the table that appears at the bottom of the page.

Creating a Table

To create a table:

1. On the Workspace home page, click SQL Workshop and then 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.

   5. Click Next.

   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 the appropriate option and click Next:

   • No Primary Key - No primary key is created.

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

   • Populated 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 populated - 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 multiple columns).

   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, then click the Add icon to move them to Key Column(s).

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

   4. Select the appropriate option:

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

      • 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 it is not necessary 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.

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. On the Workspace home page, click SQL Workshop and then 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.

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. On the Workspace home page, click SQL Workshop and then 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 1-2.

   Table 1-2 Edit Table Buttons

   Button	Description
   Add Column	Adds a column to the table. Enter a column name and select a type. Depending upon the column type, specify whether the column requires a value, 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.

   
   

Dropping a Table

To drop a table:

1. On the Workspace home page, click SQL Workshop and then 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 Drop.

Creating a View

To create a view:

1. On the Workspace home page, click SQL Workshop and then 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.

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. On the Workspace home page, click SQL Workshop and then 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.

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.

Compiling a View

If you edit and make changes to a view, you must compile 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

To drop a view:

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

   Object Browser appears.

2. From the Object list, select Views.

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

4. Select the View tab or the Code tab.

5. Click Drop to delete the current view.

Creating an Index

To create an index:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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, table type and a listing of indexed columns.

Dropping an Index

To drop an index:

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

   Object Browser appears.

2. From the Object list, select Indexes.

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

4. Under Object Details, click the Drop tab.

Creating a Sequence

To create a sequence:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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.

Dropping a Sequence

To drop a sequence:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Drop.

Creating a Type

To create a collection type:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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.

Dropping a Type

To drop a collection type:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Drop.

Creating a Package

To create a package:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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.

Editing a Package

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

Compiling a Package

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

To compile a package:

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

   Object Browser appears.

2. From the Object list, select Packages.

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

   The Specification appears.

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

To download a package:

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

   Object Browser appears.

2. From the Object list, select Packages.

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

   The Specification appears.

4. Click Download to save the current package as a file.

Dropping a Package

To drop a package while viewing the Specification:

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

   Object Browser appears.

2. From the Object list, select Packages.

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

   The Specification appears.

4. Click Drop to delete the current package.

To drop a package while viewing the Body:

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

   Object Browser appears.

2. From the Object list, select Packages.

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

   The Specification appears.

4. Click the Body tab.

5. Click Drop.

Creating a Procedure

To create a procedure:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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.

Editing a Procedure

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

Compiling a Procedure

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

To compile a procedure:

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

   Object Browser appears.

2. From the Object list, select Procedures.

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

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

To download a procedure:

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

   Object Browser appears.

2. From the Object list, select Procedures.

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

4. Click Download to save the current procedure as a file.

Dropping a Procedure

To drop a procedure:

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

   Object Browser appears.

2. From the Object list, select Procedures.

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

4. Click Drop to delete the current procedure.

Creating a Function

To create a function:

1. On the Workspace home page, click SQL Workshop and then 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 SQL Commands, 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. On the Workspace home page, click SQL Workshop and then 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.

Editing a Function

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

Compiling a Function

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

To compile a function in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

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

To save a function to a file in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Download to save the current function as a file.

Dropping a Function

To drop a function in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Drop to delete the current function.

Creating Triggers

To create a trigger in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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.

Editing a Trigger

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

Compiling a Trigger

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

To compile a trigger in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

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

To save the current trigger as a file:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Download to save the current trigger as a file.

Dropping a Trigger

To save drop a trigger in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Drop to delete the current trigger.

Creating a Database Link

To create a database link:

1. On the Workspace home page, click SQL Workshop and then 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. On the Workspace home page, click SQL Workshop and then 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.

Dropping a Database Link

To drop a database link in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Drop.

Creating a Materialized View

To create a materialized view:

1. On the Workspace home page, click SQL Workshop and then 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.

Browsing a Materialized View

To view a materialized view:

1. On the Workspace home page, click SQL Workshop and then 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.

Dropping a Materialized View

To drop a materialized view in Object Browser:

1. On the Workspace home page, click SQL Workshop and then 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.

4. Click Drop.

Creating Synonyms

To create a synonym:

1. On the Workspace home page, click SQL Workshop and then 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 user name) 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.

Viewing a Synonym

To view a synonym:

1. On the Workspace home page, click SQL Workshop and then 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 drop a synonym in Object Browser:

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

   Object Browser appears.

2. From the Object list, select Synonyms.

3. From the Object Selection pane, select a synonym and click Drop.