Default Schema in Oracle

, ,
1

I want to use JDBC adapter to read some data, but database schema is different in test\prod. I would like to use current ..TableA in my JDBC adapter, but I can’t find how to set it up - does Oracle JDBC support that? I expect it to be at JDBC connection screen, where we have Other Properties…

Thanks a lot!

2

Leonid, you can specify this in the Adapter Service itself. It’s also possible to filter the choices you get in the Adapter Service by using an additional parameter in the Connection settings, but that shouldn’t be necessary ordinarily. Save your connection and create a new JDBC Adapter Service based on it. When you pull down the table selection list you’ll see choices for current catalog and current schema. HTH.

Mike Rossi

3

Thanks a lot, but it does not solve the problem. E.g. I have a user XYZ, his default schema is for him is XYZ and this schema has tables A, B, C. So when you configure new adapter - . point to tables A, B and C for that user. At the same time we have schema TEST and it has tables Q, W, E, R, T, Y and in prod environment we are going to have schema PROD with the same tables Q, W, E, R, T, Y. So I want to configure user XYZ to connect to the schema of my choice - in dev it will be TEST and in Prod it will be PROD, in this case I can build adapters with . and they will point to the same tables Q, W, E, R, T, Y regardless of PROD or TEST.

DB2 JDBC adapter has a parameter to do it - I can set library name in Other Properties when I configure my connection…

4

Hi Leonid,

what you describe will only work if you configure your Connections in Test and Production to login as user TEST or PROD respectively.

<current_catalog> is the list of user/schemas that exist in the configured database.
But this does not mean that you have access to all of them.

<current_schema> always point to the schema named the same as the user you used for connecting to the DB (setup in the connection).

I have a similar issue in my current project, where I cannot connect with main DB-User to the tables for security reasons. On the other side the same tables are not named the same in the different stages as they are automatically created by the foreign application. That means, that I need to modify the affected AdapterServices and Notifications every time when I move from one stage to the next.

Regards,
Holger

5

Hi Holger,

I’m surprised that it can’t be done :frowning: I was using DB2 connection for some time and it had a parameter, I could specify what library it should connect to (of course, user should have proper access rights). Based on that I could change only connection string in Prod\Dev and access the same set of tables as <current_schema>. I will try to login as user TEST or PROD, not sure I can get passwords to do it. What is also strange - Oracle can change user schema on the fly

ALTER SESSION SET CURRENT_SCHEMA = TEST

Regards, Leonid