This comprehensive course introduces Oracle database programming using the PL/SQL programming language. You'll learn the syntax, structure, and features of this fundamental Oracle skill set, progressing from introductory topics to advanced application design and high-performance programming. PL/SQL is considered essential for any Oracle professional, nearly as important as SQL itself.
Database-Resident Program Units and Applications:
The course focuses on employing PL/SQL in database application development, with particular emphasis on database-resident stored program units including procedures, functions, packages, and database triggers. New features from Oracle 12c, 18c, and 19c releases are explained and demonstrated throughout. This foundation prepares you to develop sophisticated database applications using modern Oracle technologies.
Target Audience and Prerequisites:
This course serves all Oracle professionals, including application designers, database developers, database administrators, and web server administrators. You should have completed Oracle Database 12c: SQL Fundamentals before beginning this course.
Course Objectives and Coverage:
The course begins by explaining the intent and usage of PL/SQL for database applications, covering important reasons to incorporate PL/SQL modules during initial design phases. You'll learn to build executable PL/SQL program units, understanding major program segments and their interactions during execution, including error and exception handling. Advanced topics explore modular and reusable program development, increasing productivity and application maintainability. You'll understand how database-resident program units function as part of overall database architecture and their realized benefits. The course covers developing PL/SQL packages, exploring advanced database programming capabilities, and using database triggers in sophisticated application design.
Certification Relevance:
This course covers topics applicable to Oracle PL/SQL Developer Certified Associate (OCA) and Oracle Advanced PL/SQL Developer Certified Professional (OCP) certification, specifically for "Exam 1Z0-144: Program With PL/SQL".
Core Topics and Sections:
Database Interface Selection and Setup covers available tools, selection criteria, Oracle Net connections, Oracle PaaS database connections, and setup for SQL Developer, SQL Plus, and JDeveloper.
Bind and Substitution Variables explains usage with SQL Developer and SQL Plus.
Choosing a Database Programming Language covers database programming concepts, PL/SQL performance advantages, and integration with other languages.
PL/SQL Language Fundamentals includes program structure, language syntax rules, embedding SQL, writing readable code, database output generation, and SQL Plus input methods.
Declare Section covers the declare section, primitive type declaration, declaration options, NOT NULL constraint, CONSTANT declaration, data dictionary integration, percent type syntax, and user-defined types, including tables and records.
Begin Section covers program data manipulation, logic control, branching statements including GOTO, LOOP, IF-THEN-ELSE, and CASE statements.
Exception Section teaches exception isolation, pragma exception initialization, SQLCODE and SQLERRM usage, and SQL percent rowcount techniques.
Beyond the Basics: Explicit Cursors covers explicit cursor techniques, FOR UPDATE clauses, WHERE CURRENT OF clauses, and FOR loop cursor usage.
Database-Resident Program Units introduces stored program concepts, physical storage and execution, unit types, advantages, and modular design principles.
Creating and Executing Stored Procedures and Functions covers procedure and function creation, parameter specification, default clauses, system and object privileges, development tool usage, calling procedures and functions, unit testing approaches, and calling functions from SQL.
Maintaining Program Units covers recompilation, mass recompilation, dropping procedures and functions, and data dictionary metadata usage, including USER_OBJECTS, USER_SOURCE, USER_ERRORS, USER_OBJECT_SIZE, and USER_DEPENDENCIES.
Managing Dependencies covers dependency internals, tracking dependencies, dependency tracking utilities, SQL Developer dependency information, and dependency strategy checklists.
Creating and Maintaining Packages covers package creation and maintenance, performance considerations, definer and invoker rights, white lists and accessible clauses, persistent global objects, initialization logic, and object orientation support.
Advanced Cursor Techniques covers cursor variables, SYS_REFCURSOR usage, and cursor expressions.
System-Supplied Packages covers DBMS_OUTPUT, UTL_FILE, and FOPEN examples.
Database Trigger Concepts and Creation covers trigger types, execution mechanisms, statement-level triggers, raising application errors, row-level triggers, trigger examples, and INSTEAD OF triggers.
Maintaining and Implementing Triggers covers trigger maintenance, error handling, dropping and altering triggers, multiple triggers per table, mutating table issue resolution, system event triggers, scope definition, available events, and event attributes.
