Self Validating Script

self_validating_script.sql

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;