1. SQL Developer for VS Code
  2. Using SQL Developer for VS Code
  3. Working with Database Objects

3.1 Working with Database Objects

The SQL Developer for VS code interface uses the left side for navigation to find and select objects, and the right side to display information about selected objects.

In the Connections panel, you can browse, edit and delete objects.

To search for an object within an object type, for example, a specific table, click Tables and type the name of the object. Even as you type, objects matching the letters entered are highlighted.

Click a database object to open it in the right panel. A tabular display of the object's properties are displayed. In the following figure, click the SQL tab to see the corresponding SQL source code for the COUNTRIES table.

Description of database_object.png follows
Description of the illustration database_object.png

The object types available are:

3.1.1 Tables

You can perform the following operations on a table by right-clicking the table name in the Connections navigator and selecting an item from the menu:

  • Open: Opens the table in the right panel.

  • Table: Table actions include Rename, Copy (create a copy using a different name), Drop (delete the table), Truncate (delete existing data without affecting the table definition), Lock (set the table lock mode: row share, exclusive, and so on), Comment (descriptive comment explaining the use or purpose of the table), Parallel (change the default degree of parallelism for queries and DML on the table), No Parallel (specify serial execution), Count Rows (return the number of rows), and Generate Table API (generate statements for a PL/SQL procedure to perform operations on the table).

  • Column: Column actions include Comment (descriptive comment about a column), Add, Drop, Rename and Normalize.

  • Index: Options include Create (create an index on specified columns), Create Text (create an Oracle Text index on a column), Create Text (create a function-based index on a column), and Drop.

  • Constraint: Options include Enable or Disable Single, Drop (delete a constraint), Add Check (add a check constraint), Add Foreign Key, and Add Unique.

  • Statistics: Options include Gather Statistics (compute exact table and column statistics and store them in the data dictionary) and Validate Structure (verifies the integrity of each data block and row, and for an index-organized table also generates the optimal prefix compression count for the primary key index on the table). Statistics are used by the Oracle Database optimizer to choose the execution plan for SQL statements that access analyzed objects.

  • Storage: Options include Shrink Table (shrink space in a table, for segments in tablespaces with automatic segment management) and Move Table (to another tablespace). The Shrink Table options include Compact (only defragments the segment space and compacts the table rows for subsequent release, but does not readjust the high water mark and does not release the space immediately) and Cascade (performs the same operations on all dependent objects of the table, including secondary indexes on index-organized tables).

  • Trigger: Options include Create, Create PK from Sequence (create a before-insert trigger to populate the primary key using values from a specified sequence), Enable or Disable All, Enable or Disable Single, and Drop (delete the trigger).

You can perform the following operations on a column in a table by right-clicking the column name in the Connections navigator and selecting an item from the menu:

  • Rename: Renames the column.

  • Drop: Deletes the column (including all data in that column) from the table.

  • Comment: Adds a descriptive comment about the column.

  • Encrypt (for Oracle Database Release 10.2 and higher, and only if the Transparent Data Encryption feature is enabled for the database): Displays a dialog box in which you specify a supported encryption algorithm to be used for encrypting all data in the column. Current data and subsequently inserted data are encrypted.

  • Decrypt (for Oracle Database Release 10.2 and higher, and only if the Transparent Data Encryption feature is enabled for the database): Decrypts data in the column that had been encrypted, and causes data that is subsequently inserted not to be encrypted.

  • Normalize: Creates a new table using the distinct values in the specified column. You must specify names for the new table and its primary key column, as well as a sequence name and trigger name.

3.1.2 Views

You can perform the following operations on a view by right-clicking the view name in the Connections panel and selecting an item from the menu:
  • Open: Opens the view in the right pane.
  • Rename: Renames the view.
  • Drop: Deletes the view.
  • Compile: Recompiles the view, to enable you to locate possible errors before runtime. You may want to recompile a view after altering one of its base tables to ensure that the change does not affect the view or other objects that depend on it.

To create a view:

  1. Right-click the Views node and select Create (simple).
  2. Enter View Name and SQL Query.
  3. Click Apply.

3.1.3 Indexes

You can perform the following operations on an index by right-clicking the index name in the Connections navigator and selecting an item from the menu:

  • Open: Opens the index in the right pane.

  • Drop: Deletes the index.

  • Rebuild: Recreates the index or one of its partitions or subpartitions. If the index is unusable, a successful rebuild operation makes the index usable. For a function-based index, rebuilding also enables the index; however, if the function on which the index is based does not exist, the rebuild operation fails.

  • Rename: Changes the name of the index.

  • Make Unusable: Prevents the index from being used by Oracle in executing queries. An unusable index must be rebuilt, or dropped and re-created, before it can be used again.

  • Coalesce: Merges the contents of index blocks, where possible, to free blocks for reuse.

  • Compute Statistics: For a function-based index, collects statistics on both the index and its base table using the DBMS_STATS package. Such statistics will enable Oracle Database to correctly decide when to use the index.

  • Rebuild Index Partition: Rebuilds a specified index partition.

  • Drop Index Partition: Drops (deletes) a specified index partition.

3.1.4 Functions

You can perform the following operations on a function by right-clicking the function name in the Connections navigator and selecting an item from the menu:

  • Open: Opens the function in the right pane.

  • Drop: Deletes the function.

  • Grant: Enables you to grant available privileges on the function to selected users.

  • Revoke: Enables you to revoke available privileges on the function from selected users.

3.1.5 Packages

You can perform the following operations on a package by right-clicking the package name in the Connections navigator and selecting an item from the menu:

  • Open: Opens the package in the right pane.

  • Run: Lets you select a member in the package and run it.

  • Drop Package: Deletes the package.

3.1.6 Procedures

You can perform the following operations on a procedure by right-clicking the procedure name in the Connections navigator and selecting an item from the menu:

  • Open: Displays the procedure text so that you can view and edit it.

  • Run: Displays the PL/SQL dialog box, and then executes the procedure.

  • Drop: Deletes the procedure.

3.1.7 Triggers

Triggers are stored PL/SQL blocks associated with a table, a schema, or the database, or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java. Oracle Database automatically executes a trigger when specified conditions occur.

See Oracle Database 2 Day Developer's Guide

3.1.8 Types

A data type associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a function or procedure. These properties cause Oracle Database to treat values of one data type differently from values of another data type. Most data types are supplied by Oracle, although users can create data types.

See Oracle Database Concepts

3.1.9 Sequences

Sequences are used to generate unique integers. You can use sequences to automatically generate primary key values.

See Oracle Database 2 Day Developer's Guide

3.1.10 Synonyms (Public and Private)

Synonyms provide alternative names for tables, views, sequences, procedures, stored functions, packages, materialized views, Java class database objects, user-defined object types, or other synonyms. The Connections navigator has a Synonyms node for all synonyms (public and private) owned by the user associated with the specified connection, and a Public Synonyms node for all public synonyms on the database associated with the connection.

See Oracle Database 2 Day Developer's Guide

3.1.11 Other Users

Database users are accounts through which you can log in to the database. In the Connections navigator, you can see the Other Users in the database associated with a connection, but the objects that you are allowed to see for each user are determined by the privileges of the database user associated with the current database connection.