Working with Oracle Databases

The Microsoft^® Visual Database Tools have been designed to work with Oracle databases transparently — you can design database diagrams, queries, and views in the same way you would for any database. In addition, the tools are Oracle-aware, allowing you to incorporate Oracle data objects such as synonyms, produce Oracle-specific SQL commands, and so on.

In a few instances, however, you will find it useful to know how the Visual Database Tools differ when you are working with Oracle databases. For example, it is helpful to understand how the Query Designer will work with case-sensitive database object names in Oracle.

The topics listed in the following table provide information specific to using the Database Designer and Query Designer with Oracle databases.

Note   These topics are not intended to explain Oracle databases. Instead, they highlight features unique to Oracle that are exposed through the Visual Database Tools. For more details about using Oracle databases, consult your Oracle database documentation.

Data View for Oracle Databases

For Oracle databases, you will see some differences in the Project Explorer (FileView in Microsoft^® Visual C++^®) and Data View windows. For example, when you add a connection to an Oracle database, you will see these folders under the connection: Database Diagrams, Tables, Views, Synonyms, Stored Procedures, and Functions.

The Tables folder contains the base tables in your database. The Views folder contains any SELECT statements saved as views.

Synonyms

The Synonyms folder contains synonyms in your database, which are names assigned to tables or views that may thereafter be used to refer to them. You can also create new synonyms.

To create a new synonym

• From a query or SQL script, execute the following statement:

  create synonym name
  for table
  

  Substitute the name of the new synonym for name and the name of a table for table.

For more details about synonyms, refer to your Oracle documentation.

Functions

The Functions folder contains the functions in your database. For details about functions, refer to your Oracle documentation.

Stored Procedures

The Stored Procedures folder contains stored procedures. For details about working with stored procedures in the Visual Database Tools, see Stored Procedures.

Triggers

When you add a new trigger to an Oracle database from Data View, a template for the new trigger is displayed in the editor, as shown below. This template adheres to correct Oracle syntax for triggers (just as new triggers added for SQL Server databases do). For details about working with triggers in the Visual Database Tools, see Triggers.

The new trigger template is:

CREATE TRIGGER TABLENAME_TRIGGERx
/* BEFORE | AFTER */
/* DELETE | INSERT | UPDATE [OF COLUMN] */
ON TABLENAME
/* REFERENCING OLD AS OLD | NEW AS NEW */
/* FOR EACH ROW */
/* WHEN (CONDITION) */

/*    DECLARE */
/*         VARIABLE DATATYPE; */

BEGIN
END;