Transcript Best Practices for PL/SQL

This Old Program,
These Old Programmers
Transforming the way we write
PL/SQL programs
Steven Feuerstein
[email protected]
[email protected]
All non-technical views expressed are those of Steven Feuerstein and do not
7/17/2015
necessarily (and not likely!) reflect those of Quest Software.
Page 1
Copyright 2002-2004 Steven Feuerstein
This Old Program
 It is just a bunch of bits
and bytes?
Or to put it another way:
 Is it spaghetti
or
or to put itcode
yet another
way:
is it something
are less time
How
can weyou
spend
proud of? How can we have
writing boring code...
the most fun
possible
 Is it engineering
or is
it
and
have
more
time
writing software?
art?
to
enjoy
the
rest
of
our
lives?
 DO YOU EVEN CARE?
7/17/2015
Page 2
Copyright 2002-2004 Steven Feuerstein
These "Old" Programmers
7/17/2015
Page 3
Copyright 2002-2004 Steven Feuerstein
Transformation Time
 Do it right the first time
– Establish a solid foundation for development
– Apply best practices proactively, maintain
discipline, don't lose your creative spark.
 Fix it right the second time
– Software is always changing; the "right" way
changes along with it.
– We will apply the concept of "refactoring" to the
world of PL/SQL development.
7/17/2015
Page 4
Copyright 2002-2004 Steven Feuerstein
"All" about Steven Feuerstein
www.stevenfeuerstein.com
 Bachelor's degree in mathematics (1980), with
three computer 101 classes to my name (a selftaught programmer).
 Five years with Oracle Corporation (1987 - 1992),
with too much time spent helping salespeople sell.
Life is too short...
 Author/co-author of nine texts on PL/SQL, most
notably Oracle PL/SQL Programming.
 I live in Chicago with one wife (Veva), two sons
(Chris and Eli) and three cats (Sister, Moshe and
Mica).
7/17/2015
Page 5
Copyright 2002-2004 Steven Feuerstein
Ten Years of Writing on
the Oracle PL/SQL Language
7/17/2015
Page 6
Copyright 2002-2004 Steven Feuerstein
Software Used in Training
 You can download all my training materials and
demonstration scripts from:
– www.minmaxplsql.com, click on "Training
Materials".
 Toad and/or SQL Navigator: make sure you've got a topnotch IDE, otherwise you are wasting lots of time.
 Ounit and utPLSQL, software for unit testing of PL/SQL
code at www.ounit.com.
 Mastoid Mind and Set: have fun while keeping
your brain tuned up.
 QCGU, active mentoring software: a repository of
reusable and templated code, www.qcgu.net.
plsql_ides.txt
7/17/2015
Page 7
Copyright 2002-2004 Steven Feuerstein
About
 QCGU is a searchable, customizable
repository for reusable code and templates.
– Starter set: "PL/SQL by Feuerstein"
– You can create our own toolboxes and libraries.
 QCGU is a flexible code generator that helps
you avoid writing tedious, repetitive code.
 QCGU is an error manager for PL/SQL-based
applications.
7/17/2015
Page 8
Copyright 2002-2004 Steven Feuerstein
QCGU is not....
 An integrated development environment, aka
IDE, aka editor, for PL/SQL programming.
– It complements Toad, SQL Navigator, PL/SQL Developer,
etc.
 Free. QCGU is sold on a subscription basis (you are
paying for content). But we will raffle off a one year sub this afternoon!
– There is a trial version and there will be a free version in the
future (design platform minus content).
 Needed in order to benefit from this class.
– QCGU contains a repository of examples.
– The QCGU backend reflects my latest (and, I believe, best)
thinking on how to build high quality PL/SQL applications.
7/17/2015
Page 9
Copyright 2002-2004 Steven Feuerstein
Do it right the first time
 Be a disciplined programmer.
– Remember you are not a machine.
– Don't repeat anything.
– Write tiny chunks of code.
– Integrate unit testing into your development work.
 Establish a solid foundation for development.
– Pick your tools and set your coding standards.
– Decide how you will write SQL.
– Decide how you will handle errors.
7/17/2015
Page 10
Copyright 2002-2004 Steven Feuerstein
Discipline: Remember you are not a machine
 Computers are machines. You are a human
being.
– To be a programmer, you have to think like a
computer.
– But you don't have to live like a machine.
 Key human being tips
– Drink lots of water.
– Take frequent breaks. Get up, stretch,
move around,
And ask for help before
you get desperate!
7/17/2015
Page 11
Copyright 2002-2004 Steven Feuerstein
Discipline: Don't repeat anything!
 This is the #1 tip for developers to follow.
 Examples:
– Normalization of data structures
PL/Vision at Quest-Pipelines.com
– Code libraries
utl_file_constants.pks
– Named constants
 Not all that hard to do in PL/SQL, you just
need to be disciplined and patient.
– Use packages, local modules, generated code,
anchored types, object types.
 Oh and did I mention discipline?
7/17/2015
Page 12
Copyright 2002-2004 Steven Feuerstein
Discipline: Write tiny chunks of code.
 Keep your executable sections small, readable
and manageable.
No executable sections with more than 50 lines of code!
 Use top down design
– Break up big, seemingly impossible problems into
smaller more manageable problems.
 Create lots of small, focused packages
– Keep the complexity down.
 Make generous use of local modules
– We will see many examples in class.
locmod.sp
7/17/2015
Page 13
Copyright 2002-2004 Steven Feuerstein
Create lots of small, focused packages
 Establish "buckets" of functionality, each with their
own clear purpose and scope.
– Respect the boundaries, avoid entanglements.
Mortgage
Rate
Analysis
Error
Handling
DBMS_OUTPUT
replacement
Date
Stuff
String
stuff
Constants
Utilities
Example:
QCGU packages
7/17/2015
Page 14
Copyright 2002-2004 Steven Feuerstein
Discipline: integrate unit testing
 For too many of us and for too much of the
time, unit testing is all but ignored.
– The result is much buggier code and code that
is much more difficult to maintain and enhance.
 It's time to get serious and integrate
testing into the development process.
– utPLSQL and the Ounit interface offer the
best chance of doing that today in the world of
PL/SQL.
utPLSQL project home
We will put Ounit to use
later in the class.
Download code
http://utplsql.sourceforge.net/
http://www.ounit.com/
7/17/2015
Page 15
Copyright 2002-2004 Steven Feuerstein
Establish a solid foundation
 Pick your tools and set your coding
standards.
– You've got (and largely made) your choice of IDE.
– Analyze your needs and points of pain and fill out
your toolbox.
 Decide how you will write SQL.
– The Achilles heel of PL/SQL development.
 Decide how you will handle errors.
– You know you have to/should do it. Do it right,
consistently, robustly, informatively.
7/17/2015
Page 16
Copyright 2002-2004 Steven Feuerstein
Pick tools and set coding standards
 It's hard to read code if...
– The format is inconsistent.
– The naming conventions are all over the map.
 Quest Formatter Plus and Code Expert can help an
awful lot.
 QCGU also offers the possibility of defining your
naming conventions and then applying them to
generated scripts.
Download a set of naming conventions and coding standards at:
http://examples.oreilly.com/orbestprac/
7/17/2015
Page 17
Copyright 2002-2004 Steven Feuerstein
Decide how you will write SQL
 SQL statements are the Achilles heel of
your application.
– Greatest impact on performance
– Most likely to require modification over time
– And it's just too easy to write SQL statements
in PL/SQL.
 You need to apply controls or at least
guidelines to how developers write SQL in
your application code.
– Best rule to follow: avoid SQL repetition.
7/17/2015
Page 18
Copyright 2002-2004 Steven Feuerstein
How to avoid SQL repetition
 You should, as a rule, not
even write SQL in your PL/SQL
programs
– You can't repeat it if you don't
write it
 Instead, rely on pre-built, pretested, write-once, use-often
PL/SQL programs.
– "Hide" both individual SQL
statements and entire
transactions.
SQL
Data
Encapsulation
7/17/2015
Page 19
Copyright 2002-2004 Steven Feuerstein
Real world data encapsulation
 You will probably
end up with
multiple levels of
encapsulation,
some generated,
some hand-coded.
 The most important
thing is to follow
that fundamental
principle: Don't
repeat anything!
7/17/2015
Page 20
Layers of code with data encapsulation
Order Entry
Program
Order
Table
Lineitems
Table
Oracle Designer
QCGU
Copyright 2002-2004 Steven Feuerstein
Using table encapsulation – an example
 As I have been building QCGU, I have evolved
an increasingly formal and standardized
construction methodology.
 It has proven highly productive; I am writing
better code than ever before, and definitely
must faster.
 Let's take a look at a recent effort:
I need to add the ability to scan the contents of a script's source
and identify any argument definition comment lines:
#ARG arg_name | arg_description | arg_default
LISTING
sg_script_xp.pkb
I will then automatically create an argument for that script.
Example: {91879C86-D4B4-4675-81A4-C10C7676CACB}
7/17/2015
Page 21
Copyright 2002-2004 Steven Feuerstein
Simplified ER diagram of script tables
7/17/2015
Page 22
Copyright 2002-2004 Steven Feuerstein
So I take it step by step, and top-down....
Use program in table API
to get all source rows.
Generate standard WHILE
scan of collection.
Top-down specification of
complex internal logic.
PROCEDURE load_arguments (
id_in
IN
Sg_Script_Tp.id_t
,overwrite_in
IN
BOOLEAN DEFAULT FALSE
,delimiter_in
IN
VARCHAR2 DEFAULT NULL
)
IS
/*
| Search for lines of this form:
| #ARG arg_name|arg_description|arg_Default
*/
BEGIN
l_script_source :=
Sg_Script_Source_Qp.for_script_id (id_in);
l_index := l_script_source.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
parse_argdef (l_script_source (l_index).text, l_arg);
add_argument (l_arg);
l_index := l_script_source.NEXT (l_index);
END LOOP;
END load_arguments;
7/17/2015
Page 23
Copyright 2002-2004 Steven Feuerstein
Hide the literals, dip into my toolbox
Even if I use the
literal once, it is still
worth taking the time
to hide it. Think
about the future!
Parse a delimited
string....surely I
already have a
program that can
handle this. Let's
check.
FUNCTION is_arg_definition (line_in IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
RETURN UPPER (line_in) LIKE argprefix || '%';
END is_arg_definition;
PROCEDURE parse_argdef (
line_in
IN
VARCHAR2
,arg_out
OUT
Sg_Script_Argument_Tp.sg_script_argument_rt
)
IS
l_items
Sg_Admin.vc2000_tabtype;
BEGIN
IF is_arg_definition (line_in)
THEN
Sg_Parse.string1 (SUBSTR (line_in, c_argprefix_len + 1)
,l_items
,NVL (delimiter_in, c_default_delim)
);
arg_out.NAME := l_items (1);
arg_out.description := l_items (2);
arg_out.DEFAULT_VALUE := l_items (3);
END IF;
END parse_argdef;
7/17/2015
Page 24
Copyright 2002-2004 Steven Feuerstein
Apply the table API thru multiple steps
The more attention I
pay to my table
definitions (keys,
indexes, etc.), the
more code QCGU
can generate to meet
my often predictable
requirements.
Notice...
NO SQL!
PROCEDURE add_argument (
arg_in
IN
Sg_Script_Argument_Tp.sg_script_argument_rt
)
IS
l_rows
PLS_INTEGER;
BEGIN
IF arg_in.NAME IS NOT NULL
THEN
IF overwrite_in
THEN
Sg_Script_Argument_Cp.del_un_scrarg_script_name ( ... );
END IF;
IF NOT Sg_Script_Argument_Qp.ex_un_scrarg_script_name ( ... )
THEN
Sg_Script_Argument_Cp.ins ( ... );
END IF;
l_last_position := l_last_position + 1;
END IF;
END add_argument;
7/17/2015
Page 25
Copyright 2002-2004 Steven Feuerstein
Decide how you will raise & handle errors
 To build a robust PL/SQL application, you need
to decide on your strategy for exception
handling, and then build a code-based
architecture for implementing that strategy
 Use package-based utilities to raise, handle
and log exceptions.
Instead of all this exposed logic...
WHEN OTHERS THEN
v_err := SQLCODE;
v_msg := SQLERRM;
v_prog := 'fixdebt';
INSERT INTO errlog VALUES (
v_err, v_msg, v_prog,
SYSDATE, USER
);
RAISE;
7/17/2015
Simply call the predefined handler...
WHEN OTHERS
THEN
qd_runtime.raise_error (
error_name_in = 'UNANTICIPATED-ERROR'
,name1_in
=> 'CONTEXT'
,value1_in
=> 'fixdebt'
);
Page 26
Copyright 2002-2004 Steven Feuerstein
Consistent, robust error management
 One of the most critical and neglected aspects
of an application. You need....
– Consistent error handling and reporting
– Standards and process so that developers do not
trip over themselves (same error number in
different contexts, duplicate/different messages)
– Ways to compensate for weaknesses in the Oracle
architecture.
 In building QCGU, I have evolved my thinking
about how best to do this.
oldexccode.sql
7/17/2015
Page 27
Copyright 2002-2004 Steven Feuerstein
The QCGU approach to error handling
 Define error categories and errors within a
category.
– You can provide error numbers, but I suggest that
you try using – and relying on - error names.
 Pre-define error handlers for common
situations.
– Easily generate or copy into your code.
 Gather lots of information and assign to an
error "handle".
 Calling programs access information via the
handle.
And make it easy to deploy by concentrating the logic
7/17/2015
into a single runtime package: qd_runtime
Page 28
Copyright 2002-2004 Steven Feuerstein
Define error categories and errors
7/17/2015
Page 29
Copyright 2002-2004 Steven Feuerstein
Pre-define common error handlers
 Named exceptions, extraction of constraint information,
automatic transfer to error handle. This is not the sort of thing
most developers will do, if left to their own devices.
WHEN e_integ_constraint_failure
THEN
DECLARE
l_owner ALL_CONSTRAINTS.OWNER%TYPE;
l_name ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
BEGIN
get_constraint_info (l_owner, l_name);
qd_runtime.raise_error (
error_name_in => 'INTEGRITY-CONSTRAINT-FAILURE'
,name1_in => 'OWNER'
,value1_in => l_owner
,name2_in => 'CONSTRAINT_NAME'
,value2_in => l_name
,name3_in => 'TABLE_NAME'
,value3_in => '[objname]');
END;
7/17/2015
Page 30
Copyright 2002-2004 Steven Feuerstein
About those hard-coded error names
 Certainly seems like a violation of commonsense best practices: "Avoid hard-coded
literals!"
– But what is the alternative?
RAISE_APPLICATION_ERROR (-20175, 'Problem doing something!');
Nasty old RAE hard-coding. No way!
RAISE_APPLICATION_ERROR (errnums_pkg.e_balance_zero
,'Your balance is zero!');
Hide error number behind a named constant. Better
but they have to be defined in advance....
my_err_pkg.raise_error (errnums_pkg.e_balance_zero);
Call common handler program. Great, but errors still
have to be defined in advance...
7/17/2015
Page 31
Copyright 2002-2004 Steven Feuerstein
Top-down error handling construction
 The reality is we can't know in advance all of
the different errors we'll have to handle.
– With each new named package constant, I stop
coding, update central package, recompile it and
all programs made invalid. Finally, get back to my
coding. Very disruptive.
 With the QCGU approach, I can define a new
error on the fly by name.
– The code compiles. Then later, I define that error
inside the GUI and give it all its properties.
– I can also provide context values very easily.
7/17/2015
Page 32
Copyright 2002-2004 Steven Feuerstein
Runtime error gathering/construction
 When an error occurs, you want to gather up
as much information as possible and make it
available for support and users.
– The default is a number and string. That is just a
little bit limiting.
 Other useful information...
– Error stack, backtrace stack (Oracle10g only), call
stack, environment info (machine name, OS, etc.)
and of course all that context-specific, applicationspecific information.
qd_runtime.pkb
7/17/2015
Page 33
Copyright 2002-2004 Steven Feuerstein
Build interfaces to the error information
 The error handle points to the underlying
information.
– Now the challenge is to make it easy to extract that
information.
– For a non-PL/SQL host environment, that means avoiding
composite structures.
PROCEDURE qd_runtime.get_error_info (
code_out
,name_out
,text_out
,system_error_code_out
,system_error_message_out
,recommendation_out
,error_stack_out
,call_stack_out
,environment_info_out
OUT
OUT
OUT
OUT
OUT
OUT
OUT
OUT
OUT
Qd_Error_Tp.code_t
Qd_Error_Tp.name_t
Qd_Err_Instance_Tp.message_t
Qd_Err_Instance_Tp.system_error_code_t
Qd_Err_Instance_Tp.system_error_message_t
Qd_Error_Tp.recommendation_t
Qd_Err_Instance_Tp.error_stack_t
Qd_Err_Instance_Tp.call_stack_t
Qd_Err_Instance_Tp.environment_info_t
);
7/17/2015
Page 34
Copyright 2002-2004 Steven Feuerstein
The QCGU error reporting form
 I tried to save a new
error with same
name as existing
error.
– Notice the dynamic
substitution of
context-specific data,
plus the explanation of
how to remedy the
situation.
– All is soft-coded.
7/17/2015
Page 35
Copyright 2002-2004 Steven Feuerstein
The QCGU error reporting form - expanded
 And then if you need more information, ask for it,
copy to clipboard, etc.
Sure, mostly I am
showing you a GUI
interface, but it based
simply on a call to
qd_runtime's
get_error_info
procedure. All the
processing is pushed
into the backend.
7/17/2015
Page 36
Copyright 2002-2004 Steven Feuerstein
Runtime monitoring of errors and logs
We need to make the
information as
accessible as
possible – which
means it needs to be
preserved in tables!
7/17/2015
Page 37
Copyright 2002-2004 Steven Feuerstein
Summary: Error handling standards
 Don't accept the constraints of the Oracle
architecture.
– You can communicate much more than error code
and error message.
 Use standard components that are practical,
that people will really use and want to use.
7/17/2015
Page 38
Copyright 2002-2004 Steven Feuerstein
Getting it right the first time....
 Note: it can only ever be "right" in
terms of the process, not the end point.
 Join me on a problem-solving journey
in which I try to do things RiTFiT.
7/17/2015
Page 39
Copyright 2002-2004 Steven Feuerstein
RiTFiT: Fixing DBMS_UTILITY.NAME_RESOLVE
 DBMS_UTILITY.NAME_RESOLVE is a very
handy program. It "resolves" the name of a
stored program unit into its different
components:
PROCEDURE DBMS_UTILITY.NAME_RESOLVE
Argument Name
Type
In/Out
--------------------- --------- -----NAME
VARCHAR2 IN
CONTEXT
NUMBER
IN
SCHEMA
VARCHAR2 OUT
PART1
VARCHAR2 OUT
PART2
VARCHAR2 OUT
DBLINK
VARCHAR2 OUT
PART1_TYPE
NUMBER
OUT
OBJECT_NUMBER
NUMBER
OUT
7/17/2015
Page 40
Copyright 2002-2004 Steven Feuerstein
An example of "raw" NAME_RESOLVE usage
 You pass in the name of the item you want to have
fully resolved, and the program returns the different
elements of the name (where relevant).
DECLARE
sch
VARCHAR2 (100);
part1
VARCHAR2 (100);
part2
VARCHAR2 (100);
dblink
VARCHAR2 (100);
part1_type
NUMBER;
object_number
NUMBER;
BEGIN
DBMS_UTILITY.name_resolve ('&1'
,1
,sch, part1, part2
,dblink, part1_type
,object_number);
END;
7/17/2015
nr.sql
Page 41
That can be really
helpful! I don't
have to do any
parsing myself, it
resolves multiple
layers of
synonyms.
Maybe.
OK, what's the
catch?
Copyright 2002-2004 Steven Feuerstein
The problems with NAME_RESOLVE
 Very minimal documentation (let's take a look)
– What the heck is that context parameter for?
– What are the part type values?
– How are parts 1 and 2 filled?
 Lots of OUT parameters, some of which I won't often
need, making it awkward to call the program
 Relatively incomprehensible results (as in numeric
codes for the type)
 And Oracle implies the program only works for stored
program units.
– What's with that? Is it really true? I would certainly like to
be able to use it for any sort of database object.
7/17/2015
Page 42
Copyright 2002-2004 Steven Feuerstein
What I want and need
 A program that is easy to use, that is reliable,
that works with all different types of object
names.
 In other words, I would dearly love to have
the program that Oracle should have provided
to me, but we can't always be that lucky.
 So let's see what it takes to solve this
problem myself.
– That's always much more satisfying, anyway!
7/17/2015
Page 43
Copyright 2002-2004 Steven Feuerstein
Steps to come up with a solution
 Make sure I understand my problem space
thoroughly.
– With the documentation so skimpy, I will need to
do some research and exploration.
 Develop a test suite in advance so I know
when I have completed my solution.
 Build iteratively and use top-down design.
7/17/2015
Page 44
Copyright 2002-2004 Steven Feuerstein
Research: know your problem space
 I have a number of questions about
NAME_RESOLVE. I am not even sure I
understand how it works. So let's start there.
 I build a straightforward encapsulation of
NAME_RESOLVE that is easier to use and
explains itself more fully.
 Then I build a simple test script on top of
that.
snc.sp
7/17/2015
Page 45
Copyright 2002-2004 Steven Feuerstein
Exercising NAME_RESOLVE
 Since I really don't
understand the context,
I am going to focus
there first.
 So...if I pass a context
value of 1, I get
sensible information for
my program units.
 And I have uncovered
some problems in the
Oracle documentation.
SQL> @snc_test1 0
Error resolving "proc" with context 0
ORA-20005: ORU-10034: context argument
must be 1 or 2 or 3 or 4 or 5 or 6 or 7
SQL> @snc_test1 1
Schema: SCOTT
Procedure: PROC
Schema: SCOTT
Function: FUNC
Schema: SCOTT
Package: PKG
Schema: SCOTT
Package: PKG
Name: PKGPROC
SQL> @snc_test1 2
Error resolving "proc" with context 2
ORA-04047: object specified is
incompatible with the flag specified
snc_test1.sql
7/17/2015
Page 46
Copyright 2002-2004 Steven Feuerstein
Stretching NAME_RESOLVE
 The doc says that
SQL> exec snc ('EMP', 1)
this program only
Error resolving "EMP" with context 1
ORA-04047: object specified is
works with stored
incompatible with the flag specified
program units. That SQL> exec snc ('EMP', 2)
Schema: SCOTT
seems kind of silly.
2: EMP
 Let's try it with
SQL> exec snc ('EMP', 3)
Error resolving "EMP" with context 3
other DB objects
ORA-06564: object EMP does not exist
and see what
happens.
Exciting! NAME_RESOLVE does seem to work for tables,
you just have to pass it a context value of 2.
But that means you have to remember which context goes
with which type. Ugh. But is that really necessary?
7/17/2015
Page 47
Copyright 2002-2004 Steven Feuerstein
Making my software smarter
 If we can anticipate situations, we can write
code to handle those situations.
 The second iteration of my exploration
program, snc, automatically seeks the correct
context for the "thing" that is passed in.
– Notice also the changed handling of object types.
 And as I expand the "reach" of my program, I
also discover new program types, and add
them to the code.
Context: 2
Computers are not themselves smart.
Which is all the more reason that we
should make our software as smart
7/17/2015
(but not clever) as possible.
Page 48
Schema: SCOTT
Table: TAB
Context: 4
Schema: SCOTT
View: VU
snc2.sp
snc_test2.sql
Copyright 2002-2004 Steven Feuerstein
Time to get real
 SNC is an interesting demonstration utility and
research vehicle. Now it is time to think about the
replacement I want to build.
 First, I wil concentrate on the interface, not the
implementation.
– Don't pass the context value. That's my added value.
– Other than that, keep it the same as NAME_RESOLVE.
CREATE OR REPLACE PROCEDURE
NAME
IN
,SCHEMA
OUT
,part1
OUT
,part2
OUT
,dblink
OUT
,part1_type
OUT
,object_number
OUT
) AUTHID CURRENT_USER
QCGU_name_resolve (
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
NUMBER
NUMBER
But wait – a standalone procedure?
No! Best to switch to
a package right now.
7/17/2015
Page 49
Copyright 2002-2004 Steven Feuerstein
The QNR package
 I am quite certain I will be needing more stuff
over time.
– For example, I will want to separate out the NR
helper from the "show" code.
Start with a package,
and save yourself
the trouble of adding
the dot later.
LISTING
7/17/2015 qnr1.pks
CREATE OR REPLACE PACKAGE Qnr AUTHID CURRENT_USER
IS
PROCEDURE name_resolve (
NAME
IN
VARCHAR2
,SCHEMA
OUT
VARCHAR2
,part1
OUT
VARCHAR2
,part2
OUT
VARCHAR2
,dblink
OUT
VARCHAR2
,part1_type
OUT
NUMBER
,object_number
OUT
NUMBER
);
PROCEDURE show (NAME_IN IN VARCHAR2);
END Qnr;
Page 50
Copyright 2002-2004 Steven Feuerstein
Set up test script to validate solution
 I have my interface. Now it is time to think
about how to VERIFY – not IMPLEMENT – my
solution.
 Let's apply utPLSQL/Ounit to this challenge.
 Generally, what you do with utPLSQL is:
– Create a test package with test procedure.
– Within test procedure establish control data, run
program to be tested, compare results with
control.
 Complications...
– My test data are database objects!
7/17/2015
Page 51
Copyright 2002-2004 Steven Feuerstein
Soft-coding the testing process
 Rather than hard code the individual tests, I
will drive the test from a table:
PROCEDURE ut_name_resolve
IS
Flexibility is critical!
l_results
Qnr.name_resolve_rt;
BEGIN
FOR tc IN (SELECT * FROM qnr_testcase)
LOOP
Qnr.name_resolve (tc.object_name
,l_results.SCHEMA
,l_results.part1, l_results.part2
,l_results.dblink, l_results.part1_type
,l_results.object_number
);
utassert.eq (tc.NAME || '-Schema', l_results.SCHEMA, tc.SCHEMA);
...
END LOOP;
END ut_name_resolve;
qnr_setup.sql
qnr_testcase.sql
ut_qnr.pks/pkb
7/17/2015
Page 52
Copyright 2002-2004 Steven Feuerstein
Build the solution
 I know how to test it. I know how to call it.
Time for implementation!
– First pass is straightforward....
c_max_context
CONSTANT PLS_INTEGER := 8;
l_context
PLS_INTEGER := 0;
l_resolved
BOOLEAN
:= FALSE;
BEGIN
WHILE (NOT l_resolved AND l_context <= c_max_context)
LOOP
BEGIN
DBMS_UTILITY.name_resolve (NAME, ... );
l_resolved := TRUE;
EXCEPTION
WHEN OTHERS THEN l_context := l_context + 1;
END;
END LOOP;
qnr1.pkb
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'QNR cannot resolve "' || NAME || '"');
7/17/2015 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
Copyright 2002-2004 Steven Feuerstein
END name_resolve;
Page 53
Build, test, fix, test, debug, test, test, test
 Once I complete a phase of implementation, I
can immediately run my tests with Ounit.
– I get instant feedback and my attention is tightly
focused on problem areas.
– I can also easily expand my test cases as I
encounter or want to consider new data, or fix
bugs, or enhance the program (expand scope).
 For example...what about synonyms?
– It turns out that we have a problem. Synonyms of
public synonyms are not resolved properly. It is
actually a problem with ALL_SYNONYMS.
7/17/2015
Page 54
qnr1.sql – first pass
qnr2.sql
– with
synonyms
Copyright
2002-2004
Steven Feuerstein
The problem with synonyms
 When I create a synonym over a public
synonym, ALL_SYNONYMS "loses" the
information about that public synonym.
Synonym "ls_ps_pkg" is defined as:
Owner: QNR_TEST
Synonym name: LS_PS_PKG
Should be
Table owner: QNR_TEST
PUBLIC!
Table name: PS_PKG
Database link:
BEGIN
Qnr.show_synonym (
'QNR_TEST', 'ls_ps_pkg');
Qnr.show_synonym (
'PUBLIC', 'ps_ls_pkg');
END;
/
Synonym "ps_ls_pkg" is defined as:
Owner: PUBLIC
Synonym name: PS_LS_PKG
Table owner: QNR_TEST
Table name: LS_PKG
Database link:
Time to enhance
QNR.name_resolve
7/17/2015
qnr3.sql – show synonyms
Page 55
Copyright 2002-2004 Steven Feuerstein
Enhance QNR.name_resolve for synonym support
 First, I should do some research and make
sure I understand the problem. (done)
 Next, I need to update my test script to make
sure I can reproduce the problem. (done)
 Now I can start working on the code.
BEGIN
resolve_in_loop (NAME);
IF object_number IS NULL
THEN
try_as_synonym (NAME);
END IF;
EXCEPTION
WHEN OTHERS
THEN
try_as_synonym (NAME);
END name_resolve;
7/17/2015
Page 56
I will use top down design so I
can focus on the high-level
logic: try to resolve in the loop.
If that is unsuccessful, maybe it
is one of those problematic
synonyms.
qnr3.pks– resolve synonyms
qnr3.pkb
Copyright 2002-2004 Steven Feuerstein
Trying to resolve a synonym
 Let's now drill down into the try_as_synonym
program. Hmmm. Not much to see, because all of
the logic is buried inside synonym_resolve.
PROCEDURE try_as_synonym (NAME_IN IN VARCHAR2)
IS
l_schema
ALL_OBJECTS.owner%TYPE;
l_name
ALL_OBJECTS.object_name%TYPE;
BEGIN
-- Try to resolve as synonym.
synonym_resolve (NAME_IN, l_schema, l_name);
IF l_name IS NOT NULL
THEN
resolve_in_loop (l_schema || '.' || l_name);
END IF;
END try_as_synonym;
I took this approach
because I was then able
to expose and test the
synonym_resolve
program independently
of name_resolve.
7/17/2015
Page 57
Copyright 2002-2004 Steven Feuerstein
The synonym_resolve program
 There are several complexities to address:
– Parse the provided name so that I can query for
presence in the ALL_SYNONYMS view.
– Compensating for the "bad data" in
ALL_SYNONYMS.
– Repeated synonym resolution until it is a synonym
no longer.
 Let's take a look at the code and see if you
have any ideas for improving it.
qnr3.pks
qnr3.pkb
7/17/2015
Page 58
Copyright 2002-2004 Steven Feuerstein
Summary – NAME_RESOLVE
 We identified a flaw in Oracle's utility and
took the time to research and thoroughly
understand the problem.
 We committed to full unit-testing before
construction and wrote code to support that.
 We used top down design and iterative
development to manage complexity and avoid
bug-ridden code.
 We end up with a much more useful program.
QCGU
GUID {C55F8B4A-E4C1-41ED-AFAD-3BB211DCEC8E}
7/17/2015
Page 59
qnr.pks
qnr.pkb
qnr5.sql2002-2004
– correctSteven
objectFeuerstein
type
Copyright
RiTFiT: The Codecheck utility
 Oddly, and sadly, it is possible to compile
overloadings which are not usable.
CREATE OR REPLACE PACKAGE Salespkg
IS
PROCEDURE calc_total (
dept_in IN VARCHAR2);
PROCEDURE calc_total (
dept_in IN CHAR);
END Salespkg;
BEGIN
salespkg.calc_total (
'ABC');
END;
/
?
sales.pkg
Or even worse in some ways...
CREATE OR REPLACE PACKAGE Salespkg
IS
PROCEDURE calc_total (
reg_in IN VARCHAR2
, start_in IN DATE := SYSDATE);
PROCEDURE calc_total (dept_in IN CHAR);
END Salespkg;
I would like to build a
program to identify such
ambiguous overloadings.
How should I go about this?
7/17/2015
Page 60
Copyright 2002-2004 Steven Feuerstein
The Codecheck series on OTN
 Eight article series on the Oracle Technology
Network that shows how I built this utility.
– Search for "codecheck series" or go to the URL
below.
 Excruciating details on problem solving and
application of best practices.
– Top-down design with tiny chunks of code
– Unit testing with utPLSQL
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/codech_index.html
7/17/2015
Page 61
Copyright 2002-2004 Steven Feuerstein
GAME BREAK!
 Exercise your brain
with Mastermind
and you will be
better prepared for
the "real" problem
solving in your
software life.
7/17/2015
Page 62
Copyright 2002-2004 Steven Feuerstein
Fixing it right the second time
 No one – NO ONE – gets it all right the first
time.
– If only because "it" is always changing.
– The best you can do is build it the first time so
that it is VERY easy to change it the second and
third time.
 "Fixing it right" means apply many of the
same techniques, in a careful methodical
way.
– Unit tests provide insurance and an objective
measure of the impact of your work.
– Refactoring provides a guide.
7/17/2015
Page 63
Copyright 2002-2004 Steven Feuerstein
Refactoring for the Common Programmer
"Refactoring is the process of changing a software system in such a
way that it does not alter the external behavior of the code yet improves
its internal structure.
"It is a disciplined way to clean up code that minimizes the chances of
introducing bugs. In essence when you refactor you are improving the
design of the code after it has been written."
from Refactoring by Martin Fowler
 Refactoring takes common sense and good
programming techniques and formalizes the
process.
– Fowler is much more rigorous and abstract than
I, but let's look at some examples and exercises.
7/17/2015
Page 64
Copyright 2002-2004 Steven Feuerstein
Key refactorings for PL/SQL developers


















Hide business rules inside functions
Replace single row queries with function.
Hide references to package variables.
Use SUBTYPEs and anchored types for all VARCHAR2 declarations.
Replace literal with named constant and/or function.
Ensure at least one row for numeric FOR loop.
Replace repetitive SQL operations with FORALL and BULK COLLECT.
Reduce excessively long declaration sections.
Move initialize and cleanup logic to local modules.
Move repeated logic into separate, perhaps local, modules.
Move complex logic into separate, perhaps local, modules.
Use unique names for parameters and variables in local modules.
Use explaining variables to clarify code.
Reorganize large package into several packages.
Replace status codes with exception handling within PL/SQL.
Write handlers for errors you can anticipate.
Call DBMS_OUTPUT.PUT_LINE via a wrapper.
Use DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM.
We won't get
to all these
today, but I will
define them all
inside QCGU,
and add to
them steadily.
7/17/2015
Page 65
Copyright 2002-2004 Steven Feuerstein
How to use these refactorings
 For each refactoring, I offer:
– An example of problematic code;
– A transformation or refactoring of that code;
– The QCGU Universal ID (GUID) for this
refactoring, and perhaps other GUIDS for
refactoring generators and useful code. If and
when you install QCGU, you can search for the
GUID to study and use the examples.
– File indicator so you can also download this code
and access it without installing QCGU.
QCGU GUID {F9D49584-0035-46EE-A9AF-40EAD1BA566B}
ref_hide_rules.sql
7/17/2015
Page 66
Copyright 2002-2004 Steven Feuerstein
Hide business rules inside functions
 Business rules and formulas are among the most
dynamic elements of an application.
 What is not dynamic or at issue are the following:
They only get more complicated over time.
They are used in many places in an application.
 You should always hide rules and formulas behind a
function interface, in a consolidated rules package.
QCGU
GUID {F9D49584-0035-46EE-A9AF-40EAD1BA566B}
7/17/2015
Page 67
ref_hide_rules.sql
Copyright 2002-2004 Steven Feuerstein
Exposed rules
 This is exactly the kind of code that is "easy"
to write...so we just write it, again and again.
PROCEDURE insert_employee (rec_in IN employee%ROWTYPE)
IS
l_results
qnr.name_resolve_rt;
BEGIN
IF rec_in.hiredate > ADD_MONTHS (SYSDATE, -1 * 18 * 12)
THEN
notify_employee_too_young;
ELSIF rec_in.citizen_id IS NULL
THEN
notify_no_citizen_id;
ELSIF rec_in.citizen_id NOT LIKE '___-__-____'
THEN
notify_bad_citizen_id;
ELSE
perform_insert (rec_in);
END IF;
END insert_employee;
7/17/2015
Page 68
Copyright 2002-2004 Steven Feuerstein
Hidden rules
 Create a rules package, move the logic there,
inside functions, replace the code.
PROCEDURE insert_employee (rec_in IN employee%ROWTYPE)
IS
BEGIN
IF employee_rp.too_young (rec_in.hiredate)
THEN
notify_employee_too_young;
ELSIF employee_rp.citizen_ID_not_null (rec_in.citizen_ID)
THEN
notify_no_citizen_ID;
ELSIF NOT employee_rp.valid_citizen_ID (rec_in.citizen_ID)
THEN
notify_bad_citizen_ID;
ELSE
perform_insert (rec_in);
END IF;
END insert_employee;
7/17/2015
Page 69
Copyright 2002-2004 Steven Feuerstein
Replace single row queries with function.
 We write lots of these (so easy!) but rarely do
all we should in terms of exception handling.
 In addition, there are a variety of ways to
write a single row query (implicit, explicit,
dynamic, collection-cache).
 If you expose the query, you cannot easily
change the implementations to optimize or
upgrade to latest DDL.
 Put the query into a function and these things
are possible
QCGU
GUID {9484A25D-6481-4651-B04F-FEBC5FC97E1F}
7/17/2015
Page 70
ref_singlerow.sql
Copyright 2002-2004 Steven Feuerstein
Exposed single row query
 Here's an explicit cursor, which was recommended
for many years, but is often slower than an implicit.
PROCEDURE do_employee_stuff (id_in IN NUMBER)
IS
CURSOR employee_cur IS
SELECT *
FROM employee
WHERE employee_id = id_in;
employee
employee_cur%ROWTYPE;
BEGIN
OPEN employee_cur;
FETCH employee_cur INTO employee;
IF employee_cur%FOUND
THEN
CLOSE employee_cur;
-- more stuff to do.
ELSE
-- maybe error handling
CLOSE employee_cur;
END IF;
7/17/2015END;
Page 71
Copyright 2002-2004 Steven Feuerstein
Hide the single row query
 Move the query to a function, preferably
defining this function inside a package.
– Decide on your implementation method and error
handling strategy.
 Replace the query and related logic with a call
to the function.
– May need to alter code after the query to fit the
standardized approach.
Generate stand-alone function using explicit query:
QCGU GUID {10EF5BE4-01F5-4683-987A-E23681DE4B1E}
Generate stand-alone function using implicit query:
QCGU GUID {63E5C38A-B85B-408C-A2AC-890A17E42766}
Generate query package:
7/17/2015
QCGU GUID {14DA98C7-FEB4-4315-ACC4-6A3EEBE3EC38}
Page 72
Copyright 2002-2004 Steven Feuerstein
The resulting application code
 No more query, cleaned up declarations (types and
names).
 I now check for a successful query by comparing
the old and new Ids. That might be a good general
technique.
PROCEDURE do_employee_stuff (id_in IN employee.employee_id%TYPE)
IS
employee_r employee%ROWTYPE;
BEGIN
employee_r := Or_Employee (employee_id_in => id_in);
IF employee_r.employee_id = id_in
THEN
-- more stuff to do.
ELSE
-- maybe error handling
END IF;
END;
7/17/2015
Page 73
Copyright 2002-2004 Steven Feuerstein
Hide references to package variables.
 When you declare data in a package specification, it
can be changed by any session with EXECUTE
authority on that package.
– You lose control of your data.
– You cannot guarantee integrity of the data.
– You cannot easily modify the implementation of your data.
 Solution: move the data to the package body, and
provide "get and set" programs that allow a user of
the package to read and write the data in a controlled
fashion.
Refactoring
QCGU GUID {161DD1F2-821C-47FF-992D-260EFD85F25B}
Generate get and set programs
7/17/2015
QCGU
GUID {624BA9B4-EC3A-413D-A40C-D63A483BCD73}
Page 74
ref_hidepkgvar.sql
Copyright 2002-2004 Steven Feuerstein
Exposed package data
 The g_maxdate global variable controls activity
within do_stuff. But a block outside the package
can modify the package variable, thereby
corrupting the program.
CREATE OR REPLACE PACKAGE analysis_pkg
IS
-- Dates must be in the past.
g_maxdate
DATE := SYSDATE;
PROCEDURE do_stuff (date_in IN DATE);
END analysis_pkg;
/
BEGIN
analysis_pkg.g_maxdate := SYSDATE + 10000;
analysis_pkg.do_stuff (SYSDATE + 10);
END;
/
7/17/2015
Page 75
Copyright 2002-2004 Steven Feuerstein
Hidden package data
CREATE OR REPLACE PACKAGE TEMP
IS
PROCEDURE set_maxdate (value_in IN DATE);
FUNCTION get_maxdate RETURN DATE;
END TEMP;
/
CREATE OR REPLACE PACKAGE BODY TEMP
IS
g_maxdate DATE := SYSDATE;
PROCEDURE set_maxdate (value_in IN DATE)
IS
BEGIN
IF value_in <= SYSDATE THEN
g_maxdate := value_in;
END IF;
END set_maxdate;
 You will want to add an
appropriate default
value for the variable
declaration, and correct
validation logic to the
set program
BEGIN
-- You can TRY to change it,
-- but it won't "take".
analysis_pkg.set_maxdate (
SYSDATE + 10000);
analysis_pkg.do_stuff (
SYSDATE + 10);
END;
/
FUNCTION get_maxdate RETURN DATE
IS
BEGIN
RETURN g_maxdate;
END get_maxdate;
END TEMP;
Generate get and set programs
7/17/2015
/
QCGU GUID {624BA9B4-EC3A-413D-A40C-D63A483BCD73}
Page 76
Copyright 2002-2004 Steven Feuerstein
Commentary on package data
 If your package data doesn't change, you could
define the variable as a constant and leave it in the
specification.
– If you are calling your code from a non-PL/SQL
environment, however, it may not support referencing
constants, so a function will be needed.
 A function interface also lets you change the value of
the variable or constant without requiring a
recompile.
 Sometimes you do want to put data in the package
specification....
– Must be accessible via dynamic PL/SQL blocks.
– You want to provide "scratch" workareas for users.
7/17/2015
Page 77
Copyright 2002-2004 Steven Feuerstein
Use SUBTYPEs and anchored types for all
VARCHAR2 declarations.
 Consider any and every VARCHAR2(N)
declaration to be a potential bug.
 Often, you can %TYPE against columns in
tables. These scenarios require different
action:
– Declare a large string. How large?
– Derived values that are made up of more than one
column, or not based on any column at all.
QCGU
GUID {83FBCAE6-1F3C-4B48-A3EE-BA28F281F1A8}
7/17/2015
Page 78
ref_novarchar2.sql
Copyright 2002-2004 Steven Feuerstein
Hard-coded VARCHAR2 declarations
 The company name is "usually" about 20 characters
long.
 The description is a really long field.
 The summary is a big column value, the biggest
allowed by Oracle.
 The full name of an employee is defined as "last
comma first plus middle initial".
DECLARE
l_company_name
l_description
l_summary
l_full_name
VARCHAR2
VARCHAR2
VARCHAR2
VARCHAR2
(100);
(32767);
(2000);
(200);
7/17/2015
Page 79
Copyright 2002-2004 Steven Feuerstein
SUBTYPES and anchored types to the rescue!
 Create both general and specific packages to
hold your various types.
CREATE OR REPLACE PACKAGE plsql_types
IS
SUBTYPE max_varchar2_t IS VARCHAR2 (32767);
SUBTYPE max_dbvarchar2_t IS VARCHAR2 (4000);
END plsql_types;
/
CREATE OR REPLACE PACKAGE employee_types
IS
SUBTYPE full_name_t IS VARCHAR2 (200);
END employee_types;
/
DECLARE
l_company_name
company.NAME%TYPE;
l_description
plsql_types.max_varchar2_t;
l_summary
plsql_types.max_db_varchar2_t;
l_full_name
employee_types.full_name_t;
BEGIN
7/17/2015
Page 80
Copyright 2002-2004 Steven Feuerstein
Commentary on soft-coding declarations
 We often rely on %TYPE for parameters definitions,
as well as variable declarations.
– But this means that if the table ever changes, in any way
whatsoever, all the programs must be recompiled.
– Some developers therefore avoid %TYPEs in parameter
lists; remember: parameter declarations are unconstrained
so you won't have a VARCHAR2(N) issue.
 Hopefully over time Oracle will fine-tune this
dependency model and minimize recompilation.
– It is something they are talking about.
7/17/2015
Page 81
Copyright 2002-2004 Steven Feuerstein
Replace literal with named constant and/or
function.
 We all know about this one, but they creep
into our code anyway.
 Certainly we sometimes do have literal values
that need to go into our code, but it is best to
give them names and reference them by
those names, hiding the values.
– Package level constants
– Functions that return the value. This is often
preferred because the value itself can go into the
package body, increasing protection and
minimizing the need to recompile.
QCGU GUID {FC5F22AA-A65A-45AC-842B-9E0EBA77EF40}
QCGU7/17/2015
GUID {FA9DC219-DFA3-4ECB-A501-DA559B702B94}
Page 82
ref_noliteral.sql
Copyright 2002-2004 Steven Feuerstein
Ensure both range values in numeric FOR
loop are not null.
 If either of the values are NULL, then Oracle raises a
VALUE_ERROR exception. (!)
I have seen this problem most
frequently when scanning the
contents of collections.
It's so easy to write a FOR loop
against a collection, but you are
then assuming there is something
in it – and that it is densely filled.
You are better off using a WHILE
loop to scan a collection.
DECLARE
TYPE list_tt IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
mylist
list_tt;
BEGIN
FOR indx IN mylist.FIRST .. mylist.LAST
LOOP
process_list_value (mylist (indx));
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7
QCGU
GUID {3237DF17-CDB2-4747-A06A-95DF98172B3F}
7/17/2015
Page 83
ref_forloop.sql
Copyright 2002-2004 Steven Feuerstein
Replace repetitive SQL operations with
FORALL and BULK COLLECT.
 FORALL and BULK COLLECT, first introduced
in Oracle8i, offer the opportunity to
dramatically speed up execution of repetitive
SQL statements.
 You should proactively seek out opportunities
to apply these to current applications, and
make them a standard part of any new
application construction.
 First some background on collections and
then the bulk processing feature.
7/17/2015
Page 84
Copyright 2002-2004 Steven Feuerstein
PL/SQL in Shared Memory – an overview
System Global Area (SGA) of RDBMS Instance
Shared Pool
Library cache
Shared SQL
Reserved Pool
Pre-parsed
Large Pool
calc_totals
emp_rec emp%rowtype;
tot_tab tottabtype;
User 1
Select *
from emp
upd_salaries
show_emps
emp_rec emp%rowtype;
tot_tab tottabtype;
Session 2 memory
(PGA/UGA)
Session 1 memory
(PGA/UGA)
Update emp
Set sal=...
User mysess.pkg
2
Sess2.sql
7/17/2015
Page 85
Copyright 2002-2004 Steven Feuerstein
PL/SQL Collections
 Collections are single-dimensioned lists of
information, similar to 3GL arrays.
 They are an invaluable data structure; all
PL/SQL developers should be familiar with
them -- and use them a lot.
 They take some getting used to, especially
when you want to leverage the latest
features, such as multi-level and stringindexed collections.
7/17/2015
Page 86
Copyright 2002-2004 Steven Feuerstein
Three Types of Collections
 Associative arrays - previously known as "index-by
tables" (V8) and "PL/SQL tables" (V7)
– Available in PL/SQL only; preferred structures for developers as
they are the easiest to use.
TYPE integer_aat IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
 Nested tables
– Can be defined in PL/SQL and SQL. Store large amounts of
persistent data in the column of a table, use with table functions.
– Required for some features, such as table functions
TYPE integer_nt IS TABLE OF INTEGER;
 Variable arrays
– Can be defined in PL/SQL and SQL; useful for defining small lists
in columns of relational tables.
TYPE integer_vat IS VARRAY(10) OF INTEGER;
7/17/2015
Page 87
Copyright 2002-2004 Steven Feuerstein
7/17/2015
Page 88
Copyright 2002-2004 Steven Feuerstein
Wide variety of collection methods
 Obtain information about the collection
–
–
–
–
COUNT returns number of rows currently defined in collection.
EXISTS returns TRUE if the specified row is defined.
FIRST/LAST return lowest/highest numbers of defined rows.
NEXT/PRIOR return the closest defined row after/before the
specified row.
– LIMIT tells you the max. number of elements allowed in a
VARRAY.
 Modify the contents of the collection
– DELETE deletes one or more rows from the index-by table.
– EXTEND adds rows to a nested table or VARRAY.
– TRIM removes rows from a VARRAY.
dates.sql/datemgr.pkg
psemp.pkg/tst
emplu.pkg/tst
7/17/2015
Page 89
Copyright 2002-2004 Steven Feuerstein
SQL on Steroids: About bulk processing
 Oracle8i and Oracle9i offer groundbreaking new
syntax to improve the performance of both DML
and queries.
 In Oracle8, updating from a collection (or, in
general, performing multi-row DML) meant
writing code like this:
CREATE TYPE dlist_t AS TABLE OF INTEGER;
/
PROCEDURE remove_emps_by_dept (deptlist dlist_t)
IS
BEGIN
FOR aDept IN deptlist.FIRST..deptlist.LAST
LOOP
DELETE emp WHERE deptno = deptlist(aDept);
END LOOP;
END;
7/17/2015
Page 90
“Conventional
binds” (and lots
of them!)
Copyright 2002-2004 Steven Feuerstein
Conventional binds – too much overhead
Oracle server
PL/SQL Runtime Engine
PL/SQL block
SQL Engine
Procedural
statement
FOR aDept IN deptlist.FIRST..
deptlist.LAST
LOOP
DELETE emp
WHERE deptno = deptlist(aDept);
END LOOP;
executor
SQL
statement
executor
Performance penalty
for many “context
switches”
7/17/2015
Page 91
Copyright 2002-2004 Steven Feuerstein
Enter the “Bulk Bind”
Oracle server
PL/SQL Runtime Engine
PL/SQL block
SQL Engine
Procedural
statement
FORALL aDept IN deptlist.FIRST..
deptlist.LAST
DELETE emp
WHERE deptno = deptlist(aDept);
executor
SQL
statement
executor
Much less overhead for
context switching
7/17/2015
Page 92
Copyright 2002-2004 Steven Feuerstein
Use the FORALL Bulk Bind Statement
CREATE TYPE dlist_t AS TABLE OF INTEGER;
/
PROCEDURE remove_emps_by_dept (deptlist dlist_t)
IS
BEGIN
FOR aDept IN deptlist.FIRST..deptlist.LAST
OLD
LOOP
SLOW
DELETE emp WHERE deptno = deptlist(aDept);
UGH
END LOOP;
END;
PROCEDURE remove_emps_by_dept (deptlist dlist_t)
IS
BEGIN
FORALL aDept IN deptlist.FIRST..deptlist.LAST
DELETE FROM emp WHERE deptno = deptlist(aDept);
END;
 Only the
single DML
statement is
allowed. If
you want to
INSERT and
then
UPDATE,
you need
two different
FORALL
statements.
NEW FAST OH SO COOL
7/17/2015
Page 93
Copyright 2002-2004 Steven Feuerstein
Use BULK COLLECT INTO for queries
Oracle8i requires
fetching into individual
collections of scalars.
CREATE OR REPLACE PROCEDURE process_emps
(deptno_in IN dept.deptno%TYPE)
IS
TYPE three_cols_rt IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE,
hiredate emp.hiredate%TYPE);
TYPE three_cols_tt IS TABLE OF
three_cols_rt INDEX BY PLS_INTEGER;
three_cols_t three_cols_tt;
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO three_cols_t
FROM emp
WHERE deptno = deptno_in;
...
END; 7/17/2015
Page 94
CREATE OR REPLACE PROCEDURE process_emps
(deptno_in IN dept.deptno%TYPE)
IS
TYPE numTab IS TABLE OF NUMBER;
TYPE charTab IS TABLE OF VARCHAR2(12);
TYPE dateTab IS TABLE OF DATE;
enos numTab;
names charTab;
hdates dateTab;
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO enos, names, hdates
FROM emp
WHERE deptno = deptno_in;
FOR i IN enos.FIRST..enos.LAST
LOOP
do_stuff (enos(i),
names(i), hiredates(i));
END LOOP;
END;
Oracle9i R2 supports
fetching into a collection
of records.
Copyright 2002-2004 Steven Feuerstein
LIMIT clause on BULK COLLECT
Use the LIMIT clause with
an explicit FETCH INTO.
CREATE OR REPLACE PROCEDURE bulk_oddities
(deptno_in IN dept.deptno%TYPE)
IS
TYPE three_cols_rt IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE,
hiredate emp.hiredate%TYPE);
TYPE three_cols_tt IS TABLE OF
three_cols_rt INDEX BY PLS_INTEGER;
three_cols_t three_cols_tt;
CURSOR three_cols_cur IS
SELECT empno, ename, hiredate
FROM emp
WHERE deptno = deptno_in;
BEGIN
OPEN three_cols_cur;
FETCH three_cols_cur
BULK COLLECT INTO three_cols_t
LIMIT 100;
7/17/2015
END;
Page 95
CREATE OR REPLACE PROCEDURE bulk_oddities
(deptno_in IN dept.deptno%TYPE)
IS
TYPE numTab IS TABLE OF NUMBER;
TYPE charTab IS TABLE OF VARCHAR2(12);
TYPE dateTab IS TABLE OF DATE;
enos numTab;
names charTab;
hdates dateTab;
CURSOR three_cols_cur IS
SELECT empno, ename, hiredate
FROM emp
WHERE deptno = deptno_in;
BEGIN
OPEN three_cols_cur;
FETCH three_cols_cur
BULK COLLECT INTO enos, names, hdates
LIMIT 100;
bulk_oddities.sql
But it doesn't seem to
work with SELECT INTO
or FETCH INTO
Copyright 2002-2004
Steven Feuerstein
collection
of records.
Exception handling for bulk operations
 Allows you to continue past errors and obtain Oracle9i
error information for each individual operation
only
(for dynamic and static SQL).
CREATE OR REPLACE PROCEDURE load_books (books_in IN book_obj_list_t)
IS
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
FORALL indx IN books_in.FIRST..books_in.LAST
Allows processing of all
SAVE EXCEPTIONS
rows, even after an
INSERT INTO book values (books_in(indx));
error occurs.
EXCEPTION
WHEN BULK_ERRORS THEN
New cursor
FOR indx in 1..SQL%BULK_EXCEPTIONS.COUNT
attribute, a pseudoLOOP
collection
log_error (SQL%BULK_EXCEPTIONS(indx));
END LOOP;
END;
bulkexc.sql
7/17/2015
Page 96
Copyright 2002-2004 Steven Feuerstein
Bulk processing challenge: conversion
 You probably have a lot of code written the
"old fashioned" way.
– You may not be using collections at all.
 So the question becomes: how do you convert
to using bulk processing?
 Let's take a look at a variety of scenarios.
QCGU
GUID {CD64BC87-4A27-4C37-8526-47865F6925E2}
7/17/2015
Page 97
Copyright 2002-2004 Steven Feuerstein
Convert cursor loop with DML
 Consider this common scenario: give each employee
in a department a raise and insert a row in the
history table to record the event.
– Easy to write with cursor FOR loop and two DML
statements. But kinda slow...
 To convert this code, you will take the following
three steps:
– 1. Declare collections for each piece of data queried.
– 2. Replace the cursor loop with a BULK COLLECT statement.
– 3. Write a FORALL statement for each DML operation in the
loop.
QCGU
GUID {262EF054-9F81-40D8-BD79-1D847689DB9D}
7/17/2015
Page 98
ref_loop_to_bulk.sql
Copyright 2002-2004 Steven Feuerstein
Convert cursor loop with DML and
conditional logic
 This is a more complicated situation. Rather than
process each row queried identically, the DML actions
taken depend on some data in employee's record.
 To convert this code, we will need to do some
additional work:
– 1. Declare collections for each piece of data queried.
– 2. Replace the cursor loop with a BULK COLLECT statement.
– 3. Pre-process the collections populated by BULK COLLECT
to partition the data so that it reflects the conditional logic
in the cursor loop.
– 4. Execute a FORALL statement for each DML operation in
the loop, using the appropriate array to drive the
processing.
QCGU
GUID {F780EB9C-8B84-4C68-9CD5-68C18E9A0930}
7/17/2015
Page 99
ref_cond_to_bulk.sql
cfl_to_bulk.sql
Copyright 2002-2004 Steven Feuerstein
Reduce excessively long declaration
sections.
 Pages and pages of declarations are hard to
read, intimidating to look at it, and indicate
the following problems or possibilities:
– You should probably be using records or other
composite structures to group together related
elements.
– You are probably declaring variables that are only
used in a small portion of your program – in what
should be local modules.
QCGU GUID {D2F304C6-FD37-4713-A23C-412B6EDE8B66}
LISTING
ref_longdecl.sql
7/17/2015
Page 100
Copyright 2002-2004 Steven Feuerstein
Nasty-looking declaration section
CREATE OR REPLACE PROCEDURE review_account (id_in IN INTEGER)
IS
l_id
sg_account.ID%TYPE;
l_acc_name
sg_account.NAME%TYPE;
l_description
sg_account.description%TYPE;
l_driver_type_id
sg_account.driver_type_id%TYPE;
l_engine_type_id
sg_account.engine_type_id%TYPE;
l_output_type_id
sg_account.output_type_id%TYPE;
l_is_static
sg_account.is_static%TYPE;
l_sa_object_type_id
sg_account.sa_object_type_id%TYPE;
SA_APPLICATION.def_sequence_prefix%TYPE;
l_author
sg_account.author%TYPE;
l_def_sequence_suffix
l_column_position
sg_account.column_position%TYPE;
SA_APPLICATION.def_sequence_suffix%TYPE;
l_output_prefix
sg_account.output_prefix%TYPE; l_def_pky_column_name
l_output_suffix
sg_account.output_suffix%TYPE;
SA_APPLICATION.def_pky_column_name%TYPE;
l_file_extension
sg_account.file_extension%TYPE; l_rae_error_code
SA_APPLICATION.rae_error_code%TYPE;
l_source_file_name
sg_account.source_file_name%TYPE;
l_deploy_dir
SA_APPLICATION.deploy_dir%TYPE;
l_created_on
sg_account.created_on%TYPE;
l_use_qda
SA_APPLICATION.use_qda%TYPE;
l_created_by
sg_account.created_by%TYPE;
v_item_template_1
INTEGER
:= 600;
l_changed_on
sg_account.changed_on%TYPE;
v_item_template_2
INTEGER
:= 433;
l_changed_by
sg_account.changed_by%TYPE;
v_item_template_3
INTEGER
:= 434;
l_in_context_name
sg_account.in_context_name%TYPE;v_item_template_4
NUMBER
:= 1016;
l_is_locked
sg_account.is_locked%TYPE;
l_locked_by
sg_account.locked_by%TYPE;
TYPE item_tt IS TABLE OF INTEGER
l_locked_password
sg_account.locked_password%TYPE; INDEX BY BINARY_INTEGER;
l_universal_id
sg_account.universal_id%TYPE;
l_is_top_level
sg_account.is_top_level%TYPE; l_item_list
item_tt;
l_app_id
SA_APPLICATION.ID%TYPE;
l_app_name
SA_APPLICATION.NAME%TYPE;
TYPE list_tabtype IS TABLE OF VARCHAR2 (2000)
l_app_description
SA_APPLICATION.description%TYPE; INDEX BY BINARY_INTEGER;
l_refresh_frequency
SA_APPLICATION.refresh_frequency%TYPE;
l_def_roadmap_dir
SA_APPLICATION.def_roadmap_dir%TYPE;
names
list_tabtype;
l_def_script_dir
SA_APPLICATION.def_script_dir%TYPE;
v_name
VARCHAR2 (2000)
l_def_code_dir
SA_APPLICATION.def_code_dir%TYPE;
:= accounts_rp.min_balance_account_name (SYSDATE);
l_def_sequence_prefix
v_first_name
VARCHAR2 (2000);
no_application_needed
EXCEPTION;
7/17/2015
Page 101
Copyright 2002-2004 Steven Feuerstein
Make changes one step at a time.
 Do careful substitutions, recompile, run your unit
tests. Then do another round of changes.
CREATE OR REPLACE PROCEDURE review_account (id_in IN INTEGER)
IS
l_account
sg_account%ROWTYPE;
l_application
SA_APPLICATION%ROWTYPE;
Step 1
v_item_template_1
v_item_template_2
v_item_template_3
v_item_template_4
INTEGER
INTEGER
INTEGER
NUMBER
:=
:=
:=
:=
600;
433;
434;
1016;
TYPE item_tt IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
l_item_list
CREATE OR REPLACE PROCEDURE review_account (id_in IN INTEGER)
IS
l_account
sg_account%ROWTYPE;
l_application
SA_APPLICATION%ROWTYPE;
l_item_list item_mgt_pkg.item_list_tt;
item_tt;
TYPE list_tabtype IS TABLE OF VARCHAR2 (2000)
INDEX BY BINARY_INTEGER;
names
list_tabtype;
v_name
VARCHAR2 (2000)
:= accounts_rp.min_balance_account_name (SYSDATE);
v_first_name
VARCHAR2 (2000);
no_application_needed
EXCEPTION;
Step 2
PROCEDURE handle_too_low_scenario
IS
TYPE list_tabtype IS TABLE OF VARCHAR2 (2000)
INDEX BY BINARY_INTEGER;
names
v_name
(SYSDATE);
v_first_name
BEGIN
list_tabtype;
VARCHAR2 (2000) := min_balance_account
VARCHAR2 (2000);
7/17/2015
Page 102
Copyright 2002-2004 Steven Feuerstein
Move initialize and cleanup logic to local
modules.
 Most programs follow a similar pattern:
 Initialize and check
– Initialize local variables, check parameters and
other conditions.
 Run main body of code
 Clean up when done
– This clean up takes place in the executable
section, but should also be done, at least some for
of it, in error handlers as well.
 Make this structure explicit in your program!
QCGU
GUID {A695C7EA-A124-48CF-AC8C-F6A0421CD2A0}
7/17/2015
Page 103
ref_initcleanup.sql
Copyright 2002-2004 Steven Feuerstein
Ambiguous initialization, messy cleanup
 Use of UTL_FILE or DBMS_SQL are good indicators of
the need to apply this technique.
CREATE OR REPLACE FUNCTION eqfiles (
check_this_in
IN
VARCHAR2
Here's a program to
,check_this_dir_in
IN
VARCHAR2
,against_this_in
IN
VARCHAR2
see if two files have
,against_this_dir_in
IN
VARCHAR2 := NULL
the same contents.
)
RETURN BOOLEAN
IS
...
BEGIN
-- Open both files, read-only.
checkid :=
UTL_FILE.fopen (check_this_dir_in, check_this_in, 'R'
,max_linesize
=> 32767
);
againstid :=
UTL_FILE.fopen (NVL (against_this_dir_in, check_this_dir_in)
,against_this_in, 'R'
,max_linesize
=> 32767
);
7/17/2015
Page 104
Copyright 2002-2004 Steven Feuerstein
Many opportunities to improve structure
CREATE OR REPLACE FUNCTION eqfiles (...)
RETURN BOOLEAN
IS
Self-explanatory
...
declarations of programs
PROCEDURE initialize
IS
BEGIN
-- Open both files, read-only.
END initialize;
BEGIN
initialize;
PROCEDURE cleanup_and_return (
return_value_in IN BOOLEAN)
IS
BEGIN
-- Close both files, return value.
END cleanup_and_return;
PROCEDURE get_next_line_from_file (
file_in
IN
UTL_FILE.file_type
,line_out
OUT
VARCHAR2
,eof_out
OUT
BOOLEAN
)
IS
BEGIN
-- Override default GET_LINE
END get_next_line_from_file;
Simple, clear execution
and exception sections
LOOP
get_next_line_from_file (
checkid, checkline, check_eof);
get_next_line_from_file (
againstid, againstline, against_eof);
-- set return value
END LOOP;
cleanup_and_return (retval);
EXCEPTION
WHEN OTHERS
THEN
cleanup_and_return (FALSE);
END eqfiles;
7/17/2015
Page 105
Copyright 2002-2004 Steven Feuerstein
Move repeated logic into separate, perhaps
local, modules.
 Discipline time: no matter how small the
repetition, keep it out of your code.
– Move the repetition to a procedure or function.
– Call the program instead.
– Biggest challenge: creeping repetition.
 Considerations...
– Define the module locally, globally within the
package body or in the package specification?
– How generic do you make the program?
QCGU
GUID {A4D2F1FD-545E-4E8C-A852-EF2AA0C18113}
7/17/2015
Page 106
ref_repeatlogic.sql
Copyright 2002-2004 Steven Feuerstein
It starts out so simple, but then....
PROCEDURE create_pg_script_group (id_out OUT Sg_Script_Grp_Tp.id_t)
IS
l_script_grp
Sg_Script_Grp_Tp.sg_script_grp_rt;
l_rows
PLS_INTEGER;
BEGIN
l_script_grp :=
Sg_Script_Grp_Qp.or_un_sg_script_grp_name
(NAME_IN => c_app_name || ' script group');
IF l_script_grp.ID IS NULL
THEN
Sg_Script_Grp_Cp.ins (NAME_IN
,id_out
);
END IF;
=> c_app_name || ' script group'
=> l_script_grp.ID
-- Clean out all the scripts in the group.
Sg_Script_Int_Cp.del_fk_si_hdr_id (hdr_id_in
,rows_out
);
id_out := l_script_.ID;
END create_pg_script_group;
Just in this one
program, right next to
each other. Why worry?
Then I needed to add the
USER to the name.
Then I needed the same
kind of logic for three
other tables (a deeply
nested structure).
=> l_script_grp.ID
=> l_rows
FINALLY, I gave up and
created programs in my
package body.
7/17/2015
Page 107
Copyright 2002-2004 Steven Feuerstein
Full set of programs to encapsulate names
FUNCTION rt_name (user_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
BEGIN
RETURN Sw_QCGU.c_QCGU_playground || '-' || NVL (user_in, USER);
END rt_name;
FUNCTION rt_script_grp_name (user_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
BEGIN
RETURN rt_name (user_in) || ' script grp';
END rt_script_grp_name;
Notice the two levels of
modules:
rt_name is the "core" or
prefix name.
The other programs use
that one.
FUNCTION rt_task_name (user_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
BEGIN
RETURN rt_name (user_in) || ' task';
END rt_task_name;
FUNCTION rt_task_grp_name (user_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
BEGIN
RETURN rt_name (user_in) || ' task group';
END rt_task_grp_name;
7/17/2015
Page 108
Copyright 2002-2004 Steven Feuerstein
Move complex logic into separate, perhaps
local, modules.
 This is one of the most important
refactorings.
– Minimize the complexity at any given level (block
or nested program call) in your application.
– Keep those executable sections really small.
 Considerations...
– Define the module locally, globally within the
package body or in the package specification?
– How generic do you make the program?
QCGU
GUID {38272BEB-74C6-4967-9FAA-9B33A90D13D0}
7/17/2015
Page 109
ref_hidecomplex.sql
Copyright 2002-2004 Steven Feuerstein
Dynamic "SELECT * FROM <table>" in PL/SQL
 You provide the table and WHERE clause. I
display all the data.
– I don't know in advance how many columns to
query. But I can obtain the column information
from ALL_TAB_COLUMNS...and from there the fun
begins!
 My first implementation, based on
DBMS_SQL, is one of those big blobs of code.
– Let's see what it takes to convert it something
more manageable – and up to date (native
Dynamic SQL).
7/17/2015
Page 110
intab.sp
intab9i.sp
intab9i.tst
Copyright 2002-2004 Steven Feuerstein
Pseudo-code flow for DBMS_SQL
implementation
Build the
SELECT list
BEGIN
FOR each-column-in-table LOOP
add-column-to-select-list;
END LOOP;
Parse the
variable SQL
DBMS_SQL.PARSE (cur, select_string, DBMS_SQL.NATIVE);
Define each
column
FOR each-column-in-table LOOP
DBMS_SQL.DEFINE_COLUMN (cur, nth_col, datatype);
END LOOP;
Execute the
query
Extract each
value
fdbk := DBMS_SQL.EXECUTE (cur);
LOOP
fetch-a-row;
FOR each-column-in-table LOOP
DBMS_SQL.COLUMN_VALUE (cur, nth_col, val);
END LOOP;
END LOOP;
END;
Lots of code, but relatively
straightforward
7/17/2015
Page 111
Copyright 2002-2004 Steven Feuerstein
The rewrite and shift to NDS
 The intab program has lots of declarations,
just a few, very small local modules, and a
long executable section.
 Best to start from the top (that is, the main
executable section, at the bottom of
program) and work our way down (up).
BEGIN
retrieve_column_information (table_in, line_length);
display_header (line_length);
generate_data;
END intab;
7/17/2015
Page 112
Copyright 2002-2004 Steven Feuerstein
What to do about method 4 in NDS?
 Since I don't know how many columns I am
querying, I cannot have a static INTO clause
for my EXECUTE IMMEDIATE.
 But NDS doesn't support dynamic INTO
clauses....
This
won't
work:
EXECUTE IMMEDIATE
'SELECT ' || col_list ||
' FROM ' || table_in ||
' WHERE ' || where_clause
INTO col1, col2 ... ?
How about this?
EXECUTE IMMEDIATE
'SELECT ' || col_list ||
' FROM ' || table_in ||
' WHERE ' || where_clause
INTO || into_list;
It's...dynamic PL/SQL!
7/17/2015
Page 113
Copyright 2002-2004 Steven Feuerstein
Shifting paradigms to dynamic PL/SQL
 So now I am thinking about building the entire block
to fetch from and display the data.
– Oddly, my query then becomes STATIC inside the dynamic
block!
Construct a static
query inside a CFL
BEGIN
l_block :=
'BEGIN
FOR rec IN (' || query_string || ')
LOOP
It's all one big
pl (' || concatenated_values || ');
PL/SQL block
END LOOP;
END;';
Concatenate all
EXECUTE IMMEDIATE l_block;
retrieved values into
END;
single string expression.
No need for INTO
or USING clauses
7/17/2015
Page 114
Copyright 2002-2004 Steven Feuerstein
Use unique names for parameters and variables
in local modules.
 Soon you will be addicted to local modules;
you will spin them off with barely a thought,
you will pass arguments in and out of them to
clearly document the hand-offs.
 One problem you can run into, however, is
that you will tend to make the names of
arguments at multiple levels the same.
 This can lead to problems unless you are
very, very careful.
QCGU
GUID {9C0A875E-FC37-4141-8216-58D00A7049FB}
7/17/2015
Page 115
ref_uniqueparms.sql
Copyright 2002-2004 Steven Feuerstein
Starts out simple, gets complicated fast
 No local modules, no issues.
PROCEDURE process_script (script_id_in IN
Sg_Script_Tp.id_t)
IS
l_script
Sg_Script_Tp.sg_script_rt;
BEGIN
l_script := Sg_Script_Qp.onerow (script_id_in);
IF l_script.NAME LIKE '%RUNTIME%'
THEN
delete_runtime_script (script_id_in);
ELSE
purge_results (script_id_in);
reset_script (script_id_in);
END IF;
END process_script;
I have lots more code to
write, so I move the
delete/purge logic to a
separate module.
I need to do the
delete/purge for all the
required scripts. Need to
pass in a script ID.
Suddenly the chance for
errors has increased
exponentially.
7/17/2015
Page 116
Copyright 2002-2004 Steven Feuerstein
Making sure parameter names are distinct
 The main procedure parameter name is different
from any inner procedures.
– An alternative: pass the main script id as a second
parameter.
PROCEDURE process_script (script_id_in IN Sg_Script_Tp.id_t)
IS
l_script
Sg_Script_Tp.sg_script_rt;
l_required
Sg_Script_Tp.id_tc;
l_row
PLS_INTEGER;
PROCEDURE delete_or_purge (dp_script_id_in IN Sg_Script_Tp.id_t)
IS
BEGIN
IF l_script.NAME LIKE '%RUNTIME%'
THEN
delete_runtime_script (dp_script_id_in);
ELSE
purge_results (dp_script_id_in);
reset_script (script_id_in);
END IF;
END delete_or_purge;
7/17/2015
Page 117
Copyright 2002-2004 Steven Feuerstein
Write handlers for errors you can anticipate.
 You should only allow exceptions to go unhandled, or be handled by the WHEN OTHERS
clause, when they are errors you cannot
anticipate.
– You can then provide more, better information to
users, support and code fixers.
 You will also want to distinguish between
anticipated errors that can be handled and
those that must propagate an exception.
QCGU
GUID {10256A6E-489E-4A78-ACDE-FEE690A4E927}
7/17/2015
Page 118
ref_anticipate.sql
Copyright 2002-2004 Steven Feuerstein
Single row queries: obvious example!
CREATE OR REPLACE PROCEDURE process_employee
(employee_id IN NUMBER)
IS
l_name VARCHAR2(100);
CREATE OR REPLACE PACKAGE BODY Fullname_Pkg
BEGIN
AS
SELECT last_name || ',' ||
FUNCTION fullname (
first_name
employee_id_in IN
INTO l_name
employee.employee_id%TYPE
FROM employee
) RETURN VARCHAR2
WHERE employee_id =
IS
process_employee.employee_id;
retval VARCHAR2(200);
-- Lots more processing logic.
COMMIT;
END;
BEGIN
SELECT
INTO
FROM
WHERE
last_name || ', ' || first_name
retval
employee
employee_id = employee_id_in;
RETURN retval;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RAISE;
END;
END;
Let's take a look at the
templated error
handlers for QDA
change packages.
7/17/2015
Page 119
Copyright 2002-2004 Steven Feuerstein
Call DBMS_OUTPUT.PUT_LINE via a wrapper.
 DBMS_OUTPUT.PUT_LINE is a royal pain in the ...
– Consider the code below. I trap and report on errors when I
resolve a name. Fine, except that Oracle recommends that
we no longer use SQLERRM. Instead use the
DBMS_UTILITY.FORMAT_ERROR_STACK, so you can see the
full error stack, and not just the first 255 characters.
– OK, but then DBMS_OUTPUT.PUT_LINE could fail. Argh!
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (
'QCGU_Name_Resolve cannot resolve "'
|| name_in
|| '"'
);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END name_resolve;
QCGU
GUID {774DC992-8242-4FD3-AD78-2B981DC3C513}
7/17/2015
Page 120
Copyright 2002-2004 Steven Feuerstein
Alternatives to DBMS_OUTPUT.PUT_LINE
 You should build or use a layer of code on top of
DBMS_OUTPUT.PUT_LINE that compensates.
 Options:
– Stand-alone single procedure (simple, quick to put in place)
– Overloaded package (much more robust)
– Embedded procedure (avoid external dependencies on other
programs).
– Much more flexible alternative that allows you to write to
screen, file, pipe, etc.
Stand-alone procedure
QCGU GUID {67CA49A1-6C46-4D4E-93DF-58BBDCB3C820}
Overloaded package
QCGU GUID {35CDC27F-7F12-473C-94D0-F521E6A5DF73}
Flexible output package
7/17/2015
QCGU
GUID {545C3BFA-72A5-4978-804B-C5A221967379}
Page 121
Copyright 2002-2004 Steven Feuerstein
Getting it right the SECOND time...
 The more rigorous you are in your approach
to refactoring, the more you will benefit.
– Check out Fowler's Refactoring and adapt to your
own needs.
 Make the time to fix things right.
– Being in a hurry threatens the timetable, sets
damaging expectations with users, and generally
makes us all feel lousy about our jobs.
7/17/2015
Page 122
Copyright 2002-2004 Steven Feuerstein
New Programs, New Programmers
It's never too late to improve, start anew...but you do need:
 Active intellectual engagement: you've
got to enjoy and be inspired by
abstract thinking! Play games!
 Creativity: Writing software should
almost never be drudgery.
 Discipline: Software is part art and
engineering. We need to work within
clear constraints and rules in order to
free our creative energies.
7/17/2015
Page 123
Copyright 2002-2004 Steven Feuerstein
Acknowledgements and Resources
 Very few of my ideas are
in any sense original. I
have learned a lot from
every one of these books
and authors.
7/17/2015
Page 124
Copyright 2002-2004 Steven Feuerstein
A guide to my mentors/resources








A Timeless Way of Building – a beautiful and deeply spiritual book (in a
non-religious way) on architecture that changed the way many developers
approach writing software.
Peopleware – a classic text on the human element behind writing
software.
Refactoring – formalized techniques for improving the internals of one's
code without affect its behavior.
Code Complete – another classic programming book covering many
aspects of code construction.
The Cult of Information – thought-provoking analysis of some of the
down-sides of our information age.
Patterns of Software – a book that wrestles with the realities and
problems with code reuse and design patterns.
Extreme Programming Explained – excellent introduction to XP.
Code and Other Laws of Cyberspace – a groundbreaking book that
recasts the role of software developers as law-writers, and questions the
direction that software is today taking us.
7/17/2015
Page 125
Copyright 2002-2004 Steven Feuerstein
QUESTIONS
& ANSWERS
& DISCUSSION
7/17/2015
Page 126
Copyright 2002-2004 Steven Feuerstein