Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

user12240205Oct 24 2014 — edited Oct 28 2014

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.

This post has been answered by thatJeffSmith-Oracle on Oct 28 2014
Jump to Answer

Comments

sb92075
user454349 wrote:
Hi,
To set up DB environment similar to my Office, I installed Oracle 9i on my Windows 7(64 Bit)
V9 Oracle has been obsoleted & unsupported this whole Century & never certified on Windows 7

why are you willing to upgrade & use new & supported OS but unwilling to do the same with Oracle?
Srini Chavali-Oracle
Pl identify which version of Win 7. The only database versions certified on Win 7 Professional or higher are 10.2.0.5 and 11gR2. You cannot install 9i on Win 7 and expect it to work without issues.

HTH
Srini
Sriram Muraleedaran
Hi Srini and sb92075

thanks for the reply. i have windows 7 basic home edition..I want to set up the environment same as my office ..but we have win xp at office..I am afraid of any downward compatibility issues if i install oracle 10g and develop my forms and try to run in my office...

Regards
Sri
sb92075
hokoba wrote:
Hi Srini and sb92075

thanks for the reply. i have windows 7 basic home edition..
no version of Oracle is ever certified on any Home edition of any Windows version
Srini Chavali-Oracle
If you want to emulate what you have in your office, then you will need to install Win XP Pro on your machine (or in a VM on your nachine) - I do not see any other option.

HTH
Srini
Helios-GunesEROL
Hi Sri;

You can go with

1. Download OEL 5.x version from e-delivery.
2. Create VM machine(make googling you can find many good site)
3. Use your OEL software and create Vm linux machine on your win7 laptop
4. Install 11g and use it for your purpose

Pelase review:

Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]
How to Install Oracle 10gR2 on MS Windows 7 / Windows 2008R2 [ID 1173433.1]
Is Microsoft Windows 7 certified to install/run Oracle Database Server/Client ? [ID 952302.1]
Statement of Direction: Oracle Database 11g Release 2 - Microsoft Windows 7 and Windows Server 2008 R2 [ID 867040.1]

Regard
Helios
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 25 2014
Added on Oct 24 2014
4 comments
1,189 views