Developing SQL for automated execution

We have a team of developers who have very good knowledge of our applications. Sometimes the business wants the app changed but cannot make the changes through the application itself. The developers figure out which tables and columns need to be updated, test in non-production and then pass the script to the DBA team for execution. Often these scripts require inspecting the results of queries or other SQL statements and making a decision on whether to proceed with the next step or stop and roll back the changes. Not a big deal except when the business also requires the changes go into effect late at night or on the weekend.

I have been modifying the SQL for those off-hour execution times and then scheduling them to run at the expected time. I decided to train the development team so they could write the SQL themselves. They are new to Oracle but know SQL Server and SQL in general. I showed them how to write SQL that checks the actual results and rolls back or commits depending on expected results.

This works great and saves the DBA team a lot of late night work.

Here’s an example :

drop table SCHEMANAME.TABLENAME_BKP_changenum_B4;
 create table SCHEMANAME.TABLENAME_BKP_changenum_B4 as select * from SCHEMANAME.TABLENAME
 where
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 And column2 = '*ANY'
 ) or (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 ) or (
 column1 in ('0040019','0040085','0040086','0040145','0040405','0040406')
 And column2 <>'*ANY'
 ) or (
 column1 in ('54','55','56','57','59','79')
 And column2 <>'*ANY'
 );
 -- >>>> 210 ROWs or more is okay to proceed

declare
 exp_rows number;
 u_rows number;
 rowcount_fail exception;
 BEGIN
 dbms_session.set_identifier('changenumber');
 exp_rows:=41;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL#1 - DELETE #1

delete SCHEMANAME.TABLENAME
 WHERE
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 And column2 = '*ANY'
 );
 -- >>>> 41 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=27;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL #2 - update #1

UPDATE SCHEMANAME.TABLENAME
 set column3 = '*ANY',
 column3y = '*A',
 column4 = (100000 + to_char(sysdate,'YYDDD')),
 column5 = 'changenumber',
 colummn6 = SUBSTR(sys_context('USERENV','SERVER_HOST'),1,10)
 where
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 );
 -- >>>> 27 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=129;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL #3 - update #2

UPDATE SCHEMANAME.TABLENAME
 set column3 = '*ANY',
 column3y = '*A',
 column4 = (100000 + to_char(sysdate,'YYDDD')),
 column5 = 'changenumber',
 colummn6 = SUBSTR(sys_context('USERENV','SERVER_HOST'),1,10)
 where
 (
 column1 in ('0040019','0040085','0040086','0040145','0040405','0040406')
 And column2 <>'*ANY'
 );
 -- >>>> 129 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=13;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL#4 - DELETE #2

delete SCHEMANAME.TABLENAME
 WHERE
 (
 column1 in ('54','55','56','57','59','79')
 And column2 <>'*ANY'
 );
 -- >>>> 13 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

--rollback;
 commit;

exception
 when rowcount_fail then
 dbms_output.put_line('Rows updated not >= expected row count, rolling back the change');
 rollback;
 when others then
 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 rollback;
 END;
 /

Advertisements