Name: Oracle Program with PL/SQL
Price: 30 GBP
Availability: InStock

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. Creating Functions

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

11. 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

12. Working with Packages

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

13. Using Dynamic SQL

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

14. 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

15. 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

16. 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

17. 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

18. Managing Dependencies

Track and manage procedural dependencies

Join us: to stay updated on new courses, discounts, free learning opportunities, and more

Join us toGrow Skills,together!

Get Updates

Oracle Program with PL/SQL

What you will learn?

1. Declaring PL/SQL Variables

2. Writing Executable Statements

3. Writing SQL in PL/SQL

4. Writing Control Structures

5. Working with Composite Data Types

6. Using Explicit Cursors

7. Handling Exceptions

8. Using PL/SQL Subprograms

9. Creating Procedures and Using Parameters

10. Creating Functions

11. Creating Packages

12. Working with Packages

13. Using Dynamic SQL

14. Design Considerations for PL/SQL Code

15. Creating Compound, DDL, and Event Database Triggers

16. Using the PL/SQL Compiler

17. Managing PL/SQL Code

18. Managing Dependencies

Join us to
Grow Skills,
together!