Connecting an Oracle Database Using an ODBC Connection

Worksoft Certify supports connecting to third-party databases using Open Database Connectivity (ODBC) connection. This topic shows you how to

 

connect to an Oracle® database from a Certify process and get the Oracle data using a query.

Prerequisites

Each Certify machine need to have the following installed:

• Oracle 32-bit client
• ODBC 32-bit driver
• Accessible Oracle database server and database

• Create a Windows environment variable OCI_LIB32 and provide the Oracle client installation path as a value.

  

Testing the Oracle Database Connection

There are two ways to connect:

• Basic mode
• TNS mode

To test the Oracle database connection in Basic mode:

Provide the database connection details in the Select Database Connection dialog.

You can use Service name or SID.

To test the Oracle database connection in TNS mode:

1. Create or edit the tnsnames.ora file under the Oracle Client installation path and add the following entry:

   

2. Open Oracle SQL Developer.

3. From the Oracle SQL Developer menu, select Tools > Preferences.

   The Preferences dialog opens.

   

4. From the Navigation pane, select Database >Advanced.

5. Enter your information in the following fields:
6. Use Oracle Client
7. Tnsnames Directory

6. In the New/Select Database Connection dialog, enter the following database connection values:

   

7. Test the database connection.

   If the test connection fails, consult your database administrator.

Creating an ODBC Connection

This section illustrates how to create an ODBC connection in both the following modes:

• Basic mode
• TNS mode

To create an ODBC connection in Basic mode:

1. From the Windows Control Panel, open ODBC Data Sources (32-bit).

2. Click the Add button.

   The Oracle ODBC Driver Configuration dialog opens.

   

3. Enter the following information:
4. Data source name
5. TNS service name
6. User ID

4. Click the Test Connection button.

   The Oracle ODBC Driver Connect dialog opens.

   

5. Enter the user name and password.

6. Click OK.

   If the connection is successful, a message appears confirming the connection. Without a successful ODBC connection, Worksoft Certify cannot connect to the Oracle database.

7. Click OK.

To create an ODBC connection in TNS mode:

1. Open the Windows Control Panel and select System.

   The System dialog opens.

2. Click Advanced System Settings.

   The System Properties dialog opens.

3. Click Environment Variables.

   The Environment Variables dialog opens.

4. Click New to create a new variable.

   The Edit System Variable dialog opens.

   

5. In the Variable name field, enter TNS_ADMIN.

6. In the Variable value field, enter the Oracle Client installation directory.

7. Click OK.

8. In the Environment Variables dialog, select Path in the System variables section.

   

9. Click Edit.

   The Edit environment variable dialog opens.

10. Click New.

11. In the Variables pane, enter the variable.

12. Click OK.

13. From the Windows Control Panel, open ODBC Data Sources (32-bit).

14. Click the Add button.

    The Oracle ODBC Driver Configuration dialog opens.

    

15. Enter the following information:
16. Data source name
17. TNS service name
18. User ID

16. Click the Test Connection button.

    The Oracle ODBC Driver Connect dialog opens.

    

17. Enter the user name and password.

18. Click OK.

    If the connection is successful, a message appears confirming the connection. Without a successful ODBC connection, Worksoft Certify cannot connect to the Oracle database.

19. Click OK.

Connecting an Oracle Database to a Certify Process

To connect an Oracle database to a Certify process, you need to add ODBC System actions to your process.

Use the Database Store or Database Verify actions. Enter values for the following parameters:

• ODBC Connection- Data source name provided in the ODBC driver configuration
• ODBC UserName - Database user name
• ODBC Password - Database password

 

Related Topics