Oracle Advanced PL/SQL Developer Professional Guide

Master advanced PL/SQL concepts along with plenty of example questions for 1Z0-146 examination with this book and ebook

Oracle Advanced PL/SQL Developer Professional Guide

Starting
Saurabh K. Gupta

Master advanced PL/SQL concepts along with plenty of example questions for 1Z0-146 examination with this book and ebook
$35.99
$59.99
RRP $35.99
RRP $59.99
eBook
Print + eBook
$12.99 p/month

Want this title & more? Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.
+ Collection
Free Sample

Book Details

ISBN 139781849687225
Paperback440 pages

About This Book

  • Blitz the 1Z0-146 exam
  • Master the advanced features of PL/SQL to design and optimize code using real-time demonstrations
  • Efficiently design PL/SQL code with cursor design and subtypes

Who This Book Is For

If you are a PL/SQL developer looking for deeper insight and a move from mid-level programmer to professional database developer, then this is the best guide for you.

This book is also an ideal guide for all the Associate level PL/SQL programmers who are preparing for the Professional 1Z0-146 certification.

This book assumes you have prior knowledge of PL/SQL programming.

Table of Contents

Chapter 1: Overview of PL/SQL Programming Concepts
PL/SQL—the procedural aspect
PL/SQL development environments
Procedures
Functions
PL/SQL packages
Cursors—an overview
Exception handling in PL/SQL
Managing database dependencies
Reviewing Oracle-supplied packages
Summary
Practice exercise
Chapter 2: Designing PL/SQL Code
Understanding cursor structures
Cursor variables
Subtypes
Summary
Practice exercise
Chapter 3: Using Collections
Collections—an overview
Associative arrays
Nested tables
Varray
Collections—a comparative study
PL/SQL collection methods
Manipulating collection elements
Collection initialization
Summary
Practice exercise
Chapter 4: Using Advanced Interface Methods
Understanding external routines
Executing external C programs from PL/SQL
Executing Java programs from PL/SQL
Summary
Practice exercise
Chapter 5: Implementing VPD with Fine Grained Access Control
Fine Grained Access Control
VPD implementation—outline and components
VPD implementation—demonstrations
VPD policy metadata
Policy utilities—refresh and drop
Summary
Practice exercise
Chapter 6: Working with Large Objects
Introduction to the LOB data types
Understanding the LOB data types
Creating LOB data types
Managing LOB data types
Working with the CLOB, BLOB, and BFILE data types
Migrating from LONG to LOB
Using temporary LOBs
Summary
Practice exercise
Chapter 7: Using SecureFile LOBs
Introduction to SecureFiles
Working with SecureFiles
Migration from BasicFiles to SecureFiles
Summary
Practice exercise
Chapter 8: Compiling and Tuning to Improve Performance
Native and interpreted compilation techniques
Tuning PL/SQL code
Enabling intra unit inlining
PRAGMA INLINE
Summary
Practice exercise
Chapter 9: Caching to Improve Performance
Introduction to result cache
Configuring the database for the server result cache
Implementing the result cache in SQL
Implementing result cache in PL/SQL
Summary
Practice exercise
Chapter 10: Analyzing PL/SQL Code
Track coding information
Using SQL Developer to find coding information
Determining identifier types and usages
The DBMS_METADATA package
Summary
Practice exercise
Chapter 11: Profiling and Tracing PL/SQL Code
Tracing the PL/SQL programs
Profiling the PL/SQL programs
Summary
Practice exercise
Chapter 12: Safeguarding PL/SQL Code against SQL Injection Attacks
SQL injection—an introduction
Immunizing SQL injection attacks
Testing the code for SQL injection flaws
Summary
Practice exercise

What You Will Learn

  • Get to grips with using Advanced Interface Methods
  • Learn to implement VPD with Fine Grained Access Control
  • Make use of Collections and learn data caching techniques in PL/SQL programs
  • Protect PL/SQL code from injection attacks
  • Learn to use Result caching to tune PL/SQL code
  • Demonstrate tracing and profiling features in PL/SQL
  • Get started with SecureFiles for advanced storage options

In Detail

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. Server-side PL/SQL is stored and compiled in the Oracle Database and runs within the Oracle executable. With this guide Oracle developers can work towards accomplishing Oracle 11g Advanced PL/SQL Professional certification, which is the second milestone for developers working at the Associate level.

The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts. Besides the clear and precise explanation on advanced topics, it also contains example code and demonstrations, which gives a sense of application and usage to readers.

The book gives a deep insight that will help transform readers from mid-level programmers to professional database developers. It aims to cover the advanced features of PL/SQL for designing and optimizing PL/SQL code.

This book starts with an overview of PL/SQL as the programming database language and outlines the benefits and characteristics of the language. The book then covers the advanced features that include PL/SQL code writing using collections, tuning recommendations using result caching, implementing VPD to enforce row level security, and much more. Apart from programming, the book also dives deep into the usage of the development tool SQL Developer, employing best practices in database environments and safeguarding the vulnerable areas in PL/SQL code to avoid code injection.

Authors

Table of Contents

Chapter 1: Overview of PL/SQL Programming Concepts
PL/SQL—the procedural aspect
PL/SQL development environments
Procedures
Functions
PL/SQL packages
Cursors—an overview
Exception handling in PL/SQL
Managing database dependencies
Reviewing Oracle-supplied packages
Summary
Practice exercise
Chapter 2: Designing PL/SQL Code
Understanding cursor structures
Cursor variables
Subtypes
Summary
Practice exercise
Chapter 3: Using Collections
Collections—an overview
Associative arrays
Nested tables
Varray
Collections—a comparative study
PL/SQL collection methods
Manipulating collection elements
Collection initialization
Summary
Practice exercise
Chapter 4: Using Advanced Interface Methods
Understanding external routines
Executing external C programs from PL/SQL
Executing Java programs from PL/SQL
Summary
Practice exercise
Chapter 5: Implementing VPD with Fine Grained Access Control
Fine Grained Access Control
VPD implementation—outline and components
VPD implementation—demonstrations
VPD policy metadata
Policy utilities—refresh and drop
Summary
Practice exercise
Chapter 6: Working with Large Objects
Introduction to the LOB data types
Understanding the LOB data types
Creating LOB data types
Managing LOB data types
Working with the CLOB, BLOB, and BFILE data types
Migrating from LONG to LOB
Using temporary LOBs
Summary
Practice exercise
Chapter 7: Using SecureFile LOBs
Introduction to SecureFiles
Working with SecureFiles
Migration from BasicFiles to SecureFiles
Summary
Practice exercise
Chapter 8: Compiling and Tuning to Improve Performance
Native and interpreted compilation techniques
Tuning PL/SQL code
Enabling intra unit inlining
PRAGMA INLINE
Summary
Practice exercise
Chapter 9: Caching to Improve Performance
Introduction to result cache
Configuring the database for the server result cache
Implementing the result cache in SQL
Implementing result cache in PL/SQL
Summary
Practice exercise
Chapter 10: Analyzing PL/SQL Code
Track coding information
Using SQL Developer to find coding information
Determining identifier types and usages
The DBMS_METADATA package
Summary
Practice exercise
Chapter 11: Profiling and Tracing PL/SQL Code
Tracing the PL/SQL programs
Profiling the PL/SQL programs
Summary
Practice exercise
Chapter 12: Safeguarding PL/SQL Code against SQL Injection Attacks
SQL injection—an introduction
Immunizing SQL injection attacks
Testing the code for SQL injection flaws
Summary
Practice exercise

Book Details

ISBN 139781849687225
Paperback440 pages
Read More