oracle - 执行触发器时获取额外消息

Question

我在创建 db 触发器以引发 oracle 表单的错误消息时遇到问题。你能帮我一下吗？

我的代码是:

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;

一旦用户的帐户被锁定， temp_unsuccessful_attempts.locked将更新为“Y”，他/他们将无法进一步登录。和 temp_user是当用户成功登录时更新的表。

因此，一旦用户的帐户被锁定( temp_unsuccessful_attempts.locked='Y' )，然后如果他尝试使用正确的密码登录，触发器应该会被触发(更新 temp_user 表后)并且表单应该给出他的帐户的错误已锁定，不应进一步发展。

我收到的消息是:

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.

我的触发器在我的 oracle 应用程序屏幕上给出了一些额外的消息。我只想显示

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

附注: FND_SIGNON.NEW_SESSION是 temp_user.last_logon_date 更新的过程。

Answer 1

这里我们使用一个函数来获取堆栈的第一个错误:

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;

用法是:

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

编辑

P.S.:这是其他人在您的 update 的调用中处理错误的时候.在你的具体例子中应该是:

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;