Oracle 12c PL/SQL Fundamentals

Duration

5 Days

30 CPD hours

About this course

This course is intended for individuals who are Application designers and database developers, database administrators and web server administrators.

Overview

Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language.

Description

In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c.

Selection & Setup of the Database Interface

Considering Available Tools
Selecting the Appropriate Tool
Oracle Net Database Connections
Oracle PAAS Database Connections
Setup SQL Developer
Setup SQL*Plus
Setup Jdeveloper

About Bind & Substitution Variables

Using SQL Developer
Using SQL*Plus

Choosing a Database Programming Language

What is Database Programming
PLSQL Performance Advantages
Integration wth Other Languages

PL/SQL Language Fundamentals

PL/SQL Program Structure
LANGUAGE SYNTAX RULES
EMBEDDING SQL
WRITING READABLE CODE
GENERATING DATABASE OUTPUT
SQL*PLUS INPUT OF A PROGRAM BLOCK

Declare Section

About the Declare Section
DECLARE PRIMITIVE TYPES
DECLARATION OPTIONS
NOT NULL
CONSTANT
DATA DICTIONARY INTEGRATION
%TYPE
DECLARE SIMPLE USER---DEFINED TYPES
TYPE... TABLE
TYPE... RECORD
EXTENDED USER---DEFINED TYPES

Begin Section

About the Begin Section
Manipulating Program Data
Logic Control & Branching
GOTO
LOOP
IF-THEN-ELSE
CASE

Exception Section

ABOUT THE EXCEPTION SECTION
ISOLATING THE SPECIFIC EXCEPTION
PRAGMA EXCEPTION_INIT
SQLCODE &SQLERRM Example
SQL%ROWCOUNT &SELECT...INTO

Beyond the Basics: Explicit Cursors

ABOUT EXPLICIT CURSORS
EXTENDED CURSOR TECHNIQUES
FOR UPDATE OF Clause
WHERE CURRENT OF Clause
Using FORLOOP Cursors

Beyond the Basics: Nested BlocksBeyond the Basics: Declared Subprograms

USING DECLARED SUBPROGRAMS
DECLARED PROCEDURE
DECLARED FUNCTION

Introducing Databse-Resident Program Units

ABOUT DATABASE---RESIDENT PROGRAMS
PHYSICAL STORAGE & EXECUTION
TYPES OF STORED PROGRAM UNITS
STORED PROGRAM UNIT ADVANTAGES
MODULAR DESIGN PRINCIPLES

Creating Stored Procedures & Functions

STORED PROCEDURES & FUNCTIONS
CREATE PROCEDURE / CREATE FUNCTION
CREATING PROCEDURES & FUNCTIONS
RAISE_SALARY() Procedure
SALARY_VALID() Function
THE PARAMETER SPECIFICATION
DEFAULT Clause
SYSTEM & OBJECT PRIVILEGES
USING THE DEVELOPMENT TOOLS

Executing Stored Procedures & Functions

CALLING PROCEDURES & FUNCTIONS
UNIT TESTING WITH EXECUTE
ANONYMOUS BLOCK UNIT TESTING
SPECIFYING A PARAMETER NOTATION
SQL WORKSHEET UNIT TESTING
CALLING FUNCTIONS FROM SQL

Maintaining Stored Program Units

RECOMPILING PROGRAMS
Mass Recompilation Using UTL_RECOMP()
DROPPING PROCEDURES & FUNCTIONS
DROP PROCEDURE / FUNCTION
DATA DICTIONARY METADATA
Using USER_OBJECTS
Using USER_SOURCE
Using USER_ERRORS
Using USER_OBJECT_SIZE
Using USER_DEPENDENCIES

Managing Dependencies

DEPENDENCY INTERNALS
TRACKING DEPENDENCIES
THE DEPENDENCY TRACKING UTILITY
SQL DEVELOPER DEPENDENCY INFO
DEPENDENCY STRATEGY CHECKLISTS

Creating & Maintaining Packages

ABOUT PACKAGES
CREATING PACKAGES
MAINTAINING PACKAGES
PERFORMANCE CONSIDERATIONS

Advanced Package Capabilities

DEFINER & INVOKER RIGHTS
WHITE LITS & ACCESSIBLE BY
PERSISTENT GLOBAL OBJECTS
DEFINING INITIALIZATION LOGIC
OBJECT ORIENTATION SUPPORT

Advanced Cursor Techniques

USING CUSROS VARIABLES
USING SYS_REFCURSOR
USING CURSOR EXPRESSIONS

Using System-Supplied Package

DBMS_OUTPUT()
UTL_FILE()
FOPEN() EXAMPLE

Database Trigger Concepts

ABOUT DATABASE TRIGGERS
DML EVENT TRIGGER SUB---TYPES
DATABASE TRIGGER SCENARIO
TRIGGER EXECUTION MECHANISMS
TRIGGERS WITHIN SQL WORKSHEET

Creating Database Triggers

STATEMENT-LEVEL TRIGGERS
Using RAISE_APPLICATION_ERROR()
ROW---LEVEL TRIGGERS
EXAMPLES OF TRIGGERS
EMPLOYEE_SALARY_CHECK Example
EMPLOYEE_JOURNAL Example
BUDGET_EVENT Example
INSTEAD OF TRIGGERS
TRIGGERS WITHIN AN APPLICATION

Maintaining Database Triggers

CALL SYNTAX
TRIGGER MAINTENANCE TASKS
SHOW ERRORS TRIGGER
DROP TRIGGER
ALTER TRIGGER
MULTIPLE TRIGGERS FOR A TABLE
HANDLING MUTATING TABLE ISSUES

Implementing System Event Triggers

WHAT ARE SYSTEM EVENT TRIGGERS
DEFININGTHE SCOPE
AVAILABLE SYSTEM EVENTS
SYSTEM EVENT ATTRIBUTES

Additional course details:

Nexus Humans Oracle 12c PL/SQL Fundamentals training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward.

This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts.

Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success.

While we feel this is the best course for the Oracle 12c PL/SQL Fundamentals course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you.

Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

Training Insurance Included!

When you organise training, we understand that there is a risk that some people may fall ill, become unavailable. To mitigate the risk we include training insurance for each delegate enrolled on our public schedule, they are welcome to sit on the same Public class within 6 months at no charge, if the case arises.

What people say about us

Booking process was very easy. Once initial contact was made everything happened smoothly.

Pfizer

Great course yesterday and can’t give enough amazing feedback for John. I’ve never had a teacher hold attention of a class from start to finish, but yesterday he did this.

Jennifer O'Connor

Vistra

Alan delivered the training in an easy to understand and pleasant manner which was broken down into manageable chunks with an appropriate number of breaks to give folk time away from their laptops and screens. It is never easy to do 2 days of training on a fairly in-depth subject involving Software even in a classroom environment and remaining focused and engaged so doing it remotely adds another level of complexity, but I have to say that personally this was one of the best Training Events that I have attended for some years. I have gained so much knowledge of MS Project and it will certainly make my job easier going forward.

A large part of what I gained has to be credited to Alan’s manner and delivery and I should be grateful if you could pass on my thanks to him.

Also, the support, together with joining instructions & training literature etc from Nexus Human has been excellent – thank you for that. I intend feeding that back to our People and ICT Teams too

Nexus Human - Project class

December 2023

This was my 2nd course with Nexus Human and I enjoy the format of the courses.
This was very well-run, the instructor was superb and i will get a lot of benefit from having access for the next 6 months, I look forward to the next course.

Cathal

Kerry, Ireland

I would like to thank the staff of Nexus Human, who were very helpful, prompt, friendly and professional in helping me attain my Microsoft certification. I would like to thank Abdul in particular for his guidance during the course and commend him on his knowledge of software development.

Paul

ITC Group
Dublin

This is the 3rd course I have done with Arun. He is very helpful and always supportive if I have other questions outside the lecture parameters. I have already recommended him and Nexus Human to work colleges and would do so again after this course.

Philip

Nexus Human Classroom Student

The instructor was excellent. Was willing to field any questions we had and deal with our specific queries.

Excel student

The class just finished and I just wanted to mention to you that it was absolutely amazing. The quality of the training had very high standards, the trainer explained amazingly well and we actually practiced (did) all the steps. It was not learning by watching a presentation but by actually doing. The training has been delivered from Atlanta (US) and I cannot wait to go tomorrow in the office and recommend it. The connection and the lab environment were running perfectly.
Once again thank you very much and looking forward for the next trainings scheduled. Enrolling via Nexus Human was the perfect choice.

Cristina, Nexus Human IT Course Attendee

Firstly I would like to say thanks, I found the course I sat yesterday extremely beneficial and the instructor was fantastic, she was enthusiastic and engaging and I would highly recommend the course to friends and colleagues. The post class services are also excellent!

Adobe Student

Sharon was lovely and has a fantastic knowledge. Honestly I didn't think I needed part 1 but I am SO glad I came. Thank you. Will be back in for part 2.

Nexus Human Student

Excellent interaction and answered all of my questions. Looking forward to being able to apply some of the tips and features learned today which hopefully should have real impact on some of the more time consuming tasks currently encountered.

Nexus Human Power BI Attendee

Brilliant
Wonderful teacher
Course presented extremely well indeed

Nexus Human Student feedback

Find out more about this course

Interested in alternative dates? Would like to book a private session of this course for your company? Or for any other queries please simply fill out the form below.

Skill Up Card - Course Bundles