Preface

Oracle Database 11g brings in a weighted package of new features which takes the database management philosophy from instrumental to self-intelligence level. The new database features, which are more properly called "advanced", rather than "complex", aim either of the two purposes:

• Replacement of a workaround solution with a permanent one (as an enhancement)

• By virtue of routine researches and explorations, introduce a fresh feature to help database administrators and developers with their daily activities

Oracle Advanced PL/SQL Professional Guide focuses on advanced features of Oracle 11g PL/SQL. The areas targeted are PL/SQL code design, measuring and optimizing PL/SQL code performance, and analyzing PL/SQL code for reporting purposes and immunizing against attacks. The advanced programming topics such as usage of collections, implementation of VPD, interaction with external procedures in PL/SQL, performance orientation by caching results, tracing and profiling techniques, and protecting against SQL injection will familiarize you with the latest programming findings, trends and recommendations of Oracle. In addition, this book will help you to learn the latest, best practices of PL/SQL programming in terms of code writing, code analyzing for reporting purposes, tracing for performance, and safeguarding the PL/SQL code against hackers.

	An investment in knowledge pays the best interest.
	--Benjamin Franklin

The fact remains that the technical certifications from Oracle Corporation establish a benchmark of technical expertise and credibility, and set the tone of an improved career path for application developers. With the growing market in database development, Oracle introduced Advanced PL/SQL Professional Certification (1Z0-146) in the year 2008. The OCP (1Z0-146) certification exam tests aspirants on knowledge of advanced PL/SQL concepts (validated up to Oracle 11g Release 1). An advanced PL/SQL professional is expected to independently design, develop, and tune the PL/SQL code which can efficiently interface database systems and user applications.

The book, Oracle Advanced PL/SQL Professional Guide, is a sure recommendation for the preparation of the OCP certification (1Z0-146) exam. Advanced PL/SQL topics are explained thoroughly with the help of demonstrations, figures, and code examples. The book will not only explain a feature, but will also teach its implementation and application. You can easily pick up the content structure followed in the book. The code examples can be tried on your local database setups to give you a feel of the usage of a specific feature in real time scenarios.

What this book covers

Chapter 1, Overview of PL/SQL Programming Concepts, covers the overview of PL/SQL as the primary database programming language. It describes the characteristics of the language and its strengths in database development. This chapter speeds up with the structure of a PL/SQL block and reviews PL/SQL objects such as procedures, functions, and packages. In this chapter, we will also learn to work with SQL Developer.

Chapter 2, Designing PL/SQL Code, discusses the handling of cursors in a PL/SQL program. This chapter helps you to learn the guidelines for designing a cursor, usage of cursor variables, and cursor life cycle.

Chapter 3, Using Collections, introduces a very important feature of PL/SQL—collections. A collection in a database is very similar to arrays or maps in other programming languages. This chapter compares collection types and makes recommendations for the appropriate selection in a given situation. This chapter also covers the collection methods which are utility APIs for working with collections.

Chapter 4, Using Advanced Interface Methods, teaches how to interact with an external program written in a non-PL/SQL language, within PL/SQL. It demonstrates the execution steps for external procedures in PL/SQL. This steps describe the network configuration on a database server (mounted on Windows OS), library object creation, and publishing of a non-language program as an external routine.

Chapter 5, Implementing VPD with Fine Grained Access Control, introduces the concept of Fine Grained Access in PL/SQL. The working of FGAC as Virtual Private Database is explained in detail along with an insight into its key features. You will find stepwise implementation of VPD with the help of policy function and the DBMS_RLS package. This chapter also describes policy enforcement through application contexts.

Chapter 6, Working with Large Objects, discusses the traditional and conventional way of handling large objects in an Oracle database. This chapter starts with the familiarization of the available LOB data types (BLOB, CLOB, BFILE, and Temporary LOBs) and their significance. You will learn about the creation of LOB types in PL/SQL and their respective handling operations. This chapter demonstrates the management of LOB data types using SQL and the DBMS_LOB package.

Chapter 7, Using SecureFile LOBs, introduces one of the key innovations in Oracle 11g —SecureFiles. SecureFiles are upgraded LOBs which work on an improved philosophy of storage and maintenance. The key improvements of SecureFiles—deduplication, compression, and encryption—are licensed features. This chapter discusses and demonstrates the implementation of these three properties. You will learn how to migrate (or rather upgrade) the existing older LOBs into a new scheme—SecureFiles. The migration techniques covered use an online redefinition method and a partition method.

Chapter 8, Compiling and Tuning to Improve Performance, describes fair practices in effective PL/SQL programming. You will be very interested to discover how better code writing impacts code performance. This chapter explains an important aspect of query optimization—the PLSQL_OPTIMIZE_LEVEL parameter. The code behavior and optimization strategy at each level will help you to understand the language internals. Subsequently, the new PRAGMA feature will give you a deeper insight into subprogram inlining concepts.

Chapter 9, Caching to Improve Performance, covers another hot feature of Oracle 11g Database—server-side result caching. The newly introduced server-side cache component in SGA holds the results retrieved from SQL query or PL/SQL function. This chapter describes the configuration of a database server for caching feature through related parameters, implementation in SQL through RESULT_CACHE hint and implementation in PL/SQL function through the RESULT_CACHE clause. Besides the implementation section, this chapter teaches the validation and invalidation of result cache, using the DBMS_RESULT_CACHE package.

Chapter 10, Analyzing PL/SQL Code, helps you to understand and learn code diagnostics tricks and code analysis for reporting purposes. You will learn to monitor identifier usage, about compilation settings, and generate the subsequent reports from SQL Developer. This chapter discusses a very important addition in Oracle 11g—PL/Scope. It covers the explanations and illustrations to generate the structural reports through the dictionary views. In addition, this chapter also demonstrates the use of the DBMS_METADATA package to retrieve and extract metadata of database objects from the database in multiple formats.

Chapter 11, Profiling and Tracing PL/SQL Code, aims to demonstrate the tracing and profiling features in PL/SQL. The tracing demonstration uses the DBMS_TRACE package to trace the enabled or all calls in a PL/SQ program. The PL/SQL hierarchical profiler is a new innovation in 11g to identify and report the time consumed at each line of the program. The biggest benefit is that raw profiler data can be reproduced meaningfully into HTML reports.

Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks, discusses the SQL injection as a concept and its remedies. The SQL injection is a serious attack on the vulnerable areas of the PL/SQL code which can lead to extraction of confidential information and many fatal results. You will learn the impacts and precautionary recommendations to avoid injective attacks. This chapter discusses the preventive measures such as using invoker's rights, client input validation tips, and using DBMS_ASSERT to sanitize inputs. It concludes on the testing strategies which can be practiced to identify vulnerable areas in SQL.

Appendix, Answers to Practice Questions, contains the answers to the practice questions for all chapters.

What you need for this book

You need to have a sound understanding of SQL and PL/SQL basics. You must have mid-level experience of working with Oracle programming.

Who this book is for

The book is for associate-level developers who are aiming for professional-level certification. This book can also be used to understand and practice advanced PL/SQL features of Oracle.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "The SERVEROUTPUT parameter is a SQL*Plus variable which enables the printing of block debug messages."

A block of code is set as follows:

/*Start the PL/SQL block*/
DECLARE

/*Declare a local variable and initialize with a default value*/
   L_NUM NUMBER := 15;
   L_RES NUMBER;
BEGIN

/*Calculate the double of local variable*/
L_RES := L_NUM *2;

/*Print the result*/
   DBMS_OUTPUT.PUT_LINE('Double of '||TO_CHAR(L_NUM)||' is '||TO_
CHAR(L_RES));
END;
/
Double of 15 is 30

PL/SQL procedure successfully completed.

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

/*Check if the cursor is already open*/
  IF NOT C_EMP%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('Cursor is closed....Cursor has to be
opened');
END IF;

Any command-line input or output is written as follows:

SQL> HELP INDEX

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Right-click on the Connections node and select New Connection... to open the connection wizard"

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title in the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website, or added to any list of existing errata, under the Errata section of that title.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.