Introduction to Oracle Application Express What is Application

Introduction to Oracle Application Express

What is Application Express? l Formerly called HTML-DB, Project Marvel, Einstein – l It was a really bad name for a very useful product A tool for developing and deploying database-centric, web-based applications – – – works with Internet Explorer, Netscape, Fire. Fox, Opera, etc. bundled with Oracle 10 G+ (works with Oracle 9. 0. 2. 3+) provides a wizard driven development environment l requires minimal knowledge of database & web development has many pre-configured application templates – Reports, charts, data entry forms – Drill down, hypertext linking, export to spreadsheet – l 2 uses customizable style sheets for uniform UI look & feel 3/6/2021

Architecture: Data Driven l 3 Applications are data driven – All application pages are rendered in real time from metadata stored in the database – When you create or extend your application, no code is generated; instead metadata stored in the database is created or modified – The page-rendering engine reads the metadata, generates the HTML for the page, and send it to the browser. Oracle HTTP Server Browser Oracle Database 3/6/2021

Application Security l Apps use authentication and authorization schemes – authentication: establishes the user’s identity before allowing access to the application l l – authorization: controls access to an application, a page or specific page components (region, item, button, etc. ) l l l authorization either succeeds or fails component authorization failures do not generate error messages Application Express’ built-in security prevents – – – 4 Username/password, secure key, digital certificates Several preconfigured authentication schemes are available (single-sign on, LDAP, DAD credentials, Ap. Ex accounts) intentional attempts by a user to access session state belonging to someone else inadvertent access to stale session state (belonging to the same user from an earlier time/session; mainly due to book-marking a page) linking to an Ap. Ex page out of context (for example, from a hyperlink in an e-mail - “deep linking”) 3/6/2021

The Application Express URL l The application URL identifies the location of the Oracle Application Express engine, the unique application ID, the page number, and the session ID. – – http: server: port/DAD/f? p=app_id: page_num: session_id Applications and app pages may have alphanumeric aliases. l Application aliases are unique within a workspace and page aliases are unique within each application. When you run an application, the Application Express engine generates a session ID that serves as a key to your session. Example: – l – 5 http: //apex. oracle. com/pls/apex/f? p=4350: 1: 220883407765693447 3/6/2021

Developing in Application Express l Start up browser and enter URL – l Workspace: an area with the Application Express environment where applications are created – – 6 You’ll need a developer account which includes a workspace Are associated with one or more schemas Can have one or more developers Can hold one or more applications Has an administrative account 3/6/2021

Application Express Components l 7 Application Express’ development environment has three main components – Application Builder: used to build database-centric interactive Web applications – SQL Workshop: used to run SQL statements and SQL scripts, browse the database – Utilities: used to administer the application environment, export/import data, view system metrics/logs 3/6/2021

Application Builder 8 l Used to build an HTML interface (or application) on top of database objects such as tables and procedures l Application: a collection of database-driven web pages linked together using tabs, buttons, or hypertext links. 3/6/2021

SQL Workshop l Used to view and manage database objects from a web browser – – – l Used to import data into and export data from the database – – 9 Execute SQL commands Create or modify database objects Up/Download and run SQL scripts “Query by example” Interface Browse the data dictionary Supported file formats include text (comma or tab delimited), XML documents and spreadsheets (import only) Users can create and load a table directly from a spreadsheet without any SQL knowledge 3/6/2021

Utilities l Used to administer the application environment – – – 10 Data loading/Unloading Generate DDL Monitor application activity Monitor database performance Perform schema comparisons 3/6/2021

Application Builder Concepts l l 11 Application Builder is used to assemble an HTML interface (or application) on top of database objects such as tables and procedures. Each application is a collection of pages linked together using tabs, buttons, or hypertext links. A page is the basic building block of an application. Each page contains user interface elements, such as tabs, lists, buttons, items within containers called regions. There are many different types of regions including report, form, chart, tree, HTML, etc. 3/6/2021

Generic Application Creation Workflow Create Application Create/Add Page Create/Add Region Create/Add Items Create Branches Create Processes Create Validations Create Computations Create Page/Region/Item/Branch/Process/Validation/Computation 12 3/6/2021

Create Application Workflow Select Create Application from Application Builder Select ‘Create Application’ Give application a name Add at least one page Choose a ‘look and feel’ (theme) 13 Click CREATE button on Confirm screen 3/6/2021

Pages l 14 A page is the basic building block of an application. – Each page can have buttons and fields (called items), which are grouped into containers called regions. – Pages can also have application logic (or processes). – You can branch from one page to the next using conditional navigation; perform calculations (called computations); perform validations (such as edit checks); and display reports, calendars, and charts. – You view, create, and edit the controls that define a page by accessing the Page Definition page Items (some call processes) Regions 3/6/2021

Create/Add Page(s) Workflow Open application for editing Click Create Page button Select page/region type Name the page Set page tab attributes Enter page-type specific information/attributes Confirm page creation 15 3/6/2021

Regions l l l Pages consist of regions Regions are containers which hold application components (reports, buttons, charts, etc. ) There are many different types of regions – l Regions can be created during the app creation process or added later as needed – l 16 Region attributes vary by region type Regions can be added by editing an existing page The display of a region and/or its components can be conditional 3/6/2021

Create/Add Region(s) Workflow Open application Open page to be edited Click on Create Icon in Regions Section Create Icon Select region type Enter region-type specific information/attributes Specify condition(s) for display 17 3/6/2021

Application Items/Components l Items are HTML components that appear in a region on a page. l There are two types of items: – Page items are placed on a page and have associated user interface properties, such as Display As, Label and Label Template. l l – Application items are not associated with a page and therefore have no user interface properties. l 18 Examples include a check box, date picker, display as text, file browse field, popup list of values, select list, or a text area. Page items are associated with a region on the page You can use application items as global variable. l Items are created/edited in the Items section of the Page Definition page l Tip: prefix page item names with”Pnn-” where nn is the page number (example: P 12_NAME) 3/6/2021

Create/Add Item(s) Workflow Open application Open page to be edited Click on Create Icon in Items Section Create Item Select item type Enter item-type specific information/attributes Specify condition(s) for display 19 3/6/2021

Application Express Information l http: www. oracle. com/technology/products/database/ap plication_express/index. html – l Pro Oracle Application Express – – 20 Tutorials, viewlets, forum, documentation, downloads By Scott John, Spendolini Scott, Scott J. Available in Sept, 2008 3/6/2021

Build Demo Application 21 3/6/2021

Introduction To Application Express Application Builder Concepts

Application Builder Concepts l l l 23 Application Builder is used to assemble an HTML interface (or application) on top of database objects such as tables and procedures. Each application is a collection of pages linked together using tabs, buttons, or hypertext links. A page is the basic building block of an application. When you build an application in Application Builder, you create pages that contain user interface elements, such as tabs, lists, buttons, items, and regions. There are many different types of pages/regions including report, form, chart, tree, HTML, etc. 3/6/2021

Application Builder Concepts l When you create an application in Application Builder, you link pages together using tabs, buttons, or hypertext links. – – – 24 Hyperlink Button Tab Each page can have buttons and items and can include application logic. You can branch from one page to the next using conditional navigation, perform calculations and validations, and display reports, calendars, and charts. You can generate reports, charts, and forms using built-in wizards, static HTML, or deliver more custom rendering with PL/SQL programming. 3/6/2021

Rendering and Processing Pages l l l 25 The Application Express engine dynamically renders and processes pages based on data stored in Oracle database tables. To view a rendered version of your application, you request it from the Application Express engine. When you run an application, the Application Express engine relies on two processes: – Show Page is the page rendering process. It assembles all the page attributes (including regions, items, and buttons) into a viewable HTML page. – Accept Page performs page processing. It performs any computations, validations, processes, and branching. 3/6/2021

Page Definition l 26 A Page Definition is the basic building block of a page. – Each page can have buttons and fields (called items), which are grouped into containers called regions. – Pages can also have application logic (or processes). – You can branch from one page to the next using conditional navigation; perform calculations (called computations); perform validations (such as edit checks); and display reports, calendars, and charts. – You view, create, and edit the controls that define a page by accessing the Page Definition in the Page Definition page Items (some call processes) Regions 3/6/2021

Page Definition Page l 27 The Page Definition page is divided into three sections: – Page Rendering: the process of generating a page from the database. The Page Rendering section lists user interface controls and logic that execute when a page is rendered. – Page Processing: occurs once a page is submitted. Typically a page is submitted when a user clicks a button. The Page Processing section lists logic controls (such as computations and processes) that are evaluated and executed when the page is processed. – Shared Components: lists common components that can be used by one or more pages within an application. 3/6/2021

Generic Application Creation Workflow Create Application Create/Add Page Create/Add Region Create/Add Items Create Branches Create Processes Create Validations Create Computations Create Page/Region/Item/Branch/Process/Validation/Computation 28 3/6/2021

Application Creation Options l Create Application – – – l Based on Spreadsheet: – l 29 From Scratch: Specify tabs, select a user interface theme, and many other options. Based on an Existing Application: Create a copy of another application using the same user interface theme, but minus the pages. Based on an Existing Table: Create an application based on existing tables. Includes a report page for each table, a form page to insert and update data, and a page containing a graphical chart. Create an easily deployable application from a spreadsheet (or text file). Build demo application 3/6/2021

Create Application Workflow Select Create Application from Application Builder Select ‘Create Application’ Give application a name Add at least one page Choose a ‘look and feel’ (theme) 30 Click CREATE button on Confirm screen 3/6/2021

Themes and Templates l Templates: – – – l Themes: – – l 31 HTML and CSS code that defines the layout and format of application components (reports, charts, buttons, links, tabs, menus, etc. ) control the look and feel of the components in your application. templates are organized first by type (breadcrumb, button, calendar, label, list, page, popup list of values, region, and report) and then by template classes, identifying the purpose of the each template within that type. collections of templates that can be used to define the layout and style of an entire application. the idea behind a theme is to provide a complete set of templates that accommodate every UI pattern that may be needed in an application. Knowledgeable developers can define/customize their own themes and templates. This process requires knowledge of HTML, Cascading Style Sheets, and web design. 3/6/2021

Creating a Page l A page is the basic building block of an application. When you build an application in Application Builder, you Create Icon create pages that contain user interface elements, such as tabs, lists, buttons, items, and regions. l You add a new page or add a component to an existing page by running the Create Page Wizard. You can access this wizard by: – – – 32 Clicking Create Page on the Application home page. Clicking Create on the Page Definition. Click the Create link on the Developer toolbar and then select New Page. 3/6/2021

Create Initial Page (with app) Workflow • Applications are made up of pages • Pages can be created during app creation or added later 33 3/6/2021

Adding Page(s) Workflow Open application for editing Click Create Page button Select page/region type Name the page Set page tab attributes Enter page-type specific information/attributes Confirm page creation 34 3/6/2021

Page/Region Types l Wizard-driven Page Creation – Blank Page(s): Creates one or more blank pages. – Report: Creates a report based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement Chart: create three types of graphical charts: HTML, Scalable Vector Graphics (SVG), and Flash Form: Creates a form interface with which users can update a single row or multiple rows within a table – – – – 35 Wizard: Create a wizard. Calendar: Generates a calendar with monthly, weekly, and daily views. Tree: Creates a a tree to graphically communicate hierarchical or multiple level data. Login Page: Creates a login page Access Control: Creates a page containing a access control list, enabling developers to control access to an application, individual pages, or page components Page Zero: A page zero that functions as a master page. The Application Express engine renders all components you add to page zero on every page within your application. 3/6/2021

Regions l l l Pages consist of regions Regions are containers which hold application components (reports, buttons, charts, etc. ) There are many different types of regions – l Regions can be created during the app creation process or added later as needed – l 36 Region attributes vary by region type Regions can be added by editing an existing page The display of a region and/or its components can be conditional 3/6/2021

Adding Region(s) Workflow Open application Open page to be edited Click on Create Icon in Regions Section Create Icon Select region type Enter region-type specific information/attributes Specify condition(s) for display 37 3/6/2021

Application Items/Components l There are two types of items: page items and application items. – Page items are placed on a page and have associated user interface properties, such as Display As, Label and Label Template. l l – Application items are not associated with a page and therefore have no user interface properties. l 38 Examples of page-level items include a check box, date picker, display as text, file browse field, popup list of values, select list, or a text area. Page items are associated with a region on the page You can use an application item as a global variable. l Tip: prefix page item names with”Pnn-” where nn is the page number (example: P 12_NAME) l Items are created/edited in the Items section of the Page Definition page 3/6/2021

Adding Item(s) Workflow Open application Open page to be edited Click on Create Icon in Items Section Create Item Select item type Enter item-type specific information/attributes Specify condition(s) for display 39 3/6/2021

Branches l l A branch is an instruction to go to a specific page, procedure, or URL. For example, you can branch from page 1 to page 2 after page 1 is submitted. You create a new branch by running the Create Page Branch Wizard and specifying Branch Point and Branch Type. The Branch Type defines the type of branch you are creating. – – – 40 The page number of the page to which you want to branch PL/SQL procedure which ultimately renders a branch target page A URL address 3/6/2021

Branch Points l You can control when a branch executes by making a selection from the Branch Point list. l Branch Point Options: – – 41 On Submit: Before Computation - Branching occurs before computations, validations, or processing. Use this option for buttons that do not need to invoke any processing (for example, a Cancel button). On Submit: Before Validation - Branching occurs after computations, but before validations or processing. If a validation fails, page processing stops, a rollback is issued, and the page displays the error. Because of this default behavior, you do not need to create branches to accommodate validations. However, you may want to branch based on the result of a computation (for example, to a previous branch point). 3/6/2021

Branch Points (cont’d) 42 – On Submit: Before Processing - Branching occurs after computations and validations, but before processing. Use this option to branch based on a validated session state, but before performing any page processing. – On Submit: After Processing - Branching occurs after computations, validations, and processing. This option branches to a URL or page after performing computations, validations, and processing. When using this option, remember to sequence your branches if you have multiple branches for a given branch point. – On Load: Before Header - Branching occurs before a page is rendered. This option displays another page instead of the current page or redirects the user to another URL or procedure. 3/6/2021

Adding Branch(es) Workflow Open application Open page to be edited Click on Create Icon in Branches Section Create Branch Select branch point Select branch type Enter branch-type specific information/attributes 43 Specify condition(s) for display 3/6/2021

Processes 44 l A page process is a unit of logic that performs an action at a specified point (i. e. , when a specific event occurs) during the rendering or submission of the page. l From a functional perspective, there is no difference between page-level and application-level processes. The difference between these two process types is where the process is defined, that is at the page level or at the application level. l You create a process by running the Create Process Wizard. During the wizard, you define a process name, specify a sequence and the point at which the process will execute, and select a process category. 3/6/2021

Process Types l Data Manipulation – Data Manipulation process types are frequently used by wizards to support data manipulation language (DML) actions. Application Builder supports the following declarative data manipulation processes: l l Close Popup Window – 45 Select Automatic Row Fetch and Automatic Row Processing (DML) to create an automatic data manipulation language (DML) process. Use Multi Row Update and Multi Row Delete in conjunction with tabular forms. Use Add Rows to Tabular Form in conjunction with a tabular form. Applies to processes running within a popup window. Upon execution, this process type closes the current popup window. 3/6/2021

Process Types (cont’d) l Form Pagination – l On Demand – l Creates an application-level process that can only be executed when called from a specific page. When you create this process type at the page-level, you are creating a reference to an existing applicationlevel process. PL/SQL – 46 Implements pagination through the detail records associated with a master detail form. Most often used in master detail forms (such as in the Master Detail Wizard), this process checks the master table to determine which set of detail records you are in and determines what the next detail record should be Runs the PL/SQL you provide. Use this process type to execute a block of PL/SQL entered directly into the process or to simply call an existing API or a stored procedure. 3/6/2021

Process Types (cont’d) l Reset Pagination – l Session State – l Sets the values of existing session state items to null. Select this process type to clear the cache for applications, sessions, or items as well as to clear existing user Web Services – 47 In Report regions, resets pagination back to the first result set. The Application Express engine keeps track of where the user is within a given result set. This process category returns the user to the beginning result set. In other words, this category resets the counters associated with the report region to return the first part of the result set the next time the result set displays. Implements a Web Service as a process on a page. Running the process submits a request to the service provider. 3/6/2021

Process Points l l You control when a process executes by specifying a sequence number and a process point under Process Point. You can prevent a process from running during subsequent visits to a page by selecting one of the following options under Run Process: – – 48 Once for each page visit Once for each session or when reset 3/6/2021

Adding Process(es) Workflow Open application Open page to be edited Click on Create Icon in Processes Section Create Process Select process type Select process point Enter process-type specific information/attributes Specify condition(s) for display 49 3/6/2021

Validations l l 50 Validations are used to verify the correctness of the data before page processing You can define a validation declaratively by selecting a validation method. If a validation fails, subsequent page processes or computations will not occur. The validation you enter must be consistent with the validation type you selected. 3/6/2021

Validation Methods l SQL – l 51 Compares item values to data in the database. For example, you can use a SQL validation to verify whether a last name typed into a field exists in the database. In the following Exists SQL validation, the field is named P 1_LAST_NAME and the table is named customers. SELECT 1 FROM customers WHERE last_name = : P 1_LAST_NAME PL/SQL – Useful if you need complex logic to validate entered data. For example, suppose you need to create a validation for an address form that requires the user to enter a province if the address is not in the United States. You could create the validation as a Function Returning Boolean, using the following PL/SQL: BEGIN IF : P 1_COUNTRY = 'US' AND : P 1_PROVINCE IS NULL THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; – You could also create the same validation implemented as a PL/SQL Expression as follows: NOT (: P 1_COUNTRY='US' AND : P 1_PROVINCE IS NULL); 3/6/2021

Validations (cont’d) l Item Level Null – l Item String Comparison – – l Compares the value of an item to a specific string. There are several string comparison validations that compare the value of an item to a literal string. For example, you select the validation type Item in Expression 1 is contained in Expression 2 to validate a user entry in a field against a list of values you provide. In Expression 1, enter the name of the item you want to validate without a colon, (e. g. , : P 1_VALUE). In Expression 2, enter a string of values you want to validate against. Regular Expression – – 52 Checks if an item's value in session state is null. For example, you could validate that the user enters a value in a field by creating an item validation and then selecting the validation method Item Not Null. Regular expressions provide a method to describe text patterns. Use a Regular Expression validation to perform data validation. For example, you could use the following regular expression validation to verify that a string of entered data always consists of groups of six numbers separated by commas and followed by a comma: ^([[: digit: ]]{6}, )+$ 3/6/2021

Adding Validations Workflow Open application Open page to be edited Click on Create Icon in Validations Section Create Validation Select validation level Select validation method Enter validation method specific information/attributes Specify condition(s) for display 53 3/6/2021

Computations l l Computations are used to assign a value to an identified item when a page is submitted or displayed. Computations can be defined at application level or at page level. – – – l 54 Most application-level computations are performed for every page in an application Page-level computations only execute when that page is rendered or processed Similar to branches and processes, you can define a point at, (computation point), which the computation will be performed. This includes during page rendering or during page processing If a computation fails, you can optionally define an error message. 3/6/2021

Adding Computations Workflow Open application Open page to be edited Click on Create Icon in Computations Section Create Computation Select computation level Select computation item Enter computation Specify condition(s) for display 55 3/6/2021