Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


How to Write Awesome PL/SQL

No description

Bill Coulam

on 14 January 2016

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of How to Write Awesome PL/SQL

Establish a programming style and standard
Ensure the standard is followed
Use templates
Use automation
Use a formatter
It !

Placeholders for explanation in templates
Each stored object should have a comment block explaining who, when and why.
Document assumptions and test them in the code
Document tricky parameters or usage, caveats, design notes, alternatives rejected and why
Needs a Home
Everything goes in a PL/SQL package
If package becomes a dumping ground, the name was too generic.
Break up package into functional groups
Group literals in the package spec to which they are best related.
Trigger and job code should also go in a package
Instrument It!
Adopt an existing instrumentation library:
Static logging
Dynamic debugging (invisible and little overhead to production; but can be activated by changing a column value)
Change auditing
Proactive monitoring and email notifications of anomalous events
Recording of metrics
"Tag" sessions and long operations
Debug strings inherently comment the code as well!
How to Write Awesome

The Presenter
Bill Coulam
Programming C, Java and PL/SQL since 1995
Oracle data and database design and tuning since 1997
Andersen Consulting (PacBell, US West, AT&T) - San Francisco, Denver, Herndon VA
New Global Telecom - Denver, CO
The Structure Group - Houston, TX
Church of Jesus Christ of Latter Day Saints - SLC, UT
Speaking at RMOUG, IOUG and UTOUG since 2001
Passionate about excellent modeling and programming practices
Best Uses of PL/SQL
Processing close to the data = fast!
Backend, timed or event-driven transformations, calculations or processing of large data
Consumption or production of data-filled files
Ensuring data access layer is free of SQL injection
When a trigger is the right choice
Ensuring business logic is kept in one place
Data access: DBA review, tuned, instrumented, monitored, easy to modify and redeploy
Source Code

Install and configure a reliable source code control system (Subversion, Git, RCS, CVS, PVCS, VSS, etc.)
Ensure it is used consistently (100% of the time)
Development and maintenance activities always begin with the source file, not the database object
Use default read-only mode of DB Tools
Should be hard for developer to edit a DB object in-situ.
Routine does one thing, and does it well.
Routine no longer than a page, if possible.
DRY: Don't Repeat Yourself
Package related constants
Table-drive mutable parameters
Privatize vertical functions
Publicize horizontal functions
Habits of
PL/SQL Artisans
Get involved early
Model right, then make it friendly
Know and use your tools
Only handle expected exceptions
Peer review
Do it in Bulk or a Single SQL
Caller in charge of transaction
There’s this thing called “testing”...
Get involved in early stages of project work
Ask the questions of a data architect: length, uniqueness, longevity, audit, security, relationships, cardinality, etc.
Prepare conceptual and logical models for review and project discussions
Be aware of overall application architecture, hardware and human requirements
The data model is the foundation of the system.
Ensure the model is done properly the first time
Ignore those pleading for more friendly columns, quicker data access, elimination of joins.
AFTER the model is correct, normalized, and reviewed, THEN make it friendly with views, materialized views, updateable views, virtual columns, etc.
Your Tools
Take 10 minutes a day to explore the user guide of a favorite tool
Learn and configure keyboard shortcuts
DBAs can really benefit from model visualization, nameless macros, data generation, debuggers, data ETL, code/table/schema/model/database comparison tools, and DDL generators.
Ban the use of WHEN OTHERS
Except when hiding the error is intentional
Only write exception handling for
Use a standard way of logging and re-raising
Allow PL/SQL’s default exception raising and transaction rollback to handle
Pair programming is fantastic
Formal or informal second set of eyes for:
simple DML scripts
source code
design approach and assumptions
DB build manifest
whenever you’re stuck for more than 20-30 minutes
Do it in Bulk
Quickest way to do something is not do it at all. If performance is bad, ask...
“Do I really need this?”
"Can it be done another way?"
Next best is to do it in a single SQL statement
Followed by doing it with Bulk PL/SQL features (collections, bulk bind, FORALL, and DML with collections of record type)
Transaction Control
Let the client control the transaction
If you are writing a backend job that begins life in the database, then you will write the driver and control COMMIT vs. ROLLBACK.
Otherwise, leave it to database client software to decide.
Document requirements and assumptions in the interface
Write tests first, to the interface
Then write body, re-testing all cases as you add/modify the code to reach requirements.
Left with nice suite of re-usable tests
Test with typical load and data quality
Do it Big
Bulk is da Bomb
Full transcript