Oracle Configuration

[ryoung011]

We are currently using the Oracle 9i client and accessing 8i and 9i databases. We have recently been asked to extract data from an Oracle 7 database.

First, a general question. Can DataStage access two Oracle homes simultaneously? Or, can we use the OCI8 and OCI9 stages within the same project? Perhaps using the 8i client?

Second, we've attempted to setup an ODBC DSN for the Oracle 7 database using the Oracle 8 ODBC driver. The issue is that there is no parameter for SID. Hence, when we test, we get the "ORA-12505: TNS:listener could not resolve SID given in connect descriptor" error message. How do you specify the database name?

Thanks in advance,

Rick Young

[chulett]

Can DataStage access two Oracle homes simultaneously?

Nope.

Or, can we use the OCI8 and OCI9 stages within the same project? Perhaps using the 8i client?

Same project, yes. Same job, no. However, I've used an 8i client to access both 8i and 9i databases via the OCI8 plugin without a problem in the past.

Second, we've attempted to setup an ODBC DSN for the Oracle 7 database using the Oracle 8 ODBC driver. The issue is that there is no parameter for SID. Hence, when we test, we get the "ORA-12505: TNS:listener could not resolve SID given in connect descriptor" error message. How do you specify the database name?

Not really sure as I've never used ODBC to access Oracle, only the native OCI stages. I would think it would be ServerName in the .odbc.ini file - that should be the tnsnames entry it would use.