ORA-06502: PL/SQL: numeric or value error - Oracle Database

ORA-00604 ORA-06502 ORA-06512 encountered at Oracle Database Login

ORA-00604: error occurred at recursive SQL level 1 | ORA-06502: PL/SQL: numeric or value error: character string buffer too small | ORA-06512 at Oracle User Login

ORA-00604 ORA-06502 ORA-06512 encountered at Login Oracle

One Client was facing issues with Logging 2-3 users. Every time they were trying to login to PLSQL Developer with the specific user, the below mentioned error was getting populated,

'ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 10'

There can be multiple reasons for the same as it's a very common error. In my scenario, only a few users were getting this error on login and others were able to login successfully. So I logged in with SYS and checked for the existence of any LOGON triggers in the environment using the below mentioned query,

SQL> select trigger_name from dba_triggers where triggering_event like '%LOGON%';

There were 2 triggers. Since it was a TEST Environment so I disabled the triggers for a few minutes using

SQL> alter trigger <trigger name> disable;

Again I checked for Failed User Logins and this time every user was able to login successfully. So the reason for the issue was clear.

Why the Trigger was failing?

Then I tested why that trigger was creating the problem. The trigger was created to store user login information in a separate table to maintain the USER LOGIN record.  To my surprise it was found that the CHAR length for the NAME column was only given the value of 10 and the users they were trying to login were having the CHAR length of more than 10 letters. The same was rectified and both User Login as well as Triggers worked fine after that.

I hope this helps !!