Self Validating Scripts Parts

self_validating_script_part_1.sql

UPDATE TABLE1 SET COL1 = 'XYZ' WHERE STATUS = 'N'
IF SQL%ROWCOUNT <> 5 THEN
  l_msg := 'Update Failed';
  raise_application_error(-20101, l_msg);
ELSE
  l_msg := 'Upadte Successfully'
  dbms_output.put_line(l_msg);
  COMMIT;
END IF

self_validating_script_part_2.sql

SELECT COUNT(*) INTO l_number_of_date
FROM TABLE1 WHERE STATUS = 'N';

UPDATE TABLE1 SET COL1 = 'XYZ' WHERE STATUS = 'N'
IF SQL%ROWCOUNT <> l_number_of_date THEN
  l_msg := 'Update Failed';
  raise_application_error(-20101, l_msg);
ELSE
  l_msg := 'Upadte Successfully'
  dbms_output.put_line(l_msg);
  COMMIT;
END IF

self_validating_script_part_3.sql

l_exp_upper_bound := 0
l_exp_lower_bound := 0

UPDATE TABLE1 SET COL1 = 'XYZ' WHERE STATUS = 'N';

IF SQL%ROWCOUNT >= (l_exp_upper_bound * 0.2) OR SQL%ROWCOUNT <= (l_exp_lower_bound * 0.2) THEN
  l_msg := 'Update Failed';
  raise_application_error(-20101, l_msg);
ELSE
  l_msg := 'Upadte Successfully'
  dbms_output.put_line(l_msg);
  COMMIT;
END IF

self_validating_script_part_4.sql

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 := 115; --modify here
		WHEN 'DEV' THEN
			l_count_exp := 101; --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;