ORA-01422: exact fetch returns more than requested number of rows ; after creating a logon trigger
Hi , I'm trying to write a logon trigger to block certain list of users on primary database and encountered with the below error . Please help to fix this ?
CREATE OR REPLACE TRIGGER nologon_to_primary AFTER LOGON on database
declare
db_role varchar2(30);
user dba_users.username%TYPE;
begin
select database_role into db_role from v$database;
select username into user from dba_users where username like '%_ADMIN' and profile<>'PROFILE_NAME';
If db_role <> 'PHYSICAL STANDBY' and sys_context('USERENV','SESSION_USER') like '%_ADMIN' and sys_context('USERENV','SESSION_USER') not in (user)
then
raise_application_error(-20005, 'Connecting to the primary database is not allowed for %ADMIN accounts. Please connect to the standby database !');