Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


ISBN: xxx
ISBN 13: xxx
Library of Congress Number:
xxx Pages - perfect bind 9x7
Publication Date -
Shelving Databases/Oracle
Oracle In-Focus Series  # xx

Advanced PL/SQL Programming
The Definitive Reference

Boobal Ganesan

Retail Price $49.95 /  £39.95

Key Features   About the Author Reader Comments
Table of Contents   Index Errata 

Oracle PL/SQL is undoubtedly one of the most versatile and high performance yielding database languages of all time with many number of ways and methods for obtaining a single result. Oracle professionals most of time do not get their work done using the right tool which leads into two main side effects.


This comprehensive guide to Oracle PL/SQL will be a handy reference to all Oracle professionals who must write PL/SQL coding in their day to day life. This book will direct the reader through the mostly unused/ un-minded yet powerful techniques which can be used in a regular manner in their application development.


Topics will include the complete information of the widely used topics and also consists of topics and its soundness which we thought of less likely usable and forgot over time. This book takes a complex technical task and explains it in a very easy understandable method.

The advanced PL/SQL concepts like collection reduces the back and forth traverse between the SQL engine and the PL/SQL engine thus reducing the number of processes and avoiding context switching. This is an interesting topic that can greatly enhance the knowledge of the PL/SQL developer in-turn increasing the performance of an Oracle database.

Key Features

* See different types of PL/SQL data types and its benefits.

* The dynamic query processing techniques and its features.

* Learn Regular expressions and XML functions.

*  See how collections and its various types work.

* Use PL/SQL cursors and ref cursors.

* Understand cryptography in Oracle

Learn about heterogeneous services in Oracle.

* get an overview on Oracle system packages

* Use PL/SQL Advanced Interface Methods (C, Java)

* Enable the security of the database with VPD and fine grained policies.

* Manage secure LOBs and advanced table compression techniques in Oracle.

* Use PL/SQL profiling

* Safeguard your code from SQL Injection Attacks.


About the Author:


Boobal Ganesan has 9 years of learning and implementation experience out of which he has a full-time contribution as an Oracle PL/SQL developer for 5 years and has completed his OCA certification on the development track.

Boobal is one of the leading contributors to the Oracle community and other blogging sites.

Table of Contents:

CHAPTER 1 – Introduction to PLSQL programming

·         Different types of tables in Oracle

·         Anonymous block – A basic PLSQL program (Explanation with the help of returning into clause)

·         Procedures in Oracle (Explanation with nocopy parameter, return statement, alter, drop, system tables through which procedures can be analyzed)

·         Functions in Oracle (Explanation with Deterministic, Pipelined, Parallel_enable, Methods to invoke a function, Restrictions in invoking the functions with DML statements in them, alter, drop, system tables through which functions can be analyzed)

·         Packages in Oracle (Explanation with overloading concepts, Authid, alter , drop, system tables through which packages can be analyzed)


CHAPTER 2 – Overview on Labeling and the GOTO statement

·         Labeling a block

·         Using a GOTO statement to transfer control between the blocks

·         Restrictions in using the GOTO statement


CHAPTER 3 – Exceptions in Oracle

·         Purpose of using Exceptions and its types in Oracle ( Predefined, Undefined and User defined along with its allowed range -20000 to -20999 with suitable examples)

·         Different parts of Exceptions (Declaration, Raising, Handling with suitable examples)

·         Re-raising an exception and its purpose

·         Operators usage in Exceptions (“Exception when no_data_found OR too_many_rows then” with examples)


·         Getting the error message of an error code (Using SQLERRM(-01404) with suitable example)


CHAPTER 4 – Overview of Sequences in Oracle

·         Use of Sequencing in Oracle

·         Sequence creation (Multiple examples for CYCLE, NOCYLE, CACHE, NOCACHE, ORDER, NOORDER consideration)

·         Alter and Drop Sequence

·         System tables through which sequences can be analyzed



·         Briefing the session context parameters

·         User defined context parameters with examples


CHAPTER 6 – Overview on PLSQL Data types

·         Overview on PLSQL scalar data types and subtypes

·         Overview on PLSQL number data types and subtypes

·         Overview on PLSQL character data types and subtypes

·         Overview on PLSQL Boolean data types

·         Overview on PLSQL date time and interval types

·         Overview on PLSQL LOB data types (Examples depicting the advantages and usages of CLOB, BLOB, BFILE, NCLOB data types)

·         Overview on PLSQL user defined subtypes


CHAPTER 7 - Overview on dynamic query processing in Oracle

·         Overview on the execute immediate statement along with its usage tips

·         DDL query processing in PLSQL using execute immediate

·         Duplication rejection in a database using dynamic query processing (This can be achieved using the help of system tables and execute immediate)

·         Searching a string in a database (This script can be used for searching for a particular string a database whereas its table and column names are unknown)

·         Bind variables and execute immediate

·         SQL Injection and its remedy


CHAPTER 8 – Overview on PLSQL operators

·         Overview on the operator creation and its characteristics

·         Single binding operator with a suitable example

·         Multiple binding operator with a suitable example

·         Binding addition and comments addition to an existing operator


CHAPTER 9 – Overview on Regular expressions in Oracle

·         General information on the regular expressions (Anchors, Matches, Posix, Quantifiers)

·         Overview on the regular expressions with examples (regexp_count, regexp_like, regexp_instr, regexp_substr, regexp_replace with examples like removing unwanted spaces in a long text and replacing them with a single text, separating a portion of text from a long text)


CHAPTER 10 – Overview on Oracle XML programing

·         Introduction to XML programing

·         XMLTYPE data type and its functioning

·         Adding a child tag in an existing XML

·         Deleting a child tag in an existing XML

·         Extracting the value of a particular tag

·         Updating the value of a particular tag

·         Adding a comment to an XML

·         Converting a column’s result into an XML

·         Converting a table’s result set into an XML


CHAPTER 11 – Collections in Oracle

·         Introduction to collections and its types (Nested tables, Varrays and Associative arrays)

·         Objects and Records (Examples, Advantages and disadvantages over each other)

·         PLSQL Tables with suitable examples

·         Understanding nested tables (Nested table in SQL and PLSQL, %type and %rowtype, Not null constraint, sparse method, bulk collect and forall with “in indices of” and “in values of”, Returning bulk collect into, With objects and records, table function)

·         Understanding varrays (Varrays in SQL and PLSQL, %type and %rowtype, Not null constraint, dense method, bulk collect and forall with “in indices of” and “in values of”, Returning bulk collect into, With objects and records)

·         Understanding Associative arrays (Associative arrays in PLSQL, Returning bulk collect into, With objects and records)

·         Save exceptions in collection types (sql%bulk_exceptions, sql%bulk_rowcount)

·         Collection methods in oracle(first, last, delete, trim, delete)

·         Collection functions in oracle (Multiset union, Submultiset, Set, Cast)

·         PLSQL sequential control statements (EXIT, CONTINUE)

·         Object Oriented Programming in Oracle

è Overview on member functions in objects and types (Map member function, Order member function, Constructor, Inheritance – Final, Notfinal, Not instantiable, REF, SCOPE IS) with suitable examples

·         Differences and similarities between the different collection types


CHAPTER 12 – Cursors and Ref Cursors

·         Introduction to cursors in Oracle (Declare, Open, Fetch, Close)

·         Select FOR UPDATE statement

·         Where current of statement

·         Cursor attributes

·         Parameterized cursors (Simple parameterized cursors – data types in number, varchar2, date and Complex parameterized cursors – collection data types as nested tables, varrays and associative arrays)

·         Overview on Ref cursors and sys_refcursors

·         Sys_refcursor with collection types

·         Procedure, Function with their input and output parameters as sys_refcursors (Examples, cursor () function for invoking a procedure or function with a sys_refcursor as its input data type)


CHAPTER 13 - Overview on cryptography in Oracle

·         Introduction to obfuscation tool kit

·         Obfuscation subprograms (DES3, DES3getkey, DES, MD5 with suitable explanation and examples, Restrictions)

·         Introduction to CRYPTO package

·         Crypto subprograms (DES, DES3, AES, MD5, MD4, SHA-1 MAC and Hashes with suitable explanations and examples, Restrictions, Advantages over the obfuscation tool kit)

·         The wrap utility (Purpose, Explanations and suitable examples)

·         Introduction to the UTL_ENCODE utility

·         UTL_ENCODE subprograms (Base64, Mimeheader, Quoted_printable, Text, UU)


CHAPTER 14 – Overview on external files processing

·         Introduction to UTL_FILE utility


·         Emailing the content of an external file using UTL_MAIL utility


CHAPTER 15- Overview on Oracle defined objects

·         Introduction to data dictionary tables and views (User, ALL, DBA restrictions, Dictionary table (select * from dictionary) which will provide all the data dictionary tables and views available in oracle)

·         Introduction to data dictionary packages (With examples on some widely used, important packages like DBMS_OUTPUT, DBMS_ALERT, DBMS_ASSERT, DBMS_DDL, DBMS_METADATA, DBMS_RANDOM and some more)


CHAPTER 16 – Designing PLSQL Code

·         Identify guidelines for cursor design

·         Use cursor variables

·         Create subtypes based on existing types


CHAPTER 17 – Overview of Advanced Interface Methods

·         Execute external C programs from PL/SQL( With suitable examples)

·         Execute Java programs from PL/SQL(With suitable examples)


CHAPTER 18 – Implementing Fine-Grained Access Control for VPD

·         Explain the process of fine-grained access control

·         Implement and test fine-grained access control


CHAPTER 19 – Manipulating Large Objects in PLSQL

·         Create and manage LOB data types

·         Use the DBMS_LOB PL/SQL package

·         Use of temporary LOBs


CHAPTER 20 – Administering SecureFile LOBs

·         Describe SecureFile LOB features

·         Enable SecureFile LOB deduplication, compression, and encryption

·         Migrate BasicFile LOBs to the SecureFile LOB format


CHAPTER 21 – Overview of PL/SQL performance tuning

·         Measuring PL/SQL performance

è Troubleshooting common problems in Oracle (Make your code readable for better troubleshooting, Do not hardcode any values inside the program, declare variables as a constant if possible, Anchor the variables whenever possible, Using cursor for loops instead of cursors whenever possible, Handle proper exceptions, Reuse the code if possible, Cursor memory wastage reduction, CHAR memory wastage reduction as IN, OUT, INOUT parameters)

è Debugging techniques in PLSQL (Parsing methods in PLSQL Hard for non-identical queries and soft for identical queries, DBMS_DEBUG package working functionality with a clear example to debug a PLSQL code, DBMS_PROFILER package working functionality with a clear example to portray the hot bottlenecks in a PLSQL code which explain plan or manual checking fail to accomplish)

·         Choosing the appropriate compilation method either Native execution or interpreted execution based on the requirement

·         Conditional compilation in PLSQL (Benefits, Directives, DBMS_PREPROCESSOR)

·         Tuning PLSQL Code

è Role of PLSQL data types over SQL data types in PLSQL performance tuning

è Using FORALL and bulk collect

è PLSQL Optimizer levels in oracle (Level 0, 1, 2, 3 with sufficient examples on each level)

è Pragma types (Exception init, Restrict references, Inline, Autonomous transaction, Serially reusable with sufficient examples and explanations)

è Dynamic SQL (Bind variable to avoid parsing overhead)

è Call by reference over call by value

è Closing a cursor without fail

è Pragma inline


CHAPTER 22 – Improving Performance with Caching

·         Improve memory usage by caching SQL result sets and using the DBMS_RESULT_CACHE package

·         Write queries that use the result cache hint

·         Set up PL/SQL functions to use PL/SQL result caching

·         RELIES_ON clause improving performance for a function

·         Implicit cursors over explicit cursors


CHAPTER 23 – Analyzing PLSQL Code

·         Run reports on source code

·         Determine identifier types and usages

·         Use DBMS_METADATA to retrieve object definitions


CHAPTER 25 – Profiling and Tracing PLSQL Code

·         Trace PL/SQL program execution

·         Profile PL/SQL applications


CHAPTER 26Safeguarding Your Code against SQL Injection Attacks

·         Describe SQL injections

·         Reduce attack surfaces

·         Use DBMS_ASSERT

·         Design immune code

·         Test code for SQL injection flaws



·         I am in the process of creating a function in Oracle which allows two users connected to the same database to play chess against each other. I am hoping for placing that function’s scripting in this chapter for users to read and play.


Reader Comments:




 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks