A long, long time ago in a blog long ago lost to the shrouds and clouds of memory, I published a Ten Commandments of PL/SQL Development.
A Twitter follower recently asked me for them, and lo and behold actually found them lurking in a Word doc on my drive. So I have decided to share them with you, largely unchanged. In some places I have struck through entirely irrelevant or outdated text, and also offered updates for 2019.
2019 update: In stateless execution environments like web pages and mobile apps, you should generally not rely on package-level data/state at all, and minimize the code that is executed in package initialization sections.
2019 update: Bookmark the link to the PL/SQL Packages and Types Reference, and scan through the "Changes in this release" section each time a new version is announced..
2019 update: most important of all, take advantage of PL/Scope, a powerful code analysis utility built right into the PL/SQL compilation process.
And we should share freely that knowledge will others;the PL/SQL Pipeline at www.revealnet.com/plsql-pipeline is an excellent mechanism for doing this.
Check outthe RevealNet PL/Vision code library and the Oracle Built-in Packages (O'Reilly and Associates) companion disk for lots of examples. I also offer an example of doing this for the LOG ERRORS feature on Oracle LiveSQL.
2019 update: we also encourage you to add your idea for improving our software at the Database Ideas forum. And, of course, if you find a bug, please report it to Oracle Support.
2019 update: wow, does this feel dated. That's all you've got, Steven? Tune your SGA? :-) The key thing as I say at the end is "Get your DBA involved in the code development and deployment process!". But you should also check out the PL/SQL User Guide's Optimization and Tuning section.
GUI (yes, almost all of them Windows-based) front-end for PL/SQL development, debugging and testing. You waste dozens, if not hundreds, of hours of development time each year by relying on "lowest common denominator" tools.
2019 update: it probably goes without saying, but I will say it anyway: Download and try SQL Developer and its sister command-line tool, SQLcl. They are used by millions of developers, including me, run on Windows, macOS, Linux....and THEY ARE FREE.
A Twitter follower recently asked me for them, and lo and behold actually found them lurking in a Word doc on my drive. So I have decided to share them with you, largely unchanged. In some places I have struck through entirely irrelevant or outdated text, and also offered updates for 2019.
1. Thou shalt encapsulate your SQL statements behind procedure and function calls.
Sure, it's really, really easy to write SQL in your PL/SQL programs -- in fact, it's way too easy. SQL is the "Achilles' heel" of your application code. You code and you code and you code, and before you know it, you have dozens of variations of the same SQL statement making their negative performance impact known in your application. You can't analyze the impact of data structure changes and you find enhancements to be very expensive. Solution? Put all your SQL inside packaged procedures, functions, or cursors. Maximize re-use of pre-parsed statements. Build comprehensive package APIs to underlying tables or business objects.2. Thou shalt not hard-code breakage points into your programs.
"I know this will never change." Famous last words! We all know you're supposed to avoid hard-coding, but what does that really mean in PL/SQL? Examples of hard-coding in PL/SQL include: "magic values" that will "never" change, declarations using explicit datatypes like VARCHAR2 and NUMBER (use %TYPE and %ROWTYPE instead), every SQL statement (they "freeze" your structures and relationships), cursors with local variables reference directly inside the query, FETCHing into a list of variables, and even more. Scary, isn't it?.3. Thou shalt use packages as the building block of all your applications.
Ever tried to understand, much less fix, a program that stretched on for a thousand seemingly endless lines of poorly-documented, totally un-modularized code? There's gotta be a better way -- and in PL/SQL there is: use top-down design techniques, backed up by nested modules and reusable, packaged programs to create readable, manageable applications. Always start with a package, even for single programs, and you will develop and evolve your applications more smoothly.4. Thou shalt hide package-level data in the package body.
If you define data structures in the package specification, you lose control over that data. Any program with EXECUTE authority on that package can modify the data at will. Sound familiar? That's right, it acts an awful lot like the awful :GLOBAL variables of Oracle Forms. With specification-defined data, you cannot guarantee its integrity, you cannot track access to that data, you cannot easily change the structure you use to implement that data. Instead, build "get and set" programs around your data: a procedure to set the value and a function to retrieve the value. Thank me later.2019 update: In stateless execution environments like web pages and mobile apps, you should generally not rely on package-level data/state at all, and minimize the code that is executed in package initialization sections.
5. Thou shalt learn about and use the built-in packages (DBMS_ and UTL_) from Oracle.
There's a lot more to the PL/SQL language than the language constructs and basic functions (defined, for the most part, in the default STANDARD package!). Ever felt the need to execute dynamic SQL method 4, run an operating system program, read/write files, run scheduled jobs from within the database, manipulate RAWs and LOBs or implement row-level security? You'd have a heck of a time doing any of that without using a built-in package. They are invaluable and should make regular appearances in your code.2019 update: Bookmark the link to the PL/SQL Packages and Types Reference, and scan through the "Changes in this release" section each time a new version is announced..
6. Thou shalt leverage the Oracle Data Dictionary to analyze and generate code, and implement standards.
PL/SQL is a very different creature from many other languages: all source code must be compiled and stored in the Oracle Database before it can be executed. As a consequence, information about that code is available in a large number of data dictionary views. Access these views to generate standards-based code, verify compliance with standards, and manage your applications.2019 update: most important of all, take advantage of PL/Scope, a powerful code analysis utility built right into the PL/SQL compilation process.
7. Thou shalt remove thy "Oracle Expert" chip from thy shoulder.
No one knows everything about anything. Too many Oracle developers (especially consultants?) carry enormous chips on their shoulders, with two alarming consequences: (a) they are most interested in showing everyone how smart they are, and (b) they don't want to share what they know because it might put a damper on their hourly rates. We will all benefit by reading, studying, and critiquing the code of others.And we should share freely that knowledge will others;
8. Thou shalt complain about Oracle technology, but then do something about it.
We all love to complain, but complaints don't solve problems. If Oracle has implemented functionality which you love to whine about, then first you should see if you can fix it in your application. In some cases, for example, you can build an encapsulation around an incomplete Oracle implementation and then improve upon it.Check out
2019 update: we also encourage you to add your idea for improving our software at the Database Ideas forum. And, of course, if you find a bug, please report it to Oracle Support.
9. Thou shalt tune your System Global Area to execute PL/SQL code efficiently.
You are convinced that PL/SQL is the way to go. You write hundreds of programs and packages. You install the application on your database -- and users complain bitterly of slow performance. The problem? Sure, it might be your code, but don't forget to tune the SGA. Your shared pool area must be large enough to contain the commonly-executed elements of your application. You should also pin frequently-needed programs with the DBMS_SHARED_POOL package. Get your DBA involved in the code development and deployment process!2019 update: wow, does this feel dated. That's all you've got, Steven? Tune your SGA? :-) The key thing as I say at the end is "Get your DBA involved in the code development and deployment process!". But you should also check out the PL/SQL User Guide's Optimization and Tuning section.
10. Thou shalt not develop your code in Notepad and compile/test it in SQL*Plus.
Same goes for vi (! what can I say? I wrong this a long time ago :-)), though vi is admittedly not as brain-deadening a development tool as Notepad. Why should you work in an environment more primitive than that available to any Fortran or C programmer? Join the twentieth century before it's too late and get yourself a2019 update: it probably goes without saying, but I will say it anyway: Download and try SQL Developer and its sister command-line tool, SQLcl. They are used by millions of developers, including me, run on Windows, macOS, Linux....and THEY ARE FREE.
Some other Ten Commandments of Programming
Lukas Eder offers his own Ten Commandments here, and as anyone familiar with Lukas would expect, they are entertaining and spot-on.
Jade Meskill offers The 10 Commandments of Egoless Programming.
Comments
Post a Comment