4 Replies Latest reply on Oct 28, 2014 2:15 PM by thatJeffSmith-Oracle

    Is there any real ADVANTAGE in SQL*Developer Automated Unit Testing?

    user12240205

      I am trying to understand Unit Testing in SQL*Developer. I was asked to evaluate it as a method of test automation on a integration solution we are developing.

       

      (1.) My "A" schema has this table:

      SQL> DESC emps

      Name                                      Null?    Type

      ----------------------------------------- -------- --------------------

      EMPID                                     NOT NULL NUMBER

      EMPNAME                                            VARCHAR2(30)

      SALARY                                             NUMBER(7,2)

      COMM_PCT                                           NUMBER(2,2)

       

       

      (2.) Table has these values:

      SQL> SELECT * FROM emps;

       

       

           EMPID EMPNAME        SALARY   COMM_PCT

      ---------- ---------- ---------- ----------

               1 ROHAN           10000        .05

               2 JOHN            20000

               3 PETER                        .06

               4 SMITH

       

       

      (3.) I create this procedure in A:

       

      create or replace

      PROCEDURE award_bonus(  p_empid NUMBER, sales_amt NUMBER)

        AS 

        commission    REAL; 

        comm_missing  EXCEPTION;

      BEGIN 

        SELECT comm_pct INTO commission FROM emps

        WHERE empid = p_empid;  

       

        IF commission IS NULL THEN  

          RAISE comm_missing; 

        ELSE   

          UPDATE emps

          SET salary = salary + sales_amt*commission    

          WHERE empid = p_empid; 

        END IF;

      END award_bonus;

       

       

      (4.) Now I want to create a unit test IN SQL Developer to test this procedure.

      (5.) I created a "unit_test_repos" user as instructed by the Tutorial.

      (6.) "SELECT OPERATION": I specify the award_bonus procedure.

      (7.) "SPECIFY Test Name": give a name and select "Create with single dummy".

      (8.) "SPECIFY STARTUP": Add a 'Table or row copy" Startup process.

              -- I give "emps" as the source table.

              -- Then, I get this for the "generated query"

                 SELECT ROWID AS SRC$ROWID, s.* FROM emps s

              -- Target table has "TMP$AWARD_BONUS".

       

       

              (Q1) WHAT IS THE PURPOSE OF CREATING ANOTHER TABLE?

              (Q2) WHEN THE TEST IS DONE, ARE ROWS FROM emps copied to this table?

              (Q3) Why is that? Why can't the test just read from the "emps"?

      (9.) "SPECIFY PARAMETERS": I give for "Input"

              -- 1 for P_EMPID

              -- 10000 for SALES_AMT

              -- In "Expected Result" I say "Success".

              (Q4) So, I should know in my mind the outcome of the proc, for the above given inputs???

      (10.) "SPECIFY VALIDATION": I add a "Query Returning row(s)" validation.

                -- I paste the following query: SELECT count(*) FROM emps WHERE empid=1 AND salary = 10500

                -- i.e. I know that for a 10000 sales amount, employee 1 will get a commissin of 10000*.05 = 500. So, 10000 (his sal) + 500 = 10500.

              (Q5) Is the above thinking correct?

      (11.) "SPECIFY TEARDOOWN": Give nothing.

      (12.) My unit test is created.

      (13.) I now run it by clicking the beetle button, left of the green arrow. Unit test was executed.

      (14.) Now, in my a.emps table, 1's salary has increased to 10,500, and in the unit test diag box, I get "Execution Results: SUCCESS".

      (15.) Now, I run the unit test again.

      (16.) The "emps" table salary for emp 1 has increased to 11000 (which is correct)  and the execution results say 'SUCCESS".

            (Q6)  My question is: The query I gave for validation, SELECT count(*) FROM emps WHERE empid=1 AND salary = 10500, now gives 0. so, this means that no rows returned for this condition and hence the test should FAIL. But, how come SQL*Developer is saying that the test was SUCCESS. How does this validation system work?

       

      (17.) I went through the tutorial, but I just don't understand these concepts>

               (Q7) What is the purpose of Teardown? I undersand the "drop table" option of "teardown", where you drop the temp table, but I don't understand the others, "Table or row restore" and "user pl/sql code"?

               (Q8) WHAT IS THE advantage of doing unit testing like this? I can use an Excel sheet and do dozens of unit tests faster than this method?  It's easy to understand also. This method is complicated.

       

      Your replies and advice would be greatly appreciated.

        • 1. Re: Is there any real ADVANTAGE in SQL*Developer Automated Unit Testing?
          user12240205

          I would be most greatful if anybody can give insites into the SQL*Developer unit testing. We are trying to automate the testing of our intergration app..

          • 2. Re: Is there any real ADVANTAGE in SQL*Developer Automated Unit Testing?
            I am trying to understand Unit Testing in SQL*Developer.

            Then the place to start is by RTFM. The Sql Developer User Guide has an example that it looks like you have copied but modified.

            http://docs.oracle.com/cd/E39885_01/appdev.40/e38414.pdf

             

            See page 3-6 in that doc and FOLLOW that example. It explains each step and answers many, if not most, of the questions you asked.

            (Q1) WHAT IS THE PURPOSE OF CREATING ANOTHER TABLE?

            And here is what the doc says for the 'Startup' step of that example:

            5.

            In Specify Startup, select Table or Row Copy because you want to save the current

            data values in the EMPLOYEES table before any data is modified by the unit test.

            And this question:

            (Q3) Why is that? Why can't the test just read from the "emps"?

            Because your code does an UPDATE to the table. The code doesn't just 'READ' it also UPDATEs. So how can you test that the update works properly if you don't compare the BEFORE data to the AFTER data?

             

            The doc also explains the setup and teardown steps.

             

            Testing that involves data has to compare the data BEFORE the test to the data AFTER the test in order to know that the code modified the data properly. That generally involves YOU, the test writer, creating the proper BEFORE data to use for the test and also creating the proper AFTER data that is stored in a test table.

             

            The actual test will then process the BEFORE data and produce its own AFTER data. That AFTER data then has to be compared to the AFTER data that YOU, the test writer, created as the CORRECT test results. If the data matches the test passes; otherwise the test fails.

             

            Do you really want to do ALL of that work manually for hundreds of tests EVERY TIME you want to perform testing?

            1 person found this helpful
            • 3. Re: Is there any real ADVANTAGE in SQL*Developer Automated Unit Testing?
              user12240205

              What I don't understand is this: When I ran the unit test from SD, the EMPS table's SALARY column increased.

              So, why create temp table??? I don't see any use of the temp table.

               

              Also, I still don't get the validation process:  We are saying to SD, in order to validate the test, run this query: SELECT count(*) FROM emps WHERE empid=1 AND salary = 10500;

               

              So, after doing the test, the test is a SUCCESS, only this above query gives > 0.

               

              My problem is, during the 2nd run of the test, this query gives 0, since now, the salary for this employee has been increased.  So, how can it say that the test was a SUCCESS.

               

              If we want to creat a unit test that will work propery, everytime we run it, then how can we do that?

              • 4. Re: Is there any real ADVANTAGE in SQL*Developer Automated Unit Testing?
                thatJeffSmith-Oracle

                The temp table hold the original values for EMP. After the test runs, you need to configure the teardown to reset the values back from the temp table so the next time the test runs again, it has the expected start values.