testing with utplsql made easy with sql developer · 2020. 10. 8. · 31 22.11.2018 testing with...
TRANSCRIPT
![Page 1: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/1.jpg)
Testing with utPLSQL –Made Easy with SQL Developer
Philipp Salvisberg
phsalvisberg DOAG2018
![Page 2: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/2.jpg)
About Us – Added Value from Data
Testing with utPLSQL – Made Easy with SQL Developer2 22.11.2018
![Page 3: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/3.jpg)
About Me
3
Trivadian since April 2000
– Senior Principal Consultant, Partner
– Member of the Board of Directors
– @phsalvisberg
– https://www.salvis.com/blog
– https://github.com/PhilippSalvisberg
Database centric development with Oracle database
Model Driven Software Development
Author of free SQL Developer Extensions PL/SQL Unwrapper, PL/SQL Cop,
utPLSQL, plscope-utils, oddgen and Bitemp Remodeler
Testing with utPLSQL – Made Easy with SQL Developer22.11.2018
![Page 4: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/4.jpg)
Agenda
Testing with utPLSQL – Made Easy with SQL Developer4 22.11.2018
1. Introduction
2. Installation
3. Build & Run Tests in SQL Developer
4. Run Code Coverage Reports in SQL Developer
5. Core Messages
![Page 5: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/5.jpg)
Testing with utPLSQL – Made Easy with SQL Developer5 22.11.2018
Introduction
![Page 6: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/6.jpg)
Why?
Testing with utPLSQL – Made Easy with SQL Developer6 22.11.2018
Does it
work?
Do we get
the expected
results?
Are the
requirements
met?
Is it
complete?
Can I
reproduce
the bug?Is the bug
fixed?
Are there
side-effects by
the change?
"Unit"
![Page 7: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/7.jpg)
utP
LS
QL
utPLSQL Test Scope
Testing with utPLSQL – Made Easy with SQL Developer7 22.11.2018
GUI
API
Integration
Components
Unit
Source: Miško Hevery, The Clean Code Talks, Unit Testing, October 30, 2008,
https://www.youtube.com/watch?v=wEhu57pih5w&t=991
![Page 8: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/8.jpg)
utPLSQL Units Under Test
Testing with utPLSQL – Made Easy with SQL Developer8 22.11.2018
Primary
Types
Packages
Procedures
Functions
Secondary
Non-PL/SQL Units
Views
Triggers
Tables
![Page 9: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/9.jpg)
utPLSQL Suite – Open Source – Apache 2.0 License
Testing with utPLSQL – Made Easy with SQL Developer9 22.11.2018
Mandatory
Core Testing Framework
– Schema installed in Oracle DB
– No repository
– Annotation based tests
Optional
Command Line Client
Maven Plugin
SQL Developer Extension
![Page 10: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/10.jpg)
Test Declaration
Testing with utPLSQL – Made Easy with SQL Developer10 22.11.2018
CREATE OR REPLACE PACKAGE test_package_name AS
--%suite
--%test
PROCEDURE procedure_name;
END;
--%suite(<description>)
--%suitepath(<path>)
--%displayame(<description>)
--%beforeall([…])
--%afterall([…])
--%beforeeach([…])
--%aftereach([…])
--%rollback(manual)
--%disabled
--%context
--%endcontext
--%displayname(<description>)
--%test(<description>)
--%throws(<exception>[,...])
--%beforeall
--%afterall
--%beforeeach
--%aftereach
--%beforetest([…])
--%aftertest([…])
--%rollback(manual)
--%disabled
![Page 11: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/11.jpg)
Test Implementation
Testing with utPLSQL – Made Easy with SQL Developer11 22.11.2018
CREATE OR REPLACE PACKAGE BODY test_package_name AS
PROCEDURE procedure_name IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
ut.expect(l_actual).to_equal(l_expected);
END procedure_name;
END;
Matcher:
be_between, be_empty, be_false,
be_greater_than, be_greater_or_equal,
be_less_or_equal, be_less_than, be_like,
be_not_null, be_null, be_true, equal,
have_count, match
Extended options for refcursor, object
type, nested table and varray:
- include(<items>)
- exclude(<items>)
- unordered
- join_by(<items>)
![Page 12: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/12.jpg)
Test Run
Testing with utPLSQL – Made Easy with SQL Developer12 22.11.2018
test_package_name
procedure_name [.003 sec] (FAILED - 1)
Failures:
1) procedure_name
Actual: 0 (number) was expected to equal: 1 (number)
at "TEST_PACKAGE_NAME.PROCEDURE_NAME", line 7 ut.expect(l_actual).to_equal(l_expected);
Finished in .007015 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
SET SERVEROUTPUT ON SIZE UNLIMITED
EXEC ut.run('test_package_name')
![Page 13: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/13.jpg)
Testing with utPLSQL – Made Easy with SQL Developer13 22.11.2018
Installation
![Page 14: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/14.jpg)
Install utPLSQL Core Testing Framework
Testing with utPLSQL – Made Easy with SQL Developer14 22.11.2018
Download utPLSQL.zip from https://github.com/utPLSQL/utPLSQL/releases
Unzip utPLSQL.zip
cd source
sqlplus / as sysdba @install_headless.sql
– User UT3
– Password XNtxj8eEgA6X6b6f
– Tablespace USERS
![Page 15: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/15.jpg)
Install utPLSQL for SQL Developer
Testing with utPLSQL – Made Easy with SQL Developer15 22.11.2018
Download utplsql_for_SQLDev_*.zip
from https://github.com/utPLSQL/utPLSQL-SQLDeveloper/releases
Start SQL Developer
Select "Check for Updates…" in the help menu
Use the "Install From Local File" option to install the previously downloaded
"utplsql_for_SQLDev_*.zip" file
– User must have read/write access to SQL Developer installation directory
(run as Administrator, if required)
Restart SQL Developer
![Page 16: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/16.jpg)
Testing with utPLSQL – Made Easy with SQL Developer16 22.11.2018
Build & Run Tests
in SQL Developer
![Page 17: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/17.jpg)
Starting Point?
Testing with utPLSQL – Made Easy with SQL Developer17 22.11.2018
1 2
![Page 18: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/18.jpg)
Test First – Create Test from Template
Testing with utPLSQL – Made Easy with SQL Developer18 22.11.2018
![Page 19: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/19.jpg)
Test First – Complete Test & Run
Testing with utPLSQL – Made Easy with SQL Developer19 22.11.2018
![Page 20: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/20.jpg)
Configure utPLSQL
Testing with utPLSQL – Made Easy with SQL Developer20 22.11.2018
![Page 21: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/21.jpg)
Test Last – Create Test from Existing Code
Testing with utPLSQL – Made Easy with SQL Developer21 22.11.2018
![Page 22: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/22.jpg)
Test Last – Generate Multiple Test Skeletons
Testing with utPLSQL – Made Easy with SQL Developer22 22.11.2018
![Page 23: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/23.jpg)
Testing with utPLSQL – Made Easy with SQL Developer23 22.11.2018
Run Code Coverage Reports
in SQL Developer
![Page 24: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/24.jpg)
Code Coverage – Defintion
Testing with utPLSQL – Made Easy with SQL Developer24 22.11.2018
A measure used to describe the
degree to which the source code of a program is executed
when a particular test suite runs.
Source: https://en.wikipedia.org/wiki/Code_coverage
![Page 25: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/25.jpg)
Line Coverage
Testing with utPLSQL – Made Easy with SQL Developer25 22.11.2018
CREATE OR REPLACE FUNCTION f(a IN INTEGER) RETURN INTEGER IS
BEGIN
IF a IS NULL THEN
RETURN 0;
ELSE
RETURN a*a;
END IF;
END f;
/
Two test cases for
100% coverage
![Page 26: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/26.jpg)
Code Block Coverage (12.2 and higher)
Testing with utPLSQL – Made Easy with SQL Developer26 22.11.2018
CREATE OR REPLACE FUNCTION f(a IN INTEGER) RETURN INTEGER IS
BEGIN
IF a IS NULL THEN RETURN 0; ELSE RETURN a*a; END IF;
END f;
/
CREATE OR REPLACE FUNCTION f(a IN INTEGER) RETURN INTEGER IS
BEGIN
RETURN coalesce(a*a, 0);
END f;
/
Two test cases for
100% coverage
One test case for
100% coverage
when passing NULL
![Page 27: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/27.jpg)
utPLSQL Combines Line & Code Block Coverage
Testing with utPLSQL – Made Easy with SQL Developer27 22.11.2018
1 of 2
code blocks
covered
![Page 28: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/28.jpg)
Run Code Coverage Report
Testing with utPLSQL – Made Easy with SQL Developer28 22.11.2018
![Page 29: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/29.jpg)
Code Coverage Report
Testing with utPLSQL – Made Easy with SQL Developer29 22.11.2018
![Page 30: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/30.jpg)
Testing with utPLSQL – Made Easy with SQL Developer30 22.11.2018
Core Messages
![Page 31: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/31.jpg)
The First Step Is the Hardest
Testing with utPLSQL – Made Easy with SQL Developer31 22.11.2018
Set up a test-friendly environment
– Install utPLSQL core testing framework
– Install SQL Developer for utPLSQL
Start with tests
– to reproduce bugs
– for new requirements
![Page 32: Testing with utPLSQL Made Easy with SQL Developer · 2020. 10. 8. · 31 22.11.2018 Testing with utPLSQL –Made Easy with SQL Developer Set up a test-friendly environment –Install](https://reader036.vdocuments.us/reader036/viewer/2022070217/611ef5559615ab69d06904c1/html5/thumbnails/32.jpg)
Trivadis @ DOAG 2018#opencompany
Booth: 3rd floor – next to the escalator
We share our know how!Simply drop by, live presentationsand documents archive
T-Shirts, contest and much more
We look forward to your visit
22.11.2018 Testing with utPLSQL – Made Easy with SQL Developer32