In this article I’ll describe how you can automate previously created Unit Test.
I believe that many of the Oracle SQL Developer users use only core functionality of the tool, and have never become familiar with its more advanced features.
I’ve already describes in several articles some of its advanced features (you can use search functionality of this site).
Here I’ll described command line interface of SQL Developer.
You shouldn’t mix SQL Developer command line interface (sdcli) with SQLcl which is basically SQL*Plus on steroids.
SQL Developer sdcli command line interface allows you to automate many boring tasks that can be also scheduled.
Here I’m going to use sdcli to automate test that I’ve created in the previous article.
If you run just sdcli, you’ll get all available options you can execute from the command line.
test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli
Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
Available features:
cart: Database Cart Batch Tasks
dba: Basic Batch DBA Tasks
format: Format Task
migration: Database Migration Tasks
reports: Basic Batch Reporting Tasks
unittest: Unit Testing Batch Tasks
utility: Utility Import Task
In this case I’m interested for “unittest” option, which is for automating Unit Testing Batch Tasks.
I’ll execute sdcli with unittest option to get all available options.
test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli unittest
Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
unittest ?
unittest -run ?
unittest -exp ?
unittest -imp ?
Command Completed.
For more information about Unit Test command line interface, I’ll execute the following command:
test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli unittest -run ?
Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
unittest -run -test (-id <id>|-name <name>} -repo <connection name>
-db <connection name> {-return <return id>} {-log <0,1,2,3>}
unittest -run -suite (-id <id>|-name <name>} -repo <connection name>
-db <connection name> {-return <return id>} {-log <0,1,2,3>}
To run my Unit Test, i need to find my test name (parameter -name), repo name (-repo switch) and database connection name (-db parameter).
Optionally I can specify log level, where the level 3 will return the most information about the test execution.
Parameter -db is clear. It’s my demo user connection which is in my case unit_test_demo.
Be careful! -db is connection name in SQL Developer, not the database user/schema name.
The same apply for the -repo parameter, where you need to specify repo connection name (not the repo database user/schema name).
The easiest way to find out -name is to export my Unit Test into a XML file, open that file and search for “name=” string.
In this case, name parameter is “ORDER_MANIPULATION.INSERT_ORDER” that you can check from the following XML file:
<?xml version = '1.0' encoding = 'windows-1250'?>
<UT>
<object class="oracle.dbtools.unit_test.testObjects.UtTest">
<test id="b0868eac-6483-4bef-9493-8038e64f506a" name="ORDER_MANIPULATION.INSERT_ORDER" obj_name="ORDER_MANIPULATION" obj_owner="DEMO" obj_type="PROCEDURE" obj_call="INSERT_ORDER" src_conn_name="IdeConnections%23orcl18c_unit_test_demo" coverage="false">
<arg id="1542180f-2e0c-4135-a28d-99f4a0ab0e52" owner="DEMO" object_name="INSERT_ORDER" package_name="ORDER_MANIPULATION" object_id="76186" argument_name="P_ITEM_ID" position="1" sequence="1" data_level="0" data_type="NUMBER" in_out="IN" data_length="22" radix="10" pls_type="NUMBER" char_length="0" char_used="0"/>
<arg id="83be572d-4dcc-4b07-9147-bfab2daa9221" owner="DEMO" object_name="INSERT_ORDER" package_name="ORDER_MANIPULATION" object_id="76186" argument_name="P_ACT_ID" position="2" sequence="2" data_level="0" data_type="NUMBER" in_out="IN" data_length="22" radix="10" pls_type="NUMBER" char_length="0" char_used="0"/>
<startup id="048cbece-97a3-41ff-b510-02390191c326">
<startup>
<class>oracle.dbtools.unit_test.startup.TableCopyStartup</class>
<name>Table or Row Copy</name>
<description>"TMP$ORDER_MANIPULATION.INSERT_" := SELECT ROWID AS SRC$ROWID, s.* FROM "ORDERS" s </description>
<source>"ORDERS"</source>
<predicate/>
<temp>"TMP$ORDER_MANIPULATION.INSERT_"</temp>
</startup>
</startup>
<teardown id="2ba26a6f-aac3-4033-af81-c2a2394373dc">
<teardown>
<class>oracle.dbtools.unit_test.teardown.TableRestoreTeardown</class>
<name>Table or Row Restore</name>
<description>"TMP$ORDER_MANIPULATION.INSERT_" -> "ORDERS"</description>
<source>"ORDERS"</source>
<key>PRIMARY KEY</key>
<temp>"TMP$ORDER_MANIPULATION.INSERT_"</temp>
<predicate/>
<dropTempTable>yes</dropTempTable>
</teardown>
</teardown>
<test_impl id="7e41ac90-26ca-4308-b3bd-0305c9f487f7" test_id="b0868eac-6483-4bef-9493-8038e64f506a" name="Test Implementation 1" expected_ret="Exception" expected_err="20001">
<parm argId="83be572d-4dcc-4b07-9147-bfab2daa9221" utiId="7e41ac90-26ca-4308-b3bd-0305c9f487f7" input_value="<?xml version = '1.0' encoding = 'UTF-8'?>
<DataValue name="P_ACT_ID">9999</DataValue>" test_out_val="true"/>
<parm argId="1542180f-2e0c-4135-a28d-99f4a0ab0e52" utiId="7e41ac90-26ca-4308-b3bd-0305c9f487f7" input_value="<?xml version = '1.0' encoding = 'UTF-8'?>
<DataValue name="P_ITEM_ID">9999</DataValue>" test_out_val="true"/>
</test_impl>
</test>
</object>
</UT>
Now when I know all parameters, I can safely execute my first Unit Test from the command line interface.
test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli unittest -run -test -name "ORDER_MANIPULATION.INSERT_ORDER" -repo unit_test_demo -db unit_test_demo -log 3
Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
d2528666-0d81-4078-a50f-9f8345d67df9
UT_SUCCESS
null
Command Completed.
As you can see, the Unit Test has passed.
Summary:
Oracle SQL Developer is really great tool for performing many types of various tasks.
On the first view it looks like a pretty rudimentary piece of software, but actually it’s a beast when you look at it under the hood.
When you compare SQL Developer with its competitors, I’m not aware of any similar tool with such advanced features that is completely free.
One of such features allows me to execute complete unit test from the command line.
I can execute not only the single test, but the series of tests called suites as well.
The whole process can be automated, and just that one feature can reduce a substantial amount of time for testing.
With such an extensive number of options and automation that you can define, you can’t find an excuse any longer not to start using the Unit Tests, especially because you won’t need to ask anyone to approve the budget as all those features you have for free.
Comments