Transcript Best Practices for PL/SQL

Test-Driven Development
in the world of
Oracle PL/SQL
Steven Feuerstein
PL/SQL Evangelist
Quest Software
[email protected]
Copyright 2006 Steven Feuerstein - Page 1
And Oracle says....
 The following is intended to outline Oracle's general
product direction. It is intended for information
purposes only, and may not be incorporated into
any contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions. The
development, release, and timing of any features or
functionality described for Oracle's products
remains at the sole discretion of Oracle.
Copyright 2006 Steven Feuerstein - Page 2
Eleven Years Writing Ten Books
on the Oracle PL/SQL Language
Copyright 2006 Steven Feuerstein - Page 3
How to benefit most from this seminar
 Watch, listen, ask questions.
 Download the training materials and supporting scripts:
– http://oracleplsqlprogramming.com/resources.html
– "Demo zip": all the scripts I run in my class available at
http://oracleplsqlprogramming.com/downloads/demo.zip
filename_from_demo_zip.sql
 Use these materials as an accelerator as you venture
into new territory and need to apply new techniques.
 Play games! Keep your brain fresh and active by mixing
hard work with challenging games
– I strongly recommend Set (www.setgame.com, enhanced pattern
recognition skills) and Mastermind (will improve your debugging
skills).
Copyright 2006 Steven Feuerstein - Page 4
First, let's stop and smell the roses!
 Can we really call what we do work?
Copyright 2006 Steven Feuerstein - Page 5
OK, let's get to "work."
 Before we dive into the world of testing, I
would like to step back and take a look at
some "big picture" topics
– Because testing in isolation of the rest of the workflow
and development doesn't make much sense....
 Front end and back end - what does that
really mean?
 A recommended workflow for best practicesdriven development
Copyright 2006 Steven Feuerstein - Page 6
Frontend, backend...back back backend?
 We usually just talk
about front end and
back end, but
there's more to it
than that.
 Developers spend
most of their time
in the top two
layers.
– But they often don't
have clearly defined
boundaries.
Frontend
Application Logic
B
a
c
k
e
n
d
Copyright 2006 Steven Feuerstein - Page 7
Business Rules
Backend
Infrastructure
(in the
Generic
Utilities
Oracle
Database)
Data Access
Data (tables)
Browser
Java
Oracle
Developer
"Assign calls"
app_call_mgr
"Is call open?"
cm_calls_rp.is_open
"Log error"
em_errors
"Parse string"
tb_string_utils
"Add new call"
cm_calls_cp.ins
"Call Table"
cm_calls
Choose your "hat" with intention.
 In a larger organization, layers can be assigned to
specific individuals.
– But usually each of us will move through the layers.
 You will for the most part wear your application
layer hat.
– Implement user-facing requirements.
 But when you need to write a lower-level
subprogram, consciously switch hats.
– Placing the code in the right layer makes it easier to find.
– This improves the chance of reuse, instead of redundancy.
layers_demo.sql
layer_validator.pkg
Copyright 2006 Steven Feuerstein - Page 8
Workflow for best practice development
 Developers like to write code.
– Anything else is just getting in the way of getting the job done.
 The result can be chaos, anarchy and code that is
very difficult to maintain.
– Hey, but we get the job done!
 The more we standardize the way we develop our
code, the more we can automatically (or at least
subconsciously) apply best practices.
– And the fewer details we need to decide on as we write each line of
code.
Copyright 2006 Steven Feuerstein - Page 9
My recommended development workflow
Application
Preparation
1
7
Coding
Conventions
The Build
Cycle
SQL
Access
To QA /
Production
Debug
Error
Mgt
Single
Unit
Preparation
2
Define
Req’ments
Test and
Review
3
6
Construct
Header
 I will come back to this
workflow when we focus on
testing.
Copyright 2006 Steven Feuerstein - Page 10
4
Define
Tests
Post-PostProduction
Production
Bug
Report
Build /
Fix Code
5
Build
Test Code
Enhance.
Request
Coding Conventions
 It doesn’t really matter (to me, anyway!) what
standards you decide to follow.
 The important thing is to be consistent.
 Everyone’s code should, as much as possible, look
and read the same.
– Don’t bother defining formatting standards.
– Use “pretty printers” that come with every decent
PL/SQL IDE.
 Here is a reasonably comprehensive set of coding
conventions for you to use:
http://examples.oreilly.com/orbestprac/
Copyright 2006 Steven Feuerstein - Page 11
SQL Access
 The SQL in your application is the singlemost important element of that application.
– Biggest performance issues and bottlenecks.
– Among the most volatile (changing often) aspect of
your code.
– The source of many runtime errors.
 So shouldn't we at least decide on a
strategy for writing SQL in our applications?
– Where is it written? Who writes it? How do we manage
change?
– This is addressed in Top Tip #3.
Copyright 2006 Steven Feuerstein - Page 12
Error Management
 Any high-quality application raises, handles, logs
and communicates errors in a consistent, robust
manner.
– To do this, you need to use a single package with a welldefined API, and one or more tables to store error information.
 Quest CodeGen Utility, freeware from Quest
Software currently available at www.qcgu.net,
offers a powerful error management framework.
– Reflects my latest thinking on the topic.
www.qcgu.net
Copyright 2006 Steven Feuerstein - Page 13
Time to focus on single unit development
 You need much more than testing, but
without thorough testing, how good can any
piece of software be?
 Oh, yeah?" someone must surely be thinking.
"If it's so important, than why don't we all do
thorough testing?"
 A very good question....
Copyright 2006 Steven Feuerstein - Page 14
Why don't we test?
Copyright 2006 Steven Feuerstein - Page 15
The fundamental problem: programmers
just wanna have fun
 Writing software is a lot of...
 Testing software is a
whole lot of...
Copyright 2006 Steven Feuerstein - Page 16
The result? Buggy software...
 So what? Who cares? Well, I think that
buggy software is....
Embarrassing
Expensive
Deadly
Copyright 2006 Steven Feuerstein - Page 17
Buggy software is embarrassing
 There can be as many as 20 to 30 bugs per 1,000
lines of software code. —Sustainable Computing
Consortium
 32% of organizations say that they release software
with too many defects.—Cutter Consortium
 38% of organizations believe they lack an adequate
software quality assurance program.—Cutter
Consortium
 27% of organizations do not conduct any formal
quality reviews.—Cutter Consortium
 Developers spend about 80% of development costs
on identifying and correcting defects.—The National
Institute of Standards and Technology
Copyright 2006 Steven Feuerstein - Page 18
Buggy software is expensive $60B per year in US alone!?
 JUNE 25, 2002 (COMPUTERWORLD) WASHINGTON -- Software bugs are costing the
U.S. economy an estimated $59.5 billion each
year. Of the total $59.5 billion cost, users
incurred 64% of the cost and developers 36%.
 There are very few markets where "buyers are
willing to accept products that they know are
going to malfunction," said Gregory Tassey, the
National Institute of Standards and Technology
senior economist who headed the study. "But
software is at the extreme end in terms of errors
or bugs that are in the typical product when it is
sold."
 Oh, yes and Y2K: $300B? $600B?
Copyright 2006 Steven Feuerstein - Page 19
Buggy software is deadly
 2003 Software failure contributes to power outage across the
Northeastern U.S. and Canada, killing 3 people.
 2001 Five Panamanian cancer patients die following overdoses of
radiation, amounts of which were determined by faulty use of software.
 2000 Crash of a Marine Corps Osprey tilt-rotor aircraft partially blamed
on “software anomaly" kills four soldiers.
 1997 Radar that could have prevented Korean jet crash (killing 225)
hobbled by software problem.
 1995 American Airlines jet, descending into Cali, Colombia, crashes
into a mountain, killing 159. Jury holds maker of flight-management
system 17% responsible. A report by the University of Bielefeld in
Germany found that the software presented insufficient and conflicting
information to the pilots, who got lost.
There has got to be a better way to test...
it could save lives!
Copyright 2006 Steven Feuerstein - Page 20
Different types of testing
 Functional/system tests
– Performed by QA teams and users, tests entire application.
 Stress tests
– Program works for 1 user, how about 10,000? Usually done by DBAs
and system admins.
– Quest's Benchmark Factory automates this process.
 Unit tests, aka "programmer tests"
– The test of a single unit of code.
– These are the responsibility of developers, of the people who wrote
the program.
 All testing is important, but unit tests are the most
fundamental kind of testing.
Copyright 2006 Steven Feuerstein - Page 21
The importance of unit testing
 Individual units or subprograms
are the building blocks of the
entire application.
 If those low-level blocks are
untested (shaky), then the whole
foundation of the application is
weak.
 Conversely, if we test
units thoroughly we
can debug the whole
application stack
much more easily.
Copyright 2006 Steven Feuerstein - Page 22
Testing vs Tracing vs Debugging
 We tend to confuse these various terms and
processes. Let's get them straight!
 Testing is the process of determining if there are bugs
in the code, and what test cases produce those bugs.
 Tracing (a kind of code instrumentation) is the
process of obtaining information about the application
as it is running.
 Debugging is the process of tracking down the
specific lines of code that are causing a bug.
 Oh...and then there is "trying"
– "Let's try this and see if it works."
– Trying is a poor substitute for logical analysis, debugging and real
testing. Keep it to a minimum.
Copyright 2006 Steven Feuerstein - Page 23
Truth or Dare
 How do you (or your team) unit test your
PL/SQL code today?
We use some form of automated testing software.
We have a formal test process that we each follow,
but otherwise a manual process.
Everyone does their own thing
and we hope for the best.
Our users test our code.
Copyright 2006 Steven Feuerstein - Page 24
Challenges of testing PL/SQL programs
 Testing is hard in any language, but there are
extra special challenges when testing
PL/SQL code.
– Relative inflexibility of this embedded language
– Most programs modify database objects: that is always
a substantial challenge.
– No widely adopted standard for unit testing
 Let's look at some typical testing activity and
challenges for PL/SQL.
Copyright 2006 Steven Feuerstein - Page 25
Typical Testing of a "trivial" function
CREATE OR REPLACE FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN INTEGER, end_in IN INTEGER
) RETURN VARCHAR2
IS
BEGIN
RETURN ( SUBSTR (string_in, start_in, end_in - start_in + 1 ));
END;
 Can DBMS_OUTPUT.PUT_LINE really be the
unit testing mechanism of choice?
betwnstr.sf
betwnstr.tst
BEGIN
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
END;
Copyright 2006 Steven Feuerstein - Page 26
(betwnstr
(betwnstr
(betwnstr
(betwnstr
(betwnstr
(betwnstr
(betwnstr
(NULL, 3, 5,
('abcdefgh',
('abcdefgh',
('abcdefgh',
('abcdefgh',
('abcdefgh',
('abcdefgh',
true));
0, 5, true));
3, 5, true));
-3, -5, true));
NULL, 5, true));
3, NULL, true));
3, 100, true));
Typical testing: table dependencies
 Most PL/SQL programs read from and/or change
the contents of database tables.
 Read from tables...
– You have to set up the tables with data.
– Ideally, it represents the same variety of data you will have in
production.
– Data volume is less critical here: you are testing correctness
of functionality/logic, not performance.
 Write to tables...
– You have to test to make sure that the right changes were
made.
– Usually, you will want to rollback to the original table state so
you can easily run your tests again.
Copyright 2006 Steven Feuerstein - Page 27
Test: Delete rows from table
CREATE OR REPLACE PROCEDURE HR.delete_mult_rows (
low_in
IN
PLS_INTEGER DEFAULT NULL
, high_in
IN
PLS_INTEGER DEFAULT NULL
)
IS
l_low
PLS_INTEGER := low_in;
l_high
PLS_INTEGER := high_in;
BEGIN
IF l_low IS NULL
THEN
SELECT MIN (n) INTO l_low
FROM dmr_table;
– Make
END IF;
 Simple enough, but still tough
to test.
 One approach:
IF l_high IS NULL
THEN
SELECT MAX (n) INTO l_high
FROM dmr_table;
END IF;
DELETE FROM dmr_table
WHERE n BETWEEN l_low AND l_high;
END delete_mult_rows;
Copyright 2006 Steven Feuerstein - Page 28
a copy of table beforehand.
– Change the copy to meet your
expectations.
– Compare the copy to the original after
the program has run.
delete_mult_rows.tst
Other testing challenges
 Cursor variables
– Once you fetch from them, the data is gone.
– May need to determine the column list at runtime (weak
ref cursor type).
 Collections of complex types
– Lists of records, object types, other collections, etc.,
lead to larger volumes of code.
– Cannot, in most cases, determine the structure of the
collection element at runtime.
 XML documents inside Oracle, and more...
Copyright 2006 Steven Feuerstein - Page 29
When should we test?
 We usually see testing as something to do
after writing the program.
– You work really hard building the program.
– Then you think about testing -- and the things you think about
testing are naturally related to what code you wrote.
 There is a big problem with writing test code
after writing our programs:
 We subconsciously prejudice our testing
to favor the stuff with which we are
comfortable, and we think is working!
Copyright 2006 Steven Feuerstein - Page 30
Test now? Test later? Test first?
 Of course, there is more to testing than
running a test.
 We must:
– Define the test criteria / test cases.
– Build the test code.
– Then we can run tests (assuming we have a program to
test).
 Let's now visit the world of Test Driven
Development and see how it can help us.
Copyright 2006 Steven Feuerstein - Page 31
What is Test-Driven Development?
Test driven development is emerging as one of the
most successful developer productivity enhancing
techniques to be recently discovered. The threestep: write test, write code, refactor is a dance many
of us are enjoying.
- Eric Vautier, David Vydra of testdriven.com




Write Test
Write Code
Refactor
Repeat steps
Copyright 2006 Steven Feuerstein - Page 32
Principles of Test-Driven Development
 Automated unit tests are defined and written
before the program is written.
– And you then use the test cases to direct/drive the
actual coding work.
 KISS: "Keep it simple, stupid"
– Avoid unnecessary complexity.
 YAGNI: "You ain't gonna need it."
– Never add functionality until it is necessary.
Copyright 2006 Steven Feuerstein - Page 33
Using TDD in the world of PL/SQL
 You can follow the principles of TDD without
the aid of any tools, but tools certainly help.
 Critical to successful testing:
– Standardized approach to writing test code
– Automated "red light, green light" verification of results
– Test definitions/programs that are easy to maintain and
keep in synch with the code being tested.
 Let's examine our options in the context of my
recommended workflow, focusing on testing.
Copyright 2006 Steven Feuerstein - Page 34
Revisiting the workflow: focus on testing
Application
Preparation
1
7
Coding
Conventions
The Build
Cycle
SQL
Access
To QA /
Production
Debug
Error
Mgt
Single
Unit
Preparation
2
Define
Req’ments
Test and
Review
3
6
Construct
Header
4
Define
Tests
Post-PostProduction
Production
Copyright 2006 Steven Feuerstein - Page 35
Bug
Report
Build /
Fix Code
5
Build
Test Code
Enhance.
Request
Describe the required functionality
 I need a variation of SUBSTR that will return the
portion of a string between specified start and end
locations.
 Some specific requirements:
– It should work like SUBSTR as much as makes sense (treat a start
location of 0 as 1, for example; if the end location is past the end of
the string, the treat it as the end of the string).
– Negative start and end should return a substring at the end of the
string.
– Allow the user to specify whether or not the endpoints should be
included.
Copyright 2006 Steven Feuerstein - Page 36
Create just the program header.
FUNCTION betwnstr (
string_in
IN
VARCHAR2
, start_in
IN
PLS_INTEGER
, end_in
IN
PLS_INTEGER
, inclusive_in
IN
BOOLEAN DEFAULT TRUE
)
RETURN VARCHAR2 DETERMINISTIC
 My specification or header should be compatible with all
requirements.
– I also self-document that the function is deterministic: no side effects.
 While we are at it, let's also create a compilable stub for
the program.
– That way my test code can be compile against it.
betwnstr0.sf
Copyright 2006 Steven Feuerstein - Page 37
Come up with an initial set of test cases







Start and end within the string ("normal" usage)
Start of 0, End past end of string
Null string, string of single character, 32767 len character
Null start and/or end
Negative start and end
Start larger than end (positive and negative)
Variations of the above with different inclusive values
TIP: Don't be overwhelmed by the total number of cases.
Start out with a representative sampling.
You can always add from there.
Copyright 2006 Steven Feuerstein - Page 38
And now the test code.
 The challenge (terror?) of the blank screen....
–
–
–
–
How do I define the test cases?
How do I set up those tests?
How do I verify the results?
Where do I put all this information?
 Let's see how Quest Code Tester helps me
tackle these challenges.
Copyright 2006 Steven Feuerstein - Page 39
Unit Testing Tools for PL/SQL Developers
 utPLSQL – unit test for PL/SQL
– Open-source framework, part of the xUnit family
– You must write the test code yourself.
 PL/Unit – simplified version of utPLSQL
– "Lightweight", single package, no tables
– You must write the test code yourself.
 Quest Code Tester for Oracle
– Robust, integrated test environment.
– Generates test code from repository.
Copyright 2006 Steven Feuerstein - Page 40
utPLSQL and PL/Unit
 I built the original utPLSQL back in 1999 or so. I
discovered Extreme Programming and its unit
testing principle:
– "If testing is good, then everyone should test all the time." From
there, I learned about Junit.
 It is a "cooperative paradigm."
– You "cooperate" by calling utAssert programs to verify test
results. utPLSQL "pays you back" by automatically running your
test package and displaying the results.
 Unfortunately, you still must write the test code
yourself.
Let's now use utPLSQL as a "test driven" tool.
Copyright 2006 Steven Feuerstein - Page 41
ut_betwnstr.pks
ut_betwnstr.pkb
utPLSQL: good stuff, but of limited potential
 utPLSQL is used by many development
shops, but not nearly enough to make it a
standard in the PL/SQL environment.
 The fundamental problem is that utPLSQL
requires lots of time and discipline.
 Heck, I didn't use it myself.
– That is both embarrassing and frustrating.
– About two years ago, I couldn't take it any more.
Copyright 2006 Steven Feuerstein - Page 42
Back to the drawing board...
or rather a wishful dream state.
 I want to be able to easily construct my tests...
– I wouldn't have to wonder how to write the test code, how to
perform complex tests.
– Best of all: I would love to be able to skip writing test code
altogether!
 I'd like to run my tests with the click of a button.
• All the initia1lization and cleanup is done for me.
 I want to the tool to determine if my test succeeded
or failed without having to analyze the results myself.
– That takes too long and I could too easily make a mistake.
 I need to be able to customize my tests as much as
needed to handle all my specialized requirements.
Copyright 2006 Steven Feuerstein - Page 43
Transforming dream to reality:
Quest Code Tester for Oracle
 With Quest Code Tester, you describe the tests you
need through a graphical interface It’s just like SQL vs. programming.
 Quest Code Tester then saves your descriptions in a
test repository.
– Critical information, especially for IT management
 It generates a PL/SQL test package based on your
descriptions.
 It runs the test at your request, displaying the results.
Let's now use Quest Code Tester as a "test driven" tool.
Copyright 2006 Steven Feuerstein - Page 44
Change Your Testing (and Development) Ways
 Stop separating development from testing.
– They are two sides of the same coin.
 Whatever testing framework you use (Quest
Code Tester for Oracle, utPLSQL, PLUnit or
your own "home-grown" utility), following
TDD will....
– Help you stay focused on critical, required functionality.
– Greatly reduce the number of bugs.
– Result in a regression test suite that makes safe
evolution and maintenance possible.
Copyright 2006 Steven Feuerstein - Page 45
ODTUG Kaleidoscope
June 18 – 21, 2007
Hilton Daytona Beach Oceanfront Resort
Daytona, Florida
Featuring the world's SECOND PL/SQL Test a Thon!
The ODTUG Seriously Practical
Oracle PL/SQL Programming
Conference
The Second OPP 2007 of the year will be
held in NYC this Fall.
And we will hold a test-a-thon there as well.
For more information visit www.odtug.com, www.odtugopp.com or call 910-452-7444
Copyright 2006 Steven Feuerstein - Page 46