Oracle Developer

Reading Time: 5 minutes

Oracle Developer

Module 1: WORKING WITH SQL

  • Retrieving Data Using the SQL SELECTStatement
  • List the capabilities of SQL SELECT statements
  • Execute a basic SELECT statement
  • Restricting and Sorting Data
  • Limit the rows that are retrieved by a query
  • Sort the rows that are retrieved by a query
  • Use ampersand substitution to restrict and sort output at runtime
  • Using Single-Row Functions to Customize Output
  • Describe various types of functions available in SQL
  • Use character, number, and date functions in SELECT statements
  • Using Conversion Functions and Conditional Expressions
  • Describe various types of conversion functions that are available in SQL
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Apply conditional expressions in a SELECT statement
  • Reporting Aggregated Data Using the Group Functions
  • Identify the available group functions
  • Describe the use of group functions
  • Group data by using the GROUP BY clause
  • Include or exclude grouped rows by using the HAVING clause
  • Displaying Data from Multiple Tables
  • Write SELECT statements to access data from more than one table using equijoins and nonequijoins
  • Join a table to itself by using a self-join
  • View data that generally does not meet a join condition by using outer joins
  • Generate a Cartesian product of all rows from two or more tables

Module 2: ORACLE DATABASE 11G: PL/SQL FUNDAMENTALS

  • Introduction to PL/SQL
  • Explain the need for PL/SQL
  • Explain the benefits of PL/SQL
  • Identify the different types of PL/SQL blocks
  • Output messages in PL/SQL
  • Declaring PL/SQL Variables
  • Recognize valid and invalid identifiers
  • List the uses of variables, declare and initialize variables, use bind variables
  • List and describe various data types using the %TYPE attribute
  • Writing Executable Statements
  • Identify lexical units in a PL/SQL block
  • Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
  • Write nested blocks and qualify variables with labels
  • Write readable code with appropriate indentation
  • Interacting with the Oracle Database Server
  • Create PL/SQL executable blocks using DML and transaction control statements
  • Make use of the INTO clause to hold the values returned by a SQL statement
  • Writing Control Structures
  • Identify the uses and types of control structures(IF, CASE statements and expressions)
  • Construct and identify loop statements
  • Working with Composite Data Types
  • Create user-defined PL/SQL records
  • Create a record with the %ROWTYPE attribute
  • Create an INDEX BY table and INDEX BY table of records
  • Describe the differences among records, tables,and tables of records
  • Using Explicit Cursors
  • Distinguish between usage of implicit andexplicit cursors
  • Declare and control explicit cursors, use simpleloops and cursor FOR loops to fetch data
  • Declare and use cursors with parameters
  • Using Subqueries to Solve Queries
  • Define subqueries
  • Describe the types of problems that the subqueries can solve
  • List the types of subqueries Write single-row and multiple-row subqueries
  • Using the Set Operators
  • Describe set operators
  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned
  • Manipulating Data
  • Describe each data manipulation language (DML) statement
  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table
  • Control transactions
  • Using DDL Statements to Create and Manage Tables
  • Categorize the main database objects
  • Review the table structure
  • List the data types that are available for columns
  • Create a simple table
  • Explain how constraints are created at the time of table creation
  • Describe how schema objects work
  • Creating Other Schema Objects
  • Create simple and complex views
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms
  • Creating Packages
  • Identify the benefits and the components of packages
  • Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)
  • Working with Packages
  • Overload package subprograms
  • Using the BULK COLLECT and FORALL statements
  • Use of NOCOPY
  • Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause
  • Handling Exceptions
  • Define PL/SQL exceptions
  • Recognize unhandled exceptions
  • Handle different types of exceptions (predefined exceptions, non-predefined exceptions and user-defined exceptions)

Module 3: ORACLE DATABASE 11G: DEVELOP PL/SQL PROGRAM UNITS

  • Creating Procedures
  • Create a simple procedure and invoke it from an anonymous block
  • Work with procedures
  • Handle exceptions in procedures, remove a procedure, and display a procedure’s information
  • Creating Functions
  • Differentiate between a procedure and a function
  • Describe the uses of functions
  • Work with functions (create, invoke and remove functions)
  • Navigation
  • Transaction Processing
  • Writing Flexible Code
  • Sharing Objects and Code
  • Introducing Multiple Form Applications
  • Creating a Menu Module
  • Managing Menu Modules
  • Building Multiple Form Applications
  • Working with Record Groups
  • Using Database Objects in Forms Applications
  • Using Dynamic SQL
  • Creating Triggers
  • Describe different types of triggers and their uses
  • Create database triggers
  • Manage triggers
  • Creating DML, DDL, and Event Database Triggers
  • Create triggers on DDL statements
  • Create triggers on system events
  • Hide PL/SQL source code using Wrap utility

Module 4: FORMS

  • Introduction to Oracle Forms
  • Developer and Oracle Forms Services
  • Running a Forms Developer Application
  • Working in the Forms Developer Environment
  • Creating a Basic Form Module
  • Working with Data Blocks and Frames
  • Working with Text Items
  • Creating LOVs and Editors
  • Creating Additional Input Items
  • Creating Non-input Items
  • Creating Windows and Content Canvases
  • Working with Other Canvas Types
  • Introduction to Triggers
  • Producing Triggers
  • Debugging Triggers
  • Adding Functionality to Items
  • Run Time Messages and Alerts
  • Query Triggers Validation

Module 5: REPORTS

  • Introduction To Reports
  • Types Of Reports
  • Working With Reports
  • Using Different report builder components
  • Creating Report Through Wizard
  • Modifying Report Through Wizard
  • Creating Report Manually
  • Report With Simple Data Model
  • Data Model Groups
  • Place Holder
  • Using Summary columns at Group and Report Level
  • Formula Columns
  • Adding Page Numbers And Logo
  • Conditional Formatting
  • Layout Objects
  • Building A Matrix Layout / Crosstab Report
  • Report With Simple Parameter Form
  • Bind Parameters
  • Lexical Parameter
  • Report Triggers And Their Sequence
  • Format Trigger