I'm trying to create an ON LOGON trigger where I'm trying to identify name of the client application that has initiated the connection
I know this information is in the v$session and this SQL works when running from the account that has DBA privs:
SELECT UPPER(PROGRAM) FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID');
However it does not want to compile in the trigger I'm getting the following error:
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
I tried to put this SQL into a string and do and Execute Immediate, but then it gives this error when the trigger fires:
ORA-00942: table or view does not exist
What would be the way identify the client application name for that session in the account that does not have DBA privilages?