Skip to content

Instantly share code, notes, and snippets.

@sonu041
Last active December 25, 2023 15:48
Show Gist options
  • Save sonu041/618b7e8e95ccd0c6e9e023c4e6f06703 to your computer and use it in GitHub Desktop.
Save sonu041/618b7e8e95ccd0c6e9e023c4e6f06703 to your computer and use it in GitHub Desktop.
Self Validating Script
SET SERVEROUTPUT ON SIZE 200000;
SET SCAN OFF;
WHENEVER SQLERROR EXIT;
DECLARE
l_env VARCHAR2(50) := '';
l_count_act NUMBER := 0;
l_count_exp NUMBER := 0;
l_exp_upper_bound NUMBER := 0;
l_exp_lower_bound NUMBER := 0;
l_msg VARCHAR2(2000) := '';
l_user VARCHAR2(20) := '';
PROCEDURE data_update(l_pid IN VARCHAR2) AS
l_data_exist NUMBER := 0;
l_acquired NUMBER := 0;
l_psys_id NUMBER := 0;
BEGIN
--Checking if data exists
SELECT COUNT(scope.sys_id)
INTO l_data_exist
FROM data_scope scope
WHERE scope.pid = l_pid;
IF l_data_exist = 0 THEN
RAISE_APPLICATION_ERROR(-20101,
'Data does not exist');
END IF;
--Take the map sys id
SELECT map_sys_id
INTO l_psys_id
FROM map
WHERE level = 5
AND node = '0072';
--Update data
FOR i IN (SELECT scope.sys_id
FROM data_scope scope
WHERE pid = l_pid) LOOP
UPDATE data_scope
SET mac_sys_id = l_psys_id,
last_update_date = systimestamp,
last_update_user_id = 134
WHERE sys_id = i.sys_id;
IF sql%Rowcount <> 1 THEN
l_msg := 'Update of data failed for : ' || i.sys_id ;
raise_application_error(-20101, l_msg);
ELSE
l_count_act := l_count_act + 1;
l_msg := 'Update of data successful : ' || i.sys_id ;
dbms_output.put_line(l_msg);
END IF;
END LOOP;
END data_update;
BEGIN
dbms_output.put_line('Starting Execution for data: ' ||
TO_CHAR(systimestamp, 'MM-DD-YYYY HH24:MI:SS.FF'));
--Find DB Environment specific data count
SELECT
regexp_substr(sys_context('userenv', 'db_name'), '_([^_]*)', 1, 1, 'i', 1)
INTO l_env
FROM
dual;
dbms_output.put_line('Database: ' || l_env);
BEGIN
CASE l_env
WHEN 'PROD' THEN
l_count_exp := 110; --modify here
WHEN 'UAT' THEN
l_count_exp := 100; --modify here
WHEN 'DEV' THEN
l_count_exp := 87; --modify here
ELSE
RAISE case_not_found;
END CASE;
EXCEPTION
WHEN case_not_found THEN
raise_application_error(-20102, 'No database environment found during update : '
|| sqlcode
|| ' : '
|| sqlerrm);
END;
--data_update(pid); --Modify Here
data_update('1234567');
dbms_output.put_line('Completing Execution : ' || to_char(systimestamp, 'MM-DD-YYYY HH24:MI:SS.MMSS'
));
l_exp_upper_bound := l_count_exp + l_count_exp * 0.2;
l_exp_lower_bound := l_count_exp - l_count_exp * 0.2;
IF l_count_act <= l_exp_upper_bound AND l_count_act >= l_exp_lower_bound THEN
COMMIT;
ELSE
l_msg := 'Expected update count = '
|| l_count_exp
|| ' : actual update count = '
|| l_count_act
|| ' : update failed';
raise_application_error(-20103, l_msg);
END IF;
--Avoid exit with uncommited data
IF dbms_transaction.local_transaction_id IS NULL THEN
dbms_output.put_line('.........Commit Successful.........');
ELSE
dbms_output.put_line('.........Commit Not Successful.........');
raise_application_error(-20104, 'Commit Failed');
END IF;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment