Change User at Logon When Calling Form from Custome Login Form, Read Registry From PL-SQL

use the on logon trigger along with the function logon

My friend I know that Oracle 10gR2 and 6i is a problem but my management is insisting to take this bully.

That's why I said there may be an issue of approach but we have to do it this way.

Secondly, I have tried On-Logon with logon function it doesnt change user from a MDI form to SDI form it remains the same as first passed user at on-logon trigger and even though you re-use logon function on called form on on-logon trigger it never change it.

And again it's an issue of approach that my management create user every year with a post-fix YY and reluctant to  use partition clause to avoid performance issue. It may be ignorance of lack of knowledge but thats the way it is (By the way I recently joined the comapny)

Anyways we need solution if any of the masters or guru could provide it.

And by now you must have understood that why we wana change user at logon form because user may wana go to Year 2006-2007 or 2007-2008 and so on and there are different physical DB users and management dont wana change the approach.

We can use HOST cammand and pass USERID/PASS but it that case environment global variable values gets lost which we cant afford since it is security / right concern variables.

Regards,
Zee.

We use the logon built-in in Oracle Forms6i to swithch the user, both in the on-login trigger as well as optionally via a key trigger.  Having to use a differnt logon though just to see data for a different year looks like a an application and/or database design problems.  It shouldn't be neceassry to use a different logon to see data for a different year.

You indicated that management is reluctant to use partitions, but they can be very helpful for performance and data management.  It sounds like your management doesn't want to use Oracle the way it works best.  It seems like they want Oracle to work the way they think is best.  They didn't write Oracle though.

My Friend Again,

This is an issue with us. My management is a very old school. And u'll be surprise that we don't even have primary keys and foreign keys in our system which is not even a school old or new is out of question, so u can imagine.

Anyways, if u r saying that we can use LOGON built-in to switch the user when calling a form from custom logon form to mainmenu form so we have tried it is not working. And this was my first that it should work but it isn't.

Here is the code:

Declare
      the_connect varchar2(100);
Begin
      the_connect  := Get_Application_Property(CONNECT_STRING);
      if the_connect  is not null then
      logon('tbs','XXX@'||the_connect);  
      ELSE
         LOGON('tbs','XXX@ecltest');    
      END IF;
END;

We have tried the hard coded version on connect string as well just to check even thoug it doesn't make any difference since it is only geting the service name and username and password is hardcoded in this code.

Thanks and Regards,
Zee