Show Menu

utPLSQL v3 Cheat Sheet by

oracle     tdd     unit-testing     utplsql     plsql     unit-test

Package annota­tions

--%suite
Package is a test suite
--%sui­te(­des­cri­ption)
A suite with descri­ption
--%sui­tep­ath­(or­g.m­y_o­rg.m­y_­pro­ject)
Similar to Java package.
Groups suites in namespaces
Annota­tions are sinlgl­e-line comments starting with a % sign.
Needed in package specif­ication only (documentation)

Procedure annota­tions

--%test
The procedure is a test
--%tes­t(d­esc­rip­tion)
A test with descri­ption
--%bef­ore­tes­t(p­roc­edu­re_­name)
Runs the procedure before annotated test
--%aft­ert­est­(pr­oce­dur­e_name)
Runs the procedure after annotated test
--%bef­oreall
The procedure to run before first test in suite
--%aft­erall
The procedure to run after last test in suite
--%bef­oreeach
The procedure to run before each test
--%aft­ereach
The procedure to after each test

Unary matchers

be_e­mpty
open l_cursor for select * from dual where 1 = 0;
ut.expect( l_cursor ).to_( be_empty() );
be_f­alse
ut.expect( ( 1 = 0 ) ).to_( be_false() );
be_n­ot_­null
ut.expect( to_clo­b('­ABC') ).to_( be_not­_null() );
be_n­ull
ut.expect( 1 ).to_( be_null() );
be_t­rue
ut.expect( ( 1 = 1 ) ).to_( be_true() );

Common annota­tions

--%dis­abled
Suite / test will not execute
--%rol­lba­ck(­auto)
--%rol­lba­ck(­manual)
No savepo­int­/ro­llback

Expect­ation structure

Base expect­ation block
ut.ex­pect( actua­l_v­alue ).to_( matcher );
Negated expect­ation block
ut.ex­pect( actua­l_v­alue ).not_to( matcher );
Shortcuts to matchers
ut.ex­pect( actua­l_v­alue ).[not­_]t­o_­mat­cher;

Equality matcher

equal
ut.ex­pect( 'a dog' ).to_(
  equal( 'a dog' , a_null­s_a­re_­equal => false ) );

a_nulls_are_equal is true by default
equal with cursors
open l_expected for select * from dual;
open l_actual for select * from dual where 1 = 0;
ut.expect( l_expected ).to_(
  equal(l_actual, a_excl­ude­=>'­col­umn­_a,­col­umn_b') );
equal on objects
ut.ex­pect( anydat­a.c­onv­ert­Obj­ect­(l_­exp­ected) ).to_(
  equal( anydat­a.c­onv­ert­Obj­ect­(l_­actual) ) );
equal on collec­tions
ut.ex­pect( anydat­a.c­onv­ert­Col­lec­tio­n(l­_ex­pected) ).to_(
  equal( anydat­a.c­onv­ert­Col­lec­tio­n(l­_ac­tual) ) );
 

Non-eq­uality matchers

be_l­ike
ut.ex­pect( 'Lorem­_im­psum' ).to_(
  be_like( a_mask => '%rem\_%', a_esca­pe_char => '\' ) );

ut.expect( 'Lorem­_im­psum' ).to_( be_like( '%rem%sum' ) );
a_mask, a_esca­pe_char -> see Oracle like operator
match
ut.ex­pect( a_actual => '123-4­56-­ABcd' ).to_(
  match( a_pattern => '\d{3}­-\d­{3}­-[a­-z]', a_modi­fiers => 'i' ) );

ut.expect( 'some value' ).to_( match( '^some.*' ) );
a_pattern, a_modi­fiers -> see regexp_like function
be_b­etw­een
ut.ex­pect( 3 ).to_( be_bet­ween( 1, 3 ) );
be_g­rea­ter­_or­_eq­ual
ut.ex­pect( sysdate ).to_( be_gre­ate­r_o­r_e­qual( sysdate - 1 ) );
be_g­rea­ter­_than
ut.ex­pect( 2 ).to_( be_gre­ate­r_than( 1 ) );
be_l­ess­_or­_eq­ual
ut.ex­pect( 3 ).to_( be_les­s_o­r_e­qual( 3 ) );
be_l­ess­_than
exec ut.expect( 3 ).to_( be_les­s_than( 2 ) );

Reporting

Color output
exec ut.run(a_color_console=>true);
With sqlcl
or sqlPlus (Mac, Unix, Windows ANSICON)
XUnit reporter
exec ut.run(ut_xunit_reporter());
JUnit-compatible XML report for CI servers
TeamCity reporter
exec ut.run(ut_teamcity_reporter());
TeamCity-specific report
Sonar Test reporter
exec ut.run(ut_sonar_test_reporter());
Sonar-specific XML tests report
Coverage html reporter
exec ut.run(ut_coverage_html_reporter());
Produces HTML coverage report
Docume­ntation for coverage and reporters

Executing tests

Run all unit tests in my current schema
exec ut.run();
Run all unit tests in current schema after it was changed to HR
alter session set current_schema='HR';
exec ut.run();
Run all unit tests in specific schema
exec ut.run­('H­R');
Run all unit tests in specific package of current schema
exec ut.run­('t­est­_be­twn­str');
Run all unit tests in specific schema.pa­ckage
exec ut.run­('h­r.t­est­_be­twn­str');
Run one specific test only
exec ut.run­('h­r.t­est­_be­twn­str.bi­g_e­nd_­pos­iti­on');
Run several items
exec ut.run(ut_varchar2_list(
    'hr.test_award_bonus',
    'hr.test_betwnstr.big_end_position'));
Run test using suitepath
exec ut.run­(':­com.my­_or­g.m­y_p­roj­ect');
Run the tests as a select statement
select * from table(­ut.r­un­());

Catching exceptions

procedure my_code_raises_zero_divisor is
  l_my_number number;
begin
  l_my_number := 1/0; --should raise
  ut.fail('Expected exception but nothing was raised');
exception
  when others then
    ut.expect( sqlcode ).to_equal( -1476 );
end;

Download the utPLSQL v3 Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/jgebal_utplsql-v3.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          utPLSQL v2 vs. ruby-plsql feature comparison Cheat Sheet
          utPLSQL v3.1.2 Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts

          More Cheat Sheets by jgebal

          utPLSQL v2 vs. ruby-plsql feature comparison Cheat Sheet
          utPLSQL v3.1.2 Cheat Sheet