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

Hoek
Unfortunatly no 11g for me here but on 10.2.0.4 I get immediate results:
SQL> with t as (select '0' str from dual)
  2  select *
  3  from t
  4  where regexp_like (str, '^d{0,2}*$');

no rows selected

SQL> with t as (select '0' str from dual)
  2  select *
  3  from t
  4  where regexp_like (str, 'd{0,2}*');

S
-
0
Does the second version also hang on your database?
Solomon Yakobson
Works fine on:
SQL> select * from v$version
  2  /
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> with T as (
  2  select '0' str from dual)
  3  select *
  4  from T
  5  where regexp_like (str, '^d{0,2}*$')
  6  /

no rows selected

SQL> 
SY.
Frank Kulash
Answer
Hi,
CharlesRoos wrote:
Why Oracle 11 hangs on this statement, statement never ends execution:
with T as (
select '0' str from dual)
select *
from T
where regexp_like (str, '^d{0,2}*$')
Does it start execution? In SQL*Plus, you have to end the statement with a ; or put a / on the next line.

It works for me.

What is the * near the end of the 2nd argument supposed to do?
'd' means the lower-case letter 'd'. To indicate a digit, you can use '\d' (with a \ ).
Marked as Answer by WestDrayton · Sep 27 2020
Solomon Yakobson
And, btw it should be '^<font color=red>\</font>d{0,2}*$'. Now if you did have a backslash and it was a posting typo and you use SQL*Plus check if SET PAUSE is ON. If it is, you must hit enter to get result page.

SY.
WestDrayton
Ok, thx,
i did something wrong myself here.
Aketi Jyuuzou
select * from V$version;

BANNER
--------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production 
NLSRTL Version 11.2.0.1.0 - Production
Wow! My oracle hangs too.
select *
  from dual where RegExp_Like('0','^a{0,2}*$');
But actually,This regex is wrong regex.
a{0,2}*
WestDrayton
Does it really went to sandclock on your Ora11 too?
I thought i had some other issues why i got the statement 5 hours running in my oracle.

I am afraid to run this statement again. but seems it is a asystem bug then.

Edited by: CharlesRoos on Apr 22, 2010 11:56 PM
1 - 7
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,191 views