oracle plsql testing - ghdrako/doc_snipets GitHub Wiki

utPLSQL - standard PL/SQL test frameworks

$ curl \
https://github.com/utPLSQL/utPLSQL/releases/download/v3.1.12/
utPLSQL.zip \
-L -o utPLSQL.zip


$ unzip -q utPLSQL.zip
cd utPSQL/source
$ source .bashrc; sqlplus /nolog
# connect sys as sysdba;
!pwd
alter session set "_ORACLE_SCRIPT"=true;
@create_utplsql_owner.sql ut3 ut3 users;
@install.sql ut3;
select username as schema_name
from sys.dba_users
order by username;
@create_synonyms_and_grants_for_public.sql ut3;
@create_user_grants.sql ut3 <Specific_user>;
@create_user_synonyms.sql ut3 <Specific_user>;

CREATE OR REPLACE PACKAGE test_LOGGINGPACKAGE IS
--%suite
--%suitepath(alltests)
--%test
PROCEDURE LOG_ACTION;
END test_LOGGINGPACKAGE;

CREATE OR REPLACE PACKAGE BODY test_LOGGINGPACKAGE AS
PROCEDURE LOG_ACTION IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
delete from LOGGING;
loggingpackage.log_action('An action', 'A log message');
select count(*) into l_actual from logging;
ut.expect(l_actual).to_equal(l_expected); -- to_equal is the
matcher
END LOG_ACTION;
END test_LOGGINGPACKAGE;

Auto-generating a utPLSQL Unit Test in SQL Developer

This does require installation of the utPLSQL plugin as described in “Installation of utPLSQL”. Starting with the package you want to test, right- click the package containing the logging code. This opens the context menu. Select the highlighted option:

• Generate utPLSQL Test This ganerate test code. Modify:

procedure log_action is
l_actual integer := 0;
l_expected integer := 1;
begin
-- arrange
-- act
-- package1.log_action;
delete from LOGGING;
PACKAGE1.log_action('An action', 'A log message');
select count(*) into l_actual from logging;
-- assert
ut.expect(l_actual).to_equal(l_expected);
end log_action;

Now, select all, right-click and then select the Run Statement menu option.

If all is well, you should see the following message in the result window: Package Body TEST_PACKAGE1 compiled At this point, the test package (called TEST_PACKAGE1) is stored in the database. Click the refresh button and then open the Packages control

By right-clicking the Package called TEST_PACKAGE1 and clicking the higlighted option Run utPLSQL test: