This document focuses on TIBCO Scribe® Insight and using the Scribe 6.1/7.1 Oracle Wire Protocol ODBC drivers.

Note: For TIBCO Scribe® Online see TIBCO Scribe® Online Connector For Oracle Database in the Online Help.

Older versions of Oracle (e.g. 8.x) do not work with TIBCO Scribe® Insight if it is installed in the …\Program Files (x86) folder on 64-bit servers. In these cases, install TIBCO Scribe® Insight in a folder tree that does not contain any parenthesis.

• The Scribe 6.1/7.1 Oracle Wire Protocol driver should be used in any TIBCO Scribe® Insight DTS.
• TIBCO Scribe® Insight only supports 32-bit ODBC Drivers.
• The Oracle 32-bit Client must be installed on the TIBCO Scribe® Insight server.
• The tnsnames.ora file on the TIBCO Scribe® Insight server must have the proper connection information. Either modify the file installed by the Oracle client or you may be able to copy a file from another Windows server that has the proper information. The tnsnames.ora is usually in the …\ network\admin folder such as  C:\Oracle\app\MSMITH\product\11.2.0\client_1\network\admin

Example tnsnames.ora file using Oracle Express



DSN Connection Settings

There are two types of settings that can be used – Standard and TNSNames File.

The difference is where the tnsnames.ora file is referenced. To point to the file on a remote server, use the Standard connection. If the tnsnames.ora file resides on the Scribe server, use the TNSNames File connection.
In either case a system variable needs to be created so that the tnsnames.ora file can be found on the remote or TIBCO Scribe® Insight server depending on the connection method used. In this example, a system variable named TNS_ADMIN points to the folder where tnsnames.ora resides.



System ODBC DSN Utilizing the Standard Connection

Specify the Host, which is the Oracle server, and the Service name. Port is optional.



A connection can also be made by specifying the Host, Port and SID values from the appropriate entry in the TNSNames.ora file.


System ODBC DSN Using the TNSNames Connection
 
 

Specifies the name of the TNSNAMES.ORA file. In a TNSNAMES.ORA file, connection information for Oracle services is associated with an Oracle net service name. The entry in the TNSNAMES.ORA file specifies Host, Port Number, and Service Name or SID.
 
TNSNames File is ignored if no value is specified in the Server Name option. If the Server Name option is specified but the TNSNames File option is left blank, the TNS_ADMIN environment setting is used for the TNSNAMES.ORA file path. If there is no TNS_ADMIN setting, the ORACLE_HOME environment setting is used. On Windows, if ORACLE_HOME is not set, the path is taken from the Oracle section of the Registry.
 
Using an Oracle TNSNAMES.ORA file to centralize connection information in your Oracle environment simplifies maintenance when changes occur. If, however, the TNSNAMES.ORA file is unavailable, then it is useful to be able to open a backup version of the TNSNAMES.ORA file
TNSNames file failover - You can specify one or more backup, or alternate, TNSNAMES.ORA files.
 
Optional: Example specifying the path to the tnsnames.ora file:
      C:\Oracle\app\MSMITH\product\11.2.0\client_1\network\admin\tnsnames.ora
 
If you are having issues connecting, verify that a connection can be made using another tool such as SQLPlus
        For example: sqlplus username/password@ServiceName

Other Considerations

Although this normally is not required the SQLNet.ora file may need to be modified – Enter TNSNames as the first entry in Names.Directory_Path



Oracle Schemas

A schema has the name of the user who controls it. In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

Think of a user as you normally do (username/password with access to log in and access some objects in the system) and a schema as the database version of a user's home directory. User "foo" generally creates things under schema "foo" for example, if user "foo" creates or refers to table "bar" then Oracle will assume that the user means "foo.bar".

Schema References and Synonyms

For tables to be retrieved the schema user must have permissions to the table. If tables exist in a different schema (e.g. different login) and are not listed, then Synonyms may need to be created. When you grant permission to other users for using an object, the boundary created by schema ownership forces other users to prefix the object name with your schema name to access your object. For example, SCOTT owns the EMP table. If TURNER wants to access SCOTT's EMP table, he must refer to EMP as SCOTT.EMP

Synonyms are alternative names that can be created as database objects in Oracle to refer to a table or view. You can refer to a table owned by another user using synonyms. Creating a synonym eliminates the need to qualify the object name with the schema.

One thing to try is to create a Public or Private Synonym in Oracle to ensure the schema has access to the table/view.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

Single Client Access Name (SCAN)

This s a feature used in Oracle Real Application Clusters environments that provides a single name for clients to access any Oracle Database running in a cluster. TIBCO Scribe® Oracle drivers do not appear to support this feature (e.g. connecting to the SCAN Host)

Problem: Using Stored Procedure as a Source

A user was attempting to call an Oracle stored procedure in a custom source query with an Oracle database as the source. When using exec refresh_warehouse  (stored procedure), an error occurred.

Resolution:
Connecting using the Oracle Wire Protocol driver
Checking the box (on the driver setup wizard) under the Advanced tab "Procedure Returns Results" and calling the stored procedure by using {call refresh_warehouse}
Oracle 12c Error: ora-28040: no matching authentication protocol
Follow the link to an article with a workaround.
https://developer.ibm.com/answers/questions/177075/after-upgrading-to-oracle-12c-database-i-start-get.html

Best way to connect into an Oracle Database using TIBCO Scribe® Insight and an ODBC connection