"Errors Occurred When Executing Following Statement: Declare DIRTY_USERS_CONTEXT_NAME; IO Error: Socket read timed out" When Running Publish Security (Doc ID 1673259.1)

Last updated on AUGUST 01, 2016

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 8.3 SP2 and later
Information in this document applies to any platform.

Symptoms

When attempting to run Publish Security, the following error occurs.

ERROR
-----------------------
2014-05-13 08:31:25:[TASK]:[User]: [3] CORE(SQL): Errors occurred when executing following statement: declare DIRTY_USERS_CONTEXT_NAME varchar2(12) := 'DIRTY_USERS'; vspid number; vtotal pls_integer := 0; vupd pls_integer := 0; vins pls_integer := 0; i_start pls_integer; i_end pls_integer; vsec number; vrate number; start_time timestamp; end_time timestamp; vmsg varchar2(1000); df varchar2(22) := 'yyyy-mm-dd hh24:mi:ss'; vmsg_cnt pls_integer := 0; begin vspid := sys_context('USERENV','SID') ; delete from wrk_log_results where spid = vspid; commit; i_start := dbms_utility.get_time; execute immediate 'select current_timestamp from dual' into start_time; COMMIT; for urec in (select pk1 from pkxref where system_id = vspid and context_name = DIRTY_USERS_CONTEXT_NAME) loop BEGIN DELETE /*+ INDEX(PROJECTSECURITY NDX_PK_PROJECTSECURITY) */ FROM projectsecurity WHERE RDBMSUSERID = urec.pk1; INSERT /*+ APPEND */ INTO ProjectSecurity (rdbmsuserid, projectobjectid, costflag) select uo.user_id, op.proj_id, max(allow_flag) from userobs uo inner join obsproj op on op.obs_id = uo.obs_id inner join profpriv pp on pp.prof_id = uo.prof_id and (priv_num = 23 or priv_num = 12) and uo.user_id = urec.pk1 group by uo.user_id, op.proj_id; update /*+ INDEX(USERSX PK_USERSX) */ usersx set lastprojectsecuritydate = sysdate where user_id = urec.pk1; commit; EXCEPTION WHEN others THEN vmsg := 'User id ' || urec.pk1 || ' ERROR: ' || SQLERRM; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); commit; END; end loop; vins := vins + SQL%ROWCOUNT; COMMIT; vtotal := vins; i_end := dbms_utility.get_time; execute immediate 'select current_timestamp from dual' into end_time; vsec := round(( (i_end-i_start) * 0.01),3); if vsec > 0 then vrate := round( vtotal / vsec , 3); else vrate := 0; end if; vmsg := 'Success[true],' ||' Start[' || to_char(start_time,df) || '], End[' || to_char(end_time,df) || '],' || ' Elapsed[' || trim(to_char(vsec,'99999.99')) || 's],' || ' Count[' || trim(to_char(vtotal,'999999999')) || '],' || ' Throughput[' || trim(to_char(vrate,'99999.99')) || '/s],' || ' Pages[1],' || ' Inserted[' || trim(to_char(vins,'999999999')) || '],' || ' Updated[' || trim(to_char(vupd,'999999999')) || ']'; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); commit; EXCEPTION when others then ROLLBACK; vmsg := 'Success[false]'; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); vmsg := SQLERRM; vmsg_cnt := vmsg_cnt + 1; insert into wrk_log_results ( spid, seq_num, log_message) values (vspid,vmsg_cnt,vmsg); commit; raise; end;
2014-05-13 08:31:25:[TASK]:[User]: [3] CORE(SQL): IO Error: Socket read timed out
2014-05-13 08:31:25:[TASK]:[User]: [3] CORE(SQL): Connection has been recycled. Database OPCT Pool PML

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms