Oracle Certified Database Application Developer (OCDBD)

Explore the Lifecycle Development Phases

• Define a database
• Discuss the theoretical, conceptual and physical aspects of a relational database
• Describe the Oracle implementation of the RDBMS and the ORDBMS
• Describe PL/SQL

Write and Execute a Basic SQL Statement

• Describe the SQL SELECT capabilities
• Differentiate between SQL and SQL*Plus commands
• Limit and sort the rows retrieved by a query
• Customize the SQL*Plus environment
• Save customizations

Examine Single Row Functions

• Use a variety of character, number, and date functions in SELECT statements
• Describe different types of join
• Group functions and their uses
• Insert and update rows in a table
• Delete rows from a table
• Define subqueries
• Write single-row, multi-row, and multi-column subqueries
• Control transactions

Create Tables and Views

• Describe the Oracle7 datatypes and the Oracle8 datatypes
• Alter table definitions
• Drop, rename and truncate tables
• Use logic tables
• Retrieve data via a view
• Insert, update and delete data through views
• Drop views
• Alter the definition of a view
• Create and maintain sequences and indexes

Control User Access

• Understand the concepts of users, roles, and privileges
• GRANT and REVOKE object privileges
• Create private and public synonyms
• Declare variables and constants
• Recognize and execute a basic PL/SQL block
• Distinguish between PL/SQL and non-PL/SQL variables

Write Executable Statements

• List the rules of nested blocks
• Write DML statements in PL/SQL
• Control transactions in PL/SQL
• Identify the types of control structures and loop statements
• Construct an IF statement
• Control block flow using nested loops and labels

Work with Composite Datatypes

• Create user-defined PL/SQL records
• Create a PL/SQL table and a PL/SQL table of records
• Use PL/SQL record and table variables
• Distinguish between the implicit and explicit cursor
• Write a cursor FOR loop

Handle Exceptions

• Define PL/SQL exceptions and recognize unhandled exceptions
• List different types of PL/SQL exception handlers
• Trap unanticipated errors
• Customize PL/SQL exception messages

Overview of PL/SQL

• Distinguish between anonymous PL/SQL blocks and PL/SQL subprograms
• Describe the PL/SQL development environment

Work with Procedure Builder

• Describe the features of Procedure Builder
• Manage program units using the Object Navigator
• Create and compile program units using the Program Unit Editor
• Invoke program units using the PL/SQL interpreter

Create Procedures

• Describe the uses of procedures
• Create client-side and server-side procedures
• Create procedures with arguments
• Invoke a procedure
• Remove a procedure

Create Functions

• Describe the uses of functions
• Create client-side and server-side functions
• Invoke a function
• Remove a function
• Differentiate between a procedure and a function

Create Packages

• Describe packages and list their possible components
• Create a package to group together related variables, cursors, constructs, exceptions, procedures and functions
• Make a package construct either public or private
• Invoke a package construct
• Write packages that make use of the overloading feature of PL/SQL
• Avoid errors with mutually referential subprograms
• Initialize public or private variables with an automatic one time only procedure
• Declare ref cursors in a package
• Control the extent to which functions permit database operations
• describe the use and application of the Oracle supplied packages

Create Database Triggers

• Describe database triggers and their use
• Create database triggers
• Describe database trigger firing rules
• Drop database triggers

Manage Subprograms

• Describe system privilege requirements
• Track procedural dependencies
• Predict the effect of changing a database object upon stored procedures and functions
• Describe object dependencies
• Debug client-side and server-side subprograms

Work with Object Types

• Describe object types
• Create transient objects
• Manipulate objects in object tables

Manage Large Objects

• Compare and contrast LONG and LOB (large object) datatypes
• Create and maintain LOB datatypes
• Differentiate between internal and external LOBs
• Utilize the DBMS_LOB PL/SQL packages

Define Form Builder Concepts

• Identify the main components in a form application and how they are related
• Run a form application
• Retrieve, display, modify, and store data using the forms operator interface

Work in the Developer/2000 Environment

• Navigate the Developer/2000 interface
• Use features such as Wizards and the Object Navigator
• Work with data blocks

Create a Basic Form Document

• Create a form consisting of blocks
• Build a new block using an existing table specification
• Save and run a form module
• Build a master/detail form module
• Identify characteristics of each form file storage type

Customize the Appearance of the Form

• Control the position and size of objects in a layout
• Add lines, geometric shapes, and color
• Import images onto the layout
• Create and modify text items
• Support text items with List of Values (LOVs) and editors

Create Additional Input Items

• Create check boxes, list items and radio groups
• Create non-input items
• Create display items, image items, sound items, calculated fields, and buttons

Modify Forms Using Triggers

• Identify the different categories of triggers
• Plan the type and scope of triggers included in a form
• Create and modify triggers that can access the database

Manage Windows and Canvases

• Define window and canvas-view terms
• Display a form document in multiple windows
• Create tabbed canvases

Create Reusable Objects and Code

• Write flexible code
• Manage consistency across applications
• Create applications with multiple forms
• Start another form module
• Define multiple form functionality

Course Topics

Manage Projects with Project Builder

• Use Project Builder to organize and access files
• Customize the Project Builder environment
• Deliver a completed project using Project Builder

Design and Build Menu Modules

• Enhance the default menu
• Attach a menu to a form document
• Create and design new

Menus using different menu item types and menu commands

Use Program Function Keys

• Control keys through triggers and commands
• Understand the effect of function keys in a graphical user interface (GUI) environment
• Cause the forms application to respond to mouse events
• Apply timers
• Manage events in applications based on timers

Create Advanced Multiple-Form Applications

• Call several forms in both modal and modeless windows
• Open and close multiple transactions across an application

Define Data Block Sources

• Identify data sources available for queries and DML
• Determine the appropriate data sources for data blocks
• Create data blocks based on stored procedures and nested SELECT statements

Work with Record Groups

• Use record groups to store data for lists and List of Values (LOVs)
• Use record groups to pass data to other Developer/2000 components
• Incorporate charts and reports into applications

Define Report Builder Concepts

• Identify the main components in a report document and describe how they are related
• Design and run a report
• Identify standard report design styles
• Run existing reports to various output destinations

Create and Modify Reports

• Invoke the Report Builder
• Create a tabular report
• Modify the report by adding data and altering the report style
• Insert page numbers and alter visual attributes
• Build mailing labels, letters, break and matrix reports
• Modify a selection of report-level and runtime properties and options

Manage Report Templates

• Open and modify predefined Developer/2000 report templates
• Register a customized template

Use Report Parameters and Customize a Parameter Form

• Create and reference user parameters
• Reference system parameters
• Create and customize a parameter form to display parameters at runtime

Embed a Chart in a Report

• Create a Developer/2000 graphical chart to display in a report at runtime

Enhance Report Objects and Layout

• Create user-defined columns for summaries and calculations
• Create additional layout objects such as buttons and variable-length lines
• Modify object properties to control placement and pagination of objects

Enhance Reports with Trigger Code

• Identify different trigger types
• Create conditional trigger code to control report output
• Share and reuse common code

Maximize Performance Using the Reports Server

• Control and monitor a remote reports server installation
• View and schedule reports sent from a client to the reports server
• Build reports to run on various platforms and in different environments and languages
• Identify and modify the properties needed for character-mode reports