The problem I faced with jdbc connection occurred when I needed to access object in different database schema when my new user is by default. StructDescriptor.createDescriptor adds scheme prefix which causes the problem that object is not found.
Is it possible to configure maybe Tomcat resource and set default current user scheme or the only solution would be to set default scheme on application start? I'm using Oracle in my project.
I've run into similar issues with types in Oracle. My advice would be to use full name of the type (that is, SCHEMA.TYPE) in the call to the createDescriptor method. Even if you use different schemas in your application, create one separate schema which will contain all of the types and use full name of the type in stored procedures and packages. You could make the schema name configurable to increase the portability of your app into another database.
No doubt there are other arrangements possible, but this one worked quite well for me.
Edit: you'll need to grant appropriate permission - EXECUTE - on the type to the other schemas, of course
Thanks for quick answer Martin. Adding "SCHEMA." to each call is quite complicated, but seams to work (checked one case). Are any other solutions to this problem or it is the only way to solve it?
Maybe there are other ways to stop Oracle adding default user schema to type name?
I'm not sure I fully understand your need. You don't want to use a specific schema for the type, and you don't want to use the current schema. How do you want to specify which schema the type resides in?
I currently have the old module, quite big one, which has lots of call without specifying concrete schema. Now I have requirement from the client to use different db user for connecting to db then objects owner.
After this user switch I need to be sure that the old code will work. All needed db grants for accessing objects are applied and the log on trigger to set current schema created.
Do I understand you correctly that oracle will add default user schema to any type which doesn't have it fully specified? Is any way to tell oracle to add current but not default schema to the type?
Ah, so the "default schema" is the "current (connected) user's schema", while "current schema" is the schema you set using alter session set current_schema=...?
Using this terminology, I do think that unqualified type name is resolved using default schema. I guess (just guess) this behavior is caused by the JDBC driver (since the JDBC driver generally cannot know about changes to the current schema you can do using database calls), perhaps there could be some connection properties that would have it behave otherwise. (I've found this interesting property of Oracle connection, whose name suggests it could do what you need, but I have really no experience with it. I'm afraid I won't be able to help any further on this, though.)
You can also try registering the type name with a dot in the front (that is, ".TYPENAME"). I believe this causes the type to be searched for in the current schema (I'm not 100% sure it will, as it's been some time I was experimenting with this and I've forgotten all the details in he meantime). Still, you'll have to review all these calls, so this might not be much of a help.
Unfortunately the property you mentioned exists only for newer Oracle version like Oracle 11g. In my case it is 10g and ojdbc14.jar lib. I'll keep searching for solution and post it here when find out.
Thanks for trying to help anyway.
The constant contains a name of a property. To activate it, you'll add the property into a property list with value equal to true. The details depend on how you're obtaining a connection. I'm not very skilled with setting connection properties, but if you post the details on how you create the connection, perhaps someone will be able to help.
This is really a grey area for me. But perhaps you could obtain the physical connection, cast it to OracleConnection and call its getProperties() to obtain the properties the connection was created with. If the property is set and it doesn't behave as expected, the search ends. If the property is not set, perhaps there could be a different way to slip it through.