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.

Related Content you might be interested in

Current Title:

Small book image
Oracle Advanced PL/SQL Developer Professional Guide
Saurabh K. Gupta
May, 2012 | 440 pages
No titles found
Table of Contents (22 chapters)
Oracle Advanced PL/SQL Developer Professional Guide
Oracle Advanced PL/SQL Developer Professional Guide
Credits
Credits
Foreword
Foreword
About the Author
About the Author
Acknowledgement
Acknowledgement
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
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
2
Designing PL/SQL Code
Designing PL/SQL Code
Understanding cursor structures
Cursor variables
Subtypes
Summary
Practice exercise
3
Using Collections
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
4
Using Advanced Interface Methods
Using Advanced Interface Methods
Understanding external routines
Executing external C programs from PL/SQL
Executing Java programs from PL/SQL
Summary
Practice exercise
5
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
Summary
Practice exercise
6
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
Summary
Practice exercise
7
Using SecureFile LOBs
Using SecureFile LOBs
Introduction to SecureFiles
Working with SecureFiles
Migration from BasicFiles to SecureFiles
Summary
Practice exercise
8
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
PRAGMA INLINE
Summary
Practice exercise
9
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
Summary
Practice exercise
10
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
Summary
Practice exercise
11
Profiling and Tracing PL/SQL Code
Profiling and Tracing PL/SQL Code
Tracing the PL/SQL programs
Profiling the PL/SQL programs
Summary
Practice exercise
12
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
Summary
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
Index

Practice exercise

  1. Which of the following features are not available in SQL Developer?

    1. Query builder

    2. Database export and import

    3. Database backup and recovery functions

    4. Code Subversion repository

  2. For a function to be called from the SQL expression, which of the following conditions should it obey:

    1. A function in the SELECT statement should not contain DML statements.

    2. The function should return a value.

    3. A function in the UPDATE or DELETE statement should not query the same table.

    4. A function called from SQL expressions cannot contain the TCL (COMMIT or ROLLBACK) command or the DDL (CREATE or ALTER) command.

  3. The following query is executed in the ORADEV schema:

      SELECT NAME, referenced_owner, referenced_name 
  FROM all_dependencies
  WHERE owner = USER
  AND referenced_type IN ('TABLE', 'VIEW')
  AND referenced_owner IN ('SYS')
  ORDER BY owner, NAME, referenced_owner, referenced_name;

    Which statement is true about the output of this query?

    1. It displays the schema objects created by the user ORADEV which use a table or view owned by SYS.

    2. Exception occurs as user ORADEV has insufficient privileges to access ALL_DEPENDENCIES view.

    3. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.

    4. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.

  4. Which of the following is true about PL/SQL blocks?

    1. Exception is a mandatory section without which an anonymous PL/SQL block fails to compile.

    2. Bind variables cannot be referred inside a PL/SQL block.

    3. The scope and visibility of the variables declared in the declarative section of the block is within the current block only.

    4. The RAISE_APPLICATION_ERROR procedure maps a predefined error message to a customized error code.

  5. From the following options, identify the ways of defining exceptions:

    1. Declare a EXCEPTION variable and raise it using the RAISE statement.

    2. Use PRAGMA EXCEPTION_INIT to associate a customized exception message to a pre-defined Oracle error number.

    3. Declare a EXCEPTION variable and use it in RAISE_APPLICATION_ERROR.

    4. Use RAISE_APPLICATION_ERROR to create a dynamic exception at any stage within the executable or exception section of a PL/SQL block.

  6. Chose the differences between procedures and functions:

    1. A function must mandatorily return a value, while a procedure may or may not.

    2. A function can be called from SQL queries, while a procedure can never be invoked from SQL.

    3. A function can accept parameters passed by value, while a procedure can accept parameters as passed by reference only.

    4. A standalone function can be overloaded but a procedure cannot.

  7. Examine the values of the cursor attribute for the following query and pick the attribute with the wrong value:

      BEGIN
  …
  SELECT ENAME, SAL
  INTO L_ENAME, L_SAL
  FROM EMPLOYEES
  WHERE EMPID = 7900;
  …
  END;

    1. SQL%ROWCOUNT = 1

    2. SQL%ISOPEN = FALSE

    3. SQL%FOUND = FALSE

    4. SQL%NOTFOUND = FALSE

Previous Section
End of Chapter 1
Next Chapter