alter session in an after logon on database trigger?

[DBAwhosaysNIE]

before everyone warms up their flamethrowers I realize how bad this sounds but I have a situation where I am looking for the least offensive hack to deal with the following:

we have a 3rd party application that backends into Oracle that is somewhat unstable (they're a windoze shop). to try to allieviate some of the instability we are trying to run two instances of their app active-active. the problem(s) (from an Oracle standpoint) it that they hardcode their schemanames (and apparently passwords, cool huh?). despite me trying to politely point out that hardcoding schemanames (not to mention hardcoding the password to be the same as the schemaname) is a BAD thing (it took every ounce of self control and a little yoga to be THAT polite about it) they refuse to fix it and say I should just run two Oracle instances which I adamently refuse to do so I'm looking for workarounds.

the least ugly thing I've come up with is to try to write a trigger that alters current_schema to "real" schema1 or 2 based on v$session.machine at logon.

is this possible? I know there is a proc for doing DDL in a trigger is it possiple to do an alter session?

any help would be greatly appreciated!

 

[DBAwhosaysNIE]

a reading from the book of Oracle, chapter PL/SQL, verse DBMS_SQL:

and Larry said "createst thou a trigger after logon on database which shall call dbms_sql that thou may alter thy session's current_schema from within PL/SQL. and the ordering of the procs shall be open_cursor, parse, execute and close_cursor."

and the clueless vendor got to keep their hardcoded username/password (for now) and the DBA did not have to run a 2nd instance and there was much rejoicing!

 

[SantaMufasa]

Yea, unto the rejoicing therein, Amen !


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.