The Configure Oracle Connection window is displayed to set up the connection string for a Oracle database server.

Before you can configure an Oracle database connection, the Oracle Data Provider for the .NET 12.1.0.2.1 framework or a newer version is required. If this is not installed, you are warned and you cannot add an Oracle database.

You can configure an Oracle connection that connects to a table, view, store procedure, or a private synonym.

In order to use a private synonym, the following permissions are needed:

SQL Permissions

The user consuming the private synonym must have permissions to set the underlying object from the synonym.

• If the object is a table or view, the "Select" permissions is required.

• If the object is a stored procedure, the "Execute" permissions is required.

Limits

Synonyms have the following limitations:

• Only private synonyms are supported.

• Synonyms are supported for the following objects only:

  • Table

  • View

  • Stored Procedure

SQL Commands

The user running the command requires appropriate privileges to perform operations.

• For a synonym on a table or view:

  • Create synonym.

    CREATE OR REPLACE EDITIONABLE SYNONYM "USER2"."SYNONYMNAME" FOR "USER1"."MYTABLE";

  • Give privilege.

    grant SELECT on "USER1"."MYTABLE" to "USER2" ;

• For synonym on a stored procedure:

  • Create synonym.

    CREATE OR REPLACE EDITIONABLE SYNONYM "USER2"."SYNONYMNAME" FOR "USER1"."SP_MYSTOREPROCEDURE";

  • Give privilege.

    grant EXECUTE on "USER1"."SP_MYSTOREPROCEDURE" to "USER2";

You can configure an Oracle database connection by following these steps: