[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Update the process_id and set the STATUS to 'ACTIVE' */
update ad_sessions set
OS_PROCESS_ID=process_id,
STATUS='ACTIVE',
LAST_UPDATE_DATE=sysdate,
APPL_TOP_PATH=appltop_path,
INVOKDIR=p_invokdir,
RUN_ON_NODE=node_name
where
session_id=sess_id;
update AD_WORKING_RES_LOCKS set
SESSION_STATUS='ACTIVE'
where
SESSION_ID=sess_id;
select nvl(ANY_CHANGE_WHILE_INACTIVE_FLAG,'N')
into inactive_flag
from ad_sessions
where session_id=sess_id;
SELECT count(*) INTO session_exists from ad_sessions
where session_id=sess_id;
select
SESSION_ID,UTILITY_NAME,APPL_TOP_ID,
TOPDIR,STATUS,PRIORITY,
OS_USER_NAME
into
exst_session_id,exst_utility_name,exst_appl_top_id,
exst_topdir,exst_status,exst_priority,
exst_os_user_name
from ad_sessions
where
session_id=sess_id;
update AD_SESSIONS set status='ACTIVE'
where session_id=sess_id;
INSERT INTO AD_SESSIONS
(
SESSION_ID,UTILITY_NAME,APPL_TOP_ID,
INVOKDIR,STATUS,PRIORITY,
OS_PROCESS_ID,OS_USER_NAME,
START_DATE,CREATION_DATE,LAST_UPDATE_DATE,
APPL_TOP_PATH, RUN_ON_NODE, TOPDIR
)
VALUES
(
sess_id,
utility_nm,
appltop_id,
invokedir,
'ACTIVE',
priority_value,
process_id,
osuser_name,
SYSDATE,
SYSDATE,SYSDATE,
appltop_path, node_name, p_topdir
);
/* Update the STATUS to 'INACTIVE' */
--
update AD_SESSIONS set
STATUS='INACTIVE',
LAST_UPDATE_DATE=SYSDATE
where
SESSION_ID=sess_id;
update AD_WORKING_RES_LOCKS set
SESSION_STATUS='INACTIVE'
where
SESSION_ID=sess_id;
select distinct wl.session_id
bulk collect into x_conflict_session_ids
from ad_working_res_locks wr, /* Requested */
ad_working_res_locks wl /* Locked */
where wr.session_id = x_session_id and
wl.session_id <> x_session_id and /* Look at other sessions */
wl.resource_code = wr.resource_code and
( wl.appl_top_id = -5 or
wr.appl_top_id = -5 or
wl.appl_top_id = wr.appl_top_id ) and
( wl.context = 'ALL' or
wr.context = 'ALL' or
wl.context = wr.context ) and
( wl.language = 'ALL' or
wr.language = 'ALL' or
wl.language = wr.language ) and
( wl.extra_context1 = 'ALL' or
wr.extra_context1 = 'ALL' or
wl.extra_context1 = wr.extra_context1 ) and
( wl.extra_context2 = 'ALL' or
wr.extra_context2 = 'ALL' or
wl.extra_context2 = wr.extra_context2 ) and
( wl.extra_context3 = 'ALL' or
wr.extra_context3 = 'ALL' or
wl.extra_context3 = wr.extra_context3 ) and
wl.lock_mode = decode(wr.lock_mode,
'S', 'X',
'X', wl.lock_mode,
wl.lock_mode) and
wl.session_status = 'ACTIVE' and
( wl.date_acquired is not null or
wl.session_priority > wr.session_priority or
(
wl.session_priority = wr.session_priority and
wl.date_requested < wr.date_requested
)
);
select distinct wl.session_id
bulk collect into x_conflict_session_ids
from ad_working_res_locks wr, /* Requested */
ad_working_res_locks wl /* Locked */
where wr.session_id = x_session_id and
wl.session_id <> x_session_id and /* Look at other sessions */
wl.resource_code = wr.resource_code and
( wl.appl_top_id = -5 or
wr.appl_top_id = -5 or
wl.appl_top_id = wr.appl_top_id ) and
( wl.context = 'ALL' or
wr.context = 'ALL' or
wl.context = wr.context ) and
( wl.language = 'ALL' or
wr.language = 'ALL' or
wl.language = wr.language ) and
( wl.extra_context1 = 'ALL' or
wr.extra_context1 = 'ALL' or
wl.extra_context1 = wr.extra_context1 ) and
( wl.extra_context2 = 'ALL' or
wr.extra_context2 = 'ALL' or
wl.extra_context2 = wr.extra_context2 ) and
( wl.extra_context3 = 'ALL' or
wr.extra_context3 = 'ALL' or
wl.extra_context3 = wr.extra_context3 ) and
wl.lock_mode = decode(wr.lock_mode,
'S', 'X',
'X', wl.lock_mode,
wl.lock_mode) and
wl.session_status = 'ACTIVE' and
( wl.date_acquired is not null or
wl.session_priority > wr.session_priority
);
select priority into child_priority from ad_sessions
where session_id = child_node; /* Child node */
select priority into parent_priority from ad_sessions
where session_id = parent_node;
select distinct nvl(done_flag,'N') bulk collect into x_done_flag
from ad_planned_res_locks
where session_id = x_session_id and
stage_code = x_stage_code;
select priority, status into x_priority, x_status
from ad_sessions where session_id = x_session_id;
/* Request locks -> insert lock rows in ad_working_res_locks */
begin
if (x_stage_code <> 'ACQUIRE_HELD') then
insert into ad_working_res_locks
(
session_id,
resource_code,
context,
appl_top_id,
language,
extra_context1,
extra_context2,
extra_context3,
in_process_flag,
lock_mode,
date_requested,
date_acquired,
session_status,
session_priority
)
select x_session_id, p.resource_code, p.context, p.appl_top_id,
p.language,
p.extra_context1,
p.extra_context2,
p.extra_context3,
'Y',
p.lock_mode,
sysdate,
null,
x_status,
x_priority
from ad_planned_res_locks p
where p.session_id = x_session_id and
p.stage_code = x_stage_code and
p.action_flag in ('A', 'P', 'B') and
not exists (
/* It may be a restart and rows may already exist.
* (If the failure occurred in this procedure in
* the prior run)
*/
select 'Already inserted'
from ad_working_res_locks w
where w.session_id = x_session_id and
w.resource_code = p.resource_code and
w.context = p.context and
w.appl_top_id = p.appl_top_id and
w.language = p.language and
w.extra_context1 = p.extra_context1 and
w.extra_context2 = p.extra_context2 and
w.extra_context3 = p.extra_context3 and
w.in_process_flag = 'Y');
error_code := BASE_ERROR + 8; /* Error, inserting row in
update ad_sessions set wait_loop_time = 0
where session_id = x_session_id;
select wait_loop_time into x_wait_loop_time from ad_sessions
where session_id = x_session_id;
update ad_sessions set status = 'INACTIVE'
where session_id = x_session_id;
update ad_working_res_locks set session_status = 'INACTIVE'
where session_id = x_session_id;
select control_code into x_control_code from ad_sessions
where session_id = x_session_id;
update ad_sessions set control_code = null,
status = 'INACTIVE'
where session_id = x_session_id;
update ad_working_res_locks set session_status = 'INACTIVE'
where session_id = x_session_id;
update ad_sessions set status = 'INACTIVE'
where session_id = x_session_id;
update ad_working_res_locks set session_status = 'INACTIVE'
where session_id = x_session_id;
update ad_sessions set wait_loop_time = x_wait_loop_time
where session_id = x_session_id;
select count(*) into x_mode_count from ad_working_res_locks
where session_id = x_session_id and
lock_mode = 'X';
update ad_sessions
set any_change_while_inactive_flag = 'Y',
locks_overridden_by = x_session_id
where
nvl(any_change_while_inactive_flag,'N') <> 'Y' and
session_id in (
select distinct wl.session_id
from ad_working_res_locks wr, -- Requested
ad_working_res_locks wl -- Locked
where wr.session_id = x_session_id and
wl.session_id <> x_session_id and
wl.resource_code = wr.resource_code and
( wl.appl_top_id = -5 or
wr.appl_top_id = -5 or
wl.appl_top_id = wr.appl_top_id ) and
( wl.context = 'ALL' or
wr.context = 'ALL' or
wl.context = wr.context ) and
( wl.language = 'ALL' or
wr.language = 'ALL' or
wl.language = wr.language ) and
( wl.extra_context1 = 'ALL' or
wr.extra_context1 = 'ALL' or
wl.extra_context1 = wr.extra_context1 ) and
( wl.extra_context2 = 'ALL' or
wr.extra_context2 = 'ALL' or
wl.extra_context2 = wr.extra_context2 ) and
( wl.extra_context3 = 'ALL' or
wr.extra_context3 = 'ALL' or
wl.extra_context3 = wr.extra_context3 ) and
wl.session_status = 'INACTIVE');
select count(*) into x_lock_count from ad_working_res_locks
where (
resource_code || ':' || context || ':' || appl_top_id || ':' ||
language || ':' || extra_context1 || ':' ||
extra_context2 || ':' || extra_context3 || ':' ||
decode (lock_mode, 'X', 'M', lock_mode)
in
(select wl.resource_code || ':' || wl.context || ':' ||
wl.appl_top_id || ':' || wl.language || ':' ||
wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
wl.extra_context3 || ':' || decode (lock_mode, 'S', 'M',
lock_mode)
from ad_planned_res_locks wl
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag = 'P') and
session_id = x_session_id and
in_process_flag = 'N' and
date_acquired is not null);
select count(*) into x_lock_count from (
select resource_code, context, appl_top_id,
language,
extra_context1,
extra_context2,
extra_context3
from ad_planned_res_locks wl
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag = 'P'
minus
select resource_code, context, appl_top_id,
language,
extra_context1,
extra_context2,
extra_context3
from ad_working_res_locks where (
resource_code || ':' || context || ':' ||
appl_top_id || ':' || language || ':' ||
extra_context1 || ':' || extra_context2 || ':' ||
extra_context3
in
(select wl.resource_code || ':' || wl.context || ':' ||
wl.appl_top_id || ':' || wl.language || ':' ||
wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
wl.extra_context3
from ad_planned_res_locks wl
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag = 'P') and
session_id = x_session_id and
in_process_flag = 'N' and
date_acquired is not null));
update ad_working_res_locks set date_acquired = sysdate
where session_id = x_session_id and in_process_flag = 'Y';
/* Delete duplicate rows */
delete from ad_working_res_locks where rowid in
(select w.rowid from ad_working_res_locks w,
ad_working_res_locks p
where w.session_id = x_session_id and
w.session_id = p.session_id and
w.resource_code = p.resource_code and
w.context = p.context and
w.appl_top_id = p.appl_top_id and
w.language = p.language and
w.extra_context1 = p.extra_context1 and
w.extra_context2 = p.extra_context2 and
w.extra_context3 = p.extra_context3 and
w.rowid <> p.rowid and
w.in_process_flag = 'N');
update ad_working_res_locks set in_process_flag = 'N'
where session_id = x_session_id and in_process_flag = 'Y';
update ad_planned_res_locks set done_flag = 'N' where
session_id = x_session_id and stage_code = x_stage_code;
update ad_sessions set wait_loop_time = 0
where session_id = x_session_id;
select distinct nvl(done_flag,'N') bulk collect into x_done_flag
from ad_planned_res_locks
where session_id = x_session_id and
stage_code = x_stage_code;
delete from ad_working_res_locks where (
resource_code || ':' || context || ':' || appl_top_id || ':' ||
language || ':' || extra_context1 || ':' ||
extra_context2 || ':' || extra_context3
in
(select wl.resource_code || ':' || wl.context || ':' ||
wl.appl_top_id || ':' || wl.language || ':' ||
wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
wl.extra_context3
from ad_planned_res_locks wl
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag in ('B', 'R')) and
session_id = x_session_id
);
select count(*) into x_lock_count from ad_planned_res_locks
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag = 'D' and
lock_mode = 'X';
select count(*) into x_lock_count from ad_planned_res_locks
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag = 'D';
update ad_working_res_locks set lock_mode = 'S' where (
resource_code || ':' || context || ':' || appl_top_id || ':' ||
language || ':' || extra_context1 || ':' ||
extra_context2 || ':' || extra_context3
in
(select wl.resource_code || ':' || wl.context || ':' ||
wl.appl_top_id || ':' || wl.language || ':' ||
wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
wl.extra_context3
from ad_planned_res_locks wl
where session_id = x_session_id and
stage_code = x_stage_code and
action_flag = 'D') and
session_id = x_session_id);
update ad_planned_res_locks set done_flag = 'Y' where
session_id = x_session_id and stage_code = x_stage_code;
delete from ad_working_res_locks where session_id = x_session_id;
DELETE FROM AD_PLANNED_RES_LOCKS
WHERE SESSION_ID = p_session_id AND
STAGE_CODE <> p_end_stage_cd AND
STAGE_CODE <> p_begin_stage_cd;
UPDATE AD_SESSION_TASKS
SET COMPLETION_STATUS = p_completion_status,
END_DATE = sysdate ,
LAST_UPDATE_DATE = sysdate
where session_id = p_session_id and
task_number = p_task_id;
INSERT INTO AD_SESSIONS_HISTORY
( SESSION_ID, UTILITY_NAME, APPL_TOP_PATH,
RUN_ON_NODE, INVOKDIR, STATUS, PRIORITY,
CONTEXT_INFO, ANY_CHANGE_WHILE_INACTIVE_FLAG, LOCKS_OVERRIDDEN_BY,
JS_TOTAL_JOBS, JS_COMPLETED_JOBS, JS_REMAINING_JOBS,
COMPLETION_STATUS,
START_DATE, END_DATE, CREATION_DATE, LAST_UPDATE_DATE, TOPDIR
)
SELECT
SESSION_ID,UTILITY_NAME, APPL_TOP_PATH,
RUN_ON_NODE, INVOKDIR, STATUS, PRIORITY,
CONTEXT_INFO, ANY_CHANGE_WHILE_INACTIVE_FLAG, LOCKS_OVERRIDDEN_BY,
JS_TOTAL_JOBS, JS_COMPLETED_JOBS,JS_REMAINING_JOBS,
p_complete_status,
START_DATE,SYSDATE, CREATION_DATE, LAST_UPDATE_DATE, TOPDIR
FROM
AD_SESSIONS
WHERE
SESSION_ID=p_sess_id;
DELETE FROM AD_SESSIONS
WHERE session_id = p_sess_id;
INSERT INTO AD_SESSION_TASKS_HISTORY
(SESSION_ID,TASK_NUMBER,COMPLETION_STATUS,
CONTEXT ,START_DATE ,END_DATE,CREATION_DATE,
LAST_UPDATE_DATE)
SELECT
SESSION_ID,TASK_NUMBER,COMPLETION_STATUS,
CONTEXT ,START_DATE , nvl(END_DATE,SYSDATE),SYSDATE,
SYSDATE
from
AD_SESSION_TASKS
where
SESSION_ID=p_sess_id;
DELETE FROM AD_SESSION_TASKS
WHERE session_id = p_sess_id;