Introduction To Oracle PL/SQL 12C Training

In this 5 day instructor led class, students will learn the following Oracle PL/SQL 12C skills: 

Class Outline

• Chapter 1 – PL/SQL Program Structure..  
• Overview Of This Chapter..  
• PL/SQL Vs. SQL.  
• PL/SQL Engines Available.  
• Anonymous PL/SQL Block Structure.  
• Object Naming Rules.  
• Variable Declarations.  
• Rules.  
• Available Datatypes.  
• Scalar Datatypes.  
• Using Extended Datatypes.  
• Object Types.  
• Executable Statements.  
• Expressions.  
• Block Labeling.  
• Variable Scoping Rules.  
• Comments In Programs And Scripts.  
• Basic Coding Standards.  
• LAB 1:  PL/SQL Program Structure.  
• LAB 1 Solutions:  PL/SQL Program Structure.  
• Summary Of This Chapter..  

• Chapter 2 – PL/SQL Flow Control..  
• Overview Of This Chapter..  
• Conditional Control.  
• Comparison Operators.  
• Logical Operators.  
• Truth Tables.  
• Repetition Control.  
• The Simple Loop.  
• WHILE LOOP..  
• FOR LOOP..  
• CONTINUE Statements In Loops.  
• Step Loops.  
• The GOTO Statement.  
• CASE Expressions / Statements.  
• Bind Variables.  
• Substitution Variables.  
• LAB 2:  PL/SQL Flow Control.  
• LAB 2 Solutions:  PL/SQL Flow Control.  
• Summary Of This Chapter

• Chapter 3 – SQL Developer And PL/SQL..  
• Overview Of This Chapter..  
• SQL Developer And PL/SQL.  
• Creating And Executing Scripts.  
• LAB 3:  SQL Developer And PL/SQL.  
• LAB 3 Solutions:  SQL Developer And PL/SQL.  
• Summary Of This Chapter

• Chapter 4 – SELECT INTO..  
• Overview Of This Chapter..  
• Selecting Single Rows Of Data.  
• Anchoring Variables To Datatypes.  
• DML In PL/SQL.  
• RETURNING … INTO..  
• Sequences In PL/SQL.  
• Transaction Control In PL/SQL.  
• Autonomous Transactions.  
• LAB 4:  SELECT INTO..  
• LAB 4 Solutions:  SELECT INTO..  
• Summary Of This Chapter

• Chapter 5 – The PL/SQL CURSOR..  
• Overview Of This Chapter..  
• Declaring Explicit CURSORs.  
• Opening And Closing Explicit CURSORs.  
• Using Explicit CURSORs To Retrieve Values.  
• Explicit CURSOR Attributes.  
• Using A LOOP With An Explicit CURSOR..  
• Using %ROWTYPE With CURSORs.  
• The CURSOR FOR LOOP..  
• DBMS_OUTPUT.  
• LAB 5:  The PL/SQL CURSOR..  
• LAB 5 Solutions:  The PL/SQL CURSOR..  
• Summary Of This Chapter

• Chapter 6 – Optimization..  
• Overview Of This Chapter..  
• Timing PL/SQL.  
• FOR UPDATE / WHERE CURRENT OF..  
• LAB 6:  Optimization..  
• LAB 6 Solutions:  Optimization..  
• Summary Of This Chapter

• Chapter 7 – PL/SQL Exception Handling..  
• Overview Of This Chapter..  
• The EXCEPTION Section..  
• Oracle Named Exceptions.  
• PRAGMA EXCEPTION_INIT.  
• User Defined Exceptions.  
• The Scope Of User-Defined Exceptions.  
• Raising Named Exceptions.  
• Exception Propagation..  
• Raising An Exception Again..  
• Life After An EXCEPTION..  
• WHEN OTHERS..  
• Taking Your Ball And Going Home.  
• DBMS_ERRLOG..  
• LAB 7:  PL/SQL Exception Handling..  
• LAB 7 Solutions:  PL/SQL Exception Handling..  
• Summary Of This Chapter

• Chapter 8 – Stored Procedures..  
• Overview Of This Chapter..  
• Procedures.  
• Benefits Of Stored Procedures.  
• Database Security.  
• Performance.  
• Productivity.  
• Portability.  
• Parameters And Stored Procedures.  
• Parameter Notation.  
• Stored Object Creation..  
• Syntax For Creating A Procedure.  
• Compilation Errors.  
• Viewing Compiled Code.  
• Dropping A Procedure.  
• The ALTER Command And Stored Procedures.  
• LAB 8:  Stored Procedures.  
• LAB 8 Solutions:  Stored Procedures.  
• Summary Of This Chapter..  

• Chapter 9 – Creating Functions In PL/SQL..  
• Overview Of This Chapter..  
• Functions.  
• Purity Levels.  
• Using White Lists.  
• Optimizations.  
• PARALLEL_ENABLE..  
• DETERMINISTIC Functions.  
• PL/SQL Result Cache.  
• NOCOPY..  
• DBMS_OUTPUT In Functions.  
• Using The WITH Clause For Functions.  
• PRAGMA UDF..  
• PRAGMA INLINE..  
• Using SQL Developer With Stored Procedures.  
• Debugging.  
• LAB 9: Functions.  
• LAB 9 Solutions:  Functions.  
• Summary Of This Chapter

• Chapter 10 – Packages..  
• Overview Of This Chapter..  
• Creating Packages.  
• Package Benefits.  
• Security.  
• Persistent State.  
• I/O Efficiency.  
• A Simple Package.  
• Overloading..  
• Bodiless Packages.  
• Source Code Encryption..  
• Creating Packages From Procedures And Functions.  
• LAB 10:  Packages.  
• LAB 10 Solutions:  Packages.  
• Summary Of This Chapter..  

• Chapter 11 — Creating DML Triggers..  
• Overview Of This Chapter..  
• DML Triggers.  
• DML Trigger Structure.  
• Syntax.  
• Conditional Triggering Predicates.  
• Triggers For Business Rules Enforcement.  
• Mutating And Constraining Tables.  
• Compound Triggers.  
• Controlling Firing Order..  
• DDL For Triggers.  
• Viewing Trigger Source.  
• INSTEAD OF Triggers.  
• LAB 11:  DML Triggers.  
• LAB 11 Solutions:  DML Triggers.  
• Summary Of This Chapter

• Chapter 12 – Advanced Concepts..  
• Overview Of This Chapter..  
• Embedded Procedures.  
• The Optimizing Compiler..  
• PL/SQL Compiler Warnings.  
• Compiling For Debugging..  
• Conditional Compilation / Inquiry Directives.  
• Error Directives.  
• Inquiry Directives.  
• Using Static Constants.  
• DBMS_DB_VERSION..  
• Native Compilation..  
• Recompiling All Database Objects.  
• LAB 12:  Advanced Concepts.  
• LAB 12 Solutions:  Advanced Concepts.  
• Summary Of This Chapter

• Chapter 13 – File Operations..  
• Overview Of This Chapter..  
• Moving Files Between Databases.  
• Directory Access.  
• File Manipulation..  
• FCLOSE Procedure.  
• FCLOSE_ALL Procedure.  
• FCOPY Procedure.  
• FFLUSH Procedure.  
• FGETATTR Procedure.  
• FGETPOS Function.  
• FOPEN Function.  
• FREMOVE Procedure.  
• FRENAME Procedure.  
• FSEEK Procedure.  
• GET_LINE Procedure.  
• GET_RAW Procedure.  
• IS_OPEN Function.  
• NEW_LINE Procedure.  
• PUT Procedure.  
• PUT_LINE Procedure.  
• PUTF Procedure.  
• PUT_RAW Procedure.  
• LAB 13:  File Operations.  
• LAB 13 Solutions:  File Operations.  
• Summary Of This Chapter

• Chapter 14 – Communications..  
• Overview Of This Chapter..  
• DBMS_ALERT.  
• REGISTER Procedure.  
• REMOVE Procedure.  
• REMOVEALL Procedure.  
• SET_DEFAULTS Procedure.  
• SIGNAL Procedure.  
• WAITANY Procedure.  
• WAITONE Procedure.  
• Security.  
• DBMS_ALERT Example.  
• DBMS_PIPE..  
• CREATE_PIPE Function.  
• NEXT_ITEM_TYPE Function.  
• PACK_MESSAGE Procedure.  
• PURGE Procedure.  
• RECEIVE_MESSAGE Function.  
• RESET_BUFFER Procedure.  
• REMOVE_PIPE Function.  
• SEND_MESSAGE Function.  
• UNIQUE_SESSION_NAME Function.  
• UNPACK_MESSAGE Procedure.  
• DBMS_PIPE Example.  
• UTL_SMTP..  
• UTL_HTTP..  
• UTL_TCP..  
• UTL_MAIL.  
• DBMS_NETWORK_ACL_ADMIN..  
• LAB 14:  Communications.  
• LAB 14 Solutions:  Communications.  
• Summary Of This Chapter

• Chapter 15 – Security..  
• Overview Of This Chapter..  
• DBMS_RLS..  
• Implementation Tasks.  
• Our Example Scenario.  
• The Policy Package.  
• The Dynamic Predicate Package.  
• Creating The Context And The Trigger 
• Using DBMS_RLS..  
• Exempting Policies.  
• DBMS_FGA..  
• Controlling Column Access With Virtual Columns.  
• LAB 15:  Security.  
• LAB 15 Solutions:  Security.  
• Summary Of This Chapter

• Chapter 16 — Scheduling..  
• Overview Of This Chapter..  
• Oracle Scheduling..  
• DBMS_JOB..  
• PROCEDURE BROKEN..  
• PROCEDURE CHANGE..  
• PROCEDURE INSTANCE..  
• PROCEDURE INTERVAL.  
• PROCEDURE ISUBMIT..  
• FUNCTION IS_JOBQ..  
• PROCEDURE NEXT_DATE..  
• PROCEDURE REMOVE..  
• PROCEDURE RUN..  
• PROCEDURE SUBMIT..  
• PROCEDURE USER_EXPORT..  
• PROCEDURE USER_EXPORT..  
• PROCEDURE WHAT..  
• The NEXT_DATE Parameter 
• The INTERVAL Parameter 
• The WHAT Parameter 
• DBMS_SCHEDULER..  
• Evaluating Calendaring Expressions.  
• DBMS_SCHEDULER Capabilities.  
• LAB 16:  Scheduling..  
• LAB 16 Solutions:  Scheduling..  
• Summary Of This Chapter

• Chapter 17 – Miscellaneous Packages..  
• Overview Of This Chapter..  
• DBMS_OUTPUT.  
• DBMS_RANDOM…  
• STRING Function.  
• VALUE Function.  
• DBMS_STATS..  
• GATHER_SCHEMA_STATS..  
• GATHER_TABLE_STATS..  
• DBMS_WM…  
• DBMS_METADATA..  
• DBMS_REDEFINITION..  
• Other Notable Packages / Procedures.  
• LAB 17:  Miscellaneous Packages.  
• LAB 17 Solutions:  Miscellaneous Packages.  
• Summary Of This Chapter

• Chapter 18 – Database Triggers..  
• Overview Of This Chapter..  
• Permissions Needed..  
• DDL Triggers.  
• SERVERERROR Triggers.  
• LOGON / LOGOFF Triggers.  
• STARTUP / SHUTDOWN Triggers.  
• SUSPEND Triggers.  
• LAB 18:  Database Triggers.  
• LAB 18 Solutions:  Database Triggers.  
• Summary Of This Chapter

• Chapter 19 – Collections..  
• Overview Of This Chapter..  
• Defining Records.  
• Collections.  
• Associative Arrays.  
• Nested Tables.  
• VARRAYs / VARYING ARRAYs.  
• Assignments.  
• Comparing Collections.  
• Collection Methods.  
• EXISTS..  
• FIRST..  
• LAST..  
• COUNT..  
• LIMIT..  
• PRIOR..  
• NEXT..  
• DELETE..  
• TRIM…  
• SET Theory And Nested Tables.  
• LAB 19:  Collections.  
• LAB 19 Solutions:  Collections.  
• Summary Of This Chapter

• Chapter 20 – Bulk Operations..  
• Overview Of This Chapter..  
• Bulk Binding..  
• FORALL.  
• SQL%BULK_ROWCOUNT..  
• SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS..  
• BULK COLLECT..  
• The LIMIT Clause.  
• FORALL And The INDICES OF Clause.  
• FORALL And VALUES OF..  
• Pipelined Table Functions.  
• Multidimensional Collections.  
• LAB 20:  Bulk Operations.  
• LAB 20 Solutions:  Bulk Operations.  
• Summary Of This Chapter

• Chapter 21 – Cursor Variables..  
• Overview Of This Chapter..  
• What Is A Cursor Variable?.  
• Cursor Variable Control.  
• OPEN FOR..  
• FETCH..  
• CLOSE..  
• Cursor Variable Examples.  
• ROWTYPE_MISMATCH..  
• LAB 21:  Cursor Variables.  
• LAB 21 Solutions:  Cursor Variables.  
• Summary Of This Chapter

• Chapter 22 – Dynamic SQL..  
• Overview Of This Chapter..  
• Standard Dynamic SQL.  
• New DBMS_SQL Functionality.  
• Native Dynamic SQL (NDS) 
• EXECUTE IMMEDIATE..  
• OPEN FOR, FETCH and CLOSE..  
• Additional Rules For Native Dynamic SQL.  
• BULK EXECUTE IMMEDIATE..  
• SQL Injection Attacks.  
• SQL Statement Manipulation.  
• Additional Statement Insertion.  
• The USING Clause Vs. Concatenation.  
• DBMS_ASSERT..  
• Switching Between NDS And DBMS_SQL.  
• LAB 22:  Dynamic SQL.  
• LAB 22 Solutions:  Dynamic SQL.  
• Summary Of This Chapter

• Chapter 23 – Large Objects..  
• Overview Of This Chapter..  
• External Vs. Internal Large Objects.  
• Initializing Internal LOB Locators.  
• The DBMS_LOB Package.  
• Inserting Internal Large Objects.  
• Loading Errors.  
• The SecureFile Option..  
• LAB 23:  Large Objects.  
• LAB 23 Solutions:  Large Objects.  
• Summary Of This Chapter

• Chapter 24 – Object-Oriented Programming..  
• Overview Of This Chapter..  
• Oracle Objects.  
• Ordering Object Types.  
• Object Tables.  
• SQL For Object Tables.  
• Advanced Object Concepts.  
• Lab 24: Object-Oriented Programming..  
• Lab 24 Solutions: Object-Oriented Programming..  
• Summary Of This Chapter

• Chapter 25 – Java And Other Languages..  
• Overview Of This Chapter..  
• Alternative Languages.  
• Loading Code.  
• Publishing Code.  
• Executing The Code.  
• Interfacing With C..  
• Enhanced Language Interface Features.  
• Summary Of This Chapter..  
• Appendix A: The EL Data Model..  
• The EL Sample Schema.  
• The Taxes Table.  
• The ZipCodes Table.  
• The Jobs Table.  
• The Departments Table.  
• The Employees Table.  
• Objects In The EL Model 
• Analysis.  
• Join Conditions.  
• The Entity Relationship Model.  
• Appendix B: The ELL Data Model..  
• The ELL Sample Schema.  
• The Nations Table.  
• The Customers Table.  
• The Items Table.  
• The Promotions Table.  
• The SalesData Table.  
• Objects In The ELL Model 
• Analysis.  
• Join Conditions.  
• The Entity Relationship Model

To Hire a proven Oracle PL/SQL 12C Subject Matter Expert Consultant / Course Author and Instructor who teaches this class, call 800-798-3901 today!