OraFAQ Forum: Server Administration » database logon trigger problem

Home » RDBMS Server » Server Administration » database logon trigger problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

database logon trigger problem [message #142016]

Thu, 13 October 2005 02:55

kaboorvam
Messages: 3
Registered: October 2005
Location: US

Junior Member

I create a trigger this trigger is after logon trigger
which will call a TableA with Column -- User , Schema_name
which stores userid and schema.
once the user log in, this trigger check the schema_name in my table then it
will set the session (current_schema) to schema_name which is set in the table
it works fine but it generate a lot of error in alert log

My trigger code is

CREATE OR REPLACE TRIGGER set_current_schema
AFTER
LOGON
ON DATABASE
declare
default_schema varchar2(30);
begin
select schema_name into default_schema from system.default_schema where
user_name = user;
execute immediate 'alter session set current_schema = ' || default_schema;
end;

--------
it works fine but
it generate errors in alertlog as follows

Errors in file /home/oracle/OraHome1/admin/tec/bdump/tec_j002_6509.trc:
ORA-04088: error during execution of trigger 'SYSTEM.SET_CURRENT_SCHEMA'
ORA-01403: no data found
ORA-06512: at line 5
Thu Oct 13 14:49:46 2005
Errors in file /home/oracle/OraHome1/admin/tec/bdump/tec_j000_6554.trc:
ORA-04088: error during execution of trigger 'SYSTEM.SET_CURRENT_SCHEMA'
ORA-01403: no data found
ORA-06512: at line 5
Thu Oct 13 14:59:51 2005

Report message to a moderator

Re: database logon trigger problem [message #142422 is a reply to message #142016]

Fri, 14 October 2005 09:45

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi,

pls check the following :- (As per oracle Documentation)
=============================================================
ORA-04088 error during execution of trigger 'string.string'

Cause: A runtime error occurred during execution of a trigger.

Action: Check the triggers which were involved in the operation.
================================================================
ORA-01403 no data found

Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query.

Action: Terminate processing for the SELECT statement.
================================================================
ORA-06512 at string line string

Cause: Backtrace message as the stack is unwound by unhandled exceptions.

Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or database administrator.
================================================================

You can see you have to modify your trigger

Regards
Always Friend Sunilkumar

Report message to a moderator

Re: database logon trigger problem [message #142879 is a reply to message #142422]

Tue, 18 October 2005 02:17

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

In your trigger definition, use exception handling. It is required in cases when the users' entry is not there in your system.default_schema table. Its only when you will be encoutering this error : NO Data Found.

--Girish

Report message to a moderator