Skip to content

Instantly share code, notes, and snippets.

@sonu041
Created December 25, 2023 15:52
Show Gist options
  • Save sonu041/1df0750ce292791e504df983c8923886 to your computer and use it in GitHub Desktop.
Save sonu041/1df0750ce292791e504df983c8923886 to your computer and use it in GitHub Desktop.
Self Validating Scripts Parts
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
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
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
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment