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

Unuary 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 saveop­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!

Like this cheat sheet? Check out our sponsors!

Readable.io is a collection of tools to make your writing better. More readable content means higher conversion rates and better reader engagement. Measure website and document readability, measure keyword density and more!

Click Here To Get Started!

 

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

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

          More Cheat Sheets by jgebal

          utPLSQL v2 vs. ruby-plsql feature comparison Cheat Sheet