PL/SQL Best Practices Seminar
Here are some notes from Steven Feuerstein’s Best Practice PL/SQL seminar held at St. John’s University in New York on October 16, 2006. This is not a complete blow-by-blow of the seminar, just a few points that especially piqued my interest.
Tools Discussed
- Use of the timer object for testing PL/SQL code
- PL/SQL Developer
- SQL Developer
- Code Tester
- QNXO
- Of course, TOAD and SQL Navigator.
Code-writing methodology
- First, write a functioning code stub.
- Then define your tests using the new (free but beta) Code Tester tool. DO NOT use dbms_output to test your code!
- After you have defined all your test cases, start filling up the stub with code. As each piece of functionality is added, run your tests and proceed only if the desired output for a given test is received.
- This is a revolutionary way to write code, IMO, provided the Code Tester utility can really deliver as promised. I’ll be checking it out.
Encapsulate SQL behind a standard PL/SQL API
- Since Java is fully able to work with Oracle cursors, there is absolutely no reason to permit SQL in Java code. Hide SQL within packages. That way data model changes do not break application code.
- There have been tools for generating standard database interfaces available in Java for years (newer frameworks like Ruby on Rails provide object-oriented interfaces out-of-box, with no code generation required). Now there is at least one tool available for PL/SQL: QNXO
- Downside: changes to the encapsulating packages invalidate all other code that relies on them, necessitating recompilation.
Use bulk binding (BULK COLLECT, FORALL) instead of conventional binding (cursor FOR loops)
- Downside: can exhaust PGA
- 10g optimizing compiler converts cursor FOR loops into bulk binds (unless it is disabled via the PLSQL_OPTIMIZE_MODE parameter. It is enabled by default. To enable: plsql_optimize_mode=2)
Exception Handling and Logging
- Write your exception handling FIRST, before writing code.
- The built-in logging functionality is lame. Devise your own logging tables to capture error information.
- Log information from newer features like dbms_utility.format_error_stack
- Hide exception-handling within your API. That way, as your error logging methodology becomes more robust, you don’t have to change alot of your application code.
- Don’t hard-code your custom exception numbers (-20000 to -20999) in the code; use a package declaration to alias these with meaningful constant names. Or, dispense with the numbers altogether and just use string literals! This actually works.
- Downside to using constants: everytime you add a new error constant, ALL code that relies on the package must be recompiled.
General advice
- Always use packages instead of standalone procedures/functions (variables, constants and cursors can persist throughout a session with packages).
- Become intimate with the functions provided in the standard package.
- Use the NOCOPY hint with IN OUT procedure arguments to emulate “pass by reference” and thereby speed up getting large datasets out of a procedure.
- Avoid SELECTing INTO individual variables. Use %ROWTYPE variables instead. (if the cursor’s SELECT list changes, there is potentially less code to change).
- If you must use individual variables (e.g. you are creating a “fullname” variable by concatenating FNAME || ‘ ‘ || LNAME), declare this as a SUBTYPE and hide the definition behind a package interface. Makes code changes much easier if your definition of “fullname” changes or if the field definitions for FNAME and LNAME ever change.
- Always identify END statements with the name of the block. If it’s an anonymous block, you can name it thus:
<< foo >>
BEGIN
…
END foo;
- dbms_sql.describe_cursor tells you what columns are available in a cursor.
- Declare functions as DETERMINISTIC wherever possible, so that they can be used in function-based indexes.
