Oracle Database 12c Program with PL/SQL 1.1 Training & Placements in Chennai SQL STAR

Oracle Database 12c R2: Program with PL/SQL Ed 2
(The Course materials and Course Completion Certificates are directly delivered from Oracle University to those seeking for Foreign Opportunity)

(Government of India Approved Education Center)

This Database Program with PL/SQL training shows you how to develop stored procedures, functions, packages and database triggers. You'll learn to manage PL/SQL program units and dependencies, while using of some of the Oracle-supplied packages. This Oracle Database: Program with PL/SQL training starts with an introduction to PL/SQL and then explores the benefits of this powerful programming language. Through hands-on instruction from expert Oracle instructors, you'll learn to develop stored procedures, functions, packages and more.
Learn To:

• Conditionallycontrol code flow (loops, control structures).
• Createstored procedures and functions.
• UsePL/SQL packages to group and contain related constructs.
• Createtriggers to solve business challenges.
• Usesome of the Oracle supplied PL/SQL packages to generate screenoutput and file output.
• Createcustom packages for applications.
• WriteDynamic SQL code for applications.

Benefits to You Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds. Use Oracle SQL Developer You will use Oracle SQL Developer to develop these program units. SQL*Plus is introduced in this course as optional tools. Course Bundle Note: This course is a combination of Oracle Database: PL/SQL Fundamentals and Oracle Database: Develop PL/SQL Program Units courses. Suggested Prerequisite

• Oracle Database: Introduction to SQL
• Previousprogramming experience

Required Prerequisite

• Oracle Database: SQL Workshop II Ed 2
• Oracle Database: SQL Workshop I Ed 2

Audience

• Application Developers
• Database Administrators
• Database Administrators
• System Analysts
• System Analysts
• System Analysts
• FormsDeveloper
• FormsDeveloper
• Developer
• Technical Consultant
• PortalDeveloper
• PL/SQL Developer

Course Objectives

• Manage dependencies between PL/SQL subprograms
• UsePL/SQL programming constructs and conditionally control code flow(loops, control structures, and explicit cursors)
• Createstored procedures and functions
• Useconditional compilation to customize the functionality in a PL/SQLapplication without removing any source code
• Use the Oracle supplied PL/SQL packages to generate screen output,file output and mail output
• Writedynamic SQL for more coding flexibility
• Createoverloaded package subprograms for more flexibility
• Createtriggers to solve business challenges
• Create and debug stored procedures and functions
• Describe the features and syntax of PL/SQL
• DesignPL/SQL anonymous blocks that execute efficiently
• DesignPL/SQL packages to group related constructs
• Handleruntime errors

Course Topics
Introduction

• PL/SQLdevelopment environments available in this course
• Introduction to SQL Developer
• Course Objectives
• Describe the Human Resources (HR) Schema
• CourseAgenda

Working with Oracle Cloud Exadata Express Cloud Service

• Introduction to Oracle Database Exadata Express Cloud Service
• Connecting to Exadata Express using Database Clients
• Accessing Cloud Database using SQL Workshop

Introduction to PL/SQL

• Identifythe benefits of PL/SQL Subprograms
• Create a Simple Anonymous Block
• Overview of PL/SQL
• Howto generate output from a PL/SQL Block?
• Overview of the types of PL/SQL blocks

Declare PL/SQL Variables

• Sequencesin PL/SQL Expressions
• The%TYPE Attribute
• Usevariables to store data
• Listthe different Types of Identifiers in a PL/SQL subprogram
• What are Bind Variables?
• IdentifyScalar Data Types
• Usageof the Declarative Section to Define Identifiers

Write Anonymous PL/SQL Blocks

• DescribeNested Blocks
• DescribeBasic PL/SQL Block Syntax Guidelines
• Howto convert Data Types?
• Learnto Comment the Code
• Identifythe Operators in PL/SQL
• Deploymentof SQL Functions in PL/SQL

SQL Statements in a PL/SQL block

• RetrieveData in PL/SQL
• DataManipulation in the Server using PL/SQL
• AvoidErrors by using Naming Conventions when using Retrieval and DMLStatements
• Understand the SQL Cursor concept
• InvokeSELECT Statements in PL/SQL
• UseSQL Cursor Attributes to Obtain Feedback on DML
• Saveand Discard Transactions
• SQLCursor concept

Control Structures

• Conditionalprocessing using IF Statements
• Conditionalprocessing using CASE Statements
• Use the Continue Statement
• DescribeWhile Loop Statement
• Describesimple Loop Statement
• DescribeFor Loop Statement

Composite Data Types

• The%ROWTYPE Attribute
• UsePL/SQL Records
• Insertand Update with PL/SQL Records
• ExamineINDEX BY Table Methods
• INDEXBY Tables
• UseINDEX BY Table of Records

Explicit Cursors

• Describe the FOR UPDATE Clause and WHERE CURRENT Clause
• Declarethe Cursor
• What are Explicit Cursors?
• Fetchdata from the Cursor
• CursorFOR loop
• Closethe Cursor
• The%NOTFOUND and %ROWCOUNT Attributes
• Openthe Cursor

Exception Handling

• TrapUser-Define d Exceptions
• TrapNon-Predefined Oracle Server Errors
• UnderstandExceptions
• RAISE_APPLICATION_ERRORProcedure
• PropagateExceptions
• TrapPredefined Oracle Server Errors
• HandleExceptions with PL/SQL

Stored Procedures

• Listthe benefits of using PL/SQL Subprograms
• Listthe differences between Anonymous Blocks and Subprograms
• Create a Modularized and Layered Subprogram Design
• ImplementProcedures Parameters and Parameters Modes
• Create,Call, and Remove Stored Procedures
• ModularizeDevelopment With PL/SQL Blocks
• Understand the PL/SQL Execution Environment
• ViewProcedure Information

Stored Functions

• Identifythe steps to create a stored function
• Controlside effects when calling Functions
• Create,Call, and Remove a Stored Function
• ViewFunctions Information
• Restrictionswhen calling Functions
• Identifythe advantages of using Stored Functions
• InvokeUser-Define d Functions in SQL Statements

Debugging Subprograms

• Debuggingthrough SQL Developer
• Howto debug Functions and Procedures?

Packages

• DescribePackages
• What are the components of a Package?
• Developa Package
• Invokethe Package Constructs
• Viewthe PL/SQL Source Code using the Data Dictionary
• Howto enable visibility of a Packages Components?
• Listing the advantages of Packages
• Createthe Package Specification and Body using the SQL CREATE Statement and SQL Developer

Deploying Packages

• PersistentState of a Package Cursor
• Overloading Subprograms in PL/SQL
• UseForward Declarations to solve Illegal Procedure Reference
• Controlside effects of PL/SQL Subprograms
• Use the STANDARD Package
• PersistentState of Packages
• InvokePL/SQL Tables of Records in Packages
• ImplementPackage Functions in SQL and Restrictions

Implement Oracle-Supplied Packages in Application Development

• Examplesof some of the Oracle-Supplied Packages
• Invokethe UTL_MAIL Package
• Use the UTL_FILE Package to Interact with Operating System Files
• Howdoes the DBMS_OUTPUT Package work?
• What are Oracle-Supplied Packages?
• WriteUTL_MAIL Subprograms

Dynamic SQL

• TheExecution Flow of SQL
• DeclareCursor Variables
• ConfigureNative Dynamic SQL to Compile PL/SQL Code
• DynamicSQL Functional Completeness
• Whatis Dynamic SQL?
• DynamicallyExecuting a PL/SQL Block
• Howto invoke DBMS_SQL Package?
• ImplementDBMS_SQL with a Parameterized DML Statement

Design Considerations for PL/SQL Code

• UnderstandLocal Subprograms
• Implementthe NOCOPY Compiler Hint
• TheCross-Session PL/SQL Function Result Cache
• Usageof Bulk Binding to Improve Performance
• StandardizeConstants and Exceptions
• TheDETERMINISTIC Clause with Functions
• WriteAutonomous Transactions
• Invokethe PARALLEL_ENABLE Hint

Triggers

• Identifythe Trigger Event Types and Body
• Howto Manage, Test and Remove Triggers?
• Identifythe Trigger Event Types, Body, and Firing (Timing)
• Differences between Statement Level Triggers and Row Level Triggers
• CreateDML Triggers using the CREATE TRIGGER Statement and SQL Developer
• Describe Triggers
• BusinessApplication Scenarios for Implementing Triggers
• CreateInstead of and Disabled Triggers

Creating Compound, DDL, and Event Database Triggers

• Implement a Compound Trigger to Resolve the Mutating Table Error
• Identifythe Timing-Point Sections of a Table Compound Trigger
• Comparisonof Database Triggers to Stored Procedures
• What are Compound Triggers?
• CreateDatabase-Event and System-Events Triggers
• Understand the Compound Trigger Structure for Tables and Views
• SystemPrivileges Required to Manage Triggers
• CreateTriggers on DDL Statements

PL/SQL Compiler

• Overview of PL/SQL Compile Time Warnings for Subprograms
• Listthe PL/SQL Compile Time Warning Messages Categories
• Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGSInitialization Parameter, and the DBMS_WARNING PackageSubprograms
• ViewCompiler Warnings: Using SQL Developer, SQL*Plus, or the DataDictionary Views
• Listthe benefits of Compiler Warnings
• Whatis the PL/SQL Compiler?
• Describe the Initialization Parameters for PL/SQL Compilation
• Listthe new PL/SQL Compile Time Warnings

Manage Dependencies

• QueryDirect Object Dependencies using the User_DEPENDENCIES View
• Overview of Schema Object Dependencies
• Invalidationof Dependent Objects
• Recompilea PL/SQL Program Unit
• UnderstandRemote Dependencies
• Queryan Objects Status
• Fine-GrainedDependency Management in Oracle Database 12c
• Displaythe Direct and Indirect Dependencies