Writing Maintainable Code by Donald J Bales 630

Writing Maintainable Code by Donald J. Bales (630) 776 -0071 don@donaldbales. com http: //www. pl-sql. org http: //www. donaldbales. com http: //www. facebook. com/donald. j. bales http: //www. linkedin. com/in/donaldbales

Please buy these, I’d need a vacation!

Audience Assessment § § § Who has had some experience with object-orientation? Who has done some object-oriented programming? PL/SQL? § § Who has used CREATE TYPE… AS OBJECT? Who has used CREATE TABLE … OF… § Who knows some Sixties-speak?

“Make it obvious!”

Organization

Stewardship “Inspect what you expect” (Ziglar) § § Everyone must participate, but you’ll only have a maintainable code base if someone makes sure you have a maintainable code base. Use a source control system – Hint: “The data-dictionary in an Oracle database is not a source control system!”

The Organizing Principle § All decisions must evolve around a core principal, in this case, writing maintainable code. Not two or three principals, just one! code So what is maintain-able code? § No or low defects – that way there’s less to maintain § A flexible architecture; expansion capabilities – so when you’re asked to make it do something additional, you don’t have to start over. “Plan for the future, where every one always wants more. ” § Modular coding – well defined interfaces mean less internal interaction between components § Consistency – so when changes do need to be made, it’s easy. “It's better to be consistently wrong than it is to be inconsistent. ” § Built-in troubleshooting – when something does go wrong, it’s easy to detect § Built-in performance profiling – when it’s not expanding well, it’s easy to detect § Use object-orientation – better taste and less filling

Organizing Your Source Code § Break scripts into separate files § Make scripts atomic § Make scripts re-runnable § Agree upon and follow naming conventions (stewardship) § Name files after the object they will create “make it obvious” Example: genders. tab for a table of gender codes Script Type Suffix Stand-alone functions . fun Stand-alone procedures . prc Type specifications . tps Type bodies . tpb Tables (includes column and tables constraints, sequence, indexes) . tab Package specifications . pks Package bodies . pkb Views . vw Inserts . ins Updates . upd Deletes . del Selects . sql

SQL

Naming Conventions In a Relational Setting… § § Use plural table names, after all, they are collections Create a sequence with the same name as its associate table, with a suffix of _ID – § § Example: a table named GENDERS would have a corresponding sequence named GENDER_ID. Why? “Then it’s obvious. ” Use singular package names Create a package for each table’s associated behavior – Example: a table named GENDERS would have a corresponding package named GENDER. Why? Atomicity and modularity. If you need to fix something in the GENDER package, only it will be affected. Object Type Object Name Table GENDERS Sequence GENDER_ID (table) Package GENDER

Naming Conventions In an Object-relational Setting… § § Use singular type names Create tables for each type’s persistence – § § Example: a type named GENDER would have a corresponding table named GENDERS. Why? “Then it’s obvious. ” Use plural table names, after all, they are collections Create a sequence with the same name as its associate table, with a suffix of _ID – Example: a table named GENDERS would have a corresponding sequence named GENDER_ID. Why? “Then it’s obvious. ” Object Type Object Name Table GENDERS Sequence GENDER_ID Type GENDER

Naming Conventions Consistency § Use the same name for something at every layer, after all it’s the same thing! – column -> parameter -> variable, table -> cursor -> record – attribute -> parameter -> variable, type -> table -> cursor -> record In other words, none of this %^&*: birth, birth_date, date_of_birth, born_on, bday (ahhh, your making me crazy!) § Few or no abbreviations – they create ambiguity

Use Constraints “garbage in, garbage out” § Use check constraints – – – not null date = trunc(date) if time is not used needed number = trunc(number) for integers, why? create insert select table into * from TEST_INTEGER (an_integer); TEST_INTEGER values (1. 5); TEST_INTEGER values (1. 6); TEST_INTEGER; AN_INTEGER -----1 2 § § Use primary keys against an id Use unique keys against real-world primary key values Use foreign keys – Really. I’m not kidding. Use triggers to implement conditional foreign keys

Plan For Change § § § Normalize your types and tables, don't “de-normalize for performance” – denormalization hinders expansion without modification Don't constrain numbers – things are always getting larger and more expensive Give varchar 2 attributes and columns plenty of room -- things are always getting larger and being internationalized. You’d be surprised how compact the English language is when compared to others Use CLOBS instead of varchar 2(4000) when appropriate – they are now well supported by all layers of development use the AL 32 UTF 8 character set

Plan for Re-Use § § § Design with re-use in mind Use polymorphic naming – come up with a set of common names before coding; minimize they set of names used Inheritance v. code generation – Inheritance fosters consistency, efficiency, and modularity Encapsulation improves modularity; modularity reduces interdependence; less interdependence means better maintainability Did I say use the AL 32 UTF 8 character set?

Look for the “Gotchas” § § § Never use = NULL in a SQL statement, because NULL is not equal to anything, not even NULL Search you source code for the above mistake Never use as UPDATE statement without detection, i. e. a WHERE clause that detection makes sure you are updating something that has not already been updated

PL/SQL

Prevention and Preparation § I like to think of maintainability in PL/SQL in two facets: – Prevention – Preparation § First let’s look at prevention – – § Naming Conventions Anchors Explicit Conversion Blocking And then, preparation – – – Comments Blocking Success and Failure Messages Built-in Troubleshooting Capabilities Built-in Profiling Capabilities

Naming Conventions in PL/SQL § § § Use data-type prefixes (or suffixes). Why? Parameter prefixes: – ai – argument IN – aio – argument IN OUT – ao – argument OUT For example: PROCEDURE get_code_id_description( aiov_code in out varchar 2, aon_id out number, aov_description out varchar 2, aid_on in date ) is v_code varchar 2(30); v_table_name varchar 2(100); begin. . . end get_code_id_description; Data Type Prefix Argument/Parameter a_ Cursors c_ Dates d_ Numbers n_ Objects o_ Records/Rows r_ Tables/Types t_ Varchar 2 v_ Access Modifiers IN i IN OUT io OUT o

*Anchoring Data Types in PL/SQL § § *The term anchoring originated with Steven. Use %TYPE for attributes and columns Use %ROWTYPE for records and rows Why? You can use the wrong data type. And, it makes it obvious!

Conversions in PL/SQL § § Use explicit conversions where you wrap the conversion in a PL/SQL block in order to detect a failure to convert a character value to a number or date. Wrap null-able attributes or columns variables in NVL() with a well known substitution value so your IF statements don’t fail silently.

Catching Errors in PL/SQL § § § Block (BEGIN … EXCEPTION … END; ) every SQL statement [except a cursor? ] END; Block all singletons. Oh yeah. I already stated this above, didn’t I. Must be Important? Block all character to number conversions Block all character to date conversions Never use WHEN OTHERS then NULL; (OK. On a rare occasion I do. ) Search your source code for this and make sure it really needs to exist! BEGIN select description into v_description from GENDER; EXCEPTION WHEN NO_DATA_FOUND then v_description : = ' '; WHEN OTHERS then NULL; -- It'll take you hours to find this insect. END; § Add PROCEDURE initialize() to every package that uses its initialization section, in order to exercise its initialization section on demand. -- This is the cause of the error that happens once and then mysteriously goes away!

test_initialization_section. hello code… create or replace PACKAGE test_initialization_section as PROCEDURE hello; PROCEDURE initialize; end test_initialization_section; / create or replace PACKAGE BODY test_initialization_section as v_greeting varchar 2(4); PROCEDURE hello is begin pl(v_greeting); end hello; PROCEDURE initialize is begin v_greeting : = 'Hello'; end initialize; -- THE INITIALIZATION SECTION begin initialize; initialize end test_initialization_section; /

test_initialization_section. hello output… BEGIN test_initialization_section. hello; END; . hello * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "BPS. TEST_INITIALIZATION_SECTION", line 15 ORA-06512: at "BPS. TEST_INITIALIZATION_SECTION", line 20 ORA-06512: at line 1 BEGIN test_initialization_section. hello; END; . hello PL/SQL procedure successfully completed. SQL> exec test_initialization_section. initialize; . initialize BEGIN test_initialization_section. initialize; END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "BPS. TEST_INITIALIZATION_SECTION", line 15 ORA-06512: at line 1

Comments in PL/SQL § § § Explain mysterious code Document every type specification Document every package specification Document other functions and procedures Add PROCEDURE help(); to every type and package specification. Have it extract you comments from your types and packages by querying SYS. DBA_SOURCE Name Null? ----------------OWNER NAME TYPE LINE TEXT § Type -----------VARCHAR 2(30) VARCHAR 2(12) NUMBER VARCHAR 2(4000) Produce distributable documentation – so somebody else does have to code the same thing, because they don’t know it already exists!

Error Handling in PL/SQL § § § § § Block (BEGIN … EXCEPTION … END; ) every SQL [statement except a cursor? ] END; Only use an UPDATE statement with detection, i. e. with a WHERE clause that detection makes sure you are updating something that has not already been updated! Block all singletons. Oh yeah. I already stated this above, and again? Important? Block all character to number conversions Block all character to date conversions Every unhandled exception should raise a failure (error) message Use well formatted and well know success and failure messages I use ORA-20000 – ORA-200## starting from zero at the top of a type or package, incrementing by one for each new exception clause. I use a standard format like this: when OTHERS then raise_application_error(-20000, SQLERRM|| ' on SELECT GENDERS'|| ' in GENDER. get(aiv_code)'); end;

Build-in Trouble-shooting Capability § § § § Add boolean b_debug and then use if (b_debug) for every built-in message Add a PROCEDURE set_debug_on() and PROCEDURE set_debug_off() to your packages in order to turn debug logging on and off on demand Use SYS. DBMS_OUTPUT. put_line() to print debug messages to the console Or better yet, create a DEBUG type or package that logs your if (b_debug) messages to a DEBUGS table, so you can view your PL/SQL progam’s progress as it happens by querying the DEBUGS table. Further, create a DEBUG package that sets the debug state for each PL/SQL program in a table that is in turn queried by a given package on start-up Dumb down your code by declaring intermediate variables that can be viewed by a debugger I suggest you also learn how to use the debugger!

Build-in Performance Profiling Capabilities § § § Use Explain Plan on your SQL statements Keep track of the start and stop times of your functions and procedures and log the output to a profiling table Use SYS. DBMS_PROFILER to profile your PL/SQL programs

Test Everything

Write Test Plans “It doesn't work until you prove it to me!” § § § Think about how something should work, and plan on testing it—or better yet, write the test first! On average, 40% of my code is defective the first time I run a well written test unit against it! And yet, I try so hard to prevent any errors!!! Track defects, categorize them, and then update your test plan and then test units to make sure you test for them in the future

Write Test Units (first? ) “Ah! The proof!” § § § § Strive for 100% coverage, it’s achievable in this development layer coverage Write a test package for every type and package Name the test package after the package or type it tests, just prefix the name with TEST_ Write one or more test units for each method: function or procedure. Name each test unit after the method it tests, just prefix the method name with, guess what? TEST_ Log both success and failure to the console or to a TESTS table. I personally like a table because I can more easily do a statistic analysis on my defects. And yes, even write test units for the Oracle packages you use. You know how use they’re supposed to work because you have some documentation—but do they? Write an automated testing package to query the database for all your test units and run them all any time you make a change to the source code!

create or replace TYPE base_ as object ( id number, CONSTRUCTOR FUNCTION base_( self in out nocopy base_) return self as result deterministic, CONSTRUCTOR FUNCTION base_( self in out nocopy base_, id number) return self as result deterministic, MEMBER FUNCTION sequence_name return varchar 2, MEMBER FUNCTION table_name return varchar 2, MEMBER FUNCTION type_name return varchar 2, MEMBER FUNCTION get_id return number, MEMBER PROCEDURE save, MAP MEMBER FUNCTION to_varchar 2 return varchar 2, STATIC PROCEDURE help ) not final; / show errors type base_;

create or replace package body test_base_ as PROCEDURE test is begin test_constructor; test_constructor_with_id; test_sequence_name('BASE__ID'); test_table_name('BASE_S'); test_type_name('BASE_'); test_help; test_to_varchar 2('"000000000000001234567890"'); end; PROCEDURE test_constructor is o_ base_; begin pl(chr(10)||'base_: test_constructor'); o_ : = new base_; pl('base_: test_constructor passed. '); exception when OTHERS then pl('base_: test_constructor ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_constructor_with_id is o_ base_; begin pl(chr(10)||'base_: test_constructor_with_id'); o_ : = new base_(1); pl('base_: test_constructor_with_id passed. '); exception when OTHERS then pl('base_: test_constructor_with_id ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_sequence_name( aiv_expected_name in varchar 2) is o_ base_; v_sequence_name varchar 2(100); begin pl(chr(10)||'base_: test_sequence_name'); o_ : = new base_; v_sequence_name : = o_. sequence_name; if v_sequence_name = aiv_expected_name then pl('base_: test_sequence_name passed. '); else pl('base_: test_sequence_name ***FAILED***: v_sequence_name='|| v_sequence_name||' not '||aiv_expected_name); end if; exception when OTHERS then pl('base_: test_sequence_name ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_table_name( aiv_expected_name in varchar 2) is o_ base_; v_table_name varchar 2(100); begin pl(chr(10)||'base_: test_table_name'); o_ : = new base_; v_table_name : = o_. table_name; if v_table_name = aiv_expected_name then pl('base_: test_table_name passed. '); else pl('base_: test_table_name ***FAILED***: v_table_name='|| v_table_name||' not '||aiv_expected_name); end if; exception when OTHERS then pl('base_: test_table_name ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_type_name( aiv_expected_name in varchar 2) is o_ base_; v_type_name varchar 2(100); begin pl(chr(10)||'base_: test_type_name'); o_ : = new base_; v_type_name : = o_. type_name; if v_type_name = aiv_expected_name then pl('base_: test_type_name passed. '); else pl('base_: test_type_name ***FAILED***: v_type_name='|| v_type_name||' not '||aiv_expected_name); end if; exception when OTHERS then pl('base_: test_type_name ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_get_id is o_ base_; n_id number; begin pl(chr(10)||'base_: test_get_id'); o_ : = new base_; n_id : = o_. get_id; if n_id is not NULL then pl('base_: test_get_id passed. '); else pl('base_: test_get_id ***FAILED***. '); end if; exception when OTHERS then pl('base_: test_get_id ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_help is begin pl(chr(10)||'base_: test_help'); base_. help; pl('base_: test_help passed. '); exception when OTHERS then pl('base_: test_help ***FAILED***: '||SQLERRM); raise; end; . . .

PROCEDURE test_to_varchar 2( aiv_expected_value in varchar 2) is o_ base_; v_map_value varchar 2(100); begin pl(chr(10)||'base_: test_map_value'); o_ : = new base_(1234567890); v_map_value : = '"'||o_. to_varchar 2||'"'; if v_map_value = aiv_expected_value then pl('base_: test_map_value passed. '); else pl('base_: test_map_value ***FAILED***: v_map_value='||v_map_value||' not '||aiv_expe end if; exception when OTHERS then pl('base_: test_map_value ***FAILED***: '||SQLERRM); raise; end test_base_; / show errors package body test_base_;

SQL> exec test_base_. test(); base_: test_constructor base_: CONSTRUCTOR FUNCTION base_() base_: test_constructor passed. base_: test_constructor_with_id base_: CONSTRUCTOR FUNCTION base_(id) base_: test_constructor_with_id passed. base_: base_: test_sequence_name CONSTRUCTOR FUNCTION base_() MEMBER FUNCTION sequence_name() MEMBER FUNCTION type_name() test_sequence_name passed. base_: base_: . . . test_table_name CONSTRUCTOR FUNCTION base_() MEMBER FUNCTION table_name() MEMBER FUNCTION type_name() test_table_name passed.

base_: test_type_name CONSTRUCTOR FUNCTION base_() MEMBER FUNCTION type_name() test_type_name passed. base_: test_help base_: MEMBER PROCEDURE help() No help at this time. base_: test_help passed. base_: test_map_value CONSTRUCTOR FUNCTION base_(id) MAP MEMBER FUNCTION to_varchar 2() test_map_value passed.

Document, Advertize, and Educate § Use Wiki-based documentation – – – § § § it's accessible it's version controlled and then everyone's responsible for documentation Don’t punish people for errors, instead reward them for test unit coverage Share your best practices My book, Beginning PL/SQL: From Novice to Professional, has extensive coverage Professional of this topic. Besides, I really do need a vacation.

Closing Thoughts § § § Objects (TYPEs) better model the real world, and hence provide a better solution Well though out use inheritance can significantly reduce the amount of code to write, the time it takes to write it, and the time it takes to maintain it Using objects provides better consistency, in turn, better consistency provide higher quality In an object-relational setting Packages are better suited as role players that orchestrate the use of objects Or perhaps, those roles should be objects too? You can (almost) never test too much.

References § § § § Beginning PL/SQL: From Novice to Professional by Donald J. Bales (APress) Java Programming with Oracle JDBC by Donald J. Bales (O'Reilly) Oracle® Database PL/SQL Language Reference 11 g Release 2 (11. 2) (Oracle) Oracle® Database Object-Relational Developer's Guide 11 g Release 2 (11. 2) (Oracle) Oracle PL/SQL Programming by By Steven Feuerstein, Bill Pribyl (O'Reilly) Object-Oriented Technology: A Manager's Guide by David A. Taylor (Addison-Wesley) § § http: //www. pl-sql. org http: //technet. oracle. com http: //www. apress. com/book/catalog? category=148 http: //oreilly. com/pub/topic/oracle