Oracle Application Express (APEX)

Project Implementation for COSC 5050 Distributed Database

ApplicationsLab2

Using Application BuilderOracle SQL and PL/SQL scriptNavigating the Application BuilderInstall and run the demonstration

applicationsNavigating between pagesGUI componentsReports and forms

Create application

Oracle SQLDDL with create and drop

Create table, primary key and foreign keyCreate sequenceCreate viewDrop objects

DML with Insert, update, deleteTransaction management with commit and

rollbackMaintain your database with script files

For create, data, and drop

Create TablesCreate tableColumn namesData typeConstraints

Create Views

Create SequencesCreate sequence

For generating primary keyStart withIncrement by

CREATE SEQUENCE MEMBER_SEQ INCREMENT BY 1 START WITH 1;CREATE SEQUENCE PHONE_SEQ INCREMENT BY 1 START WITH 1;

Drop ObjectsThe DROP command is used to remove any

database object from the databaseDROP TABLE MEMBER;DROP SEQUENCE MEMBER_SEQ;DROP VIEW MEMBER_LIST;

Data ManipulationDML – data manipulation language

INSERTUPDATEDELETE

Transaction managementCOMMITROLLBACK

Function and Stored Procedure

TriggerTrigger

A named PL/SQL block stored in a database and executed implicitly when a triggering event occurs

What Is Application Builder?Application Builder is an environment to

build applicationsAssemble an HTML interface (or application)

on top of database objects such as tables and views

Through wizards or direct inputEach application is a collection of pages

linked together using tabs, buttons, or hypertext links

Demonstration ApplicationTwo demonstration applications

To learn more about the different types of functionality

Using Application BuilderAccessing application builderApplication builder home

View icons/detailsApplication home

Application PropertiesConfiguring the application properties

General definitionSecurity settingsGlobalization attributes

PageA page is the basic building block of an

applicationPages also contain user interface elements

such as tabs, lists, buttons, items, and regionsTo see the definition of each page belonging

to your application, you use the Page Definition pagePage rendering Page processingShared components

Page and Page Definition

Page DefinitionPage Rendering

Controls and logic that are executed when a page is rendered

Page rendering is the process of generating a page from the database

Page ProcessingLogic controls (such as computations and processes)

that are evaluated and executed when the page is processed

Shared ComponentsComponents used by the current page that can also

be referenced by other pages within your application

Create ApplicationBuild an application with departments and

employeesTables DEPT and EMP from demo application

Inside Application builderCreate Application Type: Database From

Scratch

Create ApplicationApplication name: AnyCo Corp

Create ApplicationAdd following pages

Home (blank)Department (report from table DEPT)

Subordinate to home page Implementation: Classic Rename the page name and column headings

accordingly

Create ApplicationTabs options: One level tabsShared components: NoAttributes

Authentication scheme: Application ExpressDate format: MM/DD/YYYY

User interface theme: Theme 1 (or anyone)Confirm: Create

Run ApplicationRun application

Login with APEX user name and passwordDepartment report

Add PagesAdding an employee report and an entry formInside the application home Create Page

Form Form on a Table with ReportTable name: EMP

Define report pagePage name: EmployeeRegion title: EmployeeBreadcrumb: BreadcrumbBreadcrumb entry name: EmployeeSelect parent entry: Home

Define Report Page

Define Report PageTab options

Use an existing tab set and create a new tab within the existing tab set

Tab set: TS1 (Home)New tab label: Employee

Define Report PageSelect columns and select an edit link image

Define Form PageDefine form page

Page name: Create/Edit EmployeeRegion title: Create/Edit EmployeeBreadcrumb entry name: Create/Edit EmployeePrimary key type: Select primary key column(s)Primary key column: EMPNO

Define Form Page

Define Form PageDefine the source for the primary key

columnsExisting trigger

Define Form PageSelect the columns to include in the form

pageIdentify the process options (insert, update,

delete)

Define Form PageConfirmation and finish

Run Report and Form

Add Page NavigationAdd page navigation for employee reportApplication home page

Shared components Lists Navigation Create List Entry Sequence: 20 List Entry Label: Employee Target: Page: 3 (the employee report page)

Add Page NavigationAdd tab for departmentApplication home page

Shared components Tabs Manage tabsAdd new standard tab

Tab label: Department Tab current page: 2 (the department report page) Sequence: 15

Edit standard tab: Home Remove tab also current for pages

Add Page Navigation

ReadingsOracle Application Express SQL Workshop and

Utilities GuideManaging Database Objects with Object

BrowserUsing SQL ScriptsUsing SQL Commands

2 Day + Application Express Development GuideGetting Started with Oracle Application ExpressBuilding Your Application