This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

default schema

 
Jina Lu
Greenhorn
Posts: 26
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thanks in advance

Lu
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch!

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
 
Jina Lu
Greenhorn
Posts: 26
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jina Lu
Greenhorn
Posts: 26
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jina Lu
Greenhorn
Posts: 26
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use newer driver against older version of the database. Our Oracle FAQ has a link to a driver/database compatibility matrix.

ojdbc14.jar is actually too old for older than Oracle 10g.
 
Jina Lu
Greenhorn
Posts: 26
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even if I change ojdbc jar I still don't know how to set that property flag in my application. Seams like it is just constant...
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jina Lu
Greenhorn
Posts: 26
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Application is running on tomcat 6. Connection pool is used from which application takes the connection. I tried to use connectionProperties, but it doesn't work:

<Resource auth="Container" name="jdbc/myDB"
type="oracle.jdbc.pool.OracleDataSource" factory="oracle.jdbc.pool.OracleDataSourceFactory"
connectionCachingEnabled="true" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc: oracle:thin:@111.111.111.11:1521:dbsid"
user="user" password="psw" removeAbandoned="true"
removeAbandonedTimeout="30" maxActive="200" maxIdle="20" maxwait="600"
connectionProperties=";oracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName=true;" />

Any ideas?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic