Oracle Database 10g PL/SQL Programming Book Review

Develop Database Driven PL/SQL Applications

Oracle Press are well known for publishing easy to read and understand Oracle Books and PL/SQL Programming is no exception. This book is for everyone who wants to learn and understand the basics and advanced topics of PL/SQL.

As with all PL/SQL books the basics of PL/SQL are introduced to begin with including all areas of the PL/SQL language including, data types, conditional statements, loops, built-in Oracle functions, cursors, triggers, procedures, functions and more.

There are individual chapters on records and collections. I am not a big user of records or collections, but collections are thoroughly covered in this book, they can be viewed on as being the same as ARRAYS. There are also separate chapters on error handling, packages and database triggers.

One tip I did learn whilst reading this book, which I have not seen covered in any other Oracle 10g PL/SQL book so far, is that with Oracle 10g there is now a new way to handle the single-quote/double-quote problem when working with apostrophes. The new quoting system is q’[…]’

All the following examples are valid:

DBMS_OUTPUT.PUT_LINE(q’!It’s a test!’);
DBMS_OUTPUT.PUT_LINE(q’[It’s a test]’);
DBMS_OUTPUT.PUT_LINE(q’’);
DBMS_OUTPUT.PUT_LINE(q’%It’s a test%’);

All lines output: It’s a test

It is the same as the old way of adding extra apostrophes to get the correct output. E.g. the old way would have been:

DBMS_OUTPUT.PUT_LINE(‘It’’s a test’);

PL/SQL Programming is divided into two sections, PL/SQL basics and PL/SQL advanced features. The advanced section chapters concentrate on dynamic sql, using external routines such as calling Oracle using Java or C, as well as scheduling tasks using DBMS_JOB, and intersession communication by using DBMS_AQADM or DBMS_AQ which involve setting up advanced queuing for each user or using DBMS_PIPE or DBMS_ALERT.

A number of chapters of the advanced section are leant to Oracle Objects, including an introduction to Oracle objects, objects in the database such as accessing object views, and how to use objects within SQL and PL/SQL. Large Objects (LOBs) are also discussed such as converting LONGs to LOBs, using DBMS_LOB, and using external LOBs with BFILE.

There are two appendices which cover PL/SQL reserved words and a guide to PL/SQL supplied packages.

PL/SQL programming covers most areas of the PL/SQL language and includes all the basics you need plus some great in-depth chapters on some PL/SQL advanced features. It also includes a lot of the new Oracle PL/SQL features including a small section on regular expressions, which could have been a bit bigger.

If you want to learn the basics of PL/SQL then this book is perfect for you. If you are already a seasoned PL/SQL user then this book will be of interest to you if you want to learn about Oracle Objects, task scheduling, intersession communication, collections and records.