PL/SQL (MOSC)

MOSC Banner

ORA-01422: exact fetch returns more than requested number of rows ; after creating a logon trigger

edited Jun 9, 2022 8:29PM in PL/SQL (MOSC) 5 commentsAnswered

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 !');

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center