Book Image

Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition

By : Saurabh K. Gupta
Book Image

Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition

By: Saurabh K. Gupta

Overview of this book

Oracle Database is one of the most popular databases and allows users to make efficient use of their resources and to enhance service levels while reducing the IT costs incurred. Oracle Database is sometimes compared with Microsoft SQL Server, however, Oracle Database clearly supersedes SQL server in terms of high availability and addressing planned and unplanned downtime. Oracle PL/SQL provides a rich platform for application developers to code and build scalable database applications and introduces multiple new features and enhancements to improve development experience. Advanced Oracle PL/SQL Developer's Guide, Second Edition is a handy technical reference for seasoned professionals in the database development space. This book starts with a refresher of fundamental concepts of PL/SQL, such as anonymous block, subprograms, and exceptions, and prepares you for the upcoming advanced concepts. The next chapter introduces you to the new features of Oracle Database 12c, not limited to PL/SQL. In this chapter, you will understand some of the most talked about features such as Multitenant and Database In-Memory. Moving forward, each chapter introduces advanced concepts with the help of demonstrations, and provides you with the latest update from Oracle Database 12c context. This helps you to visualize the pre- and post-applications of a feature over the database releases. By the end of this book, you will have become an expert in PL/SQL programming and will be able to implement advanced concepts of PL/SQL for efficient management of Oracle Database.

Related Content you might be interested in

Current Title:

Small book image
Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition
Saurabh K. Gupta
Feb, 2016 | 428 pages
No titles found
Table of Contents (19 chapters)
Advanced Oracle PL/SQL Developer's Guide Second Edition
Advanced Oracle PL/SQL Developer's Guide Second Edition
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Overview of PL/SQL Programming Concepts
Overview of PL/SQL Programming Concepts
Introduction to PL/SQL
Cursors – an overview
Exception handling in PL/SQL
Creating stored procedures
Functions
A PL/SQL package
Oracle Database 12c enhancements to PL/SQL subprograms
Managing database dependencies
Reviewing Oracle-supplied packages
Oracle SQL Developer
Summary
Practice exercise
2
Oracle 12c SQL and PL/SQL New Features
Oracle 12c SQL and PL/SQL New Features
Database consolidation and the new Multitenant architecture
Oracle 12c SQL and PL/SQL new features
The Oracle Database 12c (12.1.0.2) In-Memory option
Summary
3
Designing PL/SQL Code
Designing PL/SQL Code
Cursor structures
Cursor variables
Implicit statement results in Oracle Database 12c
Subtypes
Summary
Practice exercise
4
Using Collections
Using Collections
Introduction to collections
Collection types
Associative arrays
Nested tables
Varray
Comparing the collection types
Oracle 12c enhancements to collections
PL/SQL collection methods
Summary
Practice exercise
5
Using Advanced Interface Methods
Using Advanced Interface Methods
Overview of External Procedures
Components of external procedure execution flow
Executing external C programs from PL/SQL
Securing External Procedures with Oracle Database 12c
Executing Java programs from PL/SQL
Summary
Practice exercise
6
Virtual Private Database
Virtual Private Database
Oracle Database Security overview
Fine-Grained Access Control
Virtual Private Database
Virtual Private Database metadata
Oracle Database 12c Security enhancements
Summary
Practice exercise
7
Oracle SecureFiles
Oracle SecureFiles
Introduction to Large Objects
LOB data types in Oracle
Oracle SecureFiles
Working with LOBs
Migrating LONG to LOBs
Migrating BasicFiles to SecureFiles
Oracle Database 12c enhancements to SecureFiles
Summary
Practice exercise
8
Tuning the PL/SQL Code
Tuning the PL/SQL Code
The PL/SQL Compiler
Native and interpreted compilation techniques
Tuning PL/SQL code
Summary
Practice exercise
9
Result Cache
Result Cache
Oracle Database 11g Result Cache
SQL query Result Cache
PL/SQL Function Result Cache
OCI Client results cache
The DBMS_RESULT_CACHE package
Result cache in Real Application Clusters
Summary
Practice exercise
10
Analyzing, Profiling, and Tracing PL/SQL Code
Analyzing, Profiling, and Tracing PL/SQL Code
A sample PL/SQL program
Tracking PL/SQL coding information
Determining identifier types and usages
The DBMS_METADATA package
Tracing PL/SQL programs using DBMS_TRACE
Profiling PL/SQL code
Summary
Practice exercise
11
Safeguarding PL/SQL Code against SQL injection
Safeguarding PL/SQL Code against SQL injection
What is SQL injection?
Preventing SQL injection attacks
Testing the code for SQL injection flaws
Summary
Practice exercise
12
Working with Oracle SQL Developer
Working with Oracle SQL Developer
An overview of SQL Developer
Getting started with SQL Developer
SQL Developer 4.0 and 4.1 New Features
Summary
Index
Index

Determining identifier types and usages

A lexical unit in a PL/SQL program code is built up using literals, identifiers, delimiters, and comments. All items that are declared in a PL/SQL program as variables, cursors, constants, and subprogram names are identifiers. Identifiers can be reserved words (such as BEGIN and END), predefined (declared globally within STANDARD package), or quoted.

USER_IDENTIFIERS

The USER_IDENTIFIERS dictionary view reports the usage of identifiers in a PL/SQL program unit. The view includes information about an identifier's name, its type, and usage by line number in a PL/SQL program.

Tracking identifier details for all the subprograms would be additional task during code compilation and therefore, it is collected only for enabled PL/SQL objects.

The structure of the USER_IDENTIFIERS view is as follows:

Name          Null?      Type
------------- ---------- ------------------------
NAME                     VARCHAR2(128)
SIGNATURE                VARCHAR2(32)
TYPE  ...
Previous Section
End of Section 4
Next Section