Skip to Main Content

ORA-02030 : can only select from fixed tables views in trigger after logon

741795Dec 23 2011 — edited Dec 23 2011
Hello folks;
I'm under 11g.
I created this trigger :
"
create or replace
TRIGGER TRG_LOG_ON
AFTER LOGON ON DATABASE
DECLARE
v_new_passwd VARCHAR2(30):='';
v_username sys.v_$session.USERNAME%TYPE;
v_date_connexion sys.v_$session.LOGON_TIME%TYPE;
Err_username EXCEPTION;
Err_date_cnx EXCEPTION;
Err_new_passwd EXCEPTION;
L_Requete varchar2(250);
job_name varchar2(64);
v_ddl_cmd varchar2(4000);
cursor c_skip_locked is
SELECT username, logon_time FROM sys.v_$session
where logon_time=(
select min(logon_time)
from sys.v_$session
where REGEXP_LIKE (USERNAME, '^USER[0-9]{3}$')
)
FOR UPDATE SKIP LOCKED;

BEGIN
IF REGEXP_LIKE(USER,'^USER[0-9]{3}$') AND sys_context('USERENV', 'BG_JOB_ID') IS NULL THEN

OPEN C_skip_locked;
LOOP
FETCH C_skip_locked INTO v_username, v_date_connexion;
EXIT WHEN C_skip_locked%NOTFOUND;
END LOOP;
CLOSE C_skip_locked;

if v_username is null then
raise Err_username;
end if;
if v_date_connexion is null then
raise Err_date_cnx;
end if;

v_new_passwd:= PKG_OTP.CreateRandomPassword();
if v_new_passwd is null then
raise Err_new_passwd;
end if;

L_Requete:='UPDATE UTILISATEUR_ORA SET UTO_PWD_GPL=:a, UTO_DATE_STATUT=:b, UTO_STA=:c WHERE UTO_USR_GPL=:d';
Execute immediate L_Requete using v_new_passwd, v_date_connexion, 'LIBRE', v_username;
COMMIT; --Liberation d'une place dans le pool
dbms_output.put_line('TRG_LOG_ON : Liberation d une place dans le pool (COMMITED)');

v_ddl_cmd:='ALTER USER '||v_username||' IDENTIFIED BY '||v_new_passwd||'';
job_name:='trg_logon_'||to_char(current_timestamp, 'hh24missff');
/* Synchrone */
sys.DBMS_SCHEDULER.CREATE_JOB (
job_name => job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN PKG_OTP.execute_alter_user(''' || v_ddl_cmd || '''); END;',
start_date => null,
repeat_interval => null,
auto_drop => TRUE,
enabled => TRUE,
comments => 'trg_log_on job');
sys.dbms_scheduler.set_attribute(job_name,'job_priority',1); --priorité maximale = 1
sys.DBMS_SCHEDULER.RUN_JOB (
job_name,
TRUE);-- use_current_session TRUE => synchrone

END IF;
EXCEPTION
WHEN Err_username THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La lecture de USERNAME '||v_username||' a echoue',' ','TRG_LOG_ON');
WHEN Err_date_cnx THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La lecture de logon_time '||v_date_connexion ||' a echoue',' ','TRG_LOG_ON');
WHEN Err_new_passwd THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(Sqlerrm || ': ' || L_Requete, 1, 200),'La regeneration de New passwd '||v_new_passwd ||' a echoue',' ','TRG_LOG_ON');
WHEN OTHERS THEN PKG_COM.Ecrire_trace(PKG_COM.ERR,Substr(' TRG_LOG_ON OTHERS :'|| Sqlerrm , 1, 200),'Erreur OTHERS '||v_username||' ??? new_passwd:*'||v_new_passwd||'*',' ','TRG_LOG_ON');
END TRG_LOG_ON;
"

But i obtain two different errors :
1) ORA-02030 : can only select from fixed tables views in trigger after logon;
2) ORA-01786 : FOR UPDATE of this query expression is not allowed.

How to fix it ? or are there a workaround ?

Thanks for your helps, sorry for my bad english :/

Edited by: Moostiq on 23 déc. 2011 10:23
Comments
Post Details
Added on Dec 23 2011
12 comments
839 views