Getting extra messages while executing a trigger

Question

I'm facing a problem while creating a db trigger to raise an error message for oracle forms. Can you please help me out?

My code is:

CREATE OR REPLACE TRIGGER unsuccessful_attempts_lock
      BEFORE UPDATE of last_logon_date 
ON temp_user
FOR EACH ROW

DECLARE
CURSOR c_unsuccessful_attempts IS
  SELECT *
  FROM temp_unsuccessful_attempts
  WHERE user_id=:NEW.user_id; 
max_fails EXCEPTION;

BEGIN
   FOR r_unsuccessful_attempts IN c_unsuccessful_attempts
   LOOP
     IF(r_unsuccessful_attempts.locked ='Y') THEN
       RAISE max_fails;
     END IF;
   END LOOP;
EXCEPTION
  WHEN max_fails THEN
  FND_MESSAGE.SET_NAME ('FND', 'FLEX-USER DEFINED ERROR');
  FND_MESSAGE.SET_TOKEN ('MSG', 'You have reached maximum failed logins. 
                This account has been locked temporarily. Please contact 
                your system administrator')
  FND_MESSAGE.RAISE_ERROR;
  WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20400,'An error has occured.Please contact 
                 your system administrator'||SQLCODE||SQLERRM);
END unsuccessful_attempts_lock;

Once a user's account is locked, temp_unsuccessful_attempts.locked will get updated to 'Y' and he/se should not be able to login further. And temp_user is the table which gets updated when the user has a successful login.

So, once a user's acount is locked(temp_unsuccessful_attempts.locked='Y') and then if he'll try to login with his correct password, tis trigger should get fire(upon updation of temp_user table) and form should give an error that his account is locked and should not progress further.

Message I'm getting is:

ORACLE error -20001: ORA-20001: FLEX-USER DEFINED ERROR:N, MSG, You have reached maximum failed logins. Please contact your system administrator.

ORA-06512: at "APPS.FND_MESSAGE",line 66

ORA-06512: at "APPS.UNSUCCESSFUL_ATTEMPTS_LOCKS",line 38

ORA-04088: error during excution of trigger 'APPS.UNSUCCESSFUL_ATTEMPTS_LOCKS' has been detected in FND_SIGNON.NEW_SESSION.

My trigger is giving some extra message on my oracle apps screen. I just want to display

You have reached maximum failed logins.Your account is locked temporarily.Please contact your helpdesk.

P.S: FND_SIGNON.NEW_SESSION is the procedure where temp_user.last_logon_date is getting updated.

Answer 1

Here we use a function that get's the first error of the stack:

function strip_first_error(pcode in number, pmessage in varchar2) return varchar2 is
    --
    vpos number := instr(pmessage, 'ORA-', 5);
    --
  begin
    if pcode between 20000 and 20999 then
      if vpos != 0 then
        return( substr(substr(pmessage, 1, vpos -2 ),12) );
      else
        return( substr(pmessage,12) );
      end if;
    else
      return pmessage;
    end if;
  end;

And the usage is:

when others then
    message(strip_first_error(abs(sqlcode), sqlerrm));

EDIT

P.S.: this is the when others that handle errors in the call of your update. In your specific example should be:

begin
  update last_logon_date ...
exception
    when others then
      -- in forms you should use message or other function that display the error
      -- in pl/sql you should use dbms_output.put_line, for example.
      dbms_output.put_line( strip_first_error(abs(sqlcode) , sqlerrm) );
end;