Tuesday, July 7, 2009

SQLUnit - PL/SQL Unit Testing using a JUnit Framework.

For those who come from a purely SQL and PL/SQL background and enter the world of Java programming, there are many sights and sounds to behold, and wonders to experience. Entering the world of Java programming may be the first time you are exposed to the concept of programming frameworks. Frameworks exist as a base supporting structure where you can slot elements of development while being comfortable in the fact that anyone who comes along in the future can pick up your code and 'know' where everything is, assuming they are familiar with the particular framework you used.
Many frameworks eventually become open-sourced as developers collaborate on the definition of the framework.

Code Frameworks examples
  • Spring
  • ADF
Build/Deployment Frameworks examples
  • Ant
  • Maven
Testing Frameworks example
  • Java - JUnit
  • PL/SQL - utPLSQL, PLUTO, SQLUnit

utPLSQL and PLUTO both require extra database objects to be installed/created. These are then used to call your PL/SQL to be tested.

SQLUnit can be run as a Java application, or incorporated into an ant script as part of an automated build.
Since it uses Java, the database connection uses JDBC. At the outset, this warns that there may be limitations on testing program units that require or output Oracle BOOLEAN variables or PL/SQL type structures. All other types should be usable.
(The following examples use an 11g database and the ojdbc5_g.jar jdbc database driver.)

Basic SELECT

<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
</connection>
<test name="#1 Simple SELECT - SELECT count(*) FROM emp" failure-message="Error with Simple SELECT">
<sql>
<stmt>select count(*) from emp</stmt>
</sql>
<result>
<resultset id="1">
<row id="1">
<col id="1" name="c1" type="NUMERIC">14</col>
</row>
</resultset>
</result>
</test>
</sqlunit>
Basic procedure call

Given procedure exists:
CREATE OR REPLACE PROCEDURE get_emp_name(p_empno IN NUMBER,
p_empname OUT VARCHAR2)
AS
CURSOR emp_csr
IS
SELECT ename
FROM emp
WHERE empno = p_empno;
BEGIN
OPEN emp_csr;
FETCH emp_csr INTO p_empname;
CLOSE emp_csr;
END get_emp_name;

Test case would be:

<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
</connection>
<test name="#2 Simple PROC call" failure-message="Error with Simple PROC call">
<call>
<stmt>{call get_emp_name(?,?)}</stmt>
<param id="1" name="p_empno" type="INTEGER" inout="in">7934</param>
<param id="2" name="p_empname" type="VARCHAR" inout="out"></param>
</call>
<result>
<outparam id="2" type="VARCHAR">MILLER</outparam>
</result>
</test>
</sqlunit>
Note that INTEGER is used for translating PL/SQL NUMBER types.

Basic function call
Given function exists:
CREATE OR REPLACE FUNCTION get_dept_name(p_deptno IN NUMBER)
RETURN VARCHAR2
AS
CURSOR dept_csr
IS
SELECT dname
FROM dept
WHERE deptno = p_deptno;
l_dname dept.dname%TYPE;
BEGIN
OPEN dept_csr;
FETCH dept_csr INTO l_dname;
CLOSE dept_csr;
RETURN l_dname;
END get_dept_name;
Test case would be:
<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
</connection>
<test name="#3 Simple FUNCTION call" failure-message="Error with Simple FUNCTION call">
<call>
<stmt>{? = call get_dept_name(?)}</stmt>
<param id="1" name="p_out" type="VARCHAR" inout="out"></param>
<param id="2" name="p_deptno" type="INTEGER" inout="in">10</param>
</call>
<result>
<outparam id="1" name="p_out" type="VARCHAR">ACCOUNTING</outparam>
</result>
</test>
</sqlunit>
Note that the return value is always the first parameter when calling a function.

Basic Function returning Cursor
Given Function exists:
CREATE OR REPLACE FUNCTION get_all_emps
RETURN SYS_REFCURSOR
IS
my_csr SYS_REFCURSOR;
BEGIN
OPEN my_csr FOR
SELECT *
FROM emp
ORDER BY empno;
RETURN my_csr;
END get_all_emps;
Test case would be:
<test name="#4 FUNCTION call returning CURSOR" failure-message="Error with FUNCTION call returning CURSOR">
<call>
<stmt>{? = call get_all_emps}</stmt>
<param id="1" name="p_out" type="CURSOR" inout="out"></param>
</call>
<result>
<outparam id="1" name="p_out" type="CURSOR">
<resultset id="1">
<row id="1">
<col id="1" name="EMPNO" type="NUMERIC">7369</col>
<col id="2" name="ENAME" type="VARCHAR">SMITH</col>
<col id="3" name="JOB" type="VARCHAR">CLERK</col>
<col id="4" name="MGR" type="NUMERIC">7902</col>
<col id="5" name="HIREDATE" type="TIMESTAMP">1980-12-17 00:00:00.000</col>
<col id="6" name="SAL" type="NUMERIC">800</col>
<col id="7" name="COMM" type="NUMERIC">NULL</col>
<col id="8" name="DEPTNO" type="NUMERIC">20</col>
</row>
<row id="2">
<col id="1" name="EMPNO" type="NUMERIC">7499</col>
<col id="2" name="ENAME" type="VARCHAR">ALLEN</col>
<col id="3" name="JOB" type="VARCHAR">SALESMAN</col>
<col id="4" name="MGR" type="NUMERIC">7698</col>
<col id="5" name="HIREDATE" type="TIMESTAMP">1981-02-20 00:00:00.000</col>
<col id="6" name="SAL" type="NUMERIC">1600</col>
<col id="7" name="COMM" type="NUMERIC">300</col>
<col id="8" name="DEPTNO" type="NUMERIC">30</col>
</row>
...
...
<row id="14">
<col id="1" name="EMPNO" type="NUMERIC">7934</col>
<col id="2" name="ENAME" type="VARCHAR">MILLER</col>
<col id="3" name="JOB" type="VARCHAR">CLERK</col>
<col id="4" name="MGR" type="NUMERIC">7782</col>
<col id="5" name="HIREDATE" type="TIMESTAMP">1982-01-23 00:00:00.000</col>
<col id="6" name="SAL" type="NUMERIC">1300</col>
<col id="7" name="COMM" type="NUMERIC">NULL</col>
<col id="8" name="DEPTNO" type="NUMERIC">10</col>
</row>
</resultset>
</outparam>
</result>
</test>
Function returning Simple Oracle UDT
Given Type exists:
CREATE OR REPLACE TYPE empRec AS OBJECT
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGRNO NUMBER(4),
MGRNAME VARCHAR2(10),
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
DNAME VARCHAR2(14));
Given Function exists:
CREATE OR REPLACE FUNCTION get_emp_details(p_empNo IN NUMBER)
RETURN empRec
IS
CURSOR emp_csr
IS
SELECT empRec(
e.empno,
e.ename,
e.job,
e.mgr,
e2.ename,
e.sal,
e.comm,
e.deptno,
get_dept_name(e.deptno))
FROM emp e, emp e2
WHERE e.empno = p_empNo
AND e.mgr = e2.empno (+);
l_emp empRec;
BEGIN
OPEN emp_csr;
FETCH emp_csr INTO l_emp;
CLOSE emp_csr;
RETURN l_emp;
END get_emp_details;

For this test case, we need to tell SQLUnit how to handle the new Oracle UDT, so we fire up JDeveloper (or JPublisher, I suppose). Create a connection to the database, using the schema that owns the UDT. Immense THANKS to the OracleNerd (www.oraclenerd.com) for pointing this out to me. I was stumbling around for a while, trying to figure out how to construct these Type Classes, and he came to the rescue with this easy method.

JDeveloper (I used 10.1.3.3) allows us to automatically generate Java classes for accessing our UDTs from Java. Perform this step for each of your UDTs, and package them together in a JAR. Place the JAR in the lib directory.

To reference the generated classes, the <connection> tag needs to be changed to include type-mapping tags to allow SQLUnit to determine which classes to use for which UDTs.
In this case, the test will look like this:

<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
<typemap>
<typedef typename="SCOTT.EMPREC"
classname="net.sourceforge.sqlunit.types.Emprec" />
</typemap>
</connection>
<test name="#5 FUNCTION returning simple UDT" failure-message="Error with FUNCTION returning simple UDT">
<call>
<stmt>{? = call get_emp_details(?)}</stmt>
<param id="1" name="p_out" type="STRUCT" typename="SCOTT.EMPREC" inout="out"></param>
<param id="2" name="p_empno" type="INTEGER" inout="in">7839</param>
</call>
<result>
<outparam id="1" name="p_out" type="STRUCT">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7839</field>
<field name="ename">KING</field>
<field name="job">PRESIDENT</field>
<field name="mgrname">NULL</field>
<field name="mgrno">NULL</field>
<field name="sal">5000</field>
</struct>
</outparam>
</result>
</test>
</sqlunit>

Note that the struct (UDT) elements are order alphabetically. This is done to provide easier matching of the UDT data structure. Also note that the element names are in lower-case.

Function returning Table Oracle UDT
Given Type exists:
CREATE TYPE t_empRec AS TABLE OF empRec;

Given Function exists:
CREATE OR REPLACE FUNCTION get_dept_emp_details(p_deptno IN NUMBER)
RETURN t_empRec
IS
CURSOR emp_csr
IS
SELECT empRec(
e.empno,
e.ename,
e.job,
e.mgr,
e2.ename,
e.sal,
e.comm,
e.deptno,
get_dept_name(e.deptno))
FROM emp e, emp e2
WHERE e.mgr = e2.empno (+)
AND e.deptno = p_deptno;
l_emp t_empRec;
BEGIN
OPEN emp_csr;
FETCH emp_csr BULK COLLECT INTO l_emp;
CLOSE emp_csr;
RETURN l_emp;
END get_dept_emp_details;
Generate the Java Class for the Table UDT, and insert it into the appropriate JAR.
Add a typemapping to the <connection> tag
    <typedef typename="SCOTT.T_EMPREC"
classname="net.sourceforge.sqlunit.types.TEmprec" />
The test case will then be structured like this:
<test name="#6 FUNCTION returning table UDT" failure-message="Error with FUNCTION returning table UDT">
<call>
<stmt>{? = call get_dept_emp_details}</stmt>
<param id="1" name="p_out" type="STRUCT" typename="SCOTT.T_EMPREC" inout="out"></param>
<param id="2" name="p_deptno" type="INTEGER" inout="in">10</param>
</call>
<result>
<outparam id="1" name="p_out" type="STRUCT">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7934</field>
<field name="ename">MILLER</field>
<field name="job">CLERK</field>
<field name="mgrname">CLARK</field>
<field name="mgrno">7782</field>
<field name="sal">1300</field>
</struct>
</field>
<field name="array">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7782</field>
<field name="ename">CLARK</field>
<field name="job">MANAGER</field>
<field name="mgrname">KING</field>
<field name="mgrno">7839</field>
<field name="sal">2450</field>
</struct>
</field>
<field name="array">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7839</field>
<field name="ename">KING</field>
<field name="job">PRESIDENT</field>
<field name="mgrname">NULL</field>
<field name="mgrno">NULL</field>
<field name="sal">5000</field>
</struct>
</outparam>
</result>
</test>
Function returning Complex Oracle UDT
Given Type exists:
CREATE TYPE deptRec AS OBJECT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
EMPS t_EmpRec,
LOC VARCHAR2(13),
SALSUM NUMBER(7,2));

Given Function exists:
CREATE OR REPLACE FUNCTION get_dept_details(p_deptno IN NUMBER)
RETURN deptRec
IS
CURSOR dept_csr
IS
SELECT deptRec(
d.deptNo,
d.dname,
get_dept_emp_details(d.deptno),
d.loc,
sum(e.sal))
FROM dept d, emp e
WHERE d.deptno = p_deptno
AND d.deptno = e.deptno (+)
GROUP BY d.deptno, d.dname, d.loc;
l_dept deptRec;
BEGIN
OPEN dept_csr;
FETCH dept_csr INTO l_dept;
CLOSE dept_csr;
RETURN l_dept;
END get_dept_details;

Generate the Java Class for the dept UDT, and insert it into the appropriate JAR.
Add a typemapping to the <connection> tag
    <typedef typename="SCOTT.DEPTREC"
classname="net.sourceforge.sqlunit.types.Deptrec" />
The test case will then be structured like this:
<test name="#7 FUNCTION returning complex UDT" failure-message="Error with FUNCTION returning complex UDT">
<call>
<stmt>{? = call get_dept_details(?)}</stmt>
<param id="1" name="p_out" type="STRUCT" typename="SCOTT.DEPTREC" inout="out"></param>
<param id="2" name="p_deptno" type="INTEGER" inout="in">10</param>
</call>
<result>
<outparam id="1" name="p_out" type="STRUCT">
<struct>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="emps">
<struct>
<field name="array">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7934</field>
<field name="ename">MILLER</field>
<field name="job">CLERK</field>
<field name="mgrname">CLARK</field>
<field name="mgrno">7782</field>
<field name="sal">1300</field>
</struct>
</field>
<field name="array">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7782</field>
<field name="ename">CLARK</field>
<field name="job">MANAGER</field>
<field name="mgrname">KING</field>
<field name="mgrno">7839</field>
<field name="sal">2450</field>
</struct>
</field>
<field name="array">
<struct>
<field name="comm">NULL</field>
<field name="deptno">10</field>
<field name="dname">ACCOUNTING</field>
<field name="empno">7839</field>
<field name="ename">KING</field>
<field name="job">PRESIDENT</field>
<field name="mgrname">NULL</field>
<field name="mgrno">NULL</field>
<field name="sal">5000</field>
</struct>
</field>
</struct>
</field>
<field name="loc">NEW YORK</field>
<field name="salsum">8750</field>
</struct>
</outparam>
</result>
</test>

The Table of UDTs and the Complex UDT required a change of code in some of the Java classes in the SQLUnit project. Since it is open-source, the code can be downloaded and changed to suit your needs.
http://sourceforge.net/projects/sqlunit/

Change StructBean constructor method to:
public StructBean(final Object obj) {
try {
PropertyDescriptor[] props =
PropertyUtils.getPropertyDescriptors(obj);
Arrays.sort(props, new PropertyComparator());
for (int i = 0; i < props.length; i++) {
if (!"SQLTypeName".equals(props[i].getName())
&& !"class".equals(props[i].getName())
&& !"baseType".equals(props[i].getName())
&& !"baseTypeName".equals(props[i].getName())
&& !"descriptor".equals(props[i].getName())
&& !"accessDirection".equals(props[i].getName())
&& !"autoBuffering".equals(props[i].getName())
&& !"autoIndexing".equals(props[i].getName())
&& !"bytes".equals(props[i].getName())
&& !"connection".equals(props[i].getName())) {
String fieldName = props[i].getName();
Method readMethod = props[i].getReadMethod();
if (readMethod != null) {
Object fieldValue = readMethod.invoke(obj, new Object[0]);
FieldBean fBean;
if ("array".equals(props[i].getName())) {
Class<?> type = fieldValue.getClass();
if (type.isArray()) {
int length = Array.getLength(fieldValue);
for (int j = 0; j < length; j++) {
fBean = new FieldBean(fieldName, Array.get(fieldValue, j));
fields.add(fBean);
}
}
} else if (typeMap.containsValue(props[i].getPropertyType())) {
// Field is a nested struct
fBean = new FieldBean(fieldName, fieldValue);
fields.add(fBean);
} else {
// Field is a simple type
fBean = new FieldBean(fieldName,
(fieldValue == null) ? "NULL"
: fieldValue.toString());
fields.add(fBean);
}
}
}
}
} catch (Exception ex) {
LOG.error("Could not read bean: " + obj.toString(), ex);
}
}

Change OutParamHandler process method to:
...
...
} else if (elStruct != null) {
if (!(op.getType().endsWith("STRUCT") || op.getType().endsWith("ARRAY"))) {
throw new SQLUnitException(IErrorCodes.IS_A_STRUCT,
new String[] {op.getId(), op.getType()});
}
IHandler structHandler =
HandlerFactory.getInstance(elStruct.getName());
StructBean sb =
(StructBean) structHandler.process(elStruct);
op.setValue(sb);
...
...

Change CallHandler setOutputParameters method to:
...
...
} else if (params[i].getType().endsWith("STRUCT") || params[i].getType().endsWith("ARRAY")) {
// value is a user-defined type (UDT)
StructBean sb = new StructBean(value);
outParam.setValue(sb);
if (outParamSymbol != null) {
SymbolTable.setObject(outParamSymbol, sb);
}
...
...

Overview
SQLUnit provides a neat way to include PL/SQL unit tests as part of an automated build process, since it can be incorporated into an ant script, or run in an isolated standalone mode. The fact that extended User Defined Types can be interrogated and matched with pre-defined XML output provides great flexibility.
For those who have attempted JDBC calls to an Oracle database - in particular to PL/SQL requiring parameters that are either BOOLEAN or of a PL/SQL type (defined in a package specification) - you will be aware of a limitation on accessing those data types. At the moment, there is no easy way to transmit or receive those types (not that I have figured out anyway).
Whilst PLUTO and utPLSQL can handle Booleans and PL/SQL types, I found the setup of test cases for those and complex UDT types a lot more cumbersome.

2 comments:

oraclenerd said...

Well done. Cudos for sticking with SQLUnit as well.

I think it's a big piece missing from our profession (database development) which the other programmers out there, regardless of language, seem to have embraced. There's nothing like creating a piece of code (hoping that it works as expected), running the tests and finding out you did what you intended to do. It breeds confidence and it also (surprisingly to me initially) changes the way you code. You start to think of the possible exceptions before hand and your code ultimately reflects that.

I'm glad you posted this. When I (finally) get around to working up an example, I'll have a solid source now.

chet (aka ORACLENERD)

Raymond said...

I just released new framework for PL/SQL unit testing with Ruby. So you might be interested to check it out - I think Ruby has much nicer syntax for tests compared to XML :)