1	PL/SQL for Dummies
2	Overview Being a good PL/SQL programmer means more than knowing about: the latest features obscure syntax for commands in the packages. Well-written code provides good performance and is bug-free. Code should be structured so that when modifications are necessary, it is easy to see where they are needed.
3	Massive Code Failures Examples where failure to follow best practices caused companies to lose hundreds of millions of dollars: 1. Code ran so slowly that it made the system unusable. It would have taken 26.5 years for a month-end routine to run. 2. Code was so difficult to modify that changing it took 3.5 months just to add a single attribute to one table. 3. The system included so many complex rules that, even after years of development, it never worked. 4. Data validation rules – after 18 months, 6 organizations tried, only one succeeded. All of these failures were due to the way in which the software and its underlying code were designed and constructed.
4	Setting up a successful coding environment Coding is 90% thinking and 10% actual writing code. You need to learn how to think through a program before you write it. 3 basic steps...
5	1. Understand the big picture You need to understand the entire system architecture in order to create the appropriate code including: Database Business rules – how and where these will be enforced Programming languages Programming algorithms Keep the following principles in mind: Understand the business function for your code. You should be able to accurately describe what your code does in terms that users can understand. Frequently refer to a copy of the system data model showing the relevant portion of the database. You need to understand where the code you are writing fits into the bigger system.
6	2. Communicate effectively As a developer, you probably spend no more than 30 percent of your time sitting alone, in front of a terminal, writing code. In most cases you will be: Working with a second developer (or pair programming) Gathering system requirements Common mistakes: Neglecting to call potential problems to the attention of the system architects Creating an architecture that is inadequate to support the system requirements.
7	3. Create a code specification Before you start writing code, you need written specifications. A good code specification should address the following questions: Why is this code or modification being written (in business terms)? What procedures will be created? How will these procedures be named? What modifications to the database are required? What are the detailed design constraints, if any? Goal is to create functioning code - not create a large pile of documentation that few people will read.
8	Integrating Best Practices Into Your Code
9	Stub out your code Don’t just start writing code right from the beginning. Figure out how the code should be structured. Create necessary procedure and function headers with no code in them. Little “stubs” of code will help you see the overall routine. Use a “code outline” to pass parts of the code to someone else. Code will naturally be well structured and easier to debug if something goes wrong.
10	Check the architecture as you go along Make sure that the underlying system architecture is sound Don’t get so lost in endless routines that you lose sight of the big picture. Every 2 weeks “climb to the top of the tallest tree around” to see where you are Make sure that you are still going in the right direction. Look out for any obstacles between you and your goal. Climb back down the tree, have a group meeting, and have the project manager clearly point in the direction where everyone should be heading. The number of huge project failures that could have been prevented by using this strategy is surprisingly large.
11	Prove sections of code with test cases Make sure you understand how new features work Write a separate, small, example program to demonstrate functionality. When using complex expressions, isolate the piece of code in a simple SQL expression to prove that the code is correct. This can save you hours of debugging time later. Set up small test cases using DBMS_OUTPUT statements to print out interim results. Do this frequently for each section of code written.
12	Use Code Libraries You aren’t the only one who will need to use the code. Code is often rewritten dozens of times. Code should be well documented and placed for easy reuse. Code should be placed in a code library and referenced each time it is used Makes code less prone to errors Every time a piece of logic is rewritten, code will be written slightly differently. This can cause code errors that are very difficult to find. Large systems usually contain hundreds of reusable components Divide them into logical packages to avoid losing track of them.
13	Keep code maintainable Designing and coding a system that can be used and easily maintained over time requires some thought and skill. Make sure that someone else down the road will be able to read and understand your code and find potential problem areas.
14	Think about performance Have some sense about how quickly the code needs to execute and return the desired information. Understanding what constitutes acceptable performance in a given situation is very important. Know how often a given programming routine will be run. Some routines may be used only once Ex. Data migration routines– performance not critical Some routines may be accessed millions of times per day Ex. Low-level translations changing system time into local time around the world - performance critical Don’t discard routines without thinking. You may often need to run the same or similar routine to one you wrote a few months ago.
15	Compile as you go Many inexperienced programmers create an entire first draft of a program (possibly hundreds of lines of code) without ever compiling it. Often requires hours of debugging. Errors are inevitable (misspellings, typos, etc.) Compile your code every few minutes Create the function name with one line of code (which might even be NULL) and save it before doing anything else. Every few lines, compile the code again to check for errors. Never write more than about ten lines of code without compiling it.
16	Debug efficiently Always start with a piece of code that works. Comment out portions of the code until it runs successfully. Without commenting, errors in packages or complex routines can be very difficult to find. The SQL compiler is not perfect. May indicate that an error exists in a place that is far from the actual mistake. Most common types of errors: Forgetting a semicolon Missing a comma in a SELECT statement Missing an END statement Do not let your routines get too large in the first place. Limit routines to no more than a few hundred lines Find out what the program is doing at various points. Many IDEs include some sophisticated debugging functionality Set watches and breakpoints in your code.
17	Unit Test Your Code The essence of testing is the idea of an assertion. You assert that the software will do X when Y. Use different types of tests. Some involve a manual steps that need to be performed by a human tester. Whenever possible, tests should be written as code scripts that can be easily run every time someone modifies the code. Delivering bad software is much more expensive than testing. Even thorough testing does not guarantee perfect code. It is not possible to test everything. The best approach is to test all the main functions and deal with the bugs when they are found. If your software has to be perfect, you have a very difficult job. You will spend many times the software development cost to test it. When building software where bugs can result in serious consequences, count on spending lots of time testing.
18	Top 10 PL/SQL “Do’s”
19	PL/SQL “Do’s” 1 & 2 Use SELECT INTO correctly. Your query might return more than one row, or no rows at all. Must include exception handlers if there is any possibility of them occurring. Code might execute correctly for many months before some unusual condition shows up that causes it to fail. BULK COLLECT clause Check for the number of fetched records Exception NO_DATA_FOUND is never raised, even when no rows were fetched. Do not assume column order in DML statements. Explicitly list the columns you are referencing. Columns that are added to a table later will break your code. Rebuilding your database might result in the default column order being changed. Use variables of type RECORD Do not have to list anything in the INSERT statement because you are inserting a variable into the table that is defined as a row from the same table. No chance for inconsistency. Easier way of passing data to different routines.
20	PL/SQL “Do’s” 3 & 4 Use the NOCOPY command When passing an IN/OUT parameter to avoid passing by value. NOCOPY is a hint, not a directive so Oracle may ignore it. If passing a variable by value to a subroutine and that routine fails, the original value of the variable does not change. Be careful of function calls that impact performance In loops Perform the calculations before the loop Cache the calculated data in the local variables A function in an SQL statement will execute at least once for every row returned. Look closely at the execution plan
21	PL/SQL “Do’s” 5 & 6 Beware of implicit commits Any DDL command causes an implicit commit. Think about any uncommitted changes whenever performing DDL operations. TRUCNATE TABLE command is also DDL and forces a commit. Only use one RETURN statement per function (usually) Use a single OUT point in the routine. Functions should have only one RETURN statement. The RETURN statement should be the last line before the exception block.
22	PL/SQL “Do’s” 7 & 8 Use WHEN OTHERS to save debugging time Hides errors and makes the code harder to debug. Can save debugging time because it captures unpredictable events. Explicitly placing exception handlers to cover all situations is impossible. WHEN OTHERS allows you to intercept unspecified exceptions. Log an error message. Re-raise an exception in the exception block. Understand when to pass parameters or use global variables In a stateless, Web-based environment, cursors, functions, and procedures are sometimes unable to reference values outside of themselves. Instead, they should pass parameters.
23	PL/SQL “Do’s” 9 & 10 Use implicit cursors when appropriate Marginally faster than explicit cursors for single record fetches. Make code easier to read. Cursor is defined right where it is executed. Implicit cursors raise the exceptions NO_DATA_FOUND (only the first fetch is null) and TOO_MANY_ROWS. Dates are numbers Internally in Oracle, dates are really numbers Can perform numeric operations against dates. Oracle 9i and 10g have new datatypes to store dates: TIMESTAMP and INTERVAL
24	Top 10 PL/SQL “Don’ts”
25	PL/SQL “Don’ts” 1 & 2 Catch exceptions with WHEN OTHERS THEN NULL Never catch an unidentified exception without logging information about it. Forget to handle NULL values All logical operations (including NOT) that involve NULL values always return FALSE. All operations (built-in functions, arithmetic) with NULL return NULL, with the following exceptions: Concatenations of strings ignore NULL. DECODE can compare values with NULL. In grouping functions (SUM, AVG, COUNT), watch out for NULL values. Only not-NULL is processed
26	PL/SQL “Don’ts” 3 & 4 Create unintended Boolean expressions Enclose condition groups in parentheses Forget to close an explicit cursor Forgotten cursors that are left open can bring a system to a halt. In Dynamic SQL, you have to use explicit cursors. When you start typing a routine, immediately include both the OPEN and CLOSE cursor statements. When using recursive calls to the same routine, be very careful about using explicit cursors. Oracle is fairly smart about closing cursors but relying on this capability is dangerous and can ultimately result in having too many cursors open at once
27	PL/SQL “Don’ts” 5 & 6 Start endless loops Each time you create a loop, think about how the code will exit from the loop. Use CURSOR FOR loops or FOR loops whenever possible. If you do not need to interrupt processing, always use a FOR loop. This is much safer approach. Reinvent the wheel Don’t try to create code structures that have already been developed for you by Oracle. Review an Oracle manual with the list of built-in functions.
28	PL/SQL “Don’ts” 7 & 8 Convert datatypes implicitly You should not trust implicit conversions of datatypes, especially dates. Problems with implicit conversion occurs when working with numeric values that are not numeric. Not everything that looks like a numeric value is a numeric value. Cut and paste code indiscriminately Advantages (relevant for developers): You are not touching the existing code, The code has already been checked and does not contain syntax errors. No need to retest the code that is dependent on the original code. Drawbacks (relevant for people maintaining the system): Modification has to be replicated everywhere. Code becomes less readable and more spaghetti-like. Nothing technically wrong with cutting and pasting code but a few development hours saved can mean days of downtime for an entire organization later on.
29	PL/SQL “Don’ts” 9 & 10 Ignore code readability The next person who looks at your code should not have to guess about your naming conventions or program structure. Assume code does not need comments There is no such thing as self-documenting code.. All headers should include: Ownership, Functionality Comments explaining the implemented solution Possible issues Change log Use inline comments
30	Getting Help Buy books Go to conferences Join your local Oracle user group Use online resources
31	The J2EE SIG Co-Sponsored by:
32	About the J2EE SIG Mission: To identify and promote best practices in J2EE systems design, development and deployment. Look for J2EE SIG presentations and events at national and regional conferences Website: www.odtug.com/2005_J2EE.htm Join by signing up for the Java-L mailing list: http://www.odtug.com/subscrib.htm
33	J2EE SIG Member Benefits Learn about latest Java technology and hot topics via SIG whitepapers and conference sessions. Take advantage of opportunities to co-author Java papers and be published. Network with other Java developers. Get help with specific technical problems from other SIG members and from Oracle. Provide feedback to Oracle on current product enhancements and future product strategies.
34	Share your Knowledge: Call for Articles/Presentations Submit articles, questions, … to IOUG – The SELECT Journal ODTUG – Technical Journal select@ioug.org pubs@odtug.com
35	Dulcian’s BRIM^® Environment Full business rules-based development environment For Demo Write “BRIM” on business card Includes: Working Use Case system “Application” and “Validation Rules” Engines
36	Contact Information Dr. Paul Dorsey – paul_dorsey@dulcian.com Dulcian website - www.dulcian.com