Introduction to Oracle Application Express

1. Introduction to Oracle Application Express Addendum: Reports, Forms, and other Components

2. Report Regions • A report is the formatted result of a SQL query. You can generate reports by selecting and running a built-in query, or by defining a report region based on a SQL query. • Reports can be created as part of the application creation process (using a wizard) or can be added to the application later on in the development process • Report types: • SQL Report - a report based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement that you provide. • Interactive Report - an interactive report based on a custom SQL SELECT statement you provide. End users can customize the layout of their data by selecting options on the Actions menu. • Report Web Service Result - a report based on a web service result. • Wizard Report - Creates a report without requiring any SQL knowledge. Report created based on your specification of schema, table, columns.

3. Creating A Report Workflow • Invoke the Create Region feature • Choose REPORT for the region type • Select the report type • Follow the report wizard steps

4. Interactive Reports • Pre-defined report format with many built-in features • Available with ApEx 3.1 and up • Built-in capability for • Searching/filtering • column selection • chart creation • aggregation • customizing report • viewing past versions of data • Conditional formatting

5. Editing Reports • Reports and their attributes are edited from the Page Definition page • Two type of reports may be listed in this page, REPORT and RPT, INTERACTIVE • REPORT – a regular report • RPT – a report created using the Report Wizard • INTERACTIVE – a report built with standard interactive features

6. Region/Report Definition Page • Defines the region in which the report resides • Set report title, display conditions, position on screen • Can add custom HTML/Javascript to the region’s HTML header section • For a regular/interactive reports, contains the SQL statement defining the report • For wizard generated reports, a separate page will contain the query definition

7. Report Attributes Page • Alter column header, display sequence, column sorting attributes, aggregation, visibility • Provide access to the edit page for column attributes • Define report layout and pagination • Display position, number of rows • Specify report export/download settings • file type (CSV or XML) • file name (can be dynamic) Edit Column Attributes

8. Column Attributes Page • Specifies the attributes for specific columns in the report • column header • display attributes • aggregation/summary settings • Define column link, display conditions, authorization, help text

9. Forms • Pages that accept and process user input • You can include a variety of different types of forms in your applications. • You can include forms that enable users to update just a single row in a table or multiple rows at once. • Application Builder includes a number of wizards you can use to create forms automatically, or you can create forms manually.

10. Form Page Types Form Page TypeDescription Form on a Procedure Builds a form based on stored procedure arguments. Use this approach when you have implemented logic or data manipulation language (DML) in a stored procedure or package. Form on a Table or View Creates a form that enables users to update a single row in a database table. Form on a Table with Report Creates two pages. One page displays a report. Each row provides a link to the second page to enable users to update each record. Note: This wizard does not support tables having more than 127 columns. Master Detail Form Creates a form that displays a master row and multiple detail rows within a single HTML form. With this form, users can query, insert, update, and delete values from two tables or views.

11. Form Page Types (cont’d) Form Page TypeDescription Tabular Form Creates a form in which users can update multiple rows in a database. Form on a SQL Query Creates a form based on the columns returned by a SQL query such as an EQUIJOIN. Summary Page Creates a read-only version of a form. Typically used to provide a confirmation page at the end of a wizard. Form on Web Service Creates a page with items based on a Web service definition. This wizard creates a user input form, a process to call the Web service, and a submit button. Form and Report on Web Service Creates a page with items based on a Web service definition. This wizard creates a user input form, a process to call the Web service, a submit button and displays the results returned in a report.

12. Form on a Table With Report

13. Tabular Forms • Enables you to update multiple rows in a table • The Tabular Form Wizard creates a form to perform update, insert, and delete operations on multiple rows in a database table. • NOTE: Any modification of the select list of a SQL statement of a tabular form after it has been generated is not recommended. If you do modify the query, make sure the values of the updateable columns are not altered after being queried from the database by the Application Express engine.

14. Master Detail Forms • reflects a one-to-many relationship between two tables in a database. • displays a master row and multiple detail rows within a single HTML form. • with this form, users can insert, update, and delete values from two tables or views.

15. Form Processing • Once you create a form, the next step is to process the data a user types by inserting into or updating the underlying database tables or views. • There are three ways to process a form: • create an Automatic Row Processing (DML) process. • you are not required to provide any SQL coding. • Application Express performs DML processing for you. • this process automatically performs lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently. • create one or more processes to handle insert, update, and delete actions. • Instead of having the Application Express engine handling everything transparently, you are in complete control. • created a package to handle DML operations • you can call procedures and functions within this package from an After Submit PL/SQL process to process insert, updates, and delete requests.

16. Populating Forms • Oracle Application Express populates a form either “on load” or when the Application Express engine renders the page. • To populate a form: • Create a Data Manipulation process and define the type as Automated Row Fetch • Create a PL/SQL process to populate the form manually by referencing a hidden session state item. • PLSQL code can set page items values based on results of some operational logic (query, calculation, etc) • The process point for this process would be “Onload – Before Regions”

17. Validations in Forms • Used to check data before sending it to be processed. • Once you create a validation and the associated error message, you can associate it with a specific item. • You can choose to have validation error messages display inline (that is, on the page where the validation is performed) or on a separate error page. • There are several methods by which validation can be implemented. • Using PL/SQL, SQL – involves coding • Using pre-defined test conditions • comparing the current value of a page item to another item or to a constant • checking for the presence of a value (or not).

18. Charts • Application Builder includes built-in wizards for generating HTML, Scalable Vector Graphics (SVG), and Flash charts. • Oracle Application Express supports only those three types of graphical charts. • You define a chart in Application Builder using a wizard. For most chart wizards, you select a chart type and provide a SQL query using the following syntax: SELECT link, label, value FROM ... Where: link is a URL. label is the text that displays in the bar. value is the numeric column that defines the bar size. • Example: SELECT null, last_name, salary FROM employees WHERE DEPARTMENT_ID = :P101_DEPARTMENT_ID • Note: Do not change the type of an existing chart. Instead, delete the chart and then re-create it.

19. About Chart Types • About Flash Charts • support based on the Anychart Flash Chart Component • are rendered by a browser and require Flash player 8 or later. • More info available at: http://www.anychart.com • About SVG Plug-in Support • an XML-based language for Web graphics from the World Wide Web Consortium (W3C). • When evaluating whether or not an SVG chart is the appropriate chart type for your application, remember that: • some Web browsers do not support SVG charts. • most Web browsers that support SVG charts require that users download an SVG plug-in.

20. Page Items • An item is part of an HTML form (not a data entry form). • An item can be a text field, text area, password, select list, check box, etc. • Item attributes affect the display of items on a page. • You can use HTML/JavaScript techniques to customize item appearance/behavior • 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. • Application items are not associated with a page and therefore have no user interface properties. They can be treated as global variables. • You can associate validations and/or do computations with items • 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_DOC_NAME

21. Create/Edit Item 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

22. Item Types See online documentation for detail description of all items

23. Item Edit Page • Allows you to: • Name the item, • Select the item type • Specify the region in which the item resides • Set item attributes • Specify item source/default value/LOV • Specify the item’s label • Set the item’s display condition • Enter item help message

24. List of Values • A list of values (LOV) is a static or dynamic set of values used to display a specific type of page item, such as popup lists of values, a select list, a check box, a radio group, or multiple select lists. • Creating a LOV as a shared component has a number of advantages: • It can be added to any page within an application. • All LOV definitions are stored in one location, making them easy to locate and update. • You define named (or shared) LOVs at the application level by running the Create LOV Wizard and adding them to the List of Values repository. • All LOVs can be defined as static or dynamic. • Static lists are based on predefined pairs of display values and return values. • Dynamic lists are based on a SQL query you write that selects values from a table. • SELECT ename display, empno returned • FROM emp • WHERE deptno = :P1_DEPTNO • ORDER BY ename

25. Create LOV Workflow • Invoke the Create LOV feature • Choose LOV creation method – scratch/copy • Choose LOV type – static or dynamic • Define LOV source • Static: enter entries and values • Dynamic: enter SQL

26. Themes and Templates • Application Express creates the user interface of an application based on a named collection of templates called a theme. • Collections of templates that can be used to define the layout and style of an entire 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.

27. Region/Report Template Edit Pages Region Template Edit Page Report Template Edit Page

28. Lists • Lists are a shared collection of links. • Add a list to a page by creating a region and specifying the region type as List. • control the appearance of a list through list templates. • each list element has a display condition, which enables you to control when it displays. • you can define a list element to be either current or non-current for a specific page. • further specify what current looks like using template attributes. • Once created, you need to add entries to a list. You can add list entries from scratch, copy one list entry within a list, or copy existing entries from one list to another. • You can also create hierarchical lists that contain sublists.

29. Breadcrumbs • Breadcrumbs provide users with hierarchical navigation. • A breadcrumb is a hierarchical list of links that display using templates. • You can display a breadcrumb as a list of links or as a breadcrumb path. • A breadcrumb trail indicates where the user is within the application from a hierarchical perspective. • users can click a specific breadcrumb link to instantly view the page. • breadcrumbs act as a second level of navigation at the top of each page, complementing other user interface elements such as tabs and lists. • You can create breadcrumbs while creating a page, or manually by running the Create Breadcrumb Wizard.

30. Trees • Tree are used to effectively communicate hierarchical or multiple level data. • Trees are created using the Create Tree wizard (from the Shared Components page). • Unlike most components, trees reside on their own page and are referenced from other pages • A tree is based on a query and returns data that can be represented in a hierarchy. • The hierarchy is determined by the relationship between the values of a “child”column and a ”parent” column.

31. Create Tree Workflow Access Shared Components page Click on TREES link Enter name for tree Select tree and page template/attributes Enter query