Extract data from Oracle 9i and 10g

shyju · Post by **shyju** » Fri Jun 09, 2006 3:08 am

Hi All,

I have a table A containing some amount of data in both Oracle 9i and Oracle 10g database. I have to extract data from both the database 9i and 10g using DataStage. I would like to know whether we need to install plug in for both oracle 9i and Oracle 10g. Also I would like to know whether there is a plugin for Oracle 10g.

Can I extract data using ODBC stage from Oracle 10g database?

Cheers,
Shyju.

loveojha2 · Post by **loveojha2** » Fri Jun 09, 2006 3:11 am

Not very sure about the plugin,
I have tried using the ODBC, it works fins for me with 10g.
I guess,Oracle Oci Stage should work well with the 10g also.

balajisr · Post by **balajisr** » Fri Jun 09, 2006 3:18 am

Oracle OCI can be used to connect to oracle 10g.

gbusson · Post by **gbusson** » Fri Jun 09, 2006 3:20 am

right.

a 9i client can connect to a 10g database

shyju · Post by **shyju** » Fri Jun 09, 2006 5:13 am

I would like to know whether we can use Oracle 9i stage as well as Oracle 10g stage (if present) in the same job?

loveojha2 · Post by **loveojha2** » Fri Jun 09, 2006 5:21 am

shyju wrote:I would like to know whether we can use Oracle 9i stage as well as Oracle 10g stage (if present) in the same job?

I don't think that Ascential Server Edition (till 7.5.1.a) have any thing called as Oracle 10g Stage.
If you are talking about accessing Oracle 9i and 10g in the same server job then I don't think there would be an problem in that. Since both connections would be made using different Stages Altogether.

shyju · Post by **shyju** » Fri Jun 09, 2006 6:09 am

How can we use Oracle 9i and Oracle 8i stages(Since we dont have Oracle 10g stage so I took 8i as an example) in a single job? Since we define the path for the ORACLE_HOME in Administrator, we can define either for 9i or 8i but not both rite?

Please let me know if I am missing some thing here.

chulett · Post by **chulett** » Fri Jun 09, 2006 6:21 am

You can't use 'both stages' in one job if you have both - they are mutually exclusive as noted in the documentation. Well, actually you can but the job won't run and you'll get all kinds of goofy 'put' errors.

When you are dealing with multiple Oracle versions, you need to play the 'least common denominator' game. Use the client and the stage for the lower version and always use that one to connect to either database.

So, 8i and 9i? Point to the 8i Client in your dsenv file and always use the 8i stage in your jobs. 9i and 10g? Point to the 9i Client in your dsenv file and always use the 9i stage in your jobs. We are currently doing the latter because of our mixed environment.

ps. As noted, it's not until the 7.5.x versions that you have an OCI stage 'certified' against 10g. That doesn't mean you can't access 10g using earlier versions of the stage.

shyju · Post by **shyju** » Fri Jun 09, 2006 6:33 am

Thanks Craig for the detailed explaination. I have one small clarification. Since the two stages 8i and 9i cannot be used in a single job, Is there a possibility that we can split the job into where the first job holds oracle8i and the second as 9i and then run the two jobs? Do we still have errors popping up?

chulett · Post by **chulett** » Fri Jun 09, 2006 6:41 am

Sure you can, but you are still restricted to the fact that you can only point to one Oracle Client in your Server. So there's really no reason to use the other stage. You can and it will typically work but it will also generate odd errors on occassion, depending on exactly what you are doing. Been there, done that.

Now, in later versions you can 'work around' this single client restriction by overriding those specific environment variables - like ORACLE_HOME - in any given job. But that's a topic for another conversation.