Connecting to a Database

Last modified: March 14, 2025

Before you can connect to a database, you must create a server connection.

dbForge Data Generator for Oracle allows you to establish a connection between a database and a server using the Database Connection Properties dialog.

Creating a connection

To create a connection to the server:

1. On the Database menu, select New Connection to open the Database Connection Properties dialog.

2. On the General tab of the dialog, select the connection type that defines how the database connection is established and specify the general connection details.

Note

The connection details you need to enter vary based on the selected connection type.

The following table describes connection parameters for the Direct connection type.

Parameter	Description
Host	Server or host name where the Oracle database is running.
Port	Port number. The default port number is 1521.
Service Name/SID	Logical database service to connect to an Oracle database.
User and Password	User credential details. By default, the password is saved automatically. If you don’t want to save the password, clear the Allow saving password checkbox.

The following table describes connection parameters for the TNS (Transparent Network Substrate) connection type.

Parameter	Description
Server	Server name where the Oracle database is running.
User and Password	User credential details. By default, the password is saved automatically. If you don’t want to save the password, clear the Allow saving password checkbox.
Home	Oracle client home directory. The default option is Instant Client.

The following table describes connection parameters for the TNS (using OS Authentication) connection type.

Parameter	Description
Server	Server name where the Oracle database is running.
User and Password	User and password are inherited from the current OS user.
Home	Oracle client home directory. The default option is Instant Client.

3. In the Connect as list, select a role with which you want to connect to the server. The available roles include Normal (default), SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM. To show all schema objects, select the Show all objects checkbox.

4. In the Connection Name box, enter a name for the server you want to connect to. By default, the host name is used. If you want to specify a different name, enter it here. This setting is optional.

5. In the Environment Category list, select the environment category from the list, such as Development, Production, Sandbox, and Test. This setting is optional. To customize a connection category, see Windows connection coloring.

6. Optional: Switch to the Advanced tab to configure advanced connection properties:

• DBMS output options
  • Enable DBMS output - Control whether to capture and display output messages generated by the DBMS_OUTPUT package. By default, this option is turned on. If you don’t want to display output messages, clear the checkbox.
  • Buffer size - Specify the buffer size in bytes. The default value is 20000 bytes.
• Timeouts
  • Connection timeout - Set the time (in seconds) when the server should wait to establish a connection. The default value is 0 seconds.
  • Execute timeout - Set the time (in seconds) when the task should be completed. The default value is 0 seconds.
• Show PL/SQL compilation warnings - Select the checkbox to display warnings about potential PL/SQL code issues, such as performance concerns or syntax issues, during compilation.
• Use Unicode - Handle multilingual and unique character data correctly. By default, the option is turned on. To turn off it, clear the checkbox.

7. Optional: To verify the connection, select Test Connection. The window with the message informing about successful connection appears. Select OK to close the window.

8. To create the connection to the server, select Connect.

Connection Types

Direct Connection

Direct mode does not require an Oracle Client to be installed on your workstation. dbForge Data Generator for Oracle talks to the Oracle server directly, no third party libraries are required.

You can connect to a multi-threaded server using Direct mode. The server must be configured to use a specific port and TTC protocol. This can help you avoid firewall conflicts.

Direct mode advantages:

• Installation and administration of an Oracle Client are not required.
• System requirements are reduced.

Direct mode restrictions:

• Database export and import operations with Export and Import Utility wizards are not supported.
• Oracle RAC (Real Application Clusters) is not supported.
• OS authentication feature is not available.
• TCP/IP protocol connection only.

Note

You can return to a connection via a TNS at any time if restrictions above become critical for you.

Connection via the TNS

The TNS connection type is an appropriate option in any of the following circumstances:

• You have an Oracle client installed on your machine.
• You have access to many Oracle Database instances.
• You do not know the machine details of the system hosting the Oracle Database instance you want to connect to.

A TNS connection uses an alias entry from a tnsnames.ora file. dbForge Data Generator for Oracle uses only one tnsnames.ora file. You may have more than one on your local machine or want to use the tnsnames.ora file on a remote machine, so note that dbForge Data Generator for Oracle looks sequentially for the tnsnames.ora file in the following locations:

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. /etc/tnsnames.ora (non-Windows systems)
4. $ORACLE_HOME/network/admin/tnsnames.ora
5. Registry key

You need to create the TNS_ADMIN environment variable in the case when the tnsnames.ora file exists; but dbForge Data Generator for Oracle doesn’t use it.

Note

dbForge Data Generator for Oracle uses connection via a TNS by default.

Connection via TNS Using OS Authentication

With OS authentication, Oracle Database uses a database user’s OS login credentials to authenticate that user. The user doesn’t provide a username or password to access the database, and Oracle Database doesn’t store and manage the account password. Local OS authentication can be used when the client and the database server are on the same machine. Remote OS authentication is possible but is not considered secure.