Oracle Advanced PL/SQL Developer Professional Guide - Saurabh K. Gupta

www.allitebooks.com

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 PUBLISHING professional expertise distilled BIRMINGHAM - MUMBAI www.allitebooks.com

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 (tina_manthorpe@sky.com) www.allitebooks.com

Credits Author Saurabh K. Gupta Reviewers Kamran Agayev A. Mohan Dutt Marcel Hoefs Ronald Rood Acquisition Editor Rukshana Khambatta Lead Technical Editor Pramila Balan Technical Editors Vrinda Amberkar Prasad Dalvi Project Coordinator Alka Nayak Proofreaders Linda Morris Kevin McGowan Indexer Rekha Nair Graphics Valentina D'Silva Manu Joseph Production Coordinator Nilesh R. Mohite Cover Work Nilesh R. Mohite www.allitebooks.com

www.allitebooks.com

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 beneits, 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 ind 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 ind useful are the interfaces with the outside world, where external procedures can do work that does not it the characteristics of a database. Security is also something that attracts a DBA. Here you will ind 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.allitebooks.com

I won't mention everything that is covered, just ind 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 deinitely 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.allitebooks.com

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 Certiied 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.allitebooks.com

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 sacriices, and nurturing me towards all my achievements. Sincere thanks to my wife, Neha, and Sir J.B. Mall for their love, consistent support, and conidence in my endeavors and for being with me during my tough times. www.allitebooks.com

About the Reviewers Kamran Agayev A. is an Oracle ACE and Oracle Certiied 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 irst blog dedicated entirely to Oracle certiication. 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.allitebooks.com

Oracle Advanced PL/SQL Developer Professional Guide - Saurabh K. Gupta

You're Reading a Preview

Recently Viewed