Self-Validating Script in SQL

Image generated using Bing Image Creator from Designer

We all write SQL scripts to do database operations. When you are supporting an application that is running in Production it’s very critical when you are modifying the data by running SQL scripts. From web applications, some validation logics are written but when you are updating directly in the table, there is a risk that some wrong data gets modified by mistake.

There is another scenario where mistakes can happen. Generally, when we run an update script in a live application there is a standard process to test it in a lower environment first then move it to production. Sometimes you may not have access to insert/update/delete access to the production system. In that case, you prepare the script put it somewhere and another team like change control or DBA team gets the script and executes it. There is a high chance that data in the test and production environment is different and the same script behaves differently in test and production. So there is a high chance of making mistakes in production.

So what is the solution? Like the application validates the data before committing to the database, we can implement validation inside the script which will self-validate the script. It will check what it is supposed to do and then only commit. Let’s see how this can be implemented.

Suppose you want to update a table based on some condition. First, check how many number of data it is supposed to update. Let’s say 5. Now you do a check after the update query that is it updates 5 records like below.

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

Same thing applies in insert, update, and delete.

One thing needs to be kept in mind, always give the hardcoded number by checking the data beforehand. Never select the count of the query and immediately after update the data. If you select the count and update it immediately it will never fail. This is not proper validation. The select query or checking of the data needs to be done beforehand. The below query is not the correct way to validate the update.

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

Now there might be a scenario when you cannot tell the exact count of the record that needs to be updated as it’s a live system and the user keeps updating the records. The count might be different when you are making the script and executing it in production (as it might take time to test the script in a lower environment and approve it). In that case, you need to give an allowed threshold. Like you can allow a maximum 2% deviation of data (the percentage might change depending on the number of records to be updated). Let’s take an example.

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

Sometimes different environment has different numbers of records. We can create environment-specific count and put it in validation.

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;

Lets see a complete sample code where self-validation is implemented.

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;

We got this idea from Architect Lee Rosenfeld. We successfully implemented it in our day-to-day operation and this saves us a lot from bad data updates in the application.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.