Oracle Advanced PL/SQL Developer Professional Guide

Book Image

Oracle Advanced PL/SQL Developer Professional Guide

By : Saurabh K. Gupta

Book Image

Oracle Advanced PL/SQL Developer Professional Guide

By: Saurabh K. Gupta

Overview of this book

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.

Oracle Advanced PL/SQL Developer Professional Guide

Oracle Advanced PL/SQL Developer Professional Guide

Credits

Foreword

About the Author

About the Author

Acknowledgement

Acknowledgement

About the Reviewers

About the Reviewers

www.PacktPub.com

www.PacktPub.com

Preface

Free Chapter

Overview of PL/SQL Programming Concepts

Overview of PL/SQL Programming Concepts

PL/SQL—the procedural aspect

PL/SQL development environments

PL/SQL packages

Cursors—an overview

Exception handling in PL/SQL

Managing database dependencies

Reviewing Oracle-supplied packages

Practice exercise

Designing PL/SQL Code

Designing PL/SQL Code

Understanding cursor structures

Cursor variables

Practice exercise

Using Collections

Using Collections

Collections—an overview

Associative arrays

Collections—a comparative study

PL/SQL collection methods

Manipulating collection elements

Collection initialization

Practice exercise

Using Advanced Interface Methods

Using Advanced Interface Methods

Understanding external routines

Executing external C programs from PL/SQL

Executing Java programs from PL/SQL

Practice exercise

Implementing VPD with Fine Grained Access Control

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

Practice exercise

Working with Large Objects

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

Practice exercise

Using SecureFile LOBs

Using SecureFile LOBs

Introduction to SecureFiles

Working with SecureFiles

Migration from BasicFiles to SecureFiles

Practice exercise

Compiling and Tuning to Improve Performance

Compiling and Tuning to Improve Performance

Native and interpreted compilation techniques

Tuning PL/SQL code

Enabling intra unit inlining

Practice exercise

Caching to Improve Performance

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

Practice exercise

Analyzing PL/SQL Code

Analyzing PL/SQL Code

Track coding information

Using SQL Developer to find coding information

Determining identifier types and usages

The DBMS_METADATA package

Practice exercise

Profiling and Tracing PL/SQL Code

Profiling and Tracing PL/SQL Code

Tracing the PL/SQL programs

Profiling the PL/SQL programs

Practice exercise

Safeguarding PL/SQL Code against SQL Injection Attacks

Safeguarding PL/SQL Code against SQL Injection Attacks

SQL injection—an introduction

Immunizing SQL injection attacks

Testing the code for SQL injection flaws

Practice exercise

Answers to Practice Questions

Answers to Practice Questions

Chapter 1, Overview of PL/SQL Programming Concepts

Chapter 2, Designing PL/SQL Code

Chapter 3, Using Collections

Chapter 4, Using Advanced Interface Methods

Chapter 5, Implementing VPD with Fine Grained Access Control

Chapter 6, Working with Large Objects

Chapter 7, Using SecureFile LOBs

Chapter 8, Compiling and Tuning to Improve Performance

Chapter 9, Caching to Improve Performance

Chapter 10, Analyzing PL/SQL Code

Chapter 11, Profiling and Tracing PL/SQL Code

Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks

Index

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Practice exercise

Internal LOBs can be used as attributes of a user-defined data type.
1. True
2. False
Internal LOBs cannot be passed as parameters to PL/SQL subprograms.
1. True
2. False
Internal LOBs can be stored in a tablespace that is different from the tablespace that stores the table containing the LOB column.
1. True
2. False
You issue the following command to create a table called LOB_STORE:
```
CREATE TABLE lob_store
(lob_id NUMBER(3),
photo BLOB DEFAULT EMPTY_CLOB(),
cv CLOB DEFAULT NULL,
ext_file BFILE DEFAULT NULL)
/
```
Identify the issue in the above script.
1. The table is created successfully.
2. It generates an error because a BLOB column cannot be initialized with EMPTY_CLOB().
3. It generates an error because DEFAULT cannot be set to NULL for a CLOB column during table creation.
4. It generates an error because DEFAULT cannot be set to NULL for a BFILE column during table creation.
Identify the correct statements about the initialization of LOBs.
1. An internal LOB cannot be initialized in the CREATE TABLE statement.
2. The BFILE...