Oracle 11g: Programing with PL/SQL eLearning

Oracle Database 11g: PL/SQL Fundamentals

1. Oracle Database 11g: PL/SQL Variables, Attributes, and Executable Statements

• Features and capabilities of SQL Developer, SQL*Plus, and JDeveloper
• Features and benefits of PL/SQL and the steps for building basic PL/SQL blocks
• Declaring and initializing PL/SQL variables
• Uses of different PL/SQL data types, the %TYPE attribute, and BIND variables
• Constructing executable statements using various PL/SQL block elements
• Evaluate a block of PL/SQL code with respect to scoping and nesting rules and write and test PL/SQL blocks

2. Oracle Database 11g: Using DML, DDL, Cursors, Control Structures, and Loops

• Performing basic data manipulation and transaction control in PL/SQL and determining the outcome of SQL statements
• Writing control structures, such as IF statements and CASE expressions, in a PL/SQL block
• Writing and identifying loop statements
• Create PL/SQL blocks that incorporate loops and conditional control structures

3. Oracle Database 11g: Using Data Types, INDEX BY Tables, and Cursors in PL/SQL

• Creating user-defined PL/SQL records and records with the %ROWTYPE attribute
• Creating an INDEX BY table and table of records and the uses of nested tables and VARRAYs
• Define, create, and use INDEX BY tables and a PL/SQL record in a given scenario
• Declaring and controlling explicit cursors and writing FOR loops to fetch data from them
• Declaring and using cursors with parameters, locking rows, and referencing the current row in an explicit cursor

4. Oracle Database 11g: Working with Exceptions, Subprograms, JDeveloper, and REF Cursors

• Trapping non-predefined exceptions in PL/SQL
• Trapping user-defined exceptions
• Create an exception handler in a given scenario
• Creating and invoking simple stored procedures and functions
• Scope and capabilities of JDeveloper for developing and deploying Java applications and Web services
• Using cursor variables to pass query result sets and fetching rows from them

Oracle Database 11g: Program with PL/SQL

1. Oracle Database 11g: Getting Started With PL/SQL

• Features and capabilities of SQL Developer, SQL*Plus, and JDeveloper
• Features and benefits of PL/SQL and the steps for building basic PL/SQL blocks
• Steps for declaring and initializing PL/SQL variables
• Uses of different PL/SQL data types, the %TYPE attribute, and BIND variables
• Constructing executable statements using various PL/SQL block elements
• Evaluate a block of PL/SQL code with respect to scoping and nesting rules and write and test PL/SQL blocks

2. Oracle Database 11g: Using DML, DDL, Cursors, Loops, and Control Structures

• Performing basic data manipulation and transaction control in PL/SQL and determining the outcome of SQL statements
• Writing control structures, such as IF statements and CASE expressions, in a PL/SQL block
• Writing and identifying loop statements
• Create PL/SQL blocks that incorporate loops and conditional control structures

3. Oracle Database 11g: Using Data Types, INDEX BY Tables, and Cursors

• Creating user-defined PL/SQL records and records with the %ROWTYPE attribute
• Creating an INDEX BY table and table of records and the uses of nested tables and VARRAYs
• Define, create, and use INDEX BY tables and a PL/SQL record in a given scenario
• Declaring and controlling explicit cursors and for writing FOR loops to fetch data from them
• Declaring and using cursors with parameters, locking rows, and referencing the current row in an explicit cursor

4. Oracle Database 11g: Using Exceptions, Subprograms, JDeveloper, and REF Cursors

• Trapping non-predefined exceptions in PL/SQL
• Trapping user-defined exceptions
• Create an exception handler in a given scenario
• Creating and invoking simple stored procedures and functions
• Scope and capabilities of JDeveloper for developing and deploying Java applications and Web services
• Using cursor variables to pass query result sets and fetching rows from them

5. Oracle Database 11g: Subprograms, Packages, and Exception Handling

• Benefits of modularized and layered subprogram design
• Creating and calling procedures and passing parameters
• Handling exceptions in procedures and for dropping procedures
• Creating and invoking functions
• Using SQL Developer to debug PL/SQL subprograms
• Benefits and components of a package and the steps for creating one
• Create a package specification and package body in a given scenario and invoke the package’s constructs

6. Oracle Database 11g: Packages, Dynamic SQL, and Coding Considerations

• Overloading subprograms, using forward declarations, writing package initialization blocks, and maintaining persistent package state
• Using the Oracle-supplied packages DBMS_OUTPUT, UTL_FILE, and UTL_MAIL
• Using Oracle-supplied packages to generate a simple Web page and schedule PL/SQL code for execution
• Constructing and executing SQL statements at run time using native dynamic SQL statements in PL/SQL
• Alter a package so that it contains overloaded subprograms, use an Oracle-supplied package, and execute dynamic SQL in a given scenario
• Designing packages so that code is easy to maintain, efficient, and readable, and so that PL/SQL applications perform better
• Binding whole arrays of values in a single operation and retrieving information about a row affected by a SQL operation

7. Oracle Database 11g: Using Triggers and the PL/SQL Compiler

• Creating and using database triggers
• Enabling, disabling, testing, managing, and removing database triggers
• Creating and using compound, DDL, and event database triggers
• Create statement and row triggers, including a trigger for a business rule and a package with triggers that resolve a mutating table issue
• Benefits and capabilities of the PL/SQL compiler and its parameters
• Using the PL/SQL compiler initialization parameters, compiling a procedure, and managing compiler warnings

8. Oracle Database 11g: PL/SQL Code and Dependency Management

• Selectively including PL/SQL source code by using conditional compilation, and for wrapping a PL/SQL unit
• Predicting how changing a database object affects procedures and functions by tracking procedural dependencies
• Managing remote dependencies and recompiling a PL/SQL program unit
• Use conditional compilation, wrap PL/SQL code, a