Tom:

I hope you had a nice hot relaxing memorial weekend!

<<<<
You could have authenticated_username raise that exception, then your code would be: 
begin 
  my_var := authenticated_username; 
  ... 
exception 
  when session_expired then htp.P( 'you lose' ); 
end; 
>>>>

I tried what you suggested above to add expcetion to the authenticate_user function but it does not work as i thought it should.

I added an exception "session_expired" to the authenticate_user function first.
When i run the other main routine that calls that it gives me an error in that the exception needs to be defined in the function.

When i add the exception "session expired" to the function, The main program will continue executing and does not stop even though session has expired.

It seems the only way i managed to stop program execution is using "raise_application_error" but i cant use this for web page message.

Am i missing something here?

SQL> create table session_table (session_id number, timestamp date, username varchar2(30));

Table created.

SQL> insert into session_table values (21, sysdate, 'The Great Tom');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function get_user_cookie return number is
  2  begin
  3      return 22;
  4  end;
  5  /

Function created.

SQL> create or replace function authenticate_user return varchar2 is
  2       l_rec session_table%rowtype;
  3       l_id  number;
  4       l_username varchar2(30);
  5       session_expired  exception;
  6   begin
  7       l_id := get_user_cookie;
  8       update session_table
  9          set timestamp = SYSDATE
 10           where session_id = l_id
 11             and timestamp > sysdate - 1/24
 12              returning username into L_USERNAME;
 13         IF (SQL%ROWCOUNT = 0) THEN
 14           --return NULL;
 15           raise session_expired;
 16            -- raise_application_error(-20001, 'You session has expired! You need to re-login to 
system...!');
 17         else
 18            return l_username;
 19         end if;
 20   end;
 21  /

Function created.


 SQL> declare
   2     indx  number;
   3     session_expired  exception;
   4     l_user   varchar2(30);
   5   begin
   6     for indx in 1..10
   7     loop
   8      l_user := authenticate_user;
   9      dbms_output.put_line('Procedure called '||indx||' times');
  10     end loop;
  11   exception
  12    when session_expired then
  13     dbms_output.put_line('no session!');
  14   end;
  15  /
 declare
 *
 ERROR at line 1:
 ORA-06510: PL/SQL: unhandled user-defined exception
 ORA-06512: at "SYSTEM.AUTHENTICATE_USER", line 15
ORA-06512: at line 8
 
 
 After I add the expcetion to the function, the calling procedure keeps running!
 
 
 SQL>  declare
   2      indx  number;
   3      session_expired  exception;
   4      l_user   varchar2(30);
   5    begin
   6      for indx in 1..10
   7      loop
   8       l_user := authenticate_user;
   9       dbms_output.put_line('Procedure called '||indx||' times');
  10      end loop;
  11    exception
  12     when session_expired then
  13      dbms_output.put_line('no session!');
  14    end;
  15  /
 Procedure called 1 times
 Procedure called 2 times
 Procedure called 3 times
 Procedure called 4 times
 Procedure called 5 times
 Procedure called 6 times
 Procedure called 7 times
 Procedure called 8 times
 Procedure called 9 times
 Procedure called 10 times
 
PL/SQL procedure successfully completed.