Master Advanced PL/SQL concepts with “Oracle Advanced PL/SQL Developer Professional Guide”
-By Saurabh K. Gupta
The Oracle Advanced PL/SQL Developer Professional Guide helps you to master the advanced PL/SQL concepts in Oracle 11g. The book aims to be a sure selection for the Associate level Oracle professionals aspiring for Professional level. The topics covered and demonstrated are in line with the Oracle University prescription for Oracle Professional certification, which justify the version updates to be advanced and not complex. The book is under publication from Packt publishers and all set for its release in May, 2012.
The OCP 1Z0-146 certification is the second milestone for the Associate level Oracle professionals. The journey from Associate to Professional level enhances your reliability and credibility with the Oracle technology, catalyzes your employment chances, and job effectiveness. Apart from focusing the certification preparation, the book contains ample demonstrations and best programming practices which can be employed in day to day assignments.
The book aims to cover the advanced features of PL/SQL which are required to design and optimize the PL/SQL code. The recapitulation of PL/SQL programming and advanced features like collections, external procedures, server side result caching, implementing VPD to enforce row level security, handling large objects and SecureFiles build up a concrete platform for a PL/SQL professional. Apart from the programming, the book makes instrumental recommendations on the usage of development tool SQL Developer, employing best practices in database environments and safeguarding the vulnerable areas in PL/SQL code to avoid code injection.
The book gives a deeper insight to transform the readers from mid-level programmers to professional database designers. The advanced concepts covered through this book would surely agitate the readers to dig upon and explore more on the topics.
The book has been reviewed by Oracle ACEs Kamran Agayev, Ronald Rood, Mohan Dutta, and Marcel Hoefs. Orders can be placed at Packt’s website, Amazon and all leading technical book distributors.
Book details are as below
ISBN(10): 1849687226
ISBN(13): 978-1-84968-722-5
Place your order from the below links
http://www.packtpub.com/oracle-advanced-pl-sql-developer-professional-guide/book
http://www.amazon.com/Oracle-Advanced-Developer-Professional-Guide/dp/1849687226
Here is a sample chapter for download. The chapter covers the Tracing and Profiling capabilities of Oracle PL/SQL.
I shall welcome the honest reviews from the readers so that I can fill the gaps between my efforts and reader’s expectations. In addition, if you feel to provide additional feedback on other aspects of the book, you are always welcome. Do write me at my blog for any deviation in observation or understanding.
The content outline of the book is as below
Chapter 1, Overview of PL/SQL Programming Concepts
The chapter 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. The chapter paces up with the structure of a PL/SQL block and reviews the PL/SQL objects like procedures, functions, and packages. In the chapter, we shall also learn to work with SQL Developer.
Chapter 2, Designing PL/SQL Code
The chapter discusses the handling of cursors in a PL/SQL program. The readers would learn the guidelines to design a cursor, usage of cursor variables, and cursor life cycle.
Chapter 3, Using Collections
The chapter introduces a very important feature of PL/SQL i.e. Collections. A collection in database is very similar to arrays or maps in other programming languages. The chapter compares the collection types in multiple aspects and makes fair recommendations for the appropriate selection in a situation. The chapter will also teach the collection methods which are utility APIs for working with collections.
Chapter 4, Using advanced interface methods
The chapter teaches how to interact with an external program written in non PL/SQL language, within PL/SQL. The chapter demonstrates the execution steps of external procedures in PL/SQL. The 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
The chapter 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 on its key features. The readers would find stepwise implementation of VPD with the help of policy function and DBMS_RLS package. The chapter describes the policy enforcement through application contexts also.
Chapter 6, Working with large objects
The chapter discusses the traditional and conventional way of handling Large Objects in Oracle database. The chapter starts with the familiarization of available LOB data types (i.e. BLOB, CLOB, BFILE and Temporary LOBs) and their significance. The readers would learn the creation of LOB types in PL/SQL and their respective handling operations. The chapter demonstrates the management of LOB data types using SQL and DBMS_LOB package.
Chapter 7, Using SecureFile LOBs
The chapter introduces one of the key inductions of Oracle 11g i.e. SecureFiles. SecureFiles are upgraded LOBs which work on improved philosophy of storage and maintenance. The key accomplishments of SecureFiles i.e. deduplication, compression and encryption are licensed features. The chapter discusses and demonstration the implementation of these three properties. The readers would learn how to migrate (or rather upgrade) the existing older LOBs into new scheme i.e. SecureFiles. The migration techniques covered use online redefinition method and partition method.
Chapter 8, Compiling and Tuning to improve performance
The chapter describes the fair practices in effective PL/SQL programming. The readers would surely arouse great interest in discovering the fact that how a better code writing impacts the code performance. Besides, the chapter explains an important aspect of query optimization i.e. the parameter PLSQL_OPTIMIZE_LEVEL. The code behavior and optimization strategy at each level would help the readers to understand the language internals. Subsequently, the new PRAGMA induction will give the readers a deeper insight into subprogram inlining concepts.
Chapter 9, Caching to improve performance
The chapter covers another hot feature of Oracle 11g database i.e. Server side result caching. The newly introduced server side cache component in SGA holds the results retrieved from SQL query or PL/SQL function. The chapter describes the configuration a database server for caching feature through related parameters, implementation in SQL through RESULT_CACHE hint and implementation in PL/SQL function through RESULT_CACHE clause. Besides the implementation part, the chapter teaches the validation and invalidation of result cache and using DBMS_RESULT_CACHE package.
Chapter 10, Analyzing PL/SQL Code
The chapter is meant to understand and learn the code diagnostics tricks and using the code analysis for reporting purposes. The readers would learn to monitor identifier usage, compilation settings and generate the subsequent reports from SQL Developer. The chapter body discusses a very important addition of Oracle 11g, the PL/Scope. It covers the explanation and illustrations to generate the structural reports through the dictionary views. In addition, the chapter also demonstrates the use of 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
The chapter aims to demonstrate the tracing and profiling features in PL/SQL. The tracing demonstration uses DBMS_TRACE package to trace the enabled or all calls in a PL/SQ program. The PL/SQL hierarchical profiler is a new induction in 11g to identify and report the time consumed at each line of the program. The biggest benefit is that the raw profiler data can be reproduced meaningfully into HTML reports.
Chapter 12, Safeguarding PL/SQL Code against SQL Injection attacks
The chapter 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. The readers would learn the impacts and precautionary recommendations to avoid the injection attacks. The body of the chapter discusses the preventive measures like using invoker’s rights, client input validations tips, and using DBMS_ASSERT to sanitize inputs. The chapter concludes on the testing strategies which can be practiced to identify vulnerable areas in SQL.
Mock Assessment Test for 1Z0-146 Exam
Here is a mock assessment test for the 1Z0-146 certification exam which can be your final warm up game before you appear for the real exam. Questions follow similar pattern but also test your basic understanding on a concept. For answer key – comment on the post with your email id and I shall send across to you the same.
Oracle Database 11g: Advanced Programming with PL/SQL (Mock Assessment Test)
I hope the readers of my book will find the mock paper quite handy while the rest of you will discover the areas to dive in further.
Related articles
- Hidden features of PL/SQL (stackoverflow.com)
- PL/SQL # Tip: Locally declared subprograms in PL/SQL (sbhoracle.wordpress.com)
- Basic plsql questions (oracletechnotalk.wordpress.com)
- If You Have Oracle Database You Have Oracle Application Express; Use it or Lose it (awads.net)
- “Best Practices” Should Be Renamed “Contextual Practices” (awads.net)
culture, change, and tech 
Seems to be very nice book. It is covering many things of Oracle database. I will make sure, i have a copy of same.
Thanks for the encouraging word Umesh….
Hi Saurabh,
In your book in chapter 3 using collection. page 110,
the o/p of program is given as
———
L_ARRAY(4) element of the array is =
L_ARRAY(6) element of the array is =
L_ARRAY(8) element of the array is = 10
—————–
it should be
4 element of the array is =
6 element of the array is =
8 element of the array is =10
have a nice day ahead!
Regards,
Vipul Varshney
Awesome article. Brilliant examples
The table of contents of the book looks nicely drafted for the certification. I have gone through the sample chapter and found the presentation very clear. I would love to own this book. I would have been lucky if I could get it through the giveaway contest.
Here is the feedback from Navinth Bakmeedeniya who passed on his review to me through mail.
“Hi Saurabh,
I’m a Software Engineer from Sri Lanka currently reading for Oracle 11g Developer Advanced PL/SQL Developer Certified Professional. I’m using this book as my main study material for this exam & I’m really happy with its contents & your way of explaining things.
While going through this book, I found out some minor mistakes & I thought of listing them to you as a help so that you can avoid them & improve your product further in a future release. Please find the attached document.
Again, I’d like mention that this has been a good reference for me so far.”
In the final draft of the book, I could find few spell mistakes (as pointed by you). I have submitted the findouts to the publisher. Probably, they can incorporate the changes as soon as they plan to. 🙂
Thanks again for reading the book and its great to know that the product is helping you out.
Thanks for adding the Mock assessment test with the book. Really helpful.
Samar, I included after one of the readers of my book requested for additional practices tests. Thanks you like it too.
Thanks Sourabh for the mock assessments on Oracle Database 11g: Advanced Programming with PL/SQL. Please send me the answers to the question. My email-id is prakash_syan@hotmail.com
Hi Sourabh, i have completed by OCA certification in 9i and now want to complete OCP certification in 12c.
can you please guide me on syllabus and the material needed
my email is praveenkalaskar@gmail.com