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

Test-Driven Development With Oracle's PL/SQL

Test Güdümlü Programlama(Test-Driven Development), çevik(agile) yöntemlerden biri olan XP(Extreme Programming)'in bir parçasıdır. Son zamanlarda sıklıkla yaşanan, yazılımın istendiği şekilde çalışmaması olarak tanımlanan bug'ların tehlikeli ve maliyetli sonuçları ile kendinden daha bir söz ettiren TDD, kaliteli yazılım ürününün oluşması için çalışır. 2002 yılında ComputerWorld'un yaptığı araştırmaya göre bug'ların analiz ve çözümünün ABD ekonomisine yaklaşık 60 milyar $ maliyeti de düşünülürse, TDD'nin ne derece önemli bir konu olduğu daha iyi farkedilecektir.
Genel olarak test yöntemleri başta ve sonda olmak üzere 2 türlüdür. Testin sonda yapılması klasik yöntemdir. Kod yazılır ve sonunda testler(unit) yapılır. Testi başa almak ise TDD'nin temelini oluşturur. Kod yazılmadan testleri yazılır ve daha sonra kodlamaya girilir. En sonunda ise gerekli durumlarda refactoring olarak iyileştirmeler yapılır.
TDD'yi oluşturan test-first development aşağıdaki döngü ile belirtilebilir:

Kodlama Yapılmaz
Test Yazılır
Test Çalıştırılır
Test Hata Alır
Testi Geçebilecek Kadar Kod Yazılır
Tekrar Çalıştırılır
Hata Alınırsa İşlemler Tekrarlanır
Test Hatasız Geçilir
Test Yazılır
…...
TDD'nin sağladığı avantajlar ise şu şekilde belirtilebilir:

Yüksek Kalitede Kod(Yazılım Ürünü) Oluşur.
Yazılımın Doğru Çalıştığının Kanıtıdır.
Büyük Problemleri Küçük Parçalara Böler.
Her Test Döngü Sonucu Geribildirimlere İmkan Tanır.
Evrimsel Yazılım Geliştirme Yöntemlerini Destekler.
Java için JUnit, .NET için NUnit ve PL/SQL için ise OUnit TDD ile kullanılabilecek araçlara örnek verilebilir.

PL/SQL ile bu yöntemi kullanarak yazılan örnek bir uygulama aşağıda belirtilmiştir. Bu örnek, kendisine parametre olarak gelen bir tarihi, istenen formata dönüştürme işlemini gerçekleştirmektedir.

Format için gerekli tablo oluşturulur:
SQL> create table date_formats( id number, format varchar2(16));
Table created
SQL> insert into date_formats values(1, 'MMDDYY');
1 row inserted
SQL> insert into date_formats values(2, 'MM.DD.YYYY');
1 row inserted
SQL> select * from date_formats;
        ID FORMAT
---------- ----------------
         1 MMDDYY
         2 MM.DD.YYYY
SQL>

Test Yazılır:
SQL> create or replace package date_format_tests as
  2    procedure test_format1;
  3  end;
  4  /
Package created
SQL> show err;
No errors for PACKAGE HR.DATE_FORMAT_TESTS
SQL> create or replace package body date_format_tests as
  2 
  3    procedure test_format1 is
  4      vn_FormatId number;
  5      vs_FormattedDate varchar2(32);
  6      vs_Expected varchar2(32);
  7      vd_Date date;
  8    begin
  9      vn_FormatId := 1;
 10      vd_Date := to_date('01.12.2006', 'MM.DD.YYYY');
 11      vs_Expected := '011206';
 12      vs_FormattedDate := date_format.get_formatted_date(vn_FormatId, vd_Date);
 13      if vs_Expected = vs_FormattedDate then
 14        dbms_output.put_line('Test is Succesful....');
 15      else
 16        dbms_output.put_line('Test Failed!!!');
 17      end if;
 18    end;
 19 
 20  end;
 21  /

Test Hata Alır:
Warning: Package body created with compilation errors
SQL> show err;
Errors for PACKAGE BODY HR.DATE_FORMAT_TESTS:
LINE/COL ERROR
-------- -----------------------------------------------------------------------
12/25    PLS-00201: identifier 'DATE_FORMAT.GET_FORMATTED_DATE' must be declared
12/5     PL/SQL: Statement ignored
SQL> exec date_format_tests.test_format1;
begin date_format_tests.test_format1; end;
ORA-04063: package body "HR.DATE_FORMAT_TESTS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "HR.DATE_FORMAT_TESTS"
ORA-06512: at line 1

Kodlama Yapılır:
SQL> create or replace package date_format as
  2    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2;
  3  end;
  4  /
Package created
SQL> show err;
No errors for PACKAGE HR.DATE_FORMAT
SQL> create or replace package body date_format as
  2 
  3    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2 is
  4    vs_Result varchar2(32);
  5    begin
  6      vs_Result := 'N/A';
  7      return vs_Result;
  8    end;
  9 
 10  end;
 11  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.DATE_FORMAT

Test Yanlış Sonuç Üretir:SQL> exec date_format_tests.test_format1;
Test Failed!!!
PL/SQL procedure successfully completed

Tekrar Kodlama Yapılır:
SQL> create or replace package body date_format as
  2 
  3    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2 is
  4    vs_Result varchar2(32);
  5    vs_DateFormat varchar2(32);
  6    begin
  7      select df.format into vs_DateFormat from date_formats df where df.id = pin_FormatId;
  8      vs_Result := to_char(pid_Date,vs_DateFormat );
  9      return vs_Result;
 10    end;
 11 
 12  end;
 13  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.DATE_FORMAT


Test Geçer:SQL> exec date_format_tests.test_format1;
Test is Succesful....
PL/SQL procedure successfully completed

İyileştirmeler Yapılır:
SQL> create or replace package body date_format as
  2 
  3    function get_single_query_result(pis_TableName in varchar2,pis_ColumnName  in varchar2, pis_WhereCondition  in varchar2) return varchar2 is
  4      vs_Result varchar2(32);
  5      vs_SqlStatement varchar2(1024);
  6    begin
  7      vs_SqlStatement := 'select ' || pis_ColumnName || ' from ' || pis_TableName ||  '  where ' || nvl(pis_WhereCondition, '1=1');
  8      execute immediate vs_SqlStatement into vs_Result;
  9      return vs_Result;
 10    end;
 11 
 12    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2 is
 13      vs_Result varchar2(32);
 14      vs_DateFormat varchar2(32);
 15    begin
 16      vs_DateFormat := get_single_query_result('date_formats', 'format', 'id = ' || pin_FormatId);
 17      vs_Result := to_char(pid_Date,vs_DateFormat );
 18      return vs_Result;
 19    end;
 20 
 21  end;
 22  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.DATE_FORMAT
SQL> exec date_format_tests.test_format1;
Test is Succesful....
PL/SQL procedure successfully completed