16 Managing Database Objects with Object Browser

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.

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 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 For and Selecting Database Objects

• Hiding the Object Selection Pane

• Using the Find Tables Icon

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.

Description of the illustration o_brws_hide.gif

Using the Find Tables Icon

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

Description of the illustration wrkshp_find_tables.gif

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 checkbox 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, and scale as well as the SQL necessary to re-create the table appears at the bottom of the page.

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

• Dropping a Table

Creating a Table

To create a new 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 one of the following 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 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.

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

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

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

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

   
   

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.

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

Creating a View

To create a new 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 need to 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.

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

• Dropping an Index

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, and table type as well as 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.

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

• Dropping a Sequence

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.

Managing Types

A type is a user-specified object or collection definition. Oracle Application Express 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

• Dropping a 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

• Downloading a Package

• Dropping a Package

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

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.

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

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

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.

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

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

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

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.

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

• Dropping a 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 MaterializedView

• Dropping a 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