Oracle Certified Database Application Developer (OCDBD)

Introduction to Oracle: SQL and PL/SQL

In this workshop, participants will be introduced to SQL and PL/SQL functions. Participants will learn how to create and maintain database objects and how to store, retrieve, and manipulate data. In addition, they will also learn how to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

Workshop Topics:

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
• 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

Back to the Top of this page

Develop PL/SQL Program Units

In this workshop, participants will learn to write PL/SQL procedures, functions and packages. Working in both the Procedure Builder and the SQL*Plus environments, participants will learn how to create and manage PL/SQL program units and database triggers. Participants will also learn how to use some of the Oracle supplied packages.

Workshop Topics:

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

Back to the Top of this page

Developer/2000 Rel 2: Build Forms I

In this workshop, participants will learn how to build and test interactive applications and will work in a graphical user interface (GUI) environment. They will learn how to customize forms with user input items such as check boxes, list items and radio groups. Participants will also learn how to modify data access by creating event-related triggers.

Workshop Topics:

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

Back to the Top of this page

Developer/2000 Rel 2: Build Forms II

In this workshop, participants will gain an opportunity to broaden their Developer/2000 form-building skills. Participants will use Project Builder to manage application files and multiple transactions across modules. Participants will also learn how to create multiple-form applications and will practice enhancing their applications with custom menus, reports, and charts.

Workshop 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

Back to the Top of this page

Developer/2000 Rel 2: Build Reports

In this workshop, participants will learn how to build a variety of standard and custom reports in a client-server environment. Participants will work in a graphical user interface (GUI) environment and will learn how to retrieve, display and format data in many styles to create tabular, matrix, mailing label and letter reports. In addition, participants will learn how to customize more complex reports, embed graphical charts in reports and use the Intelligent Remote Reports Server.

Workshop Topics:

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