Oracle Advanced PL|SQL Developer Professional Guide

440 Pages·2012·5.69 MB·English

www.it-ebooks.info
Oracle Advanced PL/SQL
Developer Professional Guide
Master advanced PL/SQL concepts along with plenty of
example questions for 1Z0-146 examination
Saurabh K. Gupta
professional expertise distilled
P U B L I S H I N G
BIRMINGHAM - MUMBAI
www.it-ebooks.info
Oracle Advanced PL/SQL Developer Professional Guide
Copyright © 2012 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.

First published: May 2012

Production Reference: 1070512

Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-722-5
www.packtpub.com
Cover Image by Tina Negus ([email protected])
www.it-ebooks.info
Credits
Author Project Coordinator
Saurabh K. Gupta Alka Nayak
Reviewers Proofreaders
Kamran Agayev A. Linda Morris
Mohan Dutt Kevin McGowan
Marcel Hoefs
Ronald Rood Indexer
Rekha Nair
Acquisition Editor
Rukshana Khambatta Graphics
Valentina D'Silva
Manu Joseph
Lead Technical Editor
Pramila Balan
Production Coordinator
Nilesh R. Mohite
Technical Editors
Vrinda Amberkar
Prasad Dalvi Cover Work
Nilesh R. Mohite
www.it-ebooks.info
www.it-ebooks.info
Foreword
Many of us learned to use PL/SQL recently; many did this many years ago. At that
time simple problems required simple PL/SQL code, with lots of procedural code
in it. Isn't the procedural part what PL/SQL is all about? Yes, it is, but this is also a
threat because, when it is not used smartly, the procedural looping might become a
performance hog.
Pl/SQL has evolved a lot. Bulk collections should be commonly in use now. Many
programmers that support multiple vendors have little or no knowledge about
Oracle collections. Because of this they write code like we did during v7, leaving the
huge performance benefits, that Oracle has, untouched.
For these programmers this book is a very helpful addition to their library of
knowledge. It helps them to easily perform the same task, but maybe 70 times faster,
without making the code more complex. Using the advanced techniques described
in the book you can do that. Don't mix up "advanced" with "complex". The fun about
this is that many make their code complex using simple PL/SQL, trying to gain some
performance, instead of effectively using advanced constructs in PL/SQL that in the
end make the code easier to read and understand.
There is more to find in the book. Being a DBA, performance attracts a certain
amount of attention. This is because performance is important. It greatly impacts the
scalability of a database and the end user experience of the application. Other things
you might find useful are the interfaces with the outside world, where external
procedures can do work that does not fit the characteristics of a database.
Security is also something that attracts a DBA. Here you will find implementations
of Virtual Private Database and enough remarks to keep the reader learning for
quite a while. For example, how to protect against SQL injection? This again is a very
interesting topic that should be taken very seriously. These days no network is safe.
So scan every input.
www.it-ebooks.info
I won't mention everything that is covered, just find a keyboard, your local database,
and start reading. Try out the code samples and see where you can modify your
existing code to take advantage of the new insights that the book will give you.
How advanced are the techniques described here depends on your mileage. For
many the contents will be valuable enough to justify the term "advanced". As a
reviewer it was a pleasure to read it and to try to push Saurabh Gupta to his limits.
For me, I could use this book, even today.
Ronald Rood
Oracle ACE, Oracle DBA, OCM
PL/SQL is a programming language that is not only used by application developers,
but also by database administrators in their daily tasks. This book contains
information that every developer and even DBAs should know. As you read this
book, you'll definitely learn a lot of new facts about PL/SQL programming. This
book provides detailed information on general PL/SQL programming language,
analyzing, tuning, tracing, and securing your code.
What I like most about the book is that it contains a lot of examples and helpful
scripts for each chapter. This book also contains a lot of questions for the 1Z0-146
examination at the end of each chapter and it's one of the best guides for getting
ready to pass the exam.
If you're a PL/SQL developer, whether a beginner or an expert, this book is
for you.
Kamran Agayev A.
Oracle ACE, Oracle DBA Expert
www.it-ebooks.info
About the Author
Saurabh K. Gupta got introduced to Oracle database around 5 years ago. Since
then, he has been synchronizing his on job and off job interests with Oracle database
programming. As an Oracle 11g Certified Advanced PL/SQL Professional, he soon
moved from programming to database designing, development, and day-to-day
database administration activities. He has been an active Oracle blogger and OTN
forum member. He has authored and published more than 70 online articles and
papers. His work can be seen in RMOUG journal, PSOUG, dbanotes, Exforsys,
and Club Oracle. He shares his technical experience through his blog: http://
sbhoracle.wordpress.com/. He is a member of All India Oracle Users Group
(AIOUG) and loves to participate in technical meets and conferences.
Besides digging into Oracle, sketching and snooker are other pastimes for him.
He can be reached through his blog SbhOracle for any comments, suggestions, or
feedback regarding this book.
www.it-ebooks.info
Acknowledgement
On a professional note, I am obliged to Ronald Rood, Kamran Agayev, Mohan Dutta,
and Marcel Hoefs who reviewed the book with their own insights and perspectives.
I was excited with the fact that the technical reviewers of my book are Oracle ACEs,
highly respected, and recognized experts in the industry. I am grateful to Ronald
who judged the worth of the book from the DBA perspective and helped me to
extend my limits on the administrative aspect as well. Thanks to Kamran Agayev
who consistently encouraged my writing styles and gave valuable inputs on the
chapters. My obligations to Mohan Dutta and Marcel Hoefs who invested their
valuable time in my work and added to the quality of the content. I would also like
to express my gratitude for Arup Nanda, who has always been a great source of
inspiration for me. His sessions and articles, covering all areas of Oracle database,
have always been a great source of knowledge and motivation for me.
I would like to extend the appreciation to Packt Publishing for considering my
proposal and accepting to go ahead on this book. My sincere thanks to Rukshana
Khambatta, the Acquisition Editor at Packt for coordinating the kick-off activities of
the book. I deeply appreciate the efforts of the Project Coordinator, Alka Nayak; the
Lead Technical Editor, Pramila Balan; and the Technical Editors Vrinda Amberkar
and Prasad Dalvi, whose diligent work and coordination added extra miles to the
project. There was great tuning established between us and I am glad we worked
parallely on the editorial process while abiding by the timelines.
It is correctly said that a man's personal and professional achievements are a
showcase of his family's support and encouragement. I dedicate all my efforts and
works to my parents, Suresh Chandra Gupta and Swadesh Gupta and family for
their inevitable support, motivation, and sacrifices, and nurturing me towards all
my achievements. Sincere thanks to my wife, Neha, and Sir J.B. Mall for their love,
consistent support, and confidence in my endeavors and for being with me during
my tough times.
www.it-ebooks.info
About the Reviewers
Kamran Agayev A. is an Oracle ACE and Oracle Certified Professional DBA
working at Azercell Telecom. He's an author of the book, Oracle Backup & Recovery:
Expert secrets for using RMAN and Data Pump, and also shares his experience with a
lot of step-by-step articles and video tutorials in his blog at http://kamranagayev.
com. He also presents at Oracle OpenWorld, TROUG, and local events.
Mohan Dutt is an Oracle expert, having presented more than 55 sessions at Oracle
conferences worldwide. An Oracle evangelist at large, he was awarded Member of
the Year by Oracle Applications User Group (OAUG) in 2007. He authors the world's
first blog dedicated entirely to Oracle certification. He has founded and chaired 3
Oracle Special Interest Groups (SIG). He was recognized as an Oracle ACE in 2011.
Marcel Hoefs learned his trade participating in numerous Oracle development
projects, as an Oracle developer, since 1997. Being a specialist in SQL and PL/SQL
database development, Oracle Forms, Reports, and Designer, Marcel currently
works as a Technical Architect, Lead Developer, and Performance Specialist. With
the advent of web technologies such as Web Services, ADF, and APEX, he currently
specializes in innovative solutions opening up traditional Oracle database systems to
the Web. As a senior Oracle Consultant with CIBER, he is also an Oracle competence
leader, organizing and participating in knowledge sharing sessions with participants
from within and outside CIBER.
www.it-ebooks.info