Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Advanced Oracle PL/SQL Developer's Guide (Second Edition)
  • Table Of Contents Toc
  • Feedback & Rating feedback
Advanced Oracle PL/SQL Developer's Guide (Second Edition)

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

By : Saurabh K. Gupta
4.9 (9)
Buy this Book
close
close
Advanced Oracle PL/SQL Developer's Guide (Second Edition)

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

4.9 (9)
By: Saurabh K. Gupta
Buy this Book

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.
Table of Contents (14 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Free Chapter
1
1. Overview of PL/SQL Programming Concepts
chevron up
Icon
1. Overview of PL/SQL Programming Concepts
Icon
Introduction to PL/SQL
Icon
Cursors – an overview
Icon
Exception handling in PL/SQL
Icon
Creating stored procedures
Icon
Functions
Icon
A PL/SQL package
Icon
Oracle Database 12c enhancements to PL/SQL subprograms
Icon
Managing database dependencies
Icon
Reviewing Oracle-supplied packages
Icon
Oracle SQL Developer
Icon
Summary
Icon
Practice exercise
2
2. Oracle 12c SQL and PL/SQL New Features
Icon
2. Oracle 12c SQL and PL/SQL New Features
Icon
Database consolidation and the new Multitenant architecture
Icon
Oracle 12c SQL and PL/SQL new features
Icon
The Oracle Database 12c (12.1.0.2) In-Memory option
Icon
Summary
3
3. Designing PL/SQL Code
Icon
3. Designing PL/SQL Code
Icon
Cursor structures
Icon
Cursor variables
Icon
Implicit statement results in Oracle Database 12c
Icon
Subtypes
Icon
Summary
Icon
Practice exercise
4
4. Using Collections
Icon
4. Using Collections
Icon
Introduction to collections
Icon
Collection types
Icon
Associative arrays
Icon
Nested tables
Icon
Varray
Icon
Comparing the collection types
Icon
Oracle 12c enhancements to collections
Icon
PL/SQL collection methods
Icon
Summary
Icon
Practice exercise
5
5. Using Advanced Interface Methods
Icon
5. Using Advanced Interface Methods
Icon
Overview of External Procedures
Icon
Components of external procedure execution flow
Icon
Executing external C programs from PL/SQL
Icon
Securing External Procedures with Oracle Database 12c
Icon
Executing Java programs from PL/SQL
Icon
Summary
Icon
Practice exercise
6
6. Virtual Private Database
Icon
6. Virtual Private Database
Icon
Oracle Database Security overview
Icon
Fine-Grained Access Control
Icon
Virtual Private Database
Icon
Virtual Private Database metadata
Icon
Oracle Database 12c Security enhancements
Icon
Summary
Icon
Practice exercise
7
7. Oracle SecureFiles
Icon
7. Oracle SecureFiles
Icon
Introduction to Large Objects
Icon
LOB data types in Oracle
Icon
Oracle SecureFiles
Icon
Working with LOBs
Icon
Migrating LONG to LOBs
Icon
Migrating BasicFiles to SecureFiles
Icon
Oracle Database 12c enhancements to SecureFiles
Icon
Summary
Icon
Practice exercise
8
8. Tuning the PL/SQL Code
Icon
8. Tuning the PL/SQL Code
Icon
The PL/SQL Compiler
Icon
Native and interpreted compilation techniques
Icon
Tuning PL/SQL code
Icon
Summary
Icon
Practice exercise
9
9. Result Cache
Icon
9. Result Cache
Icon
Oracle Database 11g Result Cache
Icon
SQL query Result Cache
Icon
PL/SQL Function Result Cache
Icon
OCI Client results cache
Icon
The DBMS_RESULT_CACHE package
Icon
Result cache in Real Application Clusters
Icon
Summary
Icon
Practice exercise
10
10. Analyzing, Profiling, and Tracing PL/SQL Code
Icon
10. Analyzing, Profiling, and Tracing PL/SQL Code
Icon
A sample PL/SQL program
Icon
Tracking PL/SQL coding information
Icon
Determining identifier types and usages
Icon
The DBMS_METADATA package
Icon
Tracing PL/SQL programs using DBMS_TRACE
Icon
Profiling PL/SQL code
Icon
Summary
Icon
Practice exercise
11
11. Safeguarding PL/SQL Code against SQL injection
Icon
11. Safeguarding PL/SQL Code against SQL injection
Icon
What is SQL injection?
Icon
Preventing SQL injection attacks
Icon
Testing the code for SQL injection flaws
Icon
Summary
Icon
Practice exercise
12
12. Working with Oracle SQL Developer
Icon
12. Working with Oracle SQL Developer
Icon
An overview of SQL Developer
Icon
Getting started with SQL Developer
Icon
SQL Developer 4.0 and 4.1 New Features
Icon
Summary
13
Index
Icon
Index

Reviewing Oracle-supplied packages

Oracle-supplied packages exist as prebuilt programs in the database as wrapper code. These packages not only help database developers work on extended functionalities but also reduce writing extensive and complex code. The use of the Oracle-supplied API is always recommended as it improves code standardization.

The scripts for these packages are available in the $ORACLE_HOME\RDBMS\ADMIN\ folder. All packages reside on the database server. Public synonyms are available (or can be created too) for these packages so that the packages are accessible to the database users. Oracle 12c adds multiple packages to the Oracle-supplied PL/SQL package list. The latest additions provide PL/SQL interfaces for the new functionalities that have been added to the database release.

Some of the important Oracle-supplied packages are listed as follows:

  • DBMS_ALERT: This package is used for the notification of database events.
  • DBMS_LOCK: This package is used for managing lock operations (lock, conversion, and release) in PL/SQL applications.
  • DBMS_SESSION: This package is used to set session level preferences from PL/SQL programs (similar to ALTER SESSION).
  • DBMS_OUTPUT: This package is one of the most frequently used built-ins for buffering data messages and displaying debug information.
  • DBMS_HTTP: This package is used for HTTP callouts.
  • UTL_FILE: This package is used for reading, writing, and performing other file operations on the server.
  • UTL_MAIL: This package is used to compose and send mails.
  • DBMS_SCHEDULER: This package is used for scheduling execution of stored procedures at a given time.
  • DBMS_PARALLEL_EXECUTE: This package can be used to execute a user-defined task in parallel. If the PL/SQL block is running a large update on a table, the package can be used to enable the parallel execution of the task by splitting it into chunks.
  • DBMS_PRIVILEGE_CAPTURE: This package is introduced in Oracle Database 12c to set a policy in order to capture the usage of privileges (object and system) in respect to users. It helps in controlling excess privileges for users.
  • DBMS_REDACT: This package is introduced in Oracle Database 12c to create redaction policies, in order to mask data based on user authorization.
  • DBMS_RESOURCE_MANAGER: This package is used to create consumer groups, directives, and resource manager plans for containers as well as a pluggable database. The resource manager plan determines the resources allocated to a pluggable database, a schema, or a task.
  • DBMS_DATAPUMP: This package is used to move data, metadata, or both from one database to another. The source and target databases can be on different platforms.
  • DBMS_PDB: This package is introduced in Oracle Database 12c to generate or analyze the integration properties of a pluggable database for unplug/plug operations.
  • DBMS_SQL: This package enables dynamic SQL in PL/SQL. You can run DML or DDL statements using DBMS_SQL from a PL/SQL block.
  • DBMS_REDEFINITION: This package is used to perform online redefinition tasks for tables.
  • DBMS_UTILITY: This package is used to accomplish many utility operations such as analyze object, compile schema, get dependency, resolve a given name, validate database objects, format call and error stack, expand SQL text, and retrieve current database version.

Based on the objective to be achieved, the packages can be categorized as follows:

  • Standard application development: Many of the DBMS packages provide an application interface for database features. For example, DBMS_REDACT provides an interface to create and manage redaction policies. Similarly, DBMS_UTILITY provides subprograms to retrieve instance or database information, call stacks and error stacks, and analyze and validate objects. The DBMS_OUTPUT package is one of the packages most frequently used to display text messages. It can be efficiently used for tracing and debugging purposes. Accessing and writing operating system files was made possible through UTL_FILE.
  • General usage and application administration: Oracle has many packages for monitoring applications and users. Statistics generation, load history, and space management are the key objectives accomplished by these packages. DBMS_UTILITY comprises subprograms for general usage.
  • Internal support packages: Oracle maintains these packages for its own use.
  • Transaction processing packages: Oracle provides utility packages that enable the monitoring of transaction stages. Though they are rarely used, they can efficiently ensure transparent and smooth transactions. For example, DBMS_TRANSACTION is used to access SQL transactions from stored subprograms. DBMS_PARALLEL_EXECUTE executes a large update in parallel by splitting it into small tasks.
End of Section 10
Next Section

Create a Note

Modal Close icon
You need to login to use this feature.
notes
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Delete Note

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY