Oracle^® PL/SQL Programming: Hands-On

What is this course about?

This course provides comprehensive coverage of PL/SQL, Oracle's main programming language. PL/SQL is used to write stored procedures, triggers and other code stored in the powerful Oracle database. In addition, Oracle also uses this language in their frontend development environment, Developer, as well as their Web-based engine, Oracle Application Server. This course teaches you how to write PL/SQL programs to solve realistic business problems using Oracle's most strategic language. The development of an API (Application Programming Interface) for frontend code using an application framework for PL/SQL is emphasized.

What are SQL and PL/SQL?

SQL is a standard language used for accessing relational databases. Different relational database products have different procedural extensions to SQL. PL/SQL is Oracle's programming language, which provides procedural extensions in all Oracle environments. PL/SQL stands for Procedural Language (PL) extensions to Structured Query Language (SQL).

What background should I have?

A basic knowledge of SQL and PL/SQL are assumed. General database and programming experience is recommended. If you have attended Course 593: Oracle^® Database 10g: A Comprehensive Hands-On Introduction, Course 925: SQL Programming Language: A Comprehensive Hands-On Introduction, or Course 926: Oracle^® Database 11g: A Comprehensive Hands-On Introduction, you should be sufficiently prepared for this course.

Who will benefit from this course?

This course benefits those involved in programming with PL/SQL, including developers, database administrators, programmers, analysts and managers.

I have no programming experience. Will this course be appropriate for me?

As this is not a course on the concepts of programming, a novice programmer will struggle. However, a working knowledge of SQL and Oracle will enable the motivated participant to succeed in their first step toward becoming an Oracle PL/SQL developer.

Will I learn SQL in this course?

No. This course covers PL/SQL, Oracle's programming language. The course assumes basic knowledge in SQL. If you are interested in learning SQL, consider Course 925: SQL Programming Language: A Comprehensive Hands-On Introduction, Course 593: Oracle^® Database 10g: A Comprehensive Hands-On Introduction, or Course 926: Oracle^® Database 11g: A Comprehensive Hands-On Introduction for introductory information. For more depth on SQL, consider Course 234: Developing SQL Queries for Oracle^® Databases: Hands-On.

What version of Oracle PL/SQL is used?

This course uses Oracle 11g and PL/SQL 11g. Because PL/SQL is the standard programming language across all Oracle environments, this course would be applicable even if you are using an earlier version of Oracle. Recent features are identified by release using a system of icons ("8I", "9I", "10g", "11g").

What operating system is used as the basis for this course?

The operating system used in class is Windows. The course, however, is entirely independent of the operating system and is equally applicable for those using Oracle on alternative platforms.

I am moving from another database to Oracle. Will this course apply to me?

Yes! Each vendor's product has a different procedural language; PL/SQL is specific to Oracle. If you are coming from another database and plan to use stored procedures, triggers or other server-side programs in Oracle, this course is exactly what you need.

How much time is spent on each topic?

What kinds of workshop exercises are included?

More than 40% of the course is dedicated to the hands-on development of PL/SQL code. Each exercise is designed to reinforce the lecture material, using realistic problem statements. Specific hands-on exercises include:

• Writing simple blocks utilizing variable definitions and flow control constructs
• Accessing the database to retrieve and maintain data
• Performing complex data manipulations with cursors
• Coding dynamic SQL using Oracle's latest native extension
• Debugging and testing PL/SQL blocks
• Handling EXCEPTIONs in a structured manner
• Storing procedures, functions and packages in the database
• Writing user-defined SQL functions
• Grouping stored code in packages
• Creating triggers to handle data integrity and data derivation
• Handling complex data types with PL/SQL tables

What version of Oracle is used?

This course is currently running on Oracle11g, Release 2, on the Windows platform.

Why should I take this course instead of one offered by Oracle?

Learning Tree is dedicated to helping accelerate the careers of our clients by providing them with world-class technology and management training. Our independence from Oracle (and any other vendor) allows us to give an unbiased view of what works and what does not. This course is objective in its approach, having been developed independently from Oracle by industry experts who bring their real-world experience to the classroom.

How does this course relate to other Learning Tree courses?

• 492: Application Development and Tuning for Oracle® Databases: Hands-On  provides extensive knowledge and skills needed to create powerful and secure Oracle database applications
• 593: Oracle® Database 10g: A Comprehensive Hands-On Introduction  , offers a comprehensive introduction to the relational features of Oracle Database 10g
• 234: Developing SQL Queries for Oracle® Databases: Hands-On , offers the skills needed to write robust, reliable and efficient SQL queries

• 382: Relational Database Design, Tools and Techniques: Hands-On , provides a strong foundation for designing, building and working with relational databases

You also might be interested in these Learning Tree courses:

• 925: SQL Programming Language: A Comprehensive Hands-On Introduction 
• 926: Oracle® Database 11g: A Comprehensive Hands-On Introduction 
• 927: Oracle® Database 11g Administration: Hands-On 
• 928: Hands-On Oracle® Database 11g: Backup, Recovery and Server Tuning 

US Dates