Test Automation of PL/SQL Program Units on Scrum Agile Methodology

The IT department of company that I work for has made a (really) radical decision and changed its organizational structure in order to gain benefits of Scrum agile methodology. This was really a big decision; all developers, testers, architectures, business analysts and etc. should change their style of working... Anyway, there are lots to tell about transition process, as the one who was enrolled-in all of passing stage. Maybe later, I’ll write an independent post about this; but not nowJ.

Up to now, 12 sprints (the term sprint stands for short duration in Scrum) has been passed. I can admit that, I have never seen a productive and efficient development cycle before. The PBIs (product backlog items) are analyzed, developed, tested and deployed quickly, within the sprint. All team members are commitment and result oriented. The team has also an independent and autonomous structure. The scrum master, product owner and the team create scrum structure. All decisions are taken inside the team, not by the force of the management. Planning (before sprint), review (after sprint), retrospective (after sprint) and daily scrum (up to 15 minutes) meetings are the only meetings and they do not spend lots of time.  During sprint, a Burndown chart ( the chart that shows the process and status of sprint backlog items) and is creating within daily meetings. Scrum has lots of benefits that I cannot go deep inside, here.

Main development language of the company is PL/SQL. Lots of programs, scripts are written in PL/SQL and they live in the Oracle database. When a new request of change (CR) comes or a bug is submitted, mostly, we are changing a stored procedure. The stored procedures are encapsulated with packages. Sometimes changing a procedure inside a package would require some additional testing processes (regression testing). So in order to gain in terms of time, we should make some of the test processes to be executed automatically. From this starting point, we began to develop some codes for test automation. You cannot automate all test cases. You can make gain-loss analyze to decide whether you do this activity or not. In some situation it is also not possible to make tests automated; for instance, you are testing a color of GUI element and it is not possible to see RGB code values within GUI programmatically.

Test automation is one of methodologies that are used in agile software development disciplines. TDD (test-driven development) is one of them. With TDD, you are responsible to write a test case code, which will fail initially, before writing any lines of code. This will make you safe and comfortable, during refactoring phases of development. Writing a test case before development will also make you to concentrate on business requirements. It will be also a good practice to share this case to business people. I have seen some situations that business people does not what they want. In order to understand biz-requirements in a more clear fashion, you should illustrate it. Because of the test case contains input and expected output, illustration will be easy. Please note that, main aim of TDD is unit testing of a small piece of code. But it is also possible to create a high level test case that checks a complete business scenario.

There are many tools that are concentrated on execution of automated tests. I will not discuss them. I will show you a real example how can you make your tests to be executed automatically. I will use some Mock Objects (an object that simulates real object, has API functions with same signatures etc.) as Test Stubs (the code that is used for simulation of real code or function, most probably the function is not ready to use or is not suitable for using like payment transactions etc.) in my Test Driver (the function that is used to execute test case) function.

Business Request: Create an API function that sends a notification message by inserting a new record into NOTIF_RATEPLAN_CHANGE table in case of rate plan of a GSM subscriber is changed. The table should contain an amount field that the subscriber is liable for paying, until rate plan change. We are only responsible to insert a new record in the table. Changing rate plan functionality exists in the production environment and works correctly.

Mock Object, Test Stub: Calculating amount to be paid for the customer has a complex logic. We are not interested-in this functionality and we assume that it works fine. GetAmountTobePaid stub function will return a dummy amount.

Implementation: First, we will create Test Stub; then Test Driver code. Please note that, automated test driver code has more lines of code than the development one.

CREATE OR REPLACE FUNCTION GetAmountTobePaid(pin_SubscriberId IN NUMBER)

  RETURN NUMBER IS

  vn_AmountTobePaid NUMBER;

BEGIN

  --

  -- this is real function of GetAmountTobePaid.

  -- this function is quite complex and makes some dblink or web service calls..

  --

  RETURN vn_AmountTobePaid;

 

END GetAmountTobePaid;

/

-- DEVELOPMENT PHASE

--create the table for new request of change

CREATE TABLE NOTIF_RATEPLAN_CHANGE

(

  SUBSCRIBER_ID      NUMBER,

  RATEPLAN           NUMBER,

  AMOUNT_TOBE_PAID   NUMBER,

  CHANGE_DATE        DATE DEFAULT SYSDATE

);

-- this function is created for the notification, by developers

-- this function should be tested and test should also be automated

CREATE OR REPLACE PROCEDURE NotifyOnRateplanChange

(

  pin_SubscriberId  IN NUMBER,

  pin_NewRateplanId IN NUMBER

) IS

  vn_AmountTobePaid NUMBER;

BEGIN

  -- get amount to be paid by the customer

  vn_AmountTobePaid := GetAmountTobePaid(pin_SubscriberId);

  -- make a notification

  INSERT INTO NOTIF_RATEPLAN_CHANGE

    (SUBSCRIBER_ID, RATEPLAN, AMOUNT_TOBE_PAID)

  VALUES

    (pin_SubscriberId, pin_NewRateplanId, vn_AmountTobePaid);

   

  COMMIT;

END NotifyOnRateplanChange;

/

-- TESTING PHASE

-- stub version of GetAmountTobePaid

CREATE OR REPLACE FUNCTION GetAmountTobePaid(pin_SubscriberId IN NUMBER)

  RETURN NUMBER IS

  vn_AmountTobePaid NUMBER;

BEGIN

  --

  -- this is stub version of GetAmountTobePaid function.

  -- this function overwrites, real one

 

  dbms_output.put_line('Test Stub GetAmountTobePaid is starting...');

 

  -- set a dummy value for amount...

  vn_AmountTobePaid := 100;

  dbms_output.put_line('Test Stub GetAmountTobePaid is finished with returning dummy value of ' || vn_AmountTobePaid);

  RETURN vn_AmountTobePaid;

 

END GetAmountTobePaid;

/

--automaed test case procedure for NotifyOnRateplanChange function, test driver

CREATE OR REPLACE PROCEDURE TC_NotifyOnRateplanChange IS

  vn_AmountTobePaid NUMBER;

  vn_SubscriberId   NUMBER;

  vn_NewRateplanId  NUMBER;

  vn_SubsCount      NUMBER;

BEGIN

  dbms_output.put_line('Test Case TC_NotifyOnRateplanChange is starting...');

  dbms_output.put_line( 'Test setup starting...' );

  dbms_output.put_line( 'Initialising values for testing' );

  vn_SubscriberId  := 10012290;

  vn_NewRateplanId := 261;

  dbms_output.put_line('  vn_SubscriberId    = ' || vn_SubscriberId);

  dbms_output.put_line('  vn_NewRateplanId   = ' || vn_NewRateplanId);

  dbms_output.put_line( 'Deleting records for the subscriber from NOTIF_RATEPLAN_CHANGE table' );

  DELETE FROM NOTIF_RATEPLAN_CHANGE

   WHERE SUBSCRIBER_ID = vn_SubscriberId

     AND RATEPLAN = vn_NewRateplanId;

  dbms_output.put_line('  Deleted row count from NOTIF_RATEPLAN_CHANGE table ' ||  SQL%ROWCOUNT);

  COMMIT;

                      

  dbms_output.put_line( 'Test setup finished...' );

 

  dbms_output.put_line( 'Calling function to-be tested: NotifyOnRateplanChange....' );

  NotifyOnRateplanChange(vn_SubscriberId, vn_NewRateplanId);

  dbms_output.put_line( 'Function executed successfully...' );

  dbms_output.put_line( 'Checking results...' );

 

  dbms_output.put_line( 'Calling test stub to get amount to be paid by the customer.' );

  vn_AmountTobePaid := GetAmountTobePaid(vn_SubscriberId);

  dbms_output.put_line( '  vn_AmountTobePaid  = ' || vn_AmountTobePaid );

  dbms_output.put_line( 'Checking wheter a new record is inserted into NOTIF_RATEPLAN_CHANGE or not' );

  SELECT COUNT(*)

    INTO vn_SubsCount

    FROM NOTIF_RATEPLAN_CHANGE

   WHERE SUBSCRIBER_ID = vn_SubscriberId

     AND RATEPLAN = vn_NewRateplanId

     AND AMOUNT_TOBE_PAID = vn_AmountTobePaid;

  IF vn_SubsCount = 0 THEN

    dbms_output.put_line( '********   TEST CASE FAILED ******* ' );

    dbms_output.put_line( 'Record does not seem to exist in the NOTIF_RATEPLAN_CHANGE table' );

  ELSE

    dbms_output.put_line( 'TEST CASE PASSED' );

  END IF;

  dbms_output.put_line('Test Case TC_NotifyOnRateplanChange is finished succesfully.');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line( '!!!!! TEST CASE GOT ERRORS' );

    dbms_output.put_line('Test Case TC_NotifyOnRateplanChange is finished with errors:' || SQLERRM );

 

END TC_NotifyOnRateplanChange;

/

SQL> exec TC_NotifyOnRateplanChange;

Test Case TC_NotifyOnRateplanChange is starting...

Test setup starting...

Initialising values for testing

  vn_SubscriberId    = 10012290

  vn_NewRateplanId   = 261

Deleting records for the subscriber from NOTIF_RATEPLAN_CHANGE table

  Deleted row count from NOTIF_RATEPLAN_CHANGE table 1

Test setup finished...

Calling function to-be tested: NotifyOnRateplanChange....

Test Stub GetAmountTobePaid is starting...

Test Stub GetAmountTobePaid is finished with returning dummy value of 100

Function executed successfully...

Checking results...

Calling test stub to get amount to be paid by the customer.

Test Stub GetAmountTobePaid is starting...

Test Stub GetAmountTobePaid is finished with returning dummy value of 100

  vn_AmountTobePaid  = 100

Checking wheter a new record is inserted into NOTIF_RATEPLAN_CHANGE or not

TEST CASE PASSED

Test Case TC_NotifyOnRateplanChange is finished succesfully.

PL/SQL procedure successfully completed