Oracle Developer/Administrator Certified Associate (OCA)

The students also learn to use single row functions to customize output, use conversion functions and conditional expressions and use group functions to report aggregated data. Demonstrations and hands-on practice reinforce the fundamental concepts.

In this course, students use Oracle SQL Developer and SQL *Plus as the tool.

Course Objectives:

• Retrieve row and column data from tables with the SELECT statement

• Create reports of sorted and restricted data

• Employ SQL functions to generate and retrieve customized data

• Display data from multiple tables using the ANSI SQL 99 JOIN syntax

• Create reports of aggregated data

• Use the SET operators to create subsets of data

• Run data manipulation statements (DML) to update data in the Oracle Database 11g

• Run data definition language (DDL) statements to create and manage schema objects

• Identify the major structural components of the Oracle Database 11g

Introduction

• Listing the features of Oracle Database 11g
• Discussing the basic design, theoretical and physical aspects of a relational database
• Describing the development environments for SQL
• Describing Oracle SQL Developer, Describing the data set used by the course

Retrieving Data Using the SQL SELECT Statement

• Listing the capabilities of SQL SELECT statements.

• Generating a report of data from the output of a basic SELECT statement

• Using arithmetic expressions and NULL values in the SELECT statement

• Using Column aliases

• Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

• Displaying the table structure using the DESCRIBE command

Restricting and Sorting Data

• Writing queries with a WHERE clause to limit the output retrieved Using the comparison operators and logical operators
• Describing the rules of precedence for comparison and logical operators
• Using character string literals in the WHERE clause
•  Writing queries with an ORDER BY clause to sort the output
• Sorting output in descending and ascending order
• Using the Substitution Variables

Using Single-Row Functions to Customize Output

• Differentiating between single row and multiple row functions
• Manipulating strings using character functions
• Manipulating numbers with the ROUND, TRUNC and MOD functions
• Performing arithmetic with date data
• Manipulating dates with the date functions

Using Conversion Functions and Conditional Expressions

• Describing implicit and explicit data type conversion
• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nesting multiple functions
• Applying the NVL, NULLIF, and COALESCE functions to data
• Using conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Using the aggregation functions in SELECT statements to produce meaningful reports
• Using AVG, SUM, MIN, and MAX function
• Handling Null Values in a group function
• Creating queries that divide the data in groups by using the GROUP BY clause
• Creating queries that exclude groups of date by using the HAVING clause

Displaying Data from Multiple Tables

• Writing SELECT statements to access data from more than one table
• Joining Tables Using SQL:1999 Syntax
• Viewing data that does not meet a join condition by using outer joins
• Joining a table by using a self join
• Creating Cross Joins

Using Sub-queries to Solve Queries

• Using a Subquery to Solve a Problem
• Executing Single-Row Sub-queries
• Using Group Functions in a Sub-query
• Using Multiple-Row Subqueries
• Using the ANY and ALL Operator in Multiple-Row Sub-queries

Using the SET Operators

• Describing the SET operators
• Using a SET operator to combine multiple queries into a single query
• Using UNION, UNION ALL, INTERSECT, and MINUS Operator
• Using the ORDER BY Clause in Set Operations

Manipulating Data

• Adding New Rows to a Table Using the INSERT statement
• Changing Data in a Table Using the UPDATE Statement
• Using DELETE and TRUNCATE Statements
• Saving and discarding changes with the COMMIT and ROLLBACK statements
• Implementing Read Consistency
• Using the FOR UPDATE Clause

Using DDL Statements to Create and Manage Tables

• Categorizing Database Objects
• Creating Tables using the CREATE TABLE Statement Describing the data types
• Describing Constraints
• Creating a table using a subquery
• Altering and Dropping a table

Creating Other Schema Objects

• Creating, modifying, and retrieving data from a view
• Performing Data manipulation language (DML) operations on a view
• Dropping a view
• Creating, using, and modifying a sequence
• Creating and dropping indexes
• Creating and dropping synonyms

In class students learn to develop, execute and manage PL\SQL stored program units like procedures, functions, packages and database triggers. Student also learns to manage object dependencies and recompilation of invalid objects. This course also describes the characteristics and ways of manipulation of large objects. Students are introduced to the utilization of some of the Oracle-supplied packages.

Course Objectives:

• Write PL/SQL code to interface with the database
• Design PL/SQL program units that execute efficiently
• Use PL/SQL programming constructs and conditional control statements
• Handle run-time errors
• Describe stored procedures and functions
• Write dynamic SQL for more coding flexibility
• Design PL/SQL code for predefined data types, local subprograms, additional programs and standardized constants and exceptions
• Use the compiler warnings infrastructure
• Manipulate large objects
• Create triggers to solve business challenges
• Manage dependencies between PL/SQL subprograms
• Schedule PL/SQL jobs to run independently
• Create stored procedures and functions
• Design PL/SQL packages to group and contain related constructs
• Create overloaded package subprograms for more flexibility
• Categorize the Oracle supplied PL/SQL packages

Introduction to PL/SQL

• What is PL/SQL
•  PL/SQL Environment
• Benefits of PL/SQL
• Overview of the Types of PL/SQL blocks
• Create and Execute a Simple Anonymous Block
• Generate Output from a PL/SQL Block
• iSQL*Plus as PL/SQL Programming Environment

Declaring PL/SQL Identifiers

• Identify the Different Types of Identifiers in a PL/SQL subprogram
• Use the Declarative Section to Define Identifier
• List the Uses for Variables
• Store Data in Variables
• Declare PL/SQL Variables
• Writing Executable Statements
• Describe Basic Block Syntax Guidelines
• Use Literals in PL/SQL
• Customize Identifier Assignments with SQL Functions
• Use Nested Blocks as Statements
• Reference an Identifier Value in a Nested Block
• Qualify an Identifier with a Label
• Use Operators in PL/SQL
• Use Proper PL/SQL Block Syntax and Guidelines

Interacting with the Oracle Server

• Identify the SQL Statements You Can Use in PL/SQL
• Include SELECT Statements in PL/SQL
• Retrieve Data in PL/SQL with the SELECT Statement
• Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulate Data in the Server Using PL/SQL
• The SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Writing Control Structures

• Control PL/SQL Flow of Execution
• Conditional processing Using IF Statements
• Conditional Processing CASE Statements
• Handle Nulls to Avoid Common Mistakes
• Build Boolean Conditions with Logical Operators
• Use Iterative Control with Looping Statements

Working with Composite Data Types

• Learn the Composite Data Types of PL/SQL Records and Tables
• Use PL/SQL Records to Hold Multiple Values of Different Types
• Inserting and Updating with PL/SQL Records
• Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

• Cursor FOR Loops Using Sub-queries
• Increase the Flexibility of Cursors By Using Parameters
• Use the FOR UPDATE Clause to Lock Rows
• Use the WHERE CURRENT Clause to Reference the Current Row
• Use Explicit Cursors to Process Rows
• Explicit Cursor Attributes
• Cursors and Records
Handling Exceptions

Handling Exceptions with PL/SQL

• Predefined Exceptions
• Trapping Non-predefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Propagate Exceptions
• Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

Creating Stored Procedures

• Describe the block structure for PL/SQL stored procedures
• Invoke a stored procedure/function from different tools
• Call a stored procedure with host variables from iSQL*Plus, Forms, Java, C, etc
• Invoke a stored procedure from an anonymous block or another stored procedure
• List the CREATE OR REPLACE PROCEDURE syntax
• Identify the development steps for creating a stored procedure
• Use the SHOW ERRORS command
• View source code in the USER_SOURCE dictionary view

Creating Stored Functions

• Describe stored functions
• List the CREATE OR REPLACE FUNCTION syntax
• Identify the steps to create a stored function
• Execute a stored function
• Identify the advantages of using stored functions in SQL statements
• Identify the restrictions of calling functions from SQL statements
• Remove a function

Creating Packages

• List the advantages of packages
• Describe packages
• Show the components of a package Diagram the visibility of constructs within a package
• Develop a package
• Create the package specification
• Declare public constructs
• Create the package body

Using More Package Concepts

• List the benefits of overloading
• Show overloading example
• Use forward declarations in packages
• Create a one-time only procedure (package code initialization)
• List the restrictions on package functions used in SQL
• Encapsulate code in a package demonstration
• Invoke a user-defined package function from a SQL statement
• Utilize the persistent state of package variables

Utilizing Oracle Supplied Packages in Application Development

• List the various uses for the Oracle supplied packages
• Reuse pre-packaged code to complete various tasks from developer to DBA purposes
• Use the DESCRIBE command to view the package specifications and overloading
• Explain how DBMS_OUTPUT works (in conjunction with SET SERVEROUPUT ON)
• Interact with operating system files with UTL_MAIL
• Describe file processing with UTL_FILE
• Review UTL_FILE routines and exceptions
• Use UTL_FILE to generate a report to a file

Dynamic SQL and Metadata

• Describe using native dynamic SQL
• List the execution flow of SQL
• Show the syntax for the EXECUTE IMMEDIATE statement for native dynamic SQL
• Create a procedure to generate native dynamic SQL using EXECUTE IMMEDIATE to delete rows from a table
• Describe the DBMS_SQL package
• Provide an example of DBMS_SQL
• List the advantages of using Native Dynamic SQL Over the DBMS_SQL package

Design Considerations for PL/SQL Code

• Standardize constants with a constant package
• Standardize exceptions with an exception handling package
• Introduce local sub-programs
• Use local sub-programs
• Track run time errors with an exception package
• Describe the NOCOPY compiler hint
• Use the NOCOPY compiler hint
• Explain the effects of NOCOPY

Managing Dependencies

• Define dependent and referenced objects
• Diagram dependencies with code, views, procedures, and tables
• Manage local dependencies between a procedure, view, and a table
• Analyze a scenario of local dependencies
• Display direct dependencies using the USER_DEPENDENCIES view
• Run the UTL_DTREE.SQL script to create objects that enable you to view direct and indirect dependencies
• Predict the effects of changes on dependent objects

Manipulating Large Objects

• Describe a LOB object
• Diagram the anatomy of a LOB
• Manage and list the features on internal LOBs
• Describe, manage, and secure BFILEs
• Create and use the DIRECTORY object to access and use BFILEs
• Prepare BFILEs for usage
• Use the BFILENAME function to load BFILEs
• Describe the DBMS_LOB package

Creating Triggers

• Describe the different types of triggers and how they execute
• List the benefits and guidelines of using database triggers
• Show how triggers are executed with a basic database trigger example
• Show syntax and create DML triggers, and list the DML trigger components
• Explain the firing sequence of triggers
• Create a DML statement and row level triggers
• Use the OLD and NEW qualifiers to reference column values
• Use conditional predicates with triggers

Applications for Triggers

• Create triggers for DDL events of CREATE, ALTER, and DROP
• Create triggers for system events of SERVERERROR, STARTUP, SHUTDOWN, LOGON and LOGOFF
• Define a mutating table
• Describe business application scenarios for implementing with triggers
• Describe the privileges required to manage triggers

Understanding and Influencing the PL/SQL Compiler

• List the features of native compilation
• Describe the features of the PL/SQL compiler in Oracle Database 10g
• Identify the 3 parameters used to influence compilation (PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL)
• Show how to set the parameters
• Describe the dictionary view used to see how code is compiled (USER_PLSQL_OBJECTS)
• Change the parameter settings, recompile code, and view the results
• Describe the compiler warning infrastructure in Oracle Database, list the steps used in setting compiler warning levels

Course Objectives:

• Describe Oracle Database Architecture
• Install and configure Oracle Database 11g
• Configure Oracle Net services
• Manage the database storage structures
• Create and administer user accounts
• Perform basic backup and recovery of a database
• Manage users and schemas
• Manage data and concurrency
• Monitor and administer undo data
• Backup and recover a database
• Monitor performance
• Use the database diagnostic monitor

Exploring the Oracle Database Architecture

• Oracle Database Architecture Overview
• Interacting with an Oracle Database
• Process Architecture
• Database Storage Architecture
• Logical and Physical Database Structures
• Tablespaces and Data Files
• SYSTEM and SYSAUX Tablespaces
• Segments, Extents, and Blocks

Preparing the Database Environment

• Tasks of an Oracle Database Administrator
• Tools Used to Administer an Oracle Database
• Installation: System Requirements
• Optimal Flexible Architecture (OFA)
• Setting Environment Variables
• Oracle Universal Installer (OUI)
• Database Configuration Options
• Advanced Installation Options

Creating an Oracle Database

• Planning the Database
• Configuring the Listener
• Using the DBCA to Create a Database
• Password Management
• Creating a Database Design Template
• Using the DBCA to Delete a Database
• Using DBCA For Additional Tasks

Managing the Oracle Instance

• Management Framework
• Oracle Enterprise Manager
• Initialization Parameter
• Database Startup and Shutdown
• Shutdown Modes
• Viewing the Alert History
• Using Trace Files
• Data Dictionary: Overview

Configuring the Oracle Network Environment

• Oracle Net Services & Listener
• Establishing Net Connections
• Tools for Configuring and Managing the Oracle Network
• Net Services Administration Pages
• Naming Methods
• Directory Naming
• SGA and PGA
• When Not to Use a Shared Server

Managing Database Storage Structures

• Storage Structures
• How Table Data Is Stored
• Anatomy of a Database Block
• Space Management in Tablespaces
• Tablespaces in the Preconfigured Database
• Actions with Tablespaces
• Oracle Managed Files (OMF)
• ASM: Concepts

Administering User Security

• Database User Accounts
• Predefined Accounts: SYS and SYSTEM
• Administrator Authentication
• Benefits of Roles
• Predefined Roles
• Implementing Password Security Features
• Assigning Quota to Users

Managing Schema Objects

• What Is a Schema?
• Accessing Schema Objects
• Specifying Data Types in Tables
• Creating and Modifying Tables
• Understanding Data Integrity
• Indexes & Views
• Sequences
• Temporary Tables

Managing Data and Concurrency

• Manipulating Data Through SQL
• PL/SQL
• Administering PL/SQL Objects
• Functions, Procedures, Packages & Triggers
• Data Concurrency
•  Enqueue Mechanism
• Resolving Lock Conflicts Using SQL
• Deadlocks

Managing Undo Data

• Data Manipulation
• Transactions and Undo Data
• Undo Data Versus Redo Data
• Configuring Undo Retention
• Guaranteeing Undo Retention
• Using the Undo Advisor
• Viewing System Activity

Implementing Oracle Database Security

• Industry Security Requirements
• Principle of Least Privilege
• Monitoring for Compliance
• Value-Based Auditing
• Fine-Grained Auditing
• DBA Auditing
• Security Updates
• Applying Security Patches

Database Maintenance

• Introducing Terminology
• Optimizer Statistics
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Advisory Framework
• Automated Maintenance Tasks
• Server-Generated Alerts
• Reacting to Alerts

Performance Management

• Performance Monitoring
• Managing Memory Components
• Enabling Automatic Memory Management (AMM)
• Automatic Shared Memory Advisor
• Using Memory Advisors
• Dynamic Performance Statistics
• Troubleshooting and Tuning Views
• Invalid and Unusable Objects

Backup and Recovery Concepts

• Statement Failure
• User Error
• Understanding Instance Recovery
• Phases of Instance Recovery
• Using the MTTR Advisor
• Media Failure
• Archive Log Files

Performing Database Backups

• Backup Solutions: Overview
• Oracle Secure Backup
• User-Managed Backup
• Terminology
• Recovery Manager (RMAN)
• Configuring Backup Settings
• Backing Up the Control File to a Trace File
• Monitoring the Flash Recovery Area

Performing Database Recovery

• Opening a Database
• Data Recovery Advisor
• Loss of a Control File
• Loss of a Redo Log File
• Data Recovery Advisor
• Data Failures
• Listing Data Failures
• Data Recovery Advisor Views

Moving Data

• Moving Data: General Architecture
• Directory Object: Overview
• SQL*Loader: Overview
• Data Pump: Overview
• Performance Initialization Parameters
• Using Enterprise Manager to Monitor Data Pump Jobs
• Data Dictionary

Enhancing Database Capabilities

• Using EM Support Workbench
• Create a Service Request
• Package and upload diagnostic data to Oracle Support
• Track the SR and Implement Repairs
• Incident Packaging Configuration
• Working with Oracle Support
• MetaLink Integration
• Managing Patches