Declaring PL/SQL Variables

• Recognize valid and invalid identifiers
• List the uses of variables, declare and initialize variables, use bind variables
• List and describe various data types using the %TYPE and %ROWTYPE attributes

Writing Executable Statements

• Identify lexical units in a PL/SQL block
  
• Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
• Describe when implicit conversions take place and when explicit conversions have to be dealt with
• Write nested blocks and qualify variables with labels
• Write readable code with appropriate indentation

Writing SQL in PL/SQL

• Create PL/SQL executable blocks using DML and transaction control statements
  
• Make use of the INTO clause to hold the values returned by a SQL statement

Writing Control Structures

• Identify the uses and types of control structures (IF, CASE statements and expressions)
  
• Construct and identify loop statements
• Use EXIT and CONTINUE statements inside loops

Working with Composite Data Types

• Create user-defined PL/SQL records
  
• Create a record with the %ROWTYPE attribute
• Create an INDEX BY table and INDEX BY table of records
• Describe the differences among records, collections, and collections of records
• Initialize collections and records

Using Explicit Cursors

• Distinguish between implicit and explicit cursors and use SQL cursor attributes
  
• Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
• Declare and use cursors with parameters
• Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause

Handling Exceptions

• Define PL/SQL exceptions
  
• Recognize unhandled exceptions
• Handle different types of exceptions (internally defined exceptions, predefined exceptions and user-defined exceptions)
• Propagate exceptions

Using PL/SQL Subprograms

• Differentiate between anonymous blocks and subprograms
  
• Create a simple procedure and invoke it from an anonymous block
• Identify benefits of subprograms

Creating Procedures and Using Parameters

• Create a procedure with parameterrs
  
• Use named notation
• Work with procedures (create, invoke and remove procedures)
• Handle exceptions in procedures and display a procedure's information

Creating Functions

• Differentiate between a procedure and a function
  
• Describe the uses of functions
• Work with functions (create, invoke and remove functions)

Creating Packages

• Identify the benefits and the components of packages
  
• Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)
• Overload package subprograms and use forward declarations

Working with Packages

• Use package types and variables
  
• Use packaged constants and functions in SQL
• Use ACCESSIBLE BY to restrict access to package subprograms

Using Dynamic SQL

• Describe the execution flow of SQL statements
  
• Use Native Dynamic SQL (NDS)
• Bind PL/SQL types in SQL statements

Design Considerations for PL/SQL Code

• Create standard constants and exceptions
  
• Write and call local subprograms
• Control the run-time privileges of a subprogram
• Perform autonomous transactions
• Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause
• Use bulk binding and the RETURNING clause with DML

Creating Compound, DDL, and Event Database Triggers

• Create triggers on DDL statements
  
• Create triggers on system events
• Describe different types of triggers and their uses

Using the PL/SQL Compiler

• Describe the PL/SQL compiler and features
  
• Use the PL/SQL compiler initialization parameters
• Use the PL/SQL compile time warnings

Managing PL/SQL Code

• Describe and use conditional compilation
  
• Code-based access control: granting roles to program units
• Whitelist code access with the ACCESSIBLE BY clause
• Mark code as deprecated

Managing Dependencies

• Track and manage procedural dependencies