What kind of front-end application do you use? In most cases this issue is resolve by storing password on the client side. E.g. in Forms is can be accessed from GET_APPLICATION_PROPERTY built-in.
As for generic server-side solution, I think that seting idle time is the only way, though without return

Regards, Dima
http://seminihin.narod.ru

Catherine,

Following is a procedure, "CHANGE_PASSWORD", that I wrote to simulate your situation. I infer from the name, "CHANGE_PASSWORD", that you want to change passwords for a user, but I wasn't sure. Also, I was not sure "who" was changing the password. In any case, I wrote the procedure to validate a password without disconnecting and without getting into "idle time" issues.

My procedure relies upon creating a DATABASE LINK to validate the user's password. Since I didn't know the name of the "host string" that you use in your tnsnames.ora, I couldn't hard code it, but you can, if you wish, thus removing "host_service" as a procedure argument. If you are creating this procedure for use for the changing user's own password, then you can also remove procedure-argument #2, "usernm", from the code. If you the procedure is for creating a new password (as I presume it is), then you will want to add an argument for "new_password". Also, I placed "screen display stubs" in the procedure code to represent "success" or "failure" of the procedure; you'll probably want to remove the stubs, replacing them with what you actually want the procedure to do.

As my Tek-Tipster buddy, Carp, pointed out to me, for this code to work, it is not good enough for the compiling user to be a (member of the role) DBA; you must log in as SYS and explicitly "GRANT CREATE DATABASE LINK TO <compiling_username>;" (...seems like an Oracle bug to me, but that is another issue).

In any case, here is the code, along with proof-of-concept executions:

CREATE or replace PROCEDURE change_password
    (host_service in varchar2
    ,usernm varchar2
    ,old_password IN VARCHAR2)
IS
    drop_db_link    boolean;
    bad_uname_pw    boolean;
    hold_username    varchar2(100);
    bad_username_pw    exception;
    pragma exception_init(bad_username_pw,-1017);
BEGIN
    drop_db_link    := false;
    declare
        dummy    char(1);
    begin
        select 'x' into dummy
            from user_db_links
            where db_link like '%TESTPW%';
        drop_db_link    := true;
    exception
        when no_data_found then
            drop_db_link := false;
    end;
    if drop_db_link then
        execute immediate 'drop database link testpw';
    end if;
    execute immediate 'create database link testpw connect to '||
        usernm||' identified by '||old_password||' using '''||
        host_service||'''';
    execute immediate
        'select username from user_users@testpw' into hold_username;
    dbms_output.put_line('Username/password combination validated. '||
                'Do what you need to do.');
    exception
        when bad_username_pw then
            bad_uname_pw    := true;
            dbms_output.put_line('Error: Bad Username or password.');
end;
/

Procedure created.

SQL> exec change_Password('dhunt','test','test')
Username/password combination validated. Do what you need to do.

PL/SQL procedure successfully completed.

SQL> exec change_Password('dhunt','test','yada')
Error: Bad Username or password.

PL/SQL procedure successfully completed.

Let me know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:22 (15Dec03) GMT, 12:22 (15Dec03) Mountain Time)

Great! Another way (plagiarized from Dave's, of course ) is to create Java Stored Procedure that accepts username/password and tries to open connection. Drawback - java involved, advantage - no need in DDL.

Regards, Dima
http://seminihin.narod.ru

Wow!  Musafa, I am amazed!  Thank you!

Belive it or not, I had actually thought about this technique, too.  But apparently I did something wrong on my own attempt, and because the approach seemed far-fetched, I lost courage when I got error messages and figured it was just too weird to work.  But you showed me it isn't.

The one thing I'd add is that I was sometimes getting "ORA-02018: database link of same name has an open connection" when I used the procedure repeatedly.  Adding a COMMIT at the end of the procedure stopped that.

Catherine,

I am really pleased that it worked for you!

Another way to deal with the "ORA-02018: database link of same name has an open connection" error is to issue the following command:

ALTER SESSION CLOSE DATABASE LINK yada;

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:46 (17Dec03) GMT, 12:46 (17Dec03) Mountain Time)