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.

score 2 · Answer 1 · 2012-02-01T19:39:27.103

2

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;

edited Feb 01 '12 at 19:39

answered Jan 31 '12 at 19:33

Thanks for the help. But sorry to say, it is giving me an error while compilation of function itself: Errors for TRIGGER UNSUCCESSFUL_ATTEMPTS_LOCK: LINE/COL ERROR -------- ----------------------------------------------------------------- 21/5 PLS-00201: identifier 'MESSAGE' must be declared 21/5 PL/SQL: Statement ignored It seems this functionality will not work in a database trigger. Please help. P.S: I'm creating this trigger in my database to raise a error message upon my application. I'm not modifying my application code. – prashant1988 Feb 01 '12 at 19:07
@prashant1988 the sample with "message" is to use inside the forms, that have this function. If you use this in pl/sql, call just strip_first_error. – Feb 01 '12 at 19:14
I've amended the same. Still i'm getting one error. Errors for TRIGGER UNSUCCESSFUL_ATTEMPTS_LOCK: LINE/COL ERROR -------- ----------------------------------------------------------------- 21/5 PLS-00221: 'STRIP_FIRST_ERROR' is not a procedure or is undefined 21/5 PL/SQL: Statement ignored But I've already created and committed the function. ------------------------- Function created. esp0002-SQL>commit; Commit complete. ------------------- Please advise. – prashant1988 Feb 01 '12 at 19:23
Please check if you compile the function with the correct name. Using "desc" you see the function params? – Feb 01 '12 at 19:29
yes it is there with the same name.. DESC strip_first_error ..then I pressed F4 in TOAD and i can see the function there. – prashant1988 Feb 01 '12 at 19:31
One more tip: you don't need commit when you are creating functions ;) – Feb 01 '12 at 19:32
FYI.. @Sérgio Michels "afiedt.buf" 16 lines, 414 characters 1 CREATE OR REPLACE FUNCTION strip_first_error(pcode IN NUMBER, pmessage IN VARCHAR2) RETURN VARS 2 3 vpos NUMBER := INSTR(pmessage, 'ORA-', 5); 4 5 BEGIN 6 IF pcode BETWEEN 20000 AND 20999 THEN 7 IF vpos != 0 THEN 8 RETURN( SUBSTR(SUBSTR(pmessage, 1, vpos -2 ),12) ); 9 ELSE 10 RETURN( SUBSTR(pmessage,12) ); 11 END IF; 12 ELSE 13 RETURN pmessage; 14 END IF; 15* END; 16 / Function created. esp0002-SQL>commit; Commit complete. – prashant1988 Feb 01 '12 at 19:36
Ok, see my edit. If still not work, update your code, please. – Feb 01 '12 at 19:40
no help! sorry to say.. dbms_output.put_line( strip_first_error(abs(sqlcode) , sqlerrm) ); cannot display a message on a popup window in forms. And i cannot update my app code.. :( – prashant1988 Feb 01 '12 at 20:19
"cannot display a message on a popup window in forms" where are you running the dbms_output?? – Feb 01 '12 at 22:04
dbms_output I'm running on my database. I want a message to be displayed when a user logins on the oracle apps screen. My current trigger is doing so.. But it is giving some extra message. dbms_output will not work there.. i guess.. :/ – prashant1988 Feb 03 '12 at 19:59

Getting extra messages while executing a trigger

1 Answers1