Introduction to Oracle Application Express

1. Introduction to Oracle Application Express

2. What is Application Express? • Formerly called HTML-DB, Project Marvel, Einstein • 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, FireFox, Opera, etc. • bundled with Oracle 10G+ (works with Oracle 9.0.2.3+) • provides a wizard driven development environment • 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 • uses customizable style sheets for uniform UI look & feel

3. Oracle HTTP Server Browser Oracle Database Architecture: Data Driven • 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.

4. Application Security • Apps use authentication and authorization schemes • authentication: establishes the user’s identity before allowing access to the application • Username/password, secure key, digital certificates • Several preconfigured authentication schemes are available (single-sign on, LDAP, DAD credentials, ApEx accounts) • authorization: controls access to an application, a page or specific page components (region, item, button, etc.) • authorization either succeeds or fails • component authorization failures do not generate error messages • Application Express’ built-in security prevents • 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 ApEx page out of context (for example, from a hyperlink in an e-mail - “deep linking”)

5. The Application Express URL • 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. • 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: • http://apex.oracle.com/pls/apex/f?p=4350:1:220883407765693447

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

7. Application Express Components • 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

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

9. SQL Workshop • Used to view and manage database objects from a web browser • Execute SQL commands • Create or modify database objects • Up/Download and run SQL scripts • “Query by example” Interface • Browse the data dictionary • Used to import data into and export data from the database • 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

10. Utilities • Used to administer the application environment • Data loading/Unloading • Generate DDL • Monitor application activity • Monitor database performance • Perform schema comparisons

11. Application Builder Concepts • 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.

12. 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

13. 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) Click CREATE button on Confirm screen

14. Pages • 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 Regions Items (some call processes)

15. 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

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

17. 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

18. Application Items/Components • Items are HTML components that appear in a region on a page. • 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. • 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 • Application items are not associated with a page and therefore have no user interface properties. • You can use application items as global variable. • Items are created/edited in the Items section of the Page Definition page • Tip: prefix page item names with”Pnn-” where nn is the page number (example: P12_NAME)

19. 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

20. Application Express Information • http:www.oracle.com/technology/products/database/application_express/index.html • Tutorials, viewlets, forum, documentation, downloads • Pro Oracle Application Express • By Scott John, Spendolini Scott, Scott J. • Available in Sept, 2008

21. Build Demo Application

22. Introduction To Application Express Application Builder Concepts

23. Application Builder Concepts • 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.

24. Application Builder Concepts • When you create an application in Application Builder, you link pages together using tabs, buttons, or hypertext links. • 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. Hyperlink Button Tab

25. Rendering and Processing Pages • 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.

26. Page Definition • 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 Regions Items (some call processes)

27. Page Definition Page • 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.

28. 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

29. Application Creation Options • Create Application • 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. • Based on Spreadsheet: • Create an easily deployable application from a spreadsheet (or text file). • Build demo application

30. 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) Click CREATE button on Confirm screen

31. Themes and Templates • Templates: • 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. • Themes: • 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.

32. Creating a Page • 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. • 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: • 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. Create Icon

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

34. 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

35. Page/Region Types • 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 • 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.

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

37. 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

38. Application Items/Components • 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. • 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 • Application items are not associated with a page and therefore have no user interface properties. • You can use an application item as a global variable. • Tip: prefix page item names with”Pnn-” where nn is the page number (example: P12_NAME) • Items are created/edited in the Items section of the Page Definition page

39. 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

40. Branches • 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. • 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

41. Branch Points • You can control when a branch executes by making a selection from the Branch Point list. • Branch Point Options: • 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).

42. Branch Points (cont’d) • 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.

43. 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 Specify condition(s) for display

44. Processes • 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. • 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. • 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.

45. Process Types • 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: • 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. • Close Popup Window • Applies to processes running within a popup window. Upon execution, this process type closes the current popup window.

46. Process Types (cont’d) • Form Pagination • 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 • On Demand • 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 application-level process. • PL/SQL • 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.

47. Process Types (cont’d) • Reset Pagination • 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. • Session State • 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 • Implements a Web Service as a process on a page. Running the process submits a request to the service provider.

48. Process Points • 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: • Once for each page visit • Once for each session or when reset

49. 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

50. Validations • 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.