I’m using JDBC Adapter 6.0.2. I’m building a JDBC insert notification on an Oracle database. Unfortunately, the schema owner for the table changes from environment to environment (i.e. build to test to production). Also, the schema isn’t owned by the userid on the connection string. There is no way to “dynamically” specify the schema in a notification so I don’t have to log into developer on production and change the schema after I have migrated the package (tech support confirmed this). I’m handling this on other JDBC services by using JDBC Dynamic SQL to specify the schema dynamically. Any suggestions on how, process-wise, I can handle this? My ultimate goal is to avoid having to “touch” the notification in Developer after I move it to each environment.
What we’ve done in a similar situation is to create an Oracle public synonym for the table, so in your query, you specify the synonym, not the schema.table name. That way, you don’t need to change code per environment.
But I thought you could not create a notification on a synonym. It has to be a table?
ok… on second thought… how’s this idea (albeit more work)…
What we did was pull the connection services into a separate package, which unfortunately required us to recreate all our JDBC services, as for some unknown reason, you can’t update the connection info (and we’ll get into the lack of cut-n-paste in the custom sql window some other time). This way, you can push up updates to your functional code, w/o having to change the connection info in your connection services.
Just a thought…
Thanks for the thoughts. We already have our JDBC connections in a separate package.
In this case, the userid on the connection remains the same from database environment to database environment. However table owning schema is not the same, and is not owned by the connecting userid. We had this as a security standard to isolate webMethods database access from all other userid access to the target database. Fortunately, this has worked in all cases except this one … this database is an unusual installation.
The problem. In the target development database, the schema owner is A. My notification is on A.tablename. In production, the schema owner is B. My notification has to be on B.tablename. B schema does not even exist in the development database. Therefore, I can only change the schema to B on the notification in a wM IS that has a connection to the production database.
My only option may be to switch the db connection userid to be the schema owning userid, and to reference current_schema.tablename in the notification.