2020.08.17 Oracle Application Express 20.1.0.00.13 Copyright © 1999, 2020
I. BASICS Top.....2.1 Apexws_cloud.....2.2 App. builder.....2.3 Page
.....2.5 Environm .....2.7 URL
II. APP .....2.9 Sales web app - DDL, add data
III. SHARED COMPOMENTS .....3.2 SHARED C. (lists) 3.2.1 Menu......3.2.2 Rep.List.....3.2.3 Ord.WizList
.....3.3 Top. right Navig.....3.4 LOVs....3.5 IMGs
IV. APP PAGES .....4. Home p......4.4 Buttons.....4.5 PgStyles.....5. Cust.....6. Product.....7. Order
.....8. Graph. & Mobile.....9. Adv. Rep
.....10. Authoriz.....11. Search--Style--Calendar.....12. Deploy
This txt on my Github : https://github.com/slavkoss/fwphp/blob/master/fwphp/glomodul/mkd/01/001_db/oracle_apex.txt (in Markdown format) is based on https://drive.google.com/drive/my-drive 2020_Riaz_Oracle_APEX_20_For_Beginner.pdf : , URL to Download Book Code (no DDL) https://tinyurl.com/oracleapex20
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C. ......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs.....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Cloud Dev APEX : https://apex.oracle.com/pls/apex/f?p=4500:1000 is opened (APEX add :722441254522763:::::)
or dev URL https://apex.oracle.com/pls/apex/f?p=4550
To sign in Cloud Dev APEX : https://apex.oracle.com/en/ -> "Sign in" button which opens Login page
After "p=" is app alias 4500 (appID) means eg sales-web-app4, possys is workspace. 1000 is pageID.
To leave APEX dev. envir, click your name (appearing at top-right) and select Sign Out.
Cloud run Cloud APEX Sales app : https://apex.oracle.com/pls/apex/possys/r/sales-web-app4/home (APEX add ?session=710994255959550)
https://apex.oracle.com/en/ Oracle APEX site
click "Get Started for Free" button
click "Request a Free Workspace" button
On the Identification wizard screen, enter : Request a Workspace
First Name Slavko Last Name Srakocic
Email slavkoss22@gmail.com
Workspace possys Country Croatia Usage Personal
Confirm. email : Workspace Request Approved
Environment: https://apex.oracle.com/pls/apex/
Click "Create Workspace" button to complete the approval process and set your psw.
Workspace Successfully Created Your request for an account has been approved.
Button "Continue to Sign in screen" psw=MYLONGPSW opens page :
Cloud Dev APEX : https://apex.oracle.com/pls/apex/f?p=4500:1000 (+ :722441254522763:::::) is opened
button "Sign in" opens same URL, page "App Builder"
Click on APEX opens page "APEX" contans "Top menus" of page "App Builder" as icons, instead "Dashboard" is "Team development".
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
https://apex.oracle.com/pls/apex/possys/r/sales-web-app4/home (+ eg ?session=706440515653484)
Five tables : DEMO_STATES, DEMO_CUSTOMERS, DEMO_PRODUCT_INFO, DEMO_ORDER, and DEMO_ORDER_ITEMS.
in folder J:\awww\www\fwphp\glomodul\mkd\01\001_db
app 4500, pg 3002 is SQL workshop : https://apex.oracle.com/pls/apex/f?p=4500:3002
app 4500, pg 1001 is SQL workshop Object browser : https://apex.oracle.com/pls/apex/f?p=4500:1001
See at end this txt DB Objects
See at end this txt DB Objects data
In chapter 3, we create building blocks (shared components) of app.
Shared Components wizards allow us to define components we can re-use in pages (better name "modules" like Ora. Forms) throughout our app. (common, global on app level).
Work in detail on Home page in Chapter 4 to convert it into dashboard. Chapter 5 - Customers. Chapter 6 - Products. Chapter 7 -Orders.
You can see a list of all Shared Components utilized on some app page by accessing its "Shared Components" tab in the Page Designer.
See 3.1 About Shared Components
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
To access Shared Components page :
For each list entry, you specify display text, a target URL, and other properties to control when and how the list entry displays. You control the display of the list and the appearance of all list entries by linking the list to a template.
APEX App Builder wizard created default Home and Administration mnuitems.
Main top menu is hierarchical list of navigation, which appears either as a responsive side bar or at top of window. Based on available space, navigation bar either displays a full menu or collapses to a narrow icon bar.
Modify Desktop Navigation Menu List list to add app submenu level1 entries Setup, Orders, Reports :
In Shared Components -> Navigation section -> click "Navigation Menu" option
On Lists page, click "Desktop Navigation Menu" option, which carries two entries Home and Administration
On List Details page, click Create Entry button (A) to create new menu item named Setup. This menu entry will have sub-entries that will allow you to access Products and Customers modules.
Fill in values for Setup menu submenu level1 entry :
Using button labeled Create and Create Another, create two more level-1 entries to form the main menu of our app because we set No Parent List Item for all three entries.
Parent List Entry | Image/Class | List Entry Label | Target Type | PgID | Help |
---|---|---|---|---|---|
No Parent List Item | eg fa-database | Setup | No Target | not associated to any app page | |
No Parent List Item | eg fa-send-o | Orders | Page in this app | 4 | |
No Parent List Item | eg fa-table-arrow-up | Reports | Page in this app | 1 |
After adding last entry (Reports), click Create List Entry button.
Using same process create submenus level-2 menu entries. First two entries will come under the main Setup menu item, while Reports menu will contain two child entries (Graphical Reports and Advance Reports).
Parent List Entry | Image/Class | List Entry Label | Target Type | PageID | Help |
---|---|---|---|---|---|
Setup | eg ... | Manage Customers | Page in this app | 2 | |
Setup | eg ... | Manage Products | Page in this app | 3 | |
Reports | eg .. | Graphical Reports | Page in this app | 1 | |
Reports | eg ... | Advance Reports | Page in this app | 1 |
TIP: If you make a mistake while creating these menu entries, you can rectify it. After creating the last entry, click Create List Entry button on Create/Edit page to move back to the List Details page. On this page, click menu entry you want to modify (under Name column) to call its definition in Create/Edit page. Rectify the error and click Apply Changes button.
Create llevel 3 entries. First 5 entries will appear as submenu choices under Graphical Reports menu. Similarly, Monthly Review Report and Customer Invoice will be placed under Advance Reports. All the settings will set up a hierarchical navigation for your app.
Parent List Entry | Image/Class | List Entry Label | Target Type | PageID | Help |
---|---|---|---|---|---|
Graphical Reports | eg ... | Customer Orders | Page in this app | 1 | |
Graphical Reports | eg ... | Sales By Category/Products | Page in this app | 1 | |
Graphical Reports | eg ... | Sales By Category/Month | Page in this app | 1 | |
Graphical Reports | eg ... | Order Calendar | Page in this app | 1 | |
Graphical Reports | eg ... | Product Order Tree | Page in this app | 1 | |
Advance Reports | eg ... | Monthly Review Report | Page in this app | 0 | set to zero, because it will be invoked through a print request that will be configured in Chapter 9. |
Advance Reports | eg ... | Customer Invoice | Page in this app | 1 |
TIP: After making any modification in your app you can test it immediately. For example, after creating the navigation menu, hit Run Page button (at top-right) to see the app menu.
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p.
List "Reports List" will have several links that will lead to different reports in your app. Note that you created the same links in the navigation menu in the previous section to call some of these reports from app menu. Reports List being created here will be used on a dedicated report page to call respective reports - see Chapter 8 section 8.2.
Go to App 80858 -> Sales Web App -> Shared Components -> Navigation -> Lists. There are 7 default (APEX created) lists.
Click Create button to create a new list.
Select From Scratch on the Source wizard screen and click Next.
On the next screen, enter Reports List for Name, select Static as the list Type, and click Next.
When you create a static list you define a list entry label and a target (either a page or URL).
Enter the following values in Query or Static Values screen.
Initially, the wizard allows you to create five entries. The remaining entries and Image/Class properties are created and set after saving the first five.
List Entry Label | Target PageID | Image/Class |
---|---|---|
Customer Orders | 17 | |
Sales by Category and Product | 16 | |
Sales by Category / Month | 5 | |
Order Calendar | 10 | |
Product Order Tree | 19 | |
Gantt Chart | 20 | |
Box Plot | 21 | |
Pyramid Chart | 22 | |
List View (Mobile) | 23 | |
Column Toggle Report (Mobile) | 24 | |
Reflow Report (Mobile) | 25 | |
Monthly Review Report | 0 | |
Customer Invoice | 50 |
After entering the first five list entries
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p.
List 3 is another utilization of lists. Rather than associating list items to pages in the app, you will use it for visual representation. It will be used while creating orders in Chapter 7. In our app, we will create an order using a set of wizard steps in the following sequence:
So :
Go to Shared Components | Navigation | Lists and click Create button
Select the first From Scratch option and click Next
Type Order Wizard in the Name box, set Type to Static, and click Next
On the Query or Static Values screen, enter the following values and click Next
List Entry Label | Target Page ID or custom URL | Help |
---|---|---|
Identify Customer | 11 | |
Select Items | 12 | |
Order Summary | 14 |
Click Create List button on the Confirm screen.
Modify the newly created Order Wizard list.
Edit each list item, set Target Type attribute to No Target for all 3 list items.
List Entry Current for Pages Type attribute specifies when this list entry should be current. Based on the value of this attribute, you define a condition to evaluate. When this condition is true then the list item becomes current. The template associated with list item gives users a visual indication about the active list item. The following figure illustrates the use of Order Wizard list. Being the first step in the order wizard, the Identify Customer list item is marked as current (when Page 11 is called to enter a new order), while the remaining two are displayed as non-current. After selecting a customer, when you move on to the next step to select ordered items, the Select Items entry becomes current and the first and last entries become inactive.
-------------------------------
| |
| |
Non current -------------------------------
-------------------------------
| |
| |
Current -------------------------------
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p.
Also used to link various pages within app eg Help pages, Sign Out. Location eg top right of navigation bar depends upon associated page template. When you create a navigation bar, you specify an image name, label, display sequence, and target location (a URL or a page). Navigation bar used in our app will show :
feedback page icon, Page Help entry, About Page entry, id of logged in user and a Sign Out link.
All these entries are created automatically when you create new app.
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs.....Home p.
Used to control input values and limit users selection. You can define two types of lists:
See 6.4.2 Attach Categories LOV.
You will use this LOV in Chapter 7 section 7.4.2.
select apex_escape.html(product_name) || ' [$' || list_price || ']' d
, product_id r
from demo_product_info
where product_avail = 'Y'
order by 1
APEX_ESCAPE.HTML function is used to protect against XSS (Cross Site Scripting) attacks. It replaces characters that have special meaning in HTML with their escape sequence. It converts occurrence of :
```
& to &
“ to "
< to <
> to >
```
See 5.4.2 Change cust. column "P7_CUST_STATE" to hold predefined LOV! States list.
Will be incorporated in initial Order Wizard step (Chapter 7 Section 7.5.4) to select an existing customer for a new order or to create new one.
Fill in display and return values as shown in following tbl and click Create LOV button.
Sequence | Display value | Rerturn value |
---|---|---|
1 | Existing customer | EXISTING |
2 | New customer | NEW |
Top.....Apexws_cloud.....App. builder.....Page Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
You can reference images within your app by uploading them to Images Repository. When you upload an image, you can specify whether it is available to all apps or a specific app. Images uploaded as shared components can be referenced throughout an app. They may
Images uploaded to the images repository should not be directly related to the app data such as images of products and employees. Such images must be stored in the apps schema alongside the data to which the image is related. You will follow this approach in Chapter 6 to save each products image along with other information in a DB table.
APEX images are divided into two categories:
Logo appears at every page top in app.
When you select a type for your app logo, additional attributes appear depending upon your selection. With this selection, your app logo and app name both will be displayed on each app page. The built-in substitution string (APP_IMAGES) is used to reference uploaded images, JavaScript, and cascading style sheets that are specific to a given app and are not shared over many apps. You must use this substitution string if you upload a file and make it specific to an app. Note that you must use the correct case for the image file name and extension, else the logo will not be displayed at runtime. Click the Apply Changes button.
Run the app.
with 6 stacked canvases (blocks) Top.....Apexws_cloud.....App. builder.....Page Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs....Home page.....Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
4.1 About Home Page
Represents objective of app (module). Is created as a blank slate and needs to be populated with content relevant to your app (module) - stuff related to sales is :
6 regions to present different views of sales data (see 2.6 and 2.9 Page Designer interface and how to access your workspace).
4.2 Modify Home Page - 2 properties that are usually enough to set for the main page
Sign in to your workspace -> in main menu click App Builder option -> click Edit icon under Sales Web App - see Figure 3-3 in chapter 3.
Click Home page icon (if you’re browsing the page in Icon view). This action will open definitions of Home page in Page Designer interface.
Modify Page Attributes Home page Name and Title properties with meaningful labels.
Rendering tab to your left -> click root node to refresh Property Editor (on the right side) with the main page properties. Set the properties mentioned in the following table and click the Save button (at the top-right corner).
Property | Value | Note |
---|---|---|
Name | Sales Web App | |
Title | Sales Web App | APEX engine uses title in place of #TITLE# substitution string used in page template inserted between the HTML tags |
Use help : Click a property in the Property Editor and then click the Help tab (in the Central pane).
You put page items (Text Field, Select List, Radio Group, report, chart, static HTML content, buttons...) on a page under a specific region (page area, section that serves as a container for content ee to group page elements). Each region can have its own template, which controls its appearance.
Some of our regions will use Oracle JET Charts. Oracle JET Charts (charting library) is a component of JET. These charts work on any modern browser regardless of platform, screen size, or features. JET is integrated Oracle JavaScript and CSS3 and HTML5 Extension, open source Toolkit
To remove a component (such as a region or an item) from a page, right-click the desired component in the Rendering section, and select Delete from the context menu. If you just created the component, click Undo (A) on the Toolbar to remove it from the page.
Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6.....Home buttons.....Home styles
Displays top five orders by date from DB using a bar chart - horizontal bars=months and sumsales on x axe - SQL (or PL/SQL function) populated - summarized sales figures (bars) for each date from Orders table.
!!!!! Rendering tab -> right-click Regions -> select Create Region to place new region = </> New under Content Body.
Click "</> New node" (future "Top Orders by Date" stacked canvas) and set common region properties in Property Editor.
Property Editor is declarative (ee functional programming) above some prog.language procedural code.
Property | Value | Help | |
---|---|---|---|
1 | Title | Top Orders by Date | Title should be unique to every region - region purpose . Region has child node - component - named Attributes - contains region-specific properties (region properties are common to all regions). Eg properties of a Static Content type region are different from a Chart region. |
2 | Type | Chart | = page or page component purpose. Default type is "Static Content = HTML max 33.8 kB" region with an empty source text eg About section on the right side of the App Builder interface region whose source is displayed text. After setting the second attribute (Type), you will be informed through the Messages tab that there are some errors on the page. These messages relate to some mandatory properties you will set accordingly in subsequent sections. |
Location | Local Database | or sourced from : 2. Remote DB, connection is defined using REST Enabled SQL or from 3. Web Source eg RESTful web service defined using Web Source Modules | |
Type | SQL Query | or PL/SQL fn - how the data is queried | |
3 | SQL Query | select order_day, sales from (select to_char(o.order_timestamp,'Mon DD, YYYY') order_day, SUM(o.order_total) sales from demo_orders o group by to_char(o.order_timestamp,'Mon DD, YYYY'), order_timestamp order by 2 desc nulls last) where rownum < 6 | order by 2 desc nulls last is in 11g DB, but order by 2 desc nulls last fetch first 5 rows only - not in 11g DB |
4 | Start New Row | On (default) | DB apps created in Oracle APEX use layout comprising rows with 12 columns to position page elements. "On" puts region on a new row. Next region "Sales for This Month" - Start New Row attribute it is set to Off to place that region adjacent to this one. |
5 | Column | Automatic (default) | Automatically finds a column position (col_ordnum 1 to 12) for the region. There are three regions on a single row, each region spans 4 columns. Region1 will span from column number 1 to 4, R2 from 5 to 8, R3 from 9 to 12 |
6 | Column Span | 4 | narrows "Top Orders by Date" region |
7 | Show Region Icon | check mark to select this option | Under Template Options |
Body Height | 240px | Under Template Options (Click OK to close dialog screen) | |
Icon | fa-lg fa-apex | If Template property is set to default "Standard" value for a region, you can place an icon in region header. First, select Show Region Icon option (under Template Options) to display region icon in region header beside region title. Then, click the LOV for the Icon property (under Appearance), select a Style (for example, Large), and choose and icon from provided list. |
Click Attributes node under new region and set the following region-specific properties :
Set Type of this chart region to horizontal bar :
Property | Value | |
---|---|---|
8 | Type | Bar |
9 | Orientation | Horizontal |
Click the New sub-node under Series and set the following properties:
Property | Value | Help | |
---|---|---|---|
10 | Location (under Source) | Region Source | When you set the region type to Chart (2), a Series node is placed underAttributes with a New sub-node under it. In this node you specify Location (10) for the Series. Since an SQL Query has already been defined, we set it to Region Source, which points to the region’s SQL Query defined in the third attribute. By default, a chart is created with one series (named New), but you can add more (see Chapter 8 section 8.3 steps 5 and 6). |
11 | Label | ORDER_DAY | Label attribute (11) is set to ORDER_DAY column to display values from this column as labels |
12 | Value | SALES | to show sales figures (bars) |
13 | Type (under Link) | Redirect to Page in this application |
Click No Link Defined under Target and set props in Link Builder dialog box:
Define links on charts is done in properties 13-15. Links let you call another app page for browsing details.
Property | Value | Help | |
---|---|---|---|
14 | Type | Page in this application | When you click "No Link Defined" under Target prop., a small window titled Link Builder comes up, where you specify target page details. Once you set the link type to "Redirect to Page in this Application", a property named Target appears, where you provide the ID of the target application page you want to link with the chart (properties 14 and 15). The Template property (not indicated in the previous table) is set to Standard by default, which forms a border around the region and displays the regions title across the top. |
15 | Page | 4 (Click OK to close the dialog screen) |
To test your work from time to time eg after completing this region you can save and run page (by clicking the Save and Run Page button at the top-right corner) to check how the region appears on it. At this stage, your Home page will show just one region (Top Orders by Date). If you click any bar in the chart, app tries to open Page 4 (Orders) and throws an error, because Orders page does not exist. After completing Page 4 (Orders) in Chapter 7, when you run the Home page and click any of these links, Page 4 will be rendered carrying a list of orders.
Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles
Page region 2 uses a Badge List - 2 circles "Tot.sales and Tot.orders". List is dynamically rendered based on a SQL Statement (or PL/SQL function) each time the page is viewed. Later on, we will transform it to present fetched data in graphical format.
Create another region as previous region.
Rendering tab to your left App builder -> Home icon (App 80858) -> Page Designer -> right-click Regions -> select Create Region from context menu. New region will be created under previous one. Set following properties for this region in Property Editor.
TIP: If a region is not created in the desired location, drag and drop it to the appropriate location in the rendering tree.
Property | Value | Help | |
---|---|---|---|
1 | Title | Sales for This Month | |
2 | Type | Classic Report | |
Location | Local Database (default) | ||
Type | SQL Query | or PL/SQL function to fetch the desired data set. All columns you define in the query appear in a separate node (Columns) under region | |
3 | SQL Query | select sum(o.order_total) total_sales, count(distinct o.order_id) total_orders, count(distinct o.customer_id) total_customers from demo_orders o where order_timestamp >= to_date(to_char(sysdate,'YYYYMM') || '01','YYYYMMDD') | |
4 | Start New Row | Off | |
5 | Column | 5 | |
7 | Body Height (under Appearance -> Template Options) | 240px |
right-click Sales for this Month region and select Create Page Item from the context menu. A new node named Items will be created with a new item named P1_NEW. Hidden items can be seen in the Page Designer, but they do not appear on the page at run time.
Click the new item and set the following properties:
Property | Value | Help |
---|---|---|
Name | P1_THIS_MONTH | was P1_NEW |
Type | Hidden | |
Value Protected | On (default) | prevents item value from being manipulated when page is posted |
Type (under Source) | PL/SQL Expression | |
PL/SQL Expression | to_char(sysdate ,'MM') || '01'|| to_char(sysdate ,'YYYY') | or if Order Date column on Page 4 is rendered as 09-JAN-2017 : '01-'||to_char(sysdate ,'MON')||'-'||to_char(sysdate ,'YYYY') |
Select TOTAL_CUSTOMERS column and set the Type attribute of this column to Hidden Column (invisible at run-time).
How to refer to a page item in links - present it as a substitution string : &P1_THIS_MONTH. - terminated with a period !! - see Value property in serial 6 in the following table.
Rendering section -> expand Columns node under the Sales for This Month region -> click TOTAL_SALES column - Figure 4-4. Set following props :
Property | Value | Help | |
---|---|---|---|
1 | Type | Link | column is to be displayed as a link |
2 | Format Mask | in LOV select 5,234.10 | produces the mask 999G999G999G999G990D00 from list of values that shows some common currency and date/time formats, 9 is optional digit, 0 is required digit, G is thousand separator, and D is for decimal point |
Define link : Click No Link Defined under Target -> Link Builder dialog is opened -> set following properties. To call another app page, it is suffice to transform a column into a link by setting three values : Link, Page in this application, and Page Number. Recall that in previous region you formed a similar kind of link.
Property | Value | Help | |
---|---|---|---|
3 | Type | Page in this app | link should call a page in current app |
4 | Page | 4 | Target page number. Next prop. Name (5) and Value (6) are values to be passed from currpg to targetpg. They form a filter argument to display current month's order on the target page (Page 4 - Orders, to be created in Chapter 7). Values for properties (5) and (6) are usually picked from LOVs using Page attribute, but due to absence of Page 4 of our app, we entered them manually. |
5 | Name (under Set Items) | IRGTE_ORDER_DATE | to specify session state for an item |
6 | Value (under Set Items) | &P1_THIS_MONTH. (do not forget to add the trailing period) | to specify session state for an item |
7 | Clear Cache | RIR,4 | resets the interactive report on Page 4 |
8 | Action | Reset Pagination | resets pagination of the target page |
Click OK to close the Link Builder - Target dialog box
Property | Value | ||
---|---|---|---|
9 | Link Text | Text #TOTAL_SALES# (select this value using the Quick Pick button) | column to be displayed as a link |
In the current scenario, we used one name/value pair to filter interactive report on the target page. However, this section allows you to set as many filters as you want. Each time you provide a value, another row is appended, thus allowing you to enter another pair of name/value. You can use this section to also specify target pages items in the Name column and can set their values using the Value box.
For example, to set a customers credit limit items value on the target page, enter the name of that item :
(P7_CREDIT_LIMIT) in the Name box and type the corresponding value (5000) in the Value box. This way, when you call the target page, the value (5000) appears in the credit limit item.
Note that column names are enclosed in # symbol when you specify them in Link Text attribute. This is a mandatory attribute whose value can be selected using the Quick Pick button appearing next to it.
At run-time the link is formed like this (if the application's Friendly URL attribute is turned off):
f?p=145615:4:8824748217892::NO:RP,RIR,4:IRGTE_ORDER_DATE:01012018
using the following syntax:
f?p=&APPID.:Page:Sessionid::NO:RP,RIR,4:IRGTE\(itemname):itemvalue (stored in &P1_THIS_MONTH item). Table URL parameters :
Argument | Explanation |
---|---|
&APP_ID. | eg 80858. The expression used here is called a substitution string that holds the application ID used to make app more portable. |
: | colon special character is argument separator. Since the URL contains no REQUEST argument, the position of this argument is left empty - see additional colon before the debug argument (NO). |
4 | target page 4 = Orders we are calling in URL |
Sessionid | number (8824748217892) appearing in the URL is session ID of our app and is used to create links between app pages by maintaining the same session state among them. Note that session ids are managed automatically by Oracle APEX. |
NO | = do not enter the debug mode. References debug flag, which is used to display app processing details. |
RP,RIR,4 | Placed in URL's ClearCache position, this argument RP (Resets Pagination) for interactive report on Page 4. RIR=Reset Interactive Report. Pagination =info about numrows and currrownum within result set. You control how pagination displays by making selections from Pagination Type attribute in Property Editor (prop palete u F6i) - style of links or buttons used to navigate to the next or previous page. Clear cache section can have RIR or CIR or RP to reset, clear, or reset pagination of primary default reports of all interactive report regions on the target page. |
IRGTE_ORDER_DATE | is used in itemNames position. IR (Interactive Report) string is used along with greater than and equal to operator (GTE), followed by an item name (ORDER_DATE - an item on Page 4). This argument acts as a filter and is used in conjunction with itemValue (&P1_THIS_MONTH. mentioned underneath) to only display current month's orders. In simple words it says: Order date of interactive report is greater than or equal to item value. |
&P1_THIS_MONTH. | Used in the itemValue position, the value stored in this hidden item is forwarded to the target page. To create a filter on an interactive report in a link, use the string IR\ |
"Sales for This Month" region -> Click Attributes node -> Switch its Template from "Standard" to "Badge List" -> click Template Options -> set Badge Size to 128px, Layout to Span Horizontally -> click OK.
By setting these region properties, the derived one row summarized report will be presented as a badge list, spanned horizontally.
Also set Pagination Type to No Pagination (Show All Rows).
Click Save and Run Page button to see this region with two badges (circles) on it displaying 1. current month's sales and 2. number of orders placed.
First badge - Current month's sales - acts as a link and leads you to Page 4 to display details of the summarized data. Since Page 4 will be created in Chapter 7, you will get Page Not Found message if you click this badge.
Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles
Page region 3 shows sale figures (sums as pie slices) visible when you move the mouse pointer over pie slices of pie chart for individual products.
Create another region as previous region.
Property | Value | |
---|---|---|
1 | Title | Sales by Product |
2 | Type | Chart |
Location | Local Database | |
Type | SQL Query | |
3 | SQL Query | SELECT p.product_name || ' [$' | | p.list_price || ']' product, SUM(oi.quantity * oi.unit_price) sales FROM demo_order_items oi, demo_product_info p WHERE oi.product_id = p.product_id GROUP BY p.product_id, p.product_name, p.list_price ORDER BY p.product_name desc |
4 | Start New Row | Off |
5 | Column | 9 |
5 | Column span | 4 |
7 | Body Height (under Template Options) | 240px |
Click the Attributes sub-node under new region and set following properties.
Property | Value | Help | |
---|---|---|---|
8 | Type | Pie | |
9 | Show (under Legend) | Off (default) | If you are creating a multi-series chart, then you can use legend (9) to identify each series on the chart. Using legend properties you can specify whether to display it, and if so, where it should be placed on the chart. You will use these properties in Chapter 8. |
Click the New sub-node under Series and set the following properties:
Property | Value | |
---|---|---|
10 | Location (under Source) | Region Source |
11 | Label | PRODUCT |
12 | Value | SALES |
13 | Show (under Label) | On (Specifies whether the label(s) should be rendered on the chart) |
Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles
Page region 4 will present sale figures - bars for each product category : Men, Women, and Accessories.
This time, we will add a region using the drag and drop APEX feature. Drag Chart icon from Regions gallery and drop it under "Top Orders by Date region. Chart region will appear. Place chart region at its proper location, if needed.
Property | Value | |
---|---|---|
1 | Title | Sales by Category |
2 | Type | Chart |
Location | Local Database | |
Type | SQL Query | |
3 | SQL Query | SELECT p.category Category, sum(o.order_total) Sales FROM demo_orders o, demo_order_items oi, demo_product_info p WHERE o.order_id = oi.order_id AND oi.product_id = p.product_id GROUP BY category ORDER BY 2 desc |
4 | Start New Row | On |
5 | Column | 1 |
5 | Column span | 4 |
7 | Body Height (under Template Options) | 480px |
Click Attributes sub-node under new region and set following properties.
Property | Value | |
---|---|---|
8 | Type | Bar |
9 | Show (under Legend) | Off (default) |
Click the New sub-node under Series and set the following properties:
Property | Value | Help | |
---|---|---|---|
10 | Location (under Source) | Region Source | |
11 | Label | CATEGORY | |
12 | Value | SALES | |
13 | Color (under Appearance) | #18A0C2 | or Color Picker tool - to change default chart color. |
Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles
Top six customers with highest orders and will present info in text format.
Create a new region by dragging Classic Report icon from gallery and dropping it under "Sales by Category" region. Source of a Classic Report is SQL query. Each time page is rendered, APEX evaluates query and displays result within the region. Once you specify row and column properties using following table, region will appear next to the Sales by Category region.
Property | Value | |
---|---|---|
1 | Title | Top Customers |
2 | Type | Classic Report (should be already set) |
Location | Local Database | |
Type | SQL Query | |
3 | SQL Query | SELECT b.cust_last_name || ', ' || b.cust_first_name || ' - '|| count(a.order_id) ||' Order(s)' customer_name, SUM(a.ORDER_TOTAL) order_total, b.customer_id id FROM demo_orders a, DEMO_CUSTOMERS b WHERE a.customer_id = b.customer_id GROUP BY b.customer_id, b.cust_last_name || ', ' || b.cust_first_name ORDER BY NVL(SUM(a.ORDER_TOTAL),0) DESC |
4 | Start New Row | Off |
5 | Column | 5 |
5 | Column span | 4 |
7 | Body Height (under Template Options) | 240px |
Rendering tab -> expand Columns node under "Top Customers" region, and click CUSTOMER_NAME column. Set following properties to transform this column into a link to provide drill-down capability. APEX specifies CUSTOMER_NAME column in Link Text attribute.
Property | Value | |
---|---|---|
8 | Type | Link |
Click No Link Defined under Target and set the following properties:
Property | Value | Help | |
---|---|---|---|
9 | Type | Page in this app | |
10 | Page | 7 | |
11 | Name | P7_CUSTOMER_ID | This value refers to an item on Page 7 that will be populated with the value held in #ID# . It is forwarded to Page 7 from the Home page to display selected customer profile. |
12 | Value | #ID# | references third column in above SELECT query. Standard procedure in APEX to refer to a column value is to enclose it between # symbols. To refer page item we use substitution strings. |
13 | Clear Cache | 7 |
When we run this page, each customer's name appears as a hyperlink, clicking which calls customer's profile page (Page 7). We set Page attribute to 7, which is the page we want to navigate to. We also forwarded the customer’s ID (#ID#) to Page 7.
Click ORDER_TOTAL column and set Format Mask to $5,234.10 = FML999G999G999G999G990D00.
Select ID column and set Type property (under Identification) to Hidden Column to hide this column at run-time.
Click the Attributes sub-node under new region and set following properties.
Property | Value | |
---|---|---|
1 | Pagination Type | No Pagination (Show All Rows) |
2 | Maximum Row to Process (under Performance) | 6 |
3 | Type (under Heading) | None |
Pagination is suppressed since we want to see only six records in the region. We also set Heading Type to None to suppress column headings.
Click Save and Run Page button to test the progress.
Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles
Similar to Top Customers region - create this region by copying the Top Customers region. Displays six top selling products.
Right-click Top Customers region -> Duplicate from context menu. Copy of source region will be appended just under it.
Property | Value | |
---|---|---|
1 | Title | Top Products |
2 | Type | Classic Report (should be already set) |
Location | Local Database | |
Type | SQL Query | |
3 | SQL Query | SELECT p.product_name||' - '||SUM(oi.quantity)||' x' ||to_char(p.list_price,'L999G99')||'' product, SUM(oi.quantity * oi.unit_price) sales, p.product_id FROM demo_order_items oi, demo_product_info p WHERE oi.product_id = p.product_id GROUP BY p.Product_id, p.product_name, p.list_price ORDER BY 2 desc |
4 | Start New Row | Off |
5 | Column | 9 |
5 | Column span | 4 |
7 | Body Height (under Template Options) | 240px |
Expand the Columns node and click PRODUCT column to set the following properties:
Property | Value | |
---|---|---|
8 | Type | Link |
Click No Link Defined under Target and set the following properties:
Property | Value | Help | |
---|---|---|---|
9 | Type | Page in this app | |
10 | Page | 6 | |
11 | Name | P6_PRODUCT_ID | This value refers to an item on Page 7 that will be populated with the value held in #ID# . It is forwarded to Page 7 from the Home page to display selected customer profile. |
12 | Value | #PRODUCT_ID# | references third column in above SELECT query. Standard procedure in APEX to refer to a column value is to enclose it between # symbols. To refer page item we use substitution strings. |
13 | Clear Cache | 6 |
Click SALES column and set its Format Mask to $5,234.10. Select PRODUCT_ID column and set its Type property to Hidden Column.
Click the Attributes sub-node under new region and set following properties.
Property | Value | |
---|---|---|
1 | Pagination Type | No Pagination (Show All Rows) |
2 | Maximum Row to Process (under Performance) | 6 |
3 | Type (under Heading) | None |
Click Save and Run Page button to see how all six regions appear on Home page.
END creating all regions.
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
These buttons provide drill-down functionality and take user to relevant pages to dig further details for summarized information. Eg buttons add, view... - click "Add Order" btn in "Top Order by Date" region -> redirect to page 11 to add new order.
To view a list of all customer orders.
Right-click Top Orders by Date region -> select Create Button from context menu. This way, button will be created in selected region. A new node "Region Buttons" will be added with a button.
Property | Value | Help | |
---|---|---|---|
1 | Button Name | VIEW_ORDERS | |
2 | Label | View Orders | appears as tooltip when you move over button at run-tim |
3 | Region | Top Orders by Date | region where button will appear |
4 | Button Position | Edit | over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | button will be displayed as an icon |
6 | Icon | fa-chevron-right | name of icon ">" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this app Page = 4 | |
If we click new button ">" : Sorry, this page isn't available Application "80858" Page "4" not found.
Calls Order Wizard (to be created in Chapter 7) to place a new order.
Right-click Region Buttons under the Top Orders by Date region -> select Create Button. A new button will be added under previous one.
Property | Value | Help | |
---|---|---|---|
1 | Button Name | ADD_ORDER | |
2 | Label | Enter New Order | appears as tooltip when you move over button at run-tim |
3 | Region | Top Orders by Date | region where button will appear |
4 | Button Position | Edit | over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | button will be displayed as an icon |
6 | Icon | fa-plus | name of icon "+" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this app Page = 11 Clear Cache=11 | |
Drill-down into current month's order details.
A new button will be added to this region.
Link properties set here are similar to those set earlier in section 4.3.2.Figure 4-7
Property | Value | Help | |
---|---|---|---|
1 | Button Name | VIEW_MONTH_ORDERS | |
2 | Label | View Orders for This Month | appears as tooltip when you move over button at run-tim |
3 | Region | Sales for This Month | region where button will appear |
4 | Button Position | Edit | (already set) over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | (already set) button will be displayed as an icon |
6 | Icon | fa-chevron-right | name of icon ">" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this Application Page = 4 Name = IRGTE_ORDER_DATE Value = &P1_THIS_MONTH. Clear Cache = RIR,4 | |
You’ll place two buttons in the Top Customers region. Create these buttons using either of the two methods applied above.
Property | Value | Help | |
---|---|---|---|
1 | Button Name | VIEW_CUSTOMERS | |
2 | Label | View Customers | appears as tooltip when you move over button at run-tim |
3 | Region | Top Customers | region where button will appear |
4 | Button Position | Edit | over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | button will be displayed as an icon |
6 | Icon | fa-chevron-right | name of icon ">" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this app Page = 2 | |
Target page will appear on top of the Home page (as a modal dialog).
Right-click VIEW_CUSTOMERS button -> select Create Button. Set following properties for new button.
Property | Value | Help | |
---|---|---|---|
1 | Button Name | ADD_CUSTOMER | |
2 | Label | Add Customer | appears as tooltip when you move over button at run-tim |
3 | Region | Top Customers | region where button will appear |
4 | Button Position | Edit | over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | button will be displayed as an icon |
6 | Icon | fa-plus | name of icon "+" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this app Page = 11 Clear Cache=11 | |
Two buttons in the Top Products region (same as in Top Customers region).
Property | Value | Help | |
---|---|---|---|
1 | Button Name | VIEW_PRODUCTS | |
2 | Label | View Products | appears as tooltip when you move over button at run-tim |
3 | Region | Top Products | region where button will appear |
4 | Button Position | Edit | over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | button will be displayed as an icon |
6 | Icon | fa-chevron-right | name of icon ">" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this app Page = 3 | |
Property | Value | Help | |
---|---|---|---|
1 | Button Name | ADD_PRODUCT | |
2 | Label | Add Product | appears as tooltip when you move over button at run-time |
3 | Region | Top Products | region where button will appear |
4 | Button Position | Edit | over dozen values, try other options as well to observe different positions |
5 | Button Template | Icon | button will be displayed as an icon |
6 | Icon | fa-plus | name of icon "+" from the APEX's repository |
7 | Action | Redirect to Page in this App | |
8 | Target | Type = Page in this app Page = 6 Clear Cache=6 | |
At this stage, all the seven buttons are placed at their proper locations with expected functionalities and are ready for partial test.
These buttons will be productive after creating pages indicated in their Target properties.
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
CSS provides way to control style of a web page without changing its structure. When used properly, a CSS separates visual properties such as color, margins, and fonts from the structure of the HTML document. APEX includes themes containing templates to reference their own CSS. The style rules defined in each CSS for a particular theme also determine the way reports and regions display.
CSS can be added to APEX apps inline, as CSS file(s) or through ThemeRoller.
Depending on your requirements, you can add CSS to your app at the:
.region {background:white;border-radius:10px 10px 10px 10px;box-shadow: inset 0px 0px 30px #dfdbdf}
To apply CSS at user interface level:
Shared Components page -> Files section -> Static App Files
Click Upload File button
click Choose Files, select AppCss.css file from the source code and click Upload. The css file will be added to the static app files listing. Copy Reference URL entry #APP_IMAGES#AppCss.css appearing on this page to your clipboard.
APP_IMAGES substitution string is used to reference uploaded images, JS, and CSS specific to given app and are not shared over many apps. Recall that you used this substitution string earlier in chapter 3 to reference app logo.
Add the CSS file to User Interface : Shared Components User Interface section -> User Interface Attributes.
On User Interface tab, click Cascading Style Sheets sub-tab and press Ctrl+V to append the reference text in File URLs box under existing URL :
#APP_IMAGES#app-icon.css?version=#APP_VERSION#
#APP_IMAGES#AppCss.css
Click Apply Changes.
Finally, you have to apply the CSS rule to your region. Open home page (Page1) of your app and select first region - Top Orders by Date. In the properties pane, scroll down to the Appearance section and enter region (a class defined in the AppCss.css file) in CSS Classes attribute.
CSS allows you to specify your own selectors called "id" and "class".
Add the region class to the CSS Classes property of the remaining five regions.
Test Your Work
Click the Save and Run Page button to see the Home page, which should now look similar to the one illustrated in Figure 4-1 at the beginning of this chapter.
We added contents to a blank page, modified properties to customize look and feel of this page. This is uniqueness and beauty of Oracle APEX that allows you to create pages rapidly without writing tons of code. Oracle APEX features you learned :
Region - We added six regions (stacked canvases) to display different types of contents : different types of charts, badge list, and classic reports to populate these regions via simple SQL statements.
12 columns grid Layout - to arrange multiple regions on a page.
URL & Links - link app pages together by setting some properties. How APEX formulates URL and passes values to target page using some link properties.
Buttons can also be used to link application pages. You created a few buttons to access different application pages.
Apply Styles - You learned how to add custom styles to page elements through user interface level.
In the next chapter, you will learn about Interactive Grid and how to create web forms to receive user input.
tbl ID=2 CRud & form ID=7 crUD - profile page 133/419
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Customers info is used in other app segments (app modules) eg customer orders and invoices. You can evaluate how much business you have done with your customers either by location or by product, as you did in previous chapter where you created Customers region on page ID=1 Sales web app.
Page ID=1 Sales web app - was created by App Builder wizard at the time when app was created. Other pages in this app will be created manually with help of wizards and copy utility.
Customers who have some existing orders cannot be Deleted - see chapter 2 section 2.10 step 11.
Each customers name appears as link in interactive grid -> form page profile of selected customer.
Create two module pages via built-in wizard:
Main App Builder interface -> click "Sales Web Application's Edit icon" (A) -> click "Create Page" button (B).
TIP: To delete page, open page in Page Designer by clicking its name -> select Delete from top-right Utils menu.
On first wizard screen, select "Report" option -> report of customers in an interactive grid.
On next wizard screen, click Interactive Grid. This screen presents sub-categories of reports and requires a single selection the report will base on. The option you selected here means an interactive grid will act as a report to display all customers from DB.
Up to version 5.0 APEX used IR (Interactive Report) feature to present data tbl. Since version 5.1 - new feature IG (Interactive Grid) similar to IR + clicking on a cell and editing its value (Clipper Dbedit !). IG introduces fixed headers, frozen columns, scroll pagination, multiple filters, sorting, aggregates, computations... Supports all item types and item type plug-ins. You can create master-detail relationships to any number of levels deep and across. See section 5.6.
On the next wizard screen, set following
Property | Value | Help | |
---|---|---|---|
1 | Page Number | 2 | Main page of this module (form page to be created next will have number 7) |
2 | Page Name | Customers | |
3 | Page Mode | Normal | How you want to see page. It has two options: 1. Normal Dialog. New pages default to Normal. When you call a normal page, it replaces an existing page appearing in your browser. 2. Modal Dialog is stand-alone page, which appears on top of its calling page and doesn't allow users to do anything else unless it is closed. A modal page can be displayed only on top of another page. |
4 | Breadcrumb | Breadcrumb | Breadcrumb shared component was created by the App Builder when you created this app earlier (see Shared Components -> Navigation > Breadcrumbs). In this step, you selected the same breadcrumb component and added an entry name (Customers) to it. Take a look at Figure 5-1 and see where the provided entry name appears in the breadcrumb region. We use breadcrumbs as a second level of navigation at the top of each page - hierarchical list of links indicates where the user is within the app from a hierarchical perspective to switch to any level. |
5 | Parent Entry | Home (Page 1) | To create hierarchy in this app, you selected Home menu entry as Parent Entry for this page. |
6 | Entry Name | Customers |
On Navigation Menu wizard screen
This step will make Setup entry active in the main navigation menu (created in Chapter 3, section 3.2.1) when this page is accessed.
On Report Source screen, set following properties.
Property | Value | Help | |
---|---|---|---|
1 | Editing Enabled | Off | form pg ID=7 edits cust records. Examples of editing rec. in interactive grid see later in this ch. |
2 | Source Type | Table | Tbl data to populate this interactive grid |
2 | Table/View Owner | accept displayed value of Oracle schema (POSSYS) to which you are connected. | |
2 | Table/View Name | DEMO_CUSTOMERS (table) | Once you select a schema, all tables within that schema are populated in Table/View Name drop-down list from where you select a table - DEMO_CUSTOMERS in the current scenario whose data will be displayed in the interactive grid. Note that in the current scenario you can select only one table from the provided list. |
If not visible, click the arrow icon next to the Column section to see the table columns. When you choose a table, all the columns from that table are selected (moved to the right pane in the Columns section). For this exercise, leave the following columns in the right pane and exclude others by moving them to the left pane using Ctrl+click and the left arrow icon. Here are the columns we want to show in the interactive grid. Cust_First_Name, Cust_Last_Name, Cust_Street_Address1, Cust_Street_Address2, Cust_City, Cust_State, and Cust_Postal_Code.
Click Create button to finish report page creation process.
Page Customers tbl ID=2 is created and its structure is presented in Page Designer. Only significant aspect of this page is "Customers" Interactive Grid region under :
Rendering -> Regions -> Content Body node to your left. Wizard created this region with all columns you specified in step 6 - see SQL Query box in Page Designer. All these columns appear under Columns node.
Properties in the Interactive grid's Attributes node control how an interactive grid works. For example, developers use these properties to determine if end-users can edit the underlying data, configure report pagination, create error messages, configure toolbar, use download options, control if and how users can save an interactive grid, and add Icon and Detail Views to the toolbar. You will go through these properties later in this chapter. For now, walk around the Page Designer to observe page components and relevant properties.
Click Application eg 145615(=appID) breadcrumb at top-left to leave Page Designer interface.
Click Create Page button -> select "Form" option -> click another Form option on next wizard screen -> creates form page based on DB table.
Property | Value | Help | |
---|---|---|---|
1 | Page Number | 7 | Page to carry form to CrUD Customers |
2 | Page Name | Customer Details | |
3 | Page Mode | Modal Dialog | stand-alone page, which appears on top of the calling page. Oracle APEX page can be created as dialog, which supports for all the functionality of a normal page, including computations, validations, processes, and branches. |
4 | Breadcrumb | ||
5 | Parent Entry | Customers (Page 2) | called from Page 2 |
6 | Entry Name | Customers Details |
On the Navigation Menu screen, set "Navigation Preference" = Identify an existing navigation menu entry for this page, set "Existing Navigation Menu Entry" = Setup, and click Next.
On the Source screen, set the following properties and click Next.
Property | Value | Help | |
---|---|---|---|
1 | Data Source | Local DB | |
2 | Source Type | Table | |
2 | Table/View Owner | accept displayed value of Oracle schema (POSSYS) to which you are connected. | |
2 | Table/View Name | DEMO_CUSTOMERS (table) |
This time, select all columns from DEMO_CUSTOMERS tbl to display all of them in the input form (Page 7) to populate backend DB tbl. For "Primary Key Type", select second option Select Primary Key Column(s). Then, set first "Primary Key Column" attribute to CUSTOMER_ID. Click "Create" button to complete form page creation process.
In this step, you specified PK (primary key) column col or set of cols that uniquely identify record in tbl. Note that in current scenario PK col for cust tbl will be populated using DEMO_CUSTOMERS_SEQ Sequence object through BI_DEMO_CUSTOMERS trigger. Trigger fires when you insert a new customer. To browse this trigger, select SQL Workshop -> Object Browser -> Tables -> click on DEMO_CUSTOMERS table and then click the SQL tab. Sequence is a database object that automatically generates PK values for every new cust row. Rows are identified using either PK defined on tbl, or ROWID pseudo column, which uniquely identifies row in a tbl. Forms support up to 2 columns in PK. For tables using primary keys with more than two columns, ROWID option should be used. For further details, see Chapter 2.
Access main App Builder interface by clicking application ID breadcrumb to see two new pages (Customers and Customer Details) with their respective page numbers. Click Customer Details (Page 7) to open its definitions in Page Designer.
Expand Pre-Rendering node and rename process Initialize form Customer Details as "Initialize Customer Details".
Click Processing tab and rename process Process form Customer Details to "Process Customer Data". NOTE: If you see a different process name, then there is nothing to worry about as it sometimes happens due to change in APEX version.
APEX is a low-code application development platform. Two pages you just created have everything you need to view and manipulate data. Customers Page 2 contains Interactive Grid in which you can view all customers data.
Click Customer Details Page 7 to open it in Page Designer. On Rendering tab, expand Pre-Rendering node. Here, you will see an auto-generated process named Initialize Customer Details of Form Initialization type. This Process is responsible to initialize form region items. Initialization can either be fetching data from region source, using PK value(s) or simple initialization of form region items. Process fetches and displays data in page items when you select customer by clicking corresponding edit icon on the reports page and it initializes page items when you create a new customer record.
Customer Details region is a Form type region, which connects to local DB and fetches data from DEMO_CUSTOMERS table into relevant page items listed under Items node. Same page items are used to receive user input when new customer record is created.
In Buttons section, you will see a bunch of auto-generated buttons (Cancel, Delete, Save, and Create). DB Action property of these buttons specify function each button performs. When you click button (eg CREATE), corresponding DB action is submitted to process named Process Customer Data, which resides under the Processing tab. This process is of Automatic Row Processing (DML) type and performs CrUD action on form region - Customer Details region in current scenario.
Main page of this module (Page 2) holds interactive grid generated by wizard with some default data source values.
In next steps we change default data source values and produce custom output using SQL query.
Cols hdr, order, filter and cust name as link
In App Builder, click Customers page (Page 2) to open it in the Page Designer for modification.
Click Customers region under Content Body node. Standard method to modify properties of page component is to click corresponding node. This action refreshes Properties section (located to your right) with properties of selected page component for alteration.
Change Type (under Source section) = SQL Query to see default query generated for interactive grid.
select ROWID, CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1,
CUST_STREET_ADDRESS2, CUST_CITY, CUST_STATE, CUST_POSTAL_CODE, CUST_EMAIL,
PHONE_NUMBER1, PHONE_NUMBER2, URL, CREDIT_LIMIT, TAGS
from DEMO_CUSTOMERS
Enter following SQL statement in SQL Query text area, replacing existing one:
SELECT customer_id, cust_last_name || ', ' || cust_first_name customer_name
, CUST_STREET_ADDRESS1||decode(CUST_STREET_ADDRESS2, null, null, ', '||CUST_STREET_ADDRESS2) customer_address
, cust_city, cust_state, cust_postal_code
FROM demo_customers
Decode Syntax: decode( expression , search , result [, search , result]... [, default] )
If default is omitted, Oracle returns null.
Expand Customers region -> expand Columns node. Click column eg CUSTOMER_NAME and change its heading (under Heading section in Properties pane) to Name. Change headings of other cols : Address, City, State, and Postal Code
In Columns node, click the CUSTOMER_ID column, and change it Type property from Number Field to Hidden to hide col at runtime. PK cols are added to DB tbls to enforce data integrity and are not displayed in apps. This is why such column's Type property is set to hidden to make them invisible at runtime.
Run page. Click Actions menu (A) - select Columns. When you click column in left pane, right pane (D) shows its name and width. You can input a numeric value to change width of col. Using arrow icons (E), arrange selected columns in following order: Name, Address, City, State, and Postal Code
Click Save button in the Columns window to apply the changes.
After you modify interactive grid in runtime save it : Click Actions menu -> Save from Report option, otherwise you’ll lose the applied settings when you access it later.
Click Edit Page2 (F) in the Developer Toolbar at the bottom of page to access Page Designer.
Click CUSTOMER_NAME column to set properties: transforming cust name col into link that will lead to Page 7 cust. form.
When you click customer's name in interactive grid report at runtime:
- ID of that cust is stored in substitution string (&CUSTOMER_ID.) (G)
- and is forwarded to corresponding page item (P7_CUSTOMER_ID) (H) on Page 7, which displays cust profile using this ID.
You created similar kind of link in Chapter 4 for region "Sales for this Month".
Scroll down to Link section and click "No Link Defined" under Target to bring up Link Builder dialog box -> set link properties :
Property | Value | Help | |
---|---|---|---|
1 | Type | Page in this app | |
2 | Page | 7 | |
3 | Name | P7_CUSTOMER_ID | This value refers to an item on Page 7 that will be populated with the value held in #ID# . It is forwarded to Page 7 from the Home page to display selected customer profile. |
4 | Value | &CUSTOMER_ID. | references third column in above SELECT query. Standard procedure in APEX to refer to a column value is to enclose it between # symbols. To refer page item we use substitution strings. |
5 | Clear Cache | 7 |
Use LOVs (I) in Set Items section to select item name (3) and value (4).
Close Link Builder dialog box using OK button.
Save and run page. Cust Name col will now appear as link. Click any customer name to see details on Page 7, which pops up on top of Page 2.
To call Page 7 with a blank form from Page 2 - Customers :
Rendering tab to your left click Customers interactive grid region -> set its Template property to Standard (was Interactiv). Selected template will place title and border for interactive grid region.
Right-click Customers region and from context menu select Create Button. Button named New will be added. Set properties for new button :
Property | Value | Help | |
---|---|---|---|
1 | Button Name | CREATE | |
2 | Label | Create Customer | appears as tooltip when you move over button at run-time |
3 | Button Position | Copy | over dozen values, try other options as well to observe different positions |
4 | Hot | On | renders the button in dark color |
5 | Action | Redirect to Page in this App | (under Behavior) - Creates link to call Page 7. |
6 | Target | Type = Page in this app Page = 7 Clear Cache =7 | Creates link to call Page 7. Clear Cache prop. makes all items on target pg 7 blank |
Save and run Page 2, which should look similar to Figure 5-1.
Click Create Customer button to call Customer Details Page 7 on top of calling page as a modal dialog.
https://www.youtube.com/watch?v=WLE9_1I_nA0
Like 5.3.2, properties are :
Property | Value | Help | |
---|---|---|---|
1 | Button Name | CallPHPpge | |
2 | Label | Call PHP page | appears as tooltip when you move over button at run-time |
3 | Button Position | Copy | over dozen values, try other options as well to observe different positions |
4 | Hot | Off | if On : renders the button in dark color |
5 | Action | Redirect to URL | (under Behavior) - Creates link to call PHP Page |
6 | URL | "http://192.168.../" or "http://dev1:8083/fwphp/www" ?param1=&P26_PARAM | Creates link to call PHP Page |
Open in new tab : ?
With Page 7 cust. form being displayed in browser, click Edit Page 7 in Developer Toolbar at pge bottom to call this pge in PageDesigner.
Like region placement in 12 cols grid layout, done for six Home pge regions, pge items can also be placed using APEX's grid layout, as follows.
Width property sets items width on page.
If Value Required is set to Yes and page item is visible, APEX automatically performs NOT NULL validation when page is submitted and you are asked to input a value for field. If you set Value Required to No, no validation is performed and a NULL value is accepted. Value Required attribute works in conjunction with Template = Required to signify (označi) mandatory items visually.
Page flds are in 2 columns, only "Tags" fld spans both cols. Save your changes and call this Page 7 by clicking any customer's name on Page 2. It should come up with selected customer profile (Figure 5-6 Customer Details Page). Click each item under Items node and apply following properties.
to hold app-shared dynamic LOV! States list. app-shared means defined in app shared componebts in one place and reusable in all app modules pages.
both VARCHAR2 30 :
select state_name display_value, st return_value from demo_states order by 1
--select state_name display_value, state_id return_value from demo_states order by 1
Label=State.....Sequence=70.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=make it null (this item will be transformed into a select list).....Value Required=On
Modify the two phone number items in cust. form pgID=7 and set their Value Placeholder property (under Appearance) to 999-999-9999. When a new customer record is added, this placeholder is shown in the two phone number items to receive input in the specified format. As you type in values, the placeholders will be replaced by the numbers entered.
Field "Credit Limit" is used to assign a credit cap to each customer with a figure of $5,000. If you enter a value more than the assigned cap, you'll be prevented by presenting an appropriate message. NOTE: Validation error messages display when :
In left pane of Page 7 cust.form : click Processing icon (was tab) -> right-click Validating node -> Create Validation from context menu (Figure 5-7) -> Set following properties for this new validation :
Property | Value | Help | |
---|---|---|---|
1 | Name | Check Credit Limit | should be meaningful ! |
2 | Type | PL/SQL Expression | expression in valid PL/SQL syntax that evaluates to true or false |
3 | PL/SQL Expression | :P7_CREDIT_LIMIT <= 5000 | If true customer record is saved to DB. Note that you use bind variables (item name preceded with a colon) when you reference value of a session state variable from within PL/SQL code. |
4 | Error Message | Customer's Credit Limit must be less than or equal to $5,000 |
This check is performed to retain DB integrity from front-end. Validation is performed using custom PL/SQL function, which returns either a true or false (deletion process is aborted) value. Validation is associated to DELETE button in last attribute in following table, which means that validation will be performed only when Delete button is pressed.
In left pane of Page 7 cust.form : click Processing icon (was tab) -> right-click Validating node -> Create Validation from context menu (Figure 5-7) -> Set following properties for this new validation :
Setting a condition type involves selecting a condition from list that must be met in order for a validation to be processed.
Property | Value | Help | |
---|---|---|---|
1 | Name | Can't Delete Customer with Orders | should be meaningful ! |
2 | Type | PL/SQL Function Body (Returning Boolean) | fn PL/SQL that returns true or false |
3 | PL/SQL Fn Body (Ret. Boolean) | SEE BELOW | If true customer record is deleted in DB. Note that you use bind variables (item name preceded with a colon) when you reference value of a session state variable from within PL/SQL code. |
4 | Error Message | Can't Delete Customer with Orders | |
5 | When Button Pressed | DELETE | You can control when and if validation (or process) is performed by configuring When Button Pressed and Condition Type attributes of validation. If you want a validation to execute only when specified button is clicked, select a button from the list. |
| 3 | PL/SQL Fn Body :
begin
for c1 in (
select 'x' from demo_orders
where customer_id = :P7_CUSTOMER_ID
) loop
RETURN FALSE;
end loop;
RETURN TRUE;
end;
"Customer Details" - Page 7 - observe auto-generated buttons (Cancel, Delete, Save, and Create) with default functionalities. Eg when you fill in form with new customer's record and click Create button, record is added to DB table using a built-in process - discussed in a while.
Expand Pre-Rendering node (under root node - Page 7: Customer Details). Here, you will see process of Form Initialization type created by wizard - purpose of which is to fetch row from DB by key, and put row values into pge items Px_.... Eg when you click customer name in Interactive Grid on Page ID=2, ID of that customer is used by this process to fetch and display details of the selected customer on page ID=7.
Wizard also created individual input items (under Customer Details region) for each tbl column.
Click root node (Page 7: Customer Details) and scroll down to Function and Global Variable Declaration section in Property Editor, you'll see a global variable defined as var htmldb_delete_message. This variable was generated automatically along with a corresponding shortcut named DELETE CONFIRM MSG (in Shared Components > Other Components > Shortcuts) to control record deletion process by presenting a confirmation dialog box before deleting a customer’s record (other app pages will also use it).
Note that Delete button was created by wizard with SQL DELETE DB action. Similarly, INSERT and UPDATE DB actions were set automatically for Create and Save buttons, respectively - see DB Action attribute under Behavior. When clicked, these buttons perform selected SQL operations to trigger specified DB action within built-in Automatic Row Processing (DML) type process, also created automatically by wizard on Processing tab. This process is located under Processing > Processes node and it is responsible to CrUD rows into backend database table. This process is used to process form items with source of type DB Column. This process has three advantages :
In addition, wizard created Dynamic Action (Cancel Dialog) to close this form when Cancel button is clicked. These are some of the beauties of declarative development that not only generates basic functionalities of an app, but on the same time doesn't limit our abilities to manually enter specific and tailored code (demonstrated in subsequent chapters), both on the client and server sides to answer our specific needs.
Save and run app. Access this module by clicking "Manage Customers" menu item (under "Setup"). You’ll see Page 2 - Customers (Figure 5-1), carrying an interactive grid. Grid has :
See Chapter 7. Couple of save options under Actions -> Report in runtime :
Customer name column appears as a link in Page 2 -click it to modify row (Figure 5-6).Check credit limit validation by entering more than 5000 in Credit Limit box.
NOTE: You might encounter a primary key violation message while creating first customer record. This is because the Sequence object for this table is created with an initial value of 1. When you try to save the first customer record, 1 is assigned as its primary key, which already exists in the table. To cope with this situation, developers drop and re-create auto-generated sequence objects with a higher START WITH value. To keep things simple, I'd suggest beginners to click the Create button on the form page several times. After few clicks row will be saved.
Add Dynamic Action to refresh interactive grid region (Customers, Page 2) when modal dialog page (Page 7) is closed. When_window_deactivated is when mouse clicks on other window.
After C customer row or U existing one, interactive grid doesn't reflect CrUD, page doesn't get refreshed. Manually refresh your browser window eg F5. More professional approach is to refresh page automatically using dynamic action.
Save page and run it. Now you will see immediate reflection of your modifications in interactive grid.
page 177/419
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Main Products Page 3, CRud product - Figure 6-1 Products Interactive Report Page - will have 3 different views: Icon, Report, and Details. Initially, wizard will create the Report View version that you'll modify with a custom SQL statement. Remaining two views (Detail and Icon) are placed on the page by enabling respective properties found under main Products region. Once you enable Detail and Icon views, their icons appear on main Search bar. Using these icons you can switch among different views to browse products information.
Product Details Page 6, CrUD product .
To create these two pages follow same approach as you did for Customers.
New stuff added to this module
- technique to incorporate style sheet in APEX page
- image handling and styling. This module is based on DEMO_PRODUCT_INFO table. Among conventional columns exists the following four special columns to handle images in DB. Normally, specialized processing is required to handle images in DB. APEX environment has eliminated need of specialized processing with these additional columns to properly process images in BLOB column.
PRODUCT_IMAGE: This column uses BLOB data type. BLOB (Binary Large Object) is Oracle data type that can hold up to 4 GB data. BLOBs we use for storing digitized information eg images, audio, video, document files : PDF, MS Word, MS Excel, MS PowerPoint, CSV....
MIMETYPE: Multipurpose Internet Mail Extension (MIME) type identifies file format. MIME type enables apps (Internet browsers, e-mail apps...) to read (handle) file. Eg e-mail app can use MIME type to detect what type is file attached to e-mail. MIME types are composed of a top-level media type followed by a subtype identifier, separated by a forward slash character (/) eg image/jpeg. The top-level media type is a general categorization about content of file, while subtype identifier specifically identifies format of file.
You can view via Object Browser in SQL Workshop after uploading such file types to BLOB column in your table :
File Type | MIMETYPE Metadata |
---|---|
JPEG | image/jpeg |
PNG | image/png |
application/pdf | |
WORD | application/vnd.openxmlformats-officedocument.wordprocessingml.document |
EXCEL | application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
POWERPOINT | application/vnd.openxmlformats-officedocument.presentationml.presentation |
CSV | application/vnd.ms-excel |
FILENAME: A case-sensitive column name used to store filename of BLOB, such as bag.jpg or CV.pdf.
IMAGE_LAST_UPDATE: A case-sensitive column name used to store the last update date of BLOB.
Same approach as for Customers.
Main App Builder interface -> click "Sales Web Application's Edit icon" -> click "Create Page" button
TIP: To delete page, open page in Page Designer by clicking its name -> select Delete from top-right Utils menu.
On first wizard screen, select "Report" means report of products in an interactive grid
On next wizard screen, click Interactive Grid. This screen presents sub-categories of reports and requires a single selection the report will base on. The option you selected here means an interactive grid will act as a report to display all customers from DB.
On the next wizard screen, set following
Property | Value | Help | |
---|---|---|---|
1 | Page Number | 3 | Main page of this module (form page to be created next will have number 6) |
2 | Page Name | Products | |
3 | Page Mode | Normal | How you want to see page. Or Modal = Dialog is stand-alone page, appears only on top of its calling page and doesn't allow users to do anything else unless it is closed. |
4 | Breadcrumb | Breadcrumb | = hierarchical list of links. Breadcrumb shared component was created by App Builder when you created this app (see Shared Components -> Navigation > Breadcrumbs). In this step, you selected same breadcrumb component and added an entry name (Customers) to it. |
5 | Parent Entry | Home (Page 1) | |
6 | Entry Name | Products |
On Navigation Menu wizard screen
On Report Source screen, set following properties.
Property | Value | Help | |
---|---|---|---|
1 | Editing Enabled | Off | form pg ID=6 edits cust records. Examples of editing rec. in interactive grid see later in this text |
2 | Source Type | Table | Tbl data to populate this interactive grid |
2 | Table/View Owner | accept displayed value of Oracle schema (POSSYS) to which you are connected. | |
2 | Table/View Name drop-down list (from selected schema) | DEMO_PRODUCT_INFO (table) | whose data will be displayed in IG. You can select only one table from provided list. |
Click arrow icon next to the Column section to see table columns - all columns from that table are selected (moved to the right pane in the Columns section). Columns we want to show in IG : ...
Click Create button to finish report page creation process.
Click Application eg 145615(=appID) breadcrumb at top-left to leave Page Designer interface
Click Create Page button -> select "Form" option -> click another Form option on next wizard screen -> creates form page based on DB table.
Property | Value | Help | |
---|---|---|---|
1 | Page Number | 6 | Page to carry form to CrUD Products |
2 | Page Name | Product Details | |
3 | Page Mode | Modal Dialog | stand-alone page, appears on top of calling page |
4 | Breadcrumb | Breadcrumb | |
5 | Parent Entry | Products (Page 3) | called from Page 3 |
6 | Entry Name | Product Details |
On Navigation Menu screen, set "Navigation Preference" = Identify an existing navigation menu entry for this page, set "Existing Navigation Menu Entry" = Setup, and click Next.
On Source screen, set following properties and click Next.
Property | Value | Help | |
---|---|---|---|
1 | Data Source | Local DB | |
2 | Source Type | Table | |
2 | Table/View Owner | accept displayed value of Oracle schema (POSSYS) to which you are connected. | |
2 | Table/View Name | DEMO_PRODUCT_INFO (table) |
This time, select all columns from DEMO_PRODUCT_INFO tbl to display all of them in the input form (Page 6) to populate backend DB tbl. For "Primary Key Type", select second option Select Primary Key Column(s). Then, set first "Primary Key Column" attribute to PRODUCT_ID. Click "Create" button to complete form page creation process.
Forms support up to 2 cols in PK. For tbls using PK with more than two columns, ROWID option should be used. For further details, see Chapter 2.
Access main App Builder interface by clicking application ID breadcrumb to see two new pages (Customers and Customer Details) with their respective page numbers. Click Products Details (Page 6) to open its definitions in Page Designer.
Expand Pre-Rendering node and rename process Initialize form Products Details as "Initialize Products Details".
Click Processing tab and rename process Process form Products Details to "Process Products Data". NOTE: If you see a different process name, then there is nothing to worry about as it sometimes happens due to change in APEX version.
APEX is a low-code app dev. platform. Two pages you just created have everything you need to view and manipulate data. Products Page 2 contains IG in which you can view all customers data.
Click Product Details Page 6 to open it in Page Designer. On Rendering tab, expand Pre-Rendering node. Here, you will see an auto-generated process named Initialize Products Details of Form Initialization type. This Process is responsible to initialize form region items. Initialization can either be fetching data from region source, using PK value(s) or simple initialization of form region items. Process fetches and displays data in page items when you select product by clicking corresponding edit icon on the reports page and it initializes page items when you create a new product record.
Product Details region is a Form type region, which connects to local DB and fetches data from DEMO_PRODUCT_INFO table into relevant page items listed under Items node. Same page items are used to receive user input when new customer record is created.
In Buttons section, you will see a bunch of auto-generated buttons (Cancel, Delete, Save, and Create). DB Action property of these buttons specify function each button performs. When you click button (eg CREATE), corresponding DB action is submitted to process named Process Customer Data, which resides under Processing tab. This process is of Automatic Row Processing (DML) type and performs CrUD action on form region - Product Details region in current scenario.
Holds interactive grid generated by wizard with some default data source values.
click region Products of page 3 -> change Type "Table / View" to SQL Query
In SQL Query, replace existing SELECT statement with following to select (same in pge 6 crUD form) :
select PRODUCT_ID, PRODUCT_NAME , PRODUCT_DESCRIPTION, CATEGORY, PRODUCT_AVAIL
, LIST_PRICE, UNITS, SALES, CUSTOMERS, LAST_DATE_SOLD
, IMG, ICON_LINK, DETAIL_IMG, DETAIL_IMG_NO_STYLE
from (
select p.product_id, p.product_name, p.product_description, p.category
, decode(p.product_avail, 'Y','Yes','N','No') product_avail
, p.list_price
, (select sum(quantity) from demo_order_items where product_id = p.product_id) units
, (
select sum(quantity * p.list_price)
from demo_order_items where product_id = p.product_id
) sales
, (
select count(o.customer_id) from demo_orders o, demo_order_items t
where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id
) customers
, (
select max(o.order_timestamp) od
from demo_orders o, demo_order_items i
where o.order_id = i.order_id and i.product_id = p.product_id
) last_date_sold
, p.product_id img
, apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID:'||p.product_id) icon_link
, decode( nvl(dbms_lob.getlength(p.product_image),0)
,0,null,
'<img alt="'||p.product_name||'" title="'||p.product_name
||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />'
) detail_img
, decode( nvl(dbms_lob.getlength(p.product_image),0)
,0,null,
apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)
) detail_img_no_style
from demo_product_info p
)
p.product_id is img column in query above
is formed using PREPARE_URL function which is :
holds products img. HTML img tag is used to display images of products in conjunction with built-in APEX function APEX_UTIL.GET_BLOB_FILE_SRC - which enables us to format img display with height and width properties. Image is styled using CSS inline styling method. getlength fn of dbms_lob package (dbms_lob.getlength) is used to estimate BLOB column size in table to facilitate inclusion of download link in report. If BLOB column size length is 0, BLOB is NULL and no download link is displayed.
Products region -> Content Body -> Expand Columns node and set meaningful column headings as follows:
Product, Description, Category, Available, Price, Units, Sales, Customers, Last Sold, Image, Icon Link, Image Detail, and Detail Image No Style
Modify following columns. We can use Ctrl+click or Shift+click to select multiple cols to change Type properties at once.
Column : Property = Value...Help
By selecting Product Name column in Link Text attribute you specify this report column to appear as a link. You created a similar kind of link to call Customer Details page.
- In Interactive Reports, you forward a value to target page using special substitution strings (enclosed in # symbols)
- as compared to &Item. notation (for example, &CUSTOMER_ID.), which you use in the Interactive Grid - see Chapter 5 Section 5.3.1 Step 10.
Property Value
If you save and run report page at this stage, you will see an EDIT column (represented with a pencil icon), which leads to details page. Since we have already created a link (on the Product Name column), we will eliminate this column so :
Under Products region, click its Attributes node, and set in Properties pane "Link" Column = Exclude Link Column. Figure 6-3
In same Attributes node, scroll down to Icon View section and set following properties.
By default, most interactive reports display as a report. You can optionally display columns as icons. When configured, an icon (View Icons) appears on Search bar. To use this view, you must specify columns to identify icon, label, and target (that is link). As a best practice Type attribute of these columns is set to hidden (as you did in step 4), because they are typically not useful for end users. Image Attributes property will style height and width of images.
Property Value
Section "Detail View" under Icon View section -> turn on Show property. When configured, View Details icon appears on Search bar.
In Before Rows attribute of Detail View you enter HTML code to be displayed before report rows. Eg you can use TABLE element to put DB content in row/column format. Besides adding HTML code, styling information can also be incorporated using this attribute. Code below uses custom CSS rules to override default Oracle APEX Interactive Report (apexir) styles.
<style>
table.apexir_WORKSHEET_CUSTOM {
border: none !important;
box-shadow: none;
-moz-box-shadow: none;
-webkit-box-shadow: none;}
.apexir_WORKSHEET_DATA td {
border-bottom: none !important;}
table.reportDetail td {
padding: 2px 4px !important;
border: none !important;
font: 11px/16px Arial, sans-serif;}
table.reportDetail td.separator {
background: #F0F0F0 !important;
padding: 0 !important;
height: 1px !important;
padding: 0;
line-height: 2px !important;
overflow: hidden;}
table.reportDetail td h1 {margin: 0 !important}
table.reportDetail td img {
margin-top: 8px;
border: 4px solid #CCC;
-moz-border-radius: 4px;
-webkit-border-radius: 4px;}
</style>
<table class="reportDetail">
NOTE: Remember that all APEX pages are HTML pages controlled by HTML properties and cascading style sheet (CSS) settings. When you create an interactive report, Oracle APEX renders it based on CSS classes associated with current theme. Each APEX interactive report component has a CSS style definition that may be changed by applying standard CSS techniques to override defaults. CSS changes may be applied to :
In the current step, you are changing report appearance by overriding built-in styles for table and subordinate elements.
In "For Each Row" (post_query), enter following code applied to each record. In every td element you are referencing interactive report columns and labels with substitution string (#) and are styling each record using inline CSS method.
You used substitution string to reference table column names and labels of page items as #PRODUCT_NAME# and #CATEGORY_LABEL#.
<tr><td rowspan="5" valign="top">
<img width="75" height="75" src="#DETAIL_IMG_NO_STYLE#"></td>
<td colspan="6"><h1><a href="#ICON_LINK#"><strong>#PRODUCT_NAME#</strong></a></h1></td>
</tr>
<tr>
<td><strong>#CATEGORY_LABEL#:</strong></td><td>#CATEGORY#</td>
<td><strong>#PRODUCT_AVAIL_LABEL#:</strong></td><td>#PRODUCT_AVAIL# </td>
<td><strong>#LAST_DATE_SOLD_LABEL#:</strong></td><td>#LAST_DATE_SOLD#</td>
</tr>
<tr>
<td align="left"><strong>#PRODUCT_DESCRIPTION_LABEL#:</strong></td>
<td colspan="5">#PRODUCT_DESCRIPTION#</td>
</tr>
<tr>
<td style="padding-bottom: 0px;"><strong>#LIST_PRICE_LABEL#</strong></td>
<td style="padding-bottom: 0px;"><strong>#UNITS_LABEL#</strong></td>
<td style="padding-bottom: 0px;"><strong>#SALES_LABEL#</strong></td>
<td style="padding-bottom: 0px;"><strong>#CUSTOMERS_LABEL#</strong></td>
</tr>
<tr>
<td style="padding-top: 0px;">#LIST_PRICE#</td>
<td style="padding-top: 0px;">#UNITS#</td>
<td style="padding-top: 0px;">#SALES#</td>
<td style="padding-top: 0px;">#CUSTOMERS#</td>
</tr>
<tr>
<td colspan="7" class="separator"></td>
</tr>
In After Rows, enter </table>
to complete HTML code = HTML to be displayed after report rows. It is closing table tag to end HTML table.
Save and run page 3 products tbl from Manage Products option in Setup menu
Filter and order cols : Click View Reports icon. Click Actions menu in interactive report and select Columns. Make sure all columns (except Description and Last Sold) appear in Display in Report section. You can use arrow icons to arrange columns in a desired order. Click Apply button. Only columns you selected will appear in interactive report.
Click Actions menu again and select Save Report (under Report). From Save drop-down list, select As Default Report Settings. Set Default Report Type to Primary and click Apply. After modifying an interactive report you must save it using this procedure, otherwise you’ll lose the applied settings when you subsequently view this report. Developers can save two types of default interactive report: primary and alternative. Both reports display on Report list on Search bar. Primary default report (you just saved) cannot be renamed or deleted.
In next steps we change default data source values and produce custom output using SQL query.
Cols hdr, order, filter and cust name as link
In App Builder, click Customers page (Page 2) to open it in the Page Designer for modification.
Click Customers region under Content Body node. Standard method to modify properties of page component is to click corresponding node. This action refreshes Properties section (located to your right) with properties of selected page component for alteration.
Change Type (under Source section) = SQL Query to see default query generated for interactive grid.
select ROWID, CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1,
CUST_STREET_ADDRESS2, CUST_CITY, CUST_STATE, CUST_POSTAL_CODE, CUST_EMAIL,
PHONE_NUMBER1, PHONE_NUMBER2, URL, CREDIT_LIMIT, TAGS
from DEMO_CUSTOMERS
Enter following SQL statement in SQL Query text area, replacing existing one:
SELECT customer_id, cust_last_name || ', ' || cust_first_name customer_name
, CUST_STREET_ADDRESS1||decode(CUST_STREET_ADDRESS2, null, null, ', '||CUST_STREET_ADDRESS2) customer_address
, cust_city, cust_state, cust_postal_code
FROM demo_customers
Decode Syntax: decode( expression , search , result [, search , result]... [, default] ) If default is omitted, Oracle returns null.
Expand Customers region -> expand Columns node. Click column eg CUSTOMER_NAME and change its heading (under Heading section in Properties pane) to Name. Change headings of other cols : Address, City, State, and Postal Code
In Columns node, click the CUSTOMER_ID column, and change it Type property from Number Field to Hidden to hide col at runtime. PK cols are added to DB tbls to enforce data integrity and are not displayed in apps. This is why such column's Type property is set to hidden to make them invisible at runtime.
Run page. Click Actions menu (A) - select Columns. When you click column in left pane, right pane (D) shows its name and width. You can input a numeric value to change width of col. Using arrow icons (E), arrange selected columns in following order: Name, Address, City, State, and Postal Code
Click Save button in the Columns window to apply the changes.
After you modify interactive grid in runtime save it : Click Actions menu -> Save from Report option, otherwise you’ll lose the applied settings when you access it later.
Click Edit Page2 (F) in the Developer Toolbar at the bottom of page to access Page Designer.
Click CUSTOMER_NAME column to set properties: transforming cust name col into link that will lead to Page 7 cust. form.
When you click customer's name in interactive grid report at runtime:
- ID of that cust is stored in substitution string (&CUSTOMER_ID.) (G)
- and is forwarded to corresponding page item (P7_CUSTOMER_ID) (H) on Page 7, which displays cust profile using this ID.
You created similar kind of link in Chapter 4 for region "Sales for this Month".
Scroll down to Link section and click "No Link Defined" under Target to bring up Link Builder dialog box -> set link properties :
Property | Value | Help | |
---|---|---|---|
1 | Type | Page in this app | |
2 | Page | 7 | |
3 | Name | P7_CUSTOMER_ID | This value refers to an item on Page 7 that will be populated with the value held in #ID# . It is forwarded to Page 7 from the Home page to display selected customer profile. |
4 | Value | &CUSTOMER_ID. | references third column in above SELECT query. Standard procedure in APEX to refer to a column value is to enclose it between # symbols. To refer page item we use substitution strings. |
5 | Clear Cache | 7 |
Use LOVs (I) in Set Items section to select item name (3) and value (4).
Close Link Builder dialog box using OK button.
Save and run page. Cust Name col will now appear as link. Click any customer name to see details on Page 7, which pops up on top of Page 2.
To call Page 7 with a blank form from Page 2 - Customers :
Rendering tab to your left click Customers interactive grid region -> set its Template property to Standard (was Interactiv). Selected template will place title and border for interactive grid region.
Right-click Customers region and from context menu select Create Button. Button named New will be added. Set properties for new button :
Property | Value | Help | |
---|---|---|---|
1 | Button Name | CREATE | |
2 | Label | Create Customer | appears as tooltip when you move over button at run-time |
3 | Button Position | Copy | over dozen values, try other options as well to observe different positions |
4 | Hot | On | renders the button in dark color |
5 | Action | Redirect to Page in this App | (under Behavior) - Creates link to call Page 7. |
6 | Target | Type = Page in this app Page = 7 Clear Cache =7 | Creates link to call Page 7. Clear Cache prop. makes all items on target pg 7 blank |
Save and run Page 2, which should look similar to Figure 5-1.
Click Create Customer button to call Customer Details Page 7 on top of calling page as a modal dialog.
https://www.youtube.com/watch?v=WLE9_1I_nA0
Like 5.3.2, properties are :
Property | Value | Help | |
---|---|---|---|
1 | Button Name | CallPHPpge | |
2 | Label | Call PHP page | appears as tooltip when you move over button at run-time |
3 | Button Position | Copy | over dozen values, try other options as well to observe different positions |
4 | Hot | Off | if On : renders the button in dark color |
5 | Action | Redirect to URL | (under Behavior) - Creates link to call PHP Page |
6 | URL | "http://192.168.../" or "http://dev1:8083/fwphp/www" ?param1=&P26_PARAM | Creates link to call PHP Page |
Open in new tab : ?
With Page 7 cust. form being displayed in browser, click Edit Page 7 in Developer Toolbar at pge bottom to call this pge in PageDesigner.
Like region placement in 12 cols grid layout, done for six Home pge regions, pge items can also be placed using APEX's grid layout, as follows. Width property sets items width on page. If Value Required is set to Yes and page item is visible, APEX automatically performs NOT NULL validation when page is submitted and you are asked to input a value for field. If you set Value Required to No, no validation is performed and a NULL value is accepted. Value Required attribute works in conjunction with Template = Required to signify (označi) mandatory items visually.
Page flds are in 2 columns, only "Tags" fld spans both cols. Save your changes and call this Page 7 by clicking any customer's name on Page 2. It should come up with selected customer profile (Figure 5-6 Customer Details Page). Click each item under Items node and apply following properties.
to hold app-shared dynamic LOV! States list. app-shared means defined in app shared componebts in one place and reusable in all app modules pages.
both VARCHAR2 30 :
select state_name display_value, st return_value from demo_states order by 1
--select state_name display_value, state_id return_value from demo_states order by 1
Label=State.....Sequence=70.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=make it null (this item will be transformed into a select list).....Value Required=On
Modify the two phone number items in cust. form pgID=7 and set their Value Placeholder property (under Appearance) to 999-999-9999. When a new customer record is added, this placeholder is shown in the two phone number items to receive input in the specified format. As you type in values, the placeholders will be replaced by the numbers entered.
Field "Credit Limit" is used to assign a credit cap to each customer with a figure of $5,000. If you enter a value more than the assigned cap, you'll be prevented by presenting an appropriate message. NOTE: Validation error messages display when :
In left pane of Page 7 cust.form : click Processing icon (was tab) -> right-click Validating node -> Create Validation from context menu (Figure 5-7) -> Set following properties for this new validation :
Property | Value | Help | |
---|---|---|---|
1 | Name | Check Credit Limit | should be meaningful ! |
2 | Type | PL/SQL Expression | expression in valid PL/SQL syntax that evaluates to true or false |
3 | PL/SQL Expression | :P7_CREDIT_LIMIT <= 5000 | If true customer record is saved to DB. Note that you use bind variables (item name preceded with a colon) when you reference value of a session state variable from within PL/SQL code. |
4 | Error Message | Customer's Credit Limit must be less than or equal to $5,000 |
Click Form option, followed by Report with Form option. These two selections will create a report page (Figure 6-1) to display all product records from the table (selected in step 5) and a form page (Figure 6-6) to add, modify, and delete products.
On the Page Attributes wizard screen, set the following properties and click Next. The form page (Page 6) is named Product Details and it will be linked to the report page (Products - Page 3).
Property Value
Report Type = Interactive Report
Report Page Number = 3
Report Page Name = Products
Form Page Number = 6
Form Page Name = Product Details
Form Page Mode = Modal Dialog
Breadcrumb = Breadcrumb
Parent Entry = Home (Page 1)
Entry Name = Products
On Navigation Menu screen, set Navigation Preference to "Identify an existing navigation menu entry for this page", set "Existing Navigation Menu Entry" to Setup (šifrarnici), and click Next. This step will highlight Setup entry in main navigation menu when you access products setup.
On Data Source screen, select Table for Source Type, accept default schema in Table/View Owner, and select "DEMO_PRODUCT_INFO (table)" for Table/View Name. Columns from the selected table to be shown in the interactive report will appear. Accept all table columns and click Next.
On Form Page screen, add all columns (A) from the DEMO_PRODUCT_INFO table to Page 6, except : MIMETYPE, FILENAME, and IMAGE_LAST_UPDATE (B). These three columns are used in background to handle images of products.
For Primary Key Type, choose Select "Primary Key Column(s)" (C). Set "PK Column 1" attribute to PRODUCT_ID (Number) (D). PRODUCT_ID is populated behind the scene using DB sequence object (DEMO_PRODUCT_INFO_SEQ) via BI_DEMO_PRODUCT_INFO trigger when you add a new product - see the two objects by accessing Object Browser.
Click Create button to complete wizard.This time, wizard creates two pages (3 and 6) as an initial structure for this module.
Click region named Report 1 and set its Title to Products
In SQL Query, replace existing SELECT statement with following to select :
Was :
select PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY, PRODUCT_AVAIL,
LIST_PRICE, PRODUCT_IMAGE, MIMETYPE, FILENAME, IMAGE_LAST_UPDATE
from DEMO_PRODUCT_INFO
Demo app :
PRODUCT_ID PRODUCT_NAME PRODUCT_DESCRIPTION CATEGORY PRODUCT_AVAIL
LIST_PRICE UNITS SALES CUSTOMERS LAST_DATE_SOLD
IMG ICON_LINK DETAIL_IMG DETAIL_IMG_NO_STYLE TAGS
select p.product_id,
p.product_name,
p.product_description,
p.category,
decode(p.product_avail, 'Y','Yes','N','No') product_avail,
p.list_price,
(select sum(quantity) from demo_order_items where product_id = p.product_id) units,
(select sum(quantity * p.list_price) from demo_order_items where product_id = p.product_id) sales,
(select count(o.customer_id) from demo_orders o, demo_order_items t where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id) customers,
(select max(o.order_timestamp) od from demo_orders o, demo_order_items i where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
p.product_id img,
apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID,P6_BRANCH:'||p.product_id||','||3,p_dialog=> 'null') icon_link,
decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
'<img alt="'||apex_escape.html_attribute(p.product_name)||'" title="'||apex_escape.html_attribute(p.product_name)
||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />') detail_img,
decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id))
detail_img_no_style,
tags
from demo_product_info p
select PRODUCT_ID, PRODUCT_NAME , PRODUCT_DESCRIPTION, CATEGORY, PRODUCT_AVAIL
, LIST_PRICE, UNITS, SALES, CUSTOMERS, LAST_DATE_SOLD
, IMG, ICON_LINK, DETAIL_IMG, DETAIL_IMG_NO_STYLE
from (
select p.product_id, p.product_name, p.product_description, p.category
, decode(p.product_avail, 'Y','Yes','N','No') product_avail
, p.list_price
, (select sum(quantity) from demo_order_items where product_id = p.product_id) units
, (
select sum(quantity * p.list_price)
from demo_order_items where product_id = p.product_id
) sales
, (
select count(o.customer_id) from demo_orders o, demo_order_items t
where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id
) customers
, (
select max(o.order_timestamp) od
from demo_orders o, demo_order_items i
where o.order_id = i.order_id and i.product_id = p.product_id
) last_date_sold
, p.product_id img
, apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID:'||p.product_id) icon_link
, decode( nvl(dbms_lob.getlength(p.product_image),0)
,0,null,
'<img alt="'||p.product_name||'" title="'||p.product_name
||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />'
) detail_img
, decode( nvl(dbms_lob.getlength(p.product_image),0)
,0,null,
apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)
) detail_img_no_style
from demo_product_info p
)
p.product_id is img column in query above
is formed using PREPARE_URL function which is :
holds products img. HTML img tag is used to display images of products in conjunction with built-in APEX function APEX_UTIL.GET_BLOB_FILE_SRC - which enables us to format img display with height and width properties. Image is styled using CSS inline styling method. getlength fn of dbms_lob package (dbms_lob.getlength) is used to estimate BLOB column size in table to facilitate inclusion of download link in report. If BLOB column size length is 0, BLOB is NULL and no download link is displayed.
Expand Columns node (under Content Body | Products region) and set meaningful column headings as follows:
Product, Description, Category, Available, Price, Units, Sales, Customers, Last Sold, Image, Icon Link, Image Detail, and Detail Image No Style
Modify following columns using specified properties.
We can use Ctrl+click or Shift+click to select multiple cols to change Type properties at once.
Column : Property = Value...Help
Click PRODUCT_NAME column to transform it into a link. By selecting Product Name column in Link Text attribute you specify this report column to appear as a link. You created a similar kind of link in previous chapter to call Customer Details page.
Property Value
If you save and run report page at this stage, you will see an EDIT column (represented with a pencil icon), which leads to details page. Since we have already created a link (on the Product Name column), we will eliminate this column so :
Under Products region, click its Attributes node, and set in Properties pane "Link" Column = Exclude Link Column. Figure 6-3
In same Attributes node, scroll down to Icon View section and set following properties.
By default, most interactive reports display as a report. You can optionally display columns as icons. When configured, an icon (View Icons) appears on Search bar. To use this view, you must specify columns to identify icon, label, and target (that is link). As a best practice Type attribute of these columns is set to hidden (as you did in step 4), because they are typically not useful for end users. Image Attributes property will style height and width of images.
Property Value
Section "Detail View" under Icon View section -> turn on Show property. When configured, View Details icon appears on Search bar.
In Before Rows attribute of Detail View you enter HTML code to be displayed before report rows. Eg you can use TABLE element to put DB content in row/column format. Besides adding HTML code, styling information can also be incorporated using this attribute. Code below uses custom CSS rules to override default Oracle APEX Interactive Report (apexir) styles.
<style>
table.apexir_WORKSHEET_CUSTOM {
border: none !important;
box-shadow: none;
-moz-box-shadow: none;
-webkit-box-shadow: none;}
.apexir_WORKSHEET_DATA td {
border-bottom: none !important;}
table.reportDetail td {
padding: 2px 4px !important;
border: none !important;
font: 11px/16px Arial, sans-serif;}
table.reportDetail td.separator {
background: #F0F0F0 !important;
padding: 0 !important;
height: 1px !important;
padding: 0;
line-height: 2px !important;
overflow: hidden;}
table.reportDetail td h1 {margin: 0 !important}
table.reportDetail td img {
margin-top: 8px;
border: 4px solid #CCC;
-moz-border-radius: 4px;
-webkit-border-radius: 4px;}
</style>
<table class="reportDetail">
NOTE: Remember that all APEX pages are HTML pages controlled by HTML properties and cascading style sheet (CSS) settings. When you create an interactive report, Oracle APEX renders it based on CSS classes associated with current theme. Each APEX interactive report component has a CSS style definition that may be changed by applying standard CSS techniques to override defaults. CSS changes may be applied to :
In the current step, you are changing report appearance by overriding built-in styles for table and subordinate elements.
In "For Each Row" (post_query), enter following code applied to each record. In every td element you are referencing interactive report columns and labels with substitution string (#) and are styling each record using inline CSS method.
You used substitution string to reference table column names and labels of page items as #PRODUCT_NAME# and #CATEGORY_LABEL#.
<tr><td rowspan="5" valign="top">
<img width="75" height="75" src="#DETAIL_IMG_NO_STYLE#"></td>
<td colspan="6"><h1><a href="#ICON_LINK#"><strong>#PRODUCT_NAME#</strong></a></h1></td>
</tr>
<tr>
<td><strong>#CATEGORY_LABEL#:</strong></td><td>#CATEGORY#</td>
<td><strong>#PRODUCT_AVAIL_LABEL#:</strong></td><td>#PRODUCT_AVAIL# </td>
<td><strong>#LAST_DATE_SOLD_LABEL#:</strong></td><td>#LAST_DATE_SOLD#</td>
</tr>
<tr>
<td align="left"><strong>#PRODUCT_DESCRIPTION_LABEL#:</strong></td>
<td colspan="5">#PRODUCT_DESCRIPTION#</td>
</tr>
<tr>
<td style="padding-bottom: 0px;"><strong>#LIST_PRICE_LABEL#</strong></td>
<td style="padding-bottom: 0px;"><strong>#UNITS_LABEL#</strong></td>
<td style="padding-bottom: 0px;"><strong>#SALES_LABEL#</strong></td>
<td style="padding-bottom: 0px;"><strong>#CUSTOMERS_LABEL#</strong></td>
</tr>
<tr>
<td style="padding-top: 0px;">#LIST_PRICE#</td>
<td style="padding-top: 0px;">#UNITS#</td>
<td style="padding-top: 0px;">#SALES#</td>
<td style="padding-top: 0px;">#CUSTOMERS#</td>
</tr>
<tr>
<td colspan="7" class="separator"></td>
</tr>
In After Rows, enter </table>
to complete HTML code = HTML to be displayed after report rows. It is closing table tag to end HTML table.
Save and run page 3 products tbl from Manage Products option in Setup menu
Filter and order cols : Click View Reports icon. Click Actions menu in interactive report and select Columns. Make sure all columns (except Description and Last Sold) appear in Display in Report section. You can use arrow icons to arrange columns in a desired order. Click Apply button. Only columns you selected will appear in interactive report.
Click Actions menu again and select Save Report (under Report). From Save drop-down list, select As Default Report Settings. Set Default Report Type to Primary and click Apply. After modifying an interactive report you must save it using this procedure, otherwise you’ll lose the applied settings when you subsequently view this report. Developers can save two types of default interactive report: primary and alternative. Both reports display on Report list on Search bar. Primary default report (you just saved) cannot be renamed or deleted.
Page Designer toolbar carries a section called Page Selector - fld : enter page number and click Go. Navigate to Next/prev Page (up/down arrow) - Figure 6-5. Using Page Selector call Page 6 in Page Designer. Click root node and set following properties.
Property Value
img {
width: 100%;
max-width: 100%;
height: auto;
}
.imgItem::before
{
content: 'Nike';
position: absolute;
top: 30px;
left: 20px;color: #000;
opacity: 0.1;
font-size: 8em;
font-weight: 800;
}
P6_PRODUCT_NAME, P6_CATEGORY, P6_PRODUCT_AVAIL, and P6_LIST_PRICE
Click on P6_PRODUCT_NAME item, press and hold the Ctrl key , and then click the other items to select them all
Property Value
Set Template property of P6_PRODUCT_DESCRIPTION and P6_PRODUCT_IMAGE page items to Optional.
Shared Components (pyramid of 3) -> Other Components section -> Lists of Values APEX page
Click Create button (9 LOVs are created by APEX).
Select From Scratch option and click Next.
LOV Name = Categories, select LOV type = Static, click Next.
Fill in values as shown in following table. At runtime these entries will display in order they are entered. Return Value does not display, but is the value that is returned to APEX engine as user selection (user event).
Sequence | Display val | Rerturn val (user event) | Help |
---|---|---|---|
1 | Mens | Mens | Men ? |
2 | Womens | Womens | Women ? |
3 | Accessories | Accessories | Kids ? |
Click Create LOV button
Label=Category.....Sequence=70.....Start New Row=On.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=make it null (this item will be transformed into a select list).....Value Required=On
Here we're going to use LOV Categories to which page item P6_CATEGORY will bound, to display predefined values of categories in Select List - see Manage Customers module to display STATES LOV.
Next, you will change the Product Available field to a Switch comprising two options: On and Off. Just like the previous steps, here as well, you’re changing the item type from Text to Switch. At runtime, this item will show two options to specify whether the selected product is available or not. If you ignore this exercise and leave the item to its default type, users can enter whatever value they like, resulting in compromising application’s integrity. This is a good example to restrict users to select valid values. Select the P6_PRODUCT_AVAIL item and set the following properties. Note that the last two properties in the table sets Y (which stands for On) as the default value for this item.
Property Value
Modify following properties (in Settings section) for the P6_PRODUCT_IMAGE item to map table columns. This mapping is necessary to display product images on details form.
Property Value
In the Settings section, the Storage Type attribute is set to BLOB column specified in item Source attribute by default. The Storage Type attribute specifies where the uploaded file should be stored at. It has two values: BLOB column specified in item source attribute. Stores the uploaded file in the table used by the "Automatic Row Processing (DML)" process and the column specified in the item sourceattribute. The column has to be of data type BLOB. Table APEX_APPLICATION_TEMP_FILE. Stores the uploaded file in a table named APEX_APPLICATION_TEMP_FILE.
To show images of selected products on Product Details page, we will create a Static Content sub-region. Note that this section will only create a blank region to hold an image. The image will be added to this region in a subsequent section. Right-click the Regions node and select Create Region from the context menu. Select the new region and modify the following properties. The region will have a blue background and it will be displayed only when there exists an image for a product and this evaluation is made using a condition based on a PL/SQL function.
Property Value
declare
begin
if :P6_PRODUCT_ID is not null then
for c1 in (
select nvl(dbms_lob.getlength(product_image),0) A
from demo_product_info
where product_id = :P6_PRODUCT_ID
) loop
if c1.A > 0 then return true; end if;
end loop;
end if;
return false;
end;
Click the Product Details region and turn off the Start New Row property to place this region beside the Product Image region you just added.
NOTE: Page items are referenced in a PL/SQL block using bind variables inwhich a colon(:) is prefixed to the item name – :P6_PRODUCT_ID, for example.
Code Explained
In Oracle APEX you make use of conditions to control the appearance of page components. The ability to dynamically show or hide a page component is referred to as conditional rendering. You define conditional rendering for regions, items, and buttons. These page components have a Condition section in the property editor, where you select a condition type from a list. In the current scenario, you set a condition based on a PL/SQL function, which returns a single Boolean value: True or False. If the code returns True, the region is displayed carrying the image of the selected product.
After selecting a condition type, you inform Oracle APEX to execute the defined PL/SQL code. The code first executes an IF condition (line 3) to check whether the product ID is not null by evaluating the value of the page item P6_PRODUCT_ID. If the value is null, the flow of the code is transferred to line 13, where a false value is returned and the function is terminated. If there exists a value for the product ID, then line 4 is executed, which creates a FOR loop to loop through all records in the DEMO_PRODUCT_INFO table to find the record (and consequently the image) of the selected product (line 4-11). On line 8, another IF condition is used to assess whether the image exists. If so, a true value is returned on line 9 and the function is terminated.
In this section, you will create a new item named P6_IMAGE to display the product image in the Product Image region. Right-click the Product Image region and select Create Page Item from the context menu. Set the following properties for the new item. The code defined in the PL/SQL Function Body fetches image of the selected product using a function (apex_util.get_blob_file_src). By setting the Rows Returned condition and using a SQL query we ensured the existence of an image in the table. The imgItem class defined for this page item was referenced in section 6.4 to show some content before product images.
Property Value
return '<img src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',:P6_PRODUCT_ID)||'" />';
SELECT mimetype from demo_product_info
WHERE product_id = :P6_PRODUCT_ID AND mimetype like 'image%'
10 Escape special characters = Off (Otherwise the image won’t appear)
An image can be removed from the Product Details page and consequently from the underlying table by clicking this button. It is attached to a process (Delete Image) defined in the next section. Right-click the Product Image region and select Create Button. Set the following properties for the new button. The button will appear on top of the region. The Target value calls a confirmation box. This call is made using an Oracle APEX function (apex.confirm) by passing a message and the name of the Delete button. If you click Yes in the confirmation box, the process associated with the Delete button removes image references from the products table.
Property Value
javascript:apex.confirm('Are you sure you want to delete this image? It will no longer be available for others to see if you continue.','DELETE_IMAGE');
This is the process I mentioned in the previous section. It is associated with the Delete button to remove a product image. To remove an image stored in a database table, you are required to just replace the content of the relevant columns with a null. Click the Processing tab and then right-click the Processing node. From the context menu select Create Process. Set the following properties for the new process:
Property Value
UPDATE demo_product_info
SET product_image=null, mimetype=null, filename=null, image_last_update=null
WHERE product_id = :P6_PRODUCT_ID;
NOTE: The Processing node contains two processes (Process form Product Details and Close Dialog) that were created by the page creation wizard. The first one is created to handle DML operations, while the second one closes Page 6 when you click Create, Save, or Delete button. The values of these buttons are mentioned in Server-side Condition of the process, which specifies that the dialog is to be closed only when any of the three buttons are clicked. Clicking the DELETE_IMAGE button won't close the page, because the name of this button is not in the Value list. Similarly, the Delete Image process will only be executed when the DELETE_IMAGE button is pressed.
Test Your Work
Save your work and run the application. From the main navigation menu, select Manage Products from the Setup menu. On the main interactive report page (Figure 6-1), click the three report icons individually to see different views of products information. Clicking View Icons will present small icons of products. Each product is presented as a linked icon. If you click any icon, you'll be taken to the form page (Page 6 - Figure 6-6) where you'll see details of the selected product. Click the Report View icon. TheReport View presents data in a table. Here, you can access the details page by clicking products’ names. Click the Detail View icon. This View presents products information from a different perspective. You can access details of a product by clicking its name. This is the view that was styled in section 6.3.1 steps 8-11.
Click any product's name to call its details page (as illustrated in Figure 6-6). The form region (Product Details) was created by the wizard incorporating all relevant fields. The Product Image region was created in section 6.4.5. Also, note that the Remove Image button (you created in section 6.4.7) appears within this region. Figure 6-6 Product Details Page
Create a new product record using the Add Product button on the Products report page. Click the Browse button and select any small image file to test image upload. You can use an existing product image by right-clicking the image and selecting Save Image As from the context menu. Or, use the Download link provided on the Product Details form page to get one for testing.Once you have an image in place, fill in all the fields except List Price. Try to save this record by clicking the Create button. A message “Please fill out this field” will appear informing you to provide some value for the List Price. Now, provide some alpha-numeric value like abc123 in the List Price. Again, a message will come up reminding you to put a numeric value. Finally, input a numeric value in the List Price field and save the record. You'll see the new product appears on the Products page among others with the image you uploaded. Edit this record and see the image. Change the category of this product, switch availability to the other option and apply changes. Call the product again and observe the changes you just made to it. Click the Remove Image button and see what happens. Click the Delete button followed by OK in the confirmation box. The product will vanish from the list.
NOTE: You might encounter a primary key violation message (ORA-00001: unique constraint (DEMO_PRODUCT_INFO_PK) violated) while creating first product record. This is because the Sequence object for this table is created with an initial value of 1. When you try to save the first product record, 1 is assigned as the first primary key value, which already exists in the table. To cope with this situation, just click the Create button on the form page several times. After ten clicks the record will be save.
As mentioned earlier in this chapter, you can store different types of files (upto 4 GB), such as images, audio, video, PDF, CSV, XLSX, DOC and more, in the BLOB column of your table. In this exercise, I’ll demonstrate how to save and view a PDF. As far as the uploading is concerned, you are not required to perform any special steps to handle PDF or any other file type. You have already set the stage in the previous sections where you handled JPEG images. Here, you will just create two new pages that will be apart from your application.
select p.product_id, p.product_name,
dbms_lob.getlength(p.product_image) document
from demo_product_info p
Execute the following steps to modify the BLOB column attributes.
In the Page Designer, expand the Columns node (under the Products Catalog interactive report region), click the Document column and set the following attributes for this column. In the Download Text property you set a string used for the download link. If nothing is provided, Download is used. The Content Disposition specifies how the browser handles the content when downloading. If a MIME type is provided and the file is a type that can be displayed, the file is displayed. If MIME type is not provided, or the file cannot be displayed inline, the user is prompted to download.
Property Value
As just mentioned the product setup module created earlier in this chapter is ready to upload any type of file, so to save some precious time we are going to use that module to upload a PDF.
Play around with this module by tweaking the saved properties to see resulting effects on two pages. You can always restore properties to their original values by referencing exercises provided in the chapter.
page 201/419
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
This chapter will teach you how to create professional looking order forms. Orders from customers will be taken through a sequence of wizard steps. The first wizard step will allow you to select an existing customer or create a new one. In the second step, you will select ordered products. After placing the order, the last step will show summary of the placed order. Once an order is created, you can view, modify, or delete it through Order Details page using a link in orders main page. The list presented below displays the application pages you will create in this chapter: Page No. Page Name Purpose 4 Orders The main page to display all existing orders 29 Order Details Display a complete order with details for modification 11 Identify Customer (Wizard Step 1) Select an existing customer or create a new one 12 Select Order Items (Wizard Step 2) Add products to an order 14 Order Summary (Wizard Step 3) Show summary of the placed order You’ll build this module sequentially in the sequence specified above. The first two pages (Page 4 and 29) will be created initially using a new wizard option: Master Detail. Both these pages are not part of the Order Wizard and will be utilized for order modification and deletion after recording an order. Page 4 is similar to the pages you created in Customer and Product modules and lists all placed orders, while Page 29 will be used to manipulate order details. For example, you can call an order in the usual way using the provided link in the master page. The called order will appear in the details page where you can: Add/Remove products to and from an order Delete the order itself The purpose of each chapter in this book is to teach you some new features. Here as well, you’ll get some new stuff. This chapter will walk you through to get detailed practical exposure to the techniques this module contains. After completing the two main pages, you will work on actual order wizardsteps to create other pages of the module. Recall that in the previous chapter you modified the main interactive report (Page 3) to create a couple of views (Icon and Detail) and used the Actions menu to select and sort table columns. In this chapter, many other utilities provided under the Actions menu will be exposed. But first, let’s create the two main pages using the conventional route.
Execute the instructions provided in the following sub-sections to modify the Orders page. 7.3.1 Modify the Orders Interactive Report Region on Page 4 The Orders interactive report region on Page 4 fetches orders information from the DEMO_ORDERS table. Let’s replace the existing auto-generated data fetching mechanism with a custom SQL query, which incorporates customers information from the DEMO_CUSTOMERS table.
Execute the instructions provided in the following sub-sections to modify the Order Details Page. 7.4.1 Modify Master Region Properties Page 29 contains two regions. The master region (Form on DEMO_ORDERS) is of Form type and carries order header information, while the second region (Order Details) is an interactive grid, which containsline item details. Modify the master region using the following steps:
Title. The expression consists of two parts. The first one (Order #) is a string concatenated to a page item (P29_ORDER_ID), which carries the order number. The string, when combined, would be presented as: Order # 1. Make sure that region’s Template attribute (under Appearance) is set to Standard to show this title.
As mentioned earlier, you will go through a series of steps to enter a new order. You identified and created these steps in Order Wizard list in Chapter 3 section 3.2.3. The top section (A) in Figure 7-23 reflects these steps. Each step will be associated to an application page. The rest of this chapter will guide you to create the three pages individually. In this exercise, you will create Page 11 - Enter New Order. The order recording process initiates when you click the button Enter New Order on the Orders page (Page 4). The button calls Page 11, where you select a customer who placed the order. Besides selecting an existing customer, you can also create record of a new customer on this page. The Customer LOV button (B) calls a list of existing customers from which you can select one for the order. If you select the New Customer option (C), a region (New Customer Details) will be shown under the existing region. By default, this region is hidden and becomes visible when you click the New Customer option. This functionality is controlled by a dynamic action (Hide / Show Customer), which will also be created for this page. In addition to various techniques taught in this part, you’ll create this page from an existing page - Customer Details (Page 7) - to generate a new customer record. Here, you’ll make a copy of that page and will tweak it for the current scenario. Let’s see how it is done. Figure 7-23 Identify Customer Page1. In the App Builder interface, click the Customer Details - Page 7 to open its definitions in Page Designer.
Property Value
Name P11_CUSTOMER_OPTIONS Type Radio Group Label Create Order for: Number of Columns 2 Template Required Label Column Span 3 Type (under List of Values) Shared ComponentList of Values NEW OR EXISTING CUSTOMER Display Null Value Off Type (under Source) Static Value Static Value (under Source) EXISTING Type (under Default) Static Static Value (under Default) EXISTING 7.5.5 Create a Sub Region – Existing Customer Right-click the Identify Customer region and select Create Sub Region. This will add a sub region under the page item P11_CUSTOMER_OPTIONS. Set the following properties from the sub region. Property Value Title Existing Customer Type Static Content Template Blank with Attributes 7.5.6 Modify Item – P11_CUSTOMER_ID In the Items section, click P11_CUSTOMER_ID. Set the Name property of this hidden item to P11_CUSTOMER_ID_XYZ. Set Server-side Condition Type to Never (last in the list). This item is renamed and suppressed from being rendered because a new item (of Popup LOV type) with the same name is created in the next section to display a list of customers, instead. By selecting the Never value for the Server-side Condition Type property, you permanently disable a page component. That is, the component is never rendered. 7.5.7 Add LOV Right-click the Existing Customer sub-region and select Create Page Item. Set the following properties for this item. The Type value (under Source) is set to Null, because the IDs and names of customers are retreived using a SQL query and displayed in a Popup LOV. Property Value Name P11_CUSTOMER_ID Type Popup LOV Label Customer Template Required Width 70Value Required Off Type (under List of Values) SQL Query SQL Query select cust_last_name || ', ' || cust_first_name d, customer_id r from demo_customers order by cust_last_name Display Extra Values Off Display Null Value Off Type (under Source) Null Help Text Choose a customer using the pop-up selector, or to create a new customer, select the New customer option. 7.5.8 Modify Customer Details Region Click the Customer Details region and set the following properties for this region. When you specify a parent region you make a region child of a parent region. Property Value Title New Customer Details Parent Region Identify Customer 7.5.9 Delete Validation, Processes, and Buttons
Having identified the customer, the second step in the order wizard is to add products to the order. In this exercise, you will create Page 12 of the application to select ordered items and input the required quantities.
You styled the Detail View of an interactive report in the previous chapter to customize its look. Here as well, you will apply some styling rules to give the page a professional touch. Previously, you added rules to a single page element: HTML table. In the following exercise you’ll apply rules to the whole page. Before getting your feet wet, go through the following topic to understand Cascading Style Sheets (CSS). Cascading Style Sheets A cascading style sheet (CSS) provides a way to control the style of a web page without changing its structure. When used properly, a CSS separates visual properties such as color, margins, and fonts from the structure of the HTML document. In this chapter, you will use CSS to style Page 12 (Select Items - Figure 7- 27). On this page you will add class properties to PL/SQL code and will reference them in CSS in the HTML Head section. Before moving on to understand the actual functionality, let’s first take a look at a simple example on how to use class attribute in an HTML document. The class attribute is mostly used to point to a class in a style sheet. The syntax is
<element class="classname">.
<html><head>
<style type="text/css">
h1.header {color:blue;}
p.styledpara {color:red;}
</style>
</head>
<body>
<h1 class="header">Class Referenced in CSS</h1>
<p>A normal paragraph.</p>
<p class="styledpara">Note that this is an important paragraph.</p>
</body>
</html>
The body of this web page contains three sections:
<h1 class="header">Class Referenced in CSS</h1>
The text “Class Referenced in CSS” is enclosed in h1 html tag. It is called level 1 heading and is the most important heading in a document. It is usually used to indicate the title of the document. The text is preceded by a class named “header”. Considering the class syntax, h1 is the element and header is the classname. This class is referenced in the style section using a CSS rule – h1.header {color:blue;} – to present the heading in blue color. A CSS rule has two main parts: a selector and one or more declarations. The selector is normally the HTML element you want to style. Each declaration consists of a property and a value. The property is the style attribute you want to change. Each property has a value. In the h1.header {color:blue;} rule, h1 is the selector, header is the classname, and {color:blue;} is the declaration.
A normal paragraph.
– It is a plain paragraph without any style applied to it. HTML documents are divided into paragraphs and paragraphs are defined with thetag. The
tag is called the start tag or opening tag, while
is called the end or closing tag.Note that this is an importantparagraph.
– It is a paragraph with a class named “styledpara”. In the style section, the selector “p” followed by the classname “styledpara” with the declaration{color:red;} is referencing this section to present the paragraph text in red color. Now that you have understood how CSS is used in web pages, let’s figure out how it is used in Oracle APEX.save your work. You can find this code in BookCode\Chapter7\7.6.1.txt file. CSS rules entered in this box will be applied to all the referenced elements on the current page, as illustrated in Figure 7-27.
div.CustomerInfo{margin: 10px 10px 0;}
div.CustomerInfo strong{font:bold 12px/16px Arial,sans-serif;display:block;width:120px;}
div.CustomerInfo p{display:block;margin:0; font: normal 12px/16px Arial, sans-serif;}
div.Products{clear:both;margin:16px 0 0 0;padding:0 8px 0 0;}
div.Products table{border:1px solid #CCC;border-bottom:none;}
div.Products table th{background-color:#DDD;color:#000;font:bold 12px/16px Arial,sans-serif;padding:4px 10px;text-align:right;border-bottom:1px solid #CCC;}
div.Products table td{border-bottom:1px solid #CCC;font:normal 12px/16px Arial,sans-serif; padding:4px 10px;text-align:right;}
div.Products table td a{color:#000;}
div.Products .left{text-align:left;}
div.CartItem{padding:8px 8px 0 8px;font:normal 11px/14px Arial,sans-serif;}
div.CartItem a{color:#000;}
div.CartItem span{display:block;text-align:right;padding:8px 0 0 0;}
div.CartItem span.subtotal{font-weight:bold;}
div.CartTotal{margin-top:8px;padding:8px;border-top:1px dotted #AAA;}
div.CartTotal span{display:block;text-align:right;font:normal 11px/14px Arial,sans-serif;padding:0 0 4px 0;}
div.CartTotal p{padding:0;margin:0;font:normal 11px/14px Arial,sans-serif;position:relative;}
div.CartTotal p.CartTotal{font:bold 12px/14px Arial,sans-serif;padding:8px 0 0 0;}
div.CartTotal p.CartTotal span{font:bold 12px/14px Arial,sans-serif;padding:8px 0 0 0;}
div.CartTotal p span{padding:0;position:absolute;right:0;top:0;}
Right-click the Wizard Progress Bar node and select Create Region. Set the following properties for the new region. A similar region was added previously to Page 11 to display the Order Progress bar. Property ValueTitle Order Progress Type List List Order Wizard Template Blank with Attributes List Template (under Attributes node) Wizard Progress
The region being created in this section is based on a custom PL/SQL code. The code references CSS rules (defined in the previous section) to design the Select Items page, as illustrated in Figure 7-27.
What is PL/SQL?
PL/SQL stands for Procedural Language/Structured Query Language. It is a programming language that uses detailed sequential instructions to process data. A PL/SQL program combines SQL command (such as Select and Update) with procedural commands for tasks, such as manipulating variable values, evaluating IF/THEN logic structure, and creating loop structures that repeat instructions multiple times until the condition satisfies the defined criteria. PL/SQL was expressly designed for this purpose. The structure of a PL/SQL program block is:
Declare
Variable declaration
Begin
Program statements
Exception
Error-handling statements
End;
PL/SQL program variables are declared in the program’s declaration section. The beginning of the declaration section is marked with the reserved word DECLARE . You can declare multiple variables in the declaration section. The body of a PL/SQL block consists of program statements, which can be assigned statements, conditional statements, loop statements, and so on. The body lies between the BEGIN and EXCEPTION statements. The exception section contains program statements for error handling. Finally, PL/SQL programs end with the END; statement. Comments in PL/SQL code are added by prefixing them with double hyphens.In a PL/SQL program block, the DECLARE and EXCEPTION sections are optional. If there are no variables to declare, you can omit the DECLARE section and start the program with the BEGIN command.
declare
l_customer_id varchar2(30) := :P11_CUSTOMER_ID;
begin
--
-- display customer information
--
sys.htp.p('<div class="CustomerInfo">');
if :P11_CUSTOMER_OPTIONS = 'EXISTING' then
for x in (select * from demo_customers where customer_id = l_customer_id) loop
sys.htp.p('<div class="CustomerInfo">');
sys.htp.p('<strong>Customer:</strong>');
sys.htp.p('<p>');
sys.htp.p(sys.htf.escape_sc(x.cust_first_name) || ' ' ||
sys.htf.escape_sc(x.cust_last_name) || '<br />');
sys.htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />');
if x.cust_street_address2 is not null then
sys.htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
end if;
sys.htp.p(sys.htf.escape_sc(x.cust_city) || ', ' ||
sys.htf.escape_sc(x.cust_state) || ' ' ||
sys.htf.escape_sc(x.cust_postal_code));
sys.htp.p('</p>');
end loop;
else
sys.htp.p('<strong>Customer:</strong>');
sys.htp.p('<p>');
sys.htp.p(sys.htf.escape_sc(:P11_CUST_FIRST_NAME) || ' ' ||
sys.htf.escape_sc(:P11_CUST_LAST_NAME) || '<br />');
sys.htp.p(sys.htf.escape_sc(:P11_CUST_STREET_ADDRESS1) || '<br />');
if :P11_CUST_STREET_ADDRESS2 is not null then
sys.htp.p(sys.htf.escape_sc(:P11_CUST_STREET_ADDRESS2) || '<br />');
end if;
sys.htp.p(sys.htf.escape_sc(:P11_CUST_CITY) || ', ' ||
sys.htf.escape_sc(:P11_CUST_STATE) || ' ' ||
sys.htf.escape_sc(:P11_CUST_POSTAL_CODE));
sys.htp.p('</p>');
end if;
sys.htp.p('</div>');
-- display products
--
sys.htp.p('<div class="Products" >');
sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">
<thead>
<tr><th class="left">Product</th><th>Price</th><th></th></tr>
</thead>
<tbody>');
for c1 in (select product_id, product_name, list_price, 'Add to Cart' add_to_order
from demo_product_info
where product_avail = 'Y'
order by product_name) loop
sys.htp.p('<tr><td class="left">'||sys.htf.escape_sc(c1.product_name)||'</td>
<td>'||trim(to_char(c1.list_price,'999G999G990D00')) || '</td>
<td><a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'|| c1.product_id)||'" class="t-Button t-Button--simple t-Button--hot"><span>Add<i class="iR"></i></span></a></td>
</tr>');
end loop;
sys.htp.p('</tbody></table>');
sys.htp.p('</div>');
--
-- display current order
--
sys.htp.p('<div class="Products" >');
sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">
<thead>
<tr><th class="left">Current Order</th></tr>
</thead>
</table>
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tbody>');
declare
c number := 0; t number := 0;
begin
-- loop over cart values
for c1 in (select c001 pid, c002 i, to_number(c003) p, count(c002) q, sum(c003) ep, 'Remove' remove
from apex_collections
where collection_name = 'ORDER'
group by c001, c002, c003
order by c002)
loop
sys.htp.p('<div class="CartItem">
<a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:&SESSION.:REMOVE:::P12_PRODUCT_ID:'||sys.htf.escape_sc(c1.pid))||'"><img src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart" title="Remove from cart" /></a>
'||sys.htf.escape_sc(c1.i)||'
<span>'||trim(to_char(c1.p,'$999G999G999D00'))||'</span>
<span>Quantity: '||c1.q||'</span>
<span class="subtotal">Subtotal: '||trim(to_char(c1.ep,'$999G999G999D00'))||'</span>
</div>');
c := c + 1;
t := t + c1.ep;
end loop;
sys.htp.p('</tbody></table>');
if c > 0 then
sys.htp.p('<div class="CartTotal">
<p>Items: <span>'||c||'</span></p>
<p class="CartTotal">Total: <span>'||trim(to_char(t,'$999G999G999D00'))||'</span></p>
</div>');
else
sys.htp.p('<div class="alertMessage info" style="margin-top: 8px;">');
sys.htp.p('<img src="#IMAGE_PREFIX#f_spacer.gif">');
sys.htp.p('<div class="innerMessage">');
sys.htp.p('<h3>Note</h3>');
sys.htp.p('<p>You have no items in your current order.</p>');
sys.htp.p('</div>');
sys.htp.p('</div>');
end if;
end;
sys.htp.p('</div>');
end;
NOTE: The ELSE block (lines 70-76) executes when the user tries to move on without selecting a product in the current order. The block uses a built-in class (alertMessage info) that carries an image (f_spacer.gif) followed by the message specified on lines 73-74. In this PL/SQL code you merged some HTML elements to deliver the page in your browser. Before getting into the code details, let’s first acquaint ourselves with some specific terms and objects used in the PL/SQL code. Using HTML in PL/SQL Code Oracle APEX installs with your Oracle database and is comprised of data in tables and PL/SQL code. Whether you are running the Oracle APEX development environment or an application you built using Oracle APEX, the process is the same. Your browser sends a URL request, which is translated into an appropriate Oracle APEX PL/SQL call. After the database processes the PL/SQL, the results are relayed back to your browser as HTML. This cycle happens each time you either request or submit a page. Specific HTML content not handled by Oracle APEX (forms, reports, and charts) are generated using the PL/SQL region type. You can use PL/SQL to have more control over dynamically generated HTML within a region, as you do here. Let’s see how these two core technologies are used together.
htp and htf Packages:
htp (hypertext procedures) and htf (hypertext functions) are part of PL/SQL Web Toolkit package to generate HTML tags. These packages translate PL/SQL into HTML understood by a web browser. For instance, the htp.anchor procedure generates the HTML anchor tag a. The following PL/SQL block generate a simple HTML document:
CREATE OR REPLACE PROCEDURE hello AS
BEGIN
htp.htmlopen; -- generates <HTML>
htp.headopen; -- generates <HEAD>
htp.title('Hello'); -- generates <TITLE>Hello</TITLE>
htp.headclose; -- generates </HEAD>
htp.bodyopen; -- generates <BODY>
htp.header(1, 'Hello'); -- generates <H1>Hello</H1>
htp.bodyclose; -- generates </BODY>
htp.htmlclose; -- generates </HTML>
END;
Oracle provided the htp.p tag to allow you to override any PL/SQL-HTML procedure or even a tag that did not exist. If a developer wishes to use a new HTML tag or simply is unaware of the PL/SQL analog to the html tag, s/he can use the htp.p procedure. For every htp procedure that generates HTML tags, there is a corresponding htf function with identical parameters. The function versions do not directly generate output in your web page. Instead, they pass their output as return values to the statements that invoked them.
htp.p / htp.print: Generates the specified parameter as a string
htp.p(‘<p>’):
Indicates that the text coming after the tag is to be formatted as a paragraph
<strong>Customer:</strong>:
Renders the text they surround in bold
htf.escape_sc:
Escape_sc is a function, which replaces characters that have special meaning
in HTML with their escape sequence.
converts occurrence of & to &
converts occurrence of “ to "
converts occurrence of < to <
converts occurrence of > to >
To prevent XSS (Cross Site Scripting) attacks, you must call SYS.HTF.ESCAPE_SC to prevent embedded JavaScript code from being executed when you inject the string into an HTML page. The SYS prefix isused to signify Oracle’s SYS schema. The HTP and HTF packages normally exist in the SYS schema and Oracle APEX relies on them.
Cursor FOR LOOP Statement
The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.
The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor. If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in Example A. This form of the cursor FOR LOOP statement uses an implicit cursor and is called an implicit cursor FOR LOOP statement.
Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL.
Example A - Implicit Cursor FOR LOOP Statement
BEGIN
FOR item IN (
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name
)
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
If you use the SELECT statement multiple times in the same PL/SQL unit, define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement, as shown in Example B. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit.
Example B - Explicit Cursor FOR LOOP Statement
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
TABLE 7-1 PL/SQL CODE EXPLAINED
Display Customer Information (Lines 7-32)
This procedure fetches information of the selected customer and presents it in a desirable format (as shown in Figure 7-27) using the CSS rules defined under the class CustomerInfo.
Declare (Line: 1)
This is the parent PL/SQL block. A nested block is also used under the Display Current Order section on line:48.l_customer_id varchar2(30) := :P11_CUSTOMER_ID; (Line: 2) Assigns customer ID, which is retrieved from the previous order wizard step (Page 11), to the variable l_customer_id. This variable is used in a SQL statement (on Line No. 9) to fetch details of the selected customer. In PL/SQL, the symbol := is called the assignment operator. The variable, which is being assigned the new value, is placed on the left side of the assignment operator and the value is placed on the right side of the operator.
:P11_CUSTOMER_ID is called a bind variable. Bind variables are substituion variables that are used in place of literals. You can use bind variables syntax anywhere in Oracle APEX where you are using SQL or PL/SQL to reference session state of a specified item. For example:
SELECT * FROM employees WHERE last_name like '%' || :P99_SEARCH_STRING || '%'
In this example, the search string is a page item. If the region type is defined as SQL Query, then you can reference the value using standard SQL bind variable syntax. Using bind variables ensures that parsed representations of SQL queries are reused by the database, optimizing memory usage by the server.
The use of bind variables is encouraged in Oracle APEX. Bind variables help you protect your Oracle APEX application from SQL injection attacks. Bind variables work in much the same way as passing data to a stored procedure. Bind variables automatically treat all input data as “flat” data and never mistake it for SQL code. Besides the prevention of SQL injection attacks, there are other performance-related benefits to its use.
You declare a page item as a bind variable by prefixing a colon character (:) like this:
:P11_CUSTOMER_OPTIONS.
When using bind variable syntax, remember the following rules:
Although page item and application item names can be up to 255 characters, if you intend to use an application item within SQL using bind variablesyntax, the item name must be 30 characters or less.
Begin (Line: 3) Read What is PL/SQL at the beginning of this section.
The code block from line number 7 to 32 creates the first section on the page (marked as A in Figure 7-27) using div HTML element and styles it using Rule 1 and 2. The code between lines 9-20 is executed when the user selects an existing customer from the previous wizard step.
sys.htp.p('<div class="CustomerInfo">'); (Line: 7)
The <div> tag defines a division or a section in an HTML document. This is
the opening tag, which references the CustomerInfo class in CSS rules to
format the following elements. The ending tag is defined on Line 32.
for x in (select * from demo_customers where customer_id =
l_customer_id) loop (Line: 9)
Initiates the FOR loop to locate and fetch record of the selected customer
from the demo_customers table.
sys.htp.p('<strong>Customer:</strong>'); (Line: 11)
Displays the label “Customer:” in bold.
sys.htp.p('<p>'); (Line: 12)
The paragraph opening tag. It ends on Line 19.
sys.htp.p(sys.htf.escape_sc(x.cust_first_name) || ' '
||sys.htf.escape_sc(x.cust_last_name) || '<br />'); (Line: 13)
Concatenates customer’s first and last names using the concatenation
characters (||). The <br /> tag inserts a single line break.
sys.htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />'); (Line: 14)
Show customer’s first address on a new line.
if x.cust_street_address2 is not null then (Lines: 15-17)
sys.htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
end if;
It’s a condition to check whether the customer’s second address is not null. If
it’s not, print it on a new line.
sys.htp.p(sys.htf.escape_sc(x.cust_city) || ', ' ||
sys.htf.escapte_sc(x.cust_state) || ' ' ||sys.htf.escape_sc(x.cust_postal_code)); (Line: 18)
Displays city, state, and postal code data on the same row separating each
other with a comma and a blank space.
sys.htp.p('</p>'); (Line: 19)
The paragraph end tag.
end loop; (Line: 20)
The loop terminates here after fetching details of an existing customer from
the database table.
sys.htp.p('</div>'); (Line: 32)
The div tag terminates here. The output of this section is illustrated in Figure
7-27: A - CustomerInfo. The ELSE block (line 22-30) is executed when a
new customer is added to the database from the order interface. In that
situation, all values on the current page are fetched from the previous wizard
step (Page 11).
Display Products (Lines: 36-42)
Here you create a section on your web page to display all products along with
their prices and include an option, which allows users to add products to their
cart.
sys.htp.p('<div class="Products" >'); (Line: 36)
Creates a division based on the Products class. HTML elements under this
division are styled using rules 4-9.
sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0"
border="0"> (Line: 37)
Here you are initiating to draw an HTML table. The <table> tag defines an
HTML table. An HTML table consists of the <table> element and one or
more <tr>, <th>, and <td> elements. The <tr> element defines a table row,
the <th> element defines a table header, and the <td> element defines a table
cell. The Width attribute specifies the width of the table. Setting 100% width
instructs the browser to consume the full screen width to display the table
element.
<thead> (Line: 37)
<tr><th class="left">Product</th><th>Price</th><th></th></tr>
</thead>The <thead> tag is used to group header content in an HTML table. The
<thead> element is used in conjunction with the <tbody> and <tfoot>
elements to specify each part of a table (header, body, footer). The <tr> tag
creates a row for column heading. The three <th> tags specify the headings.
The first two columns are labeled Product and Price, respectively. The third
column heading is left blank. A specific declaration (class=”left”) is included
that points toward the CSS rule (9) div.Products .left{text-align:left;} to align
the title of the first column (Product) to the left. The second column (Price) is
styled using a general rule (6).
<tbody>’); (Line: 37)
The <tbody> tag is used to group the body content in an HTML table. This
section spans up to line 41 and is marked as B in Figure 7-27.
for c1 in (select product_id, product_name, list_price, 'Add to Cart'
add_to_order
from demo_product_info
where product_avail = 'Y'
order by product_name) loop (Line: 38)
The FOR loop fetches Product ID, Product Name, and List Price columns
from the products table. To display a button (Add) in the table, we appended
a column aliased add_to_order and populated all rows with a constant value
'Add to Cart'. For further information on FOR LOOP, see the Cursor FOR
LOOP Statement section earlier in this section.
sys.htp.p('<tr><td class="left">'
||sys.htf.escape_sc(c1.product_name)||'</td>
<td>'||trim(to_char(c1.list_price,'999G999G990D00')
|| '</td>
<td><a href=" '||apex_util.prepare_url('f?
p=&APP_ID.:12:'||:app_session||'
:ADD:::P12_PRODUCT_ID:'||
c1.product_id)||' "
class="t-Button t-Button--simple
t-Button--hot">
<span>Add<i class="iR"></i>
</span></a></td>
</tr>'); (Line: 39)
This line displays product names with respective prices in two separate columns. The product column is styled using Rule 9, while the price column is styled using Rule 6. There is an Add button in the third column of the table, which is presented as a link using the HTML anchor tag \ and is styled #using a built-in class (t-Button). An anchor can be used in two ways:
It is usually referred to as a link or a hyperlink. The most important attribute of \ element is the href attribute, which specifies the URL of the page to which the link goes. When this button is clicked, the product it represents is moved to the Current Order section with the help of a process (Add Product to the Order Collection) defined in section 7.6.7.
c1 prefix in front of column names, points to the FOR LOOP cursor. The TRIM function in the expression, trim(to_char(c1.list_price,'999G999G990D00')), takes a character expression and returns that expression with leading and/or trailing pad characters removed. This expression initially formats the list price column to add thousand separators and decimal place. Next, it converts the numeric price value to text expression using the TO_CHAR function and finally applies the TRIM function. The TO_CHAR function converts a DATETIME, number, or NTEXT expression to a TEXT expression in a specified format. The table that follows lists the elements of a number format model with some examples.
Element Example Description
0 0999 Returns leading zeros.
9990 Returns trailing zeros.
9 9999 Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
D 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).G 9G999 Returns the group separator (which is usually comma) in the specified position. You can specify multiple group separators in a number format model. Use the following SQL statement to check the current value for decimal and group separator characters:
SELECT value FROM v$nls_parameters
WHERE parameter='NLS_NUMERIC_CHARACTERS';
The code,
<a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'||
c1.product_id)||'" class="t-Button t-Button--simple t-Button--hot"> <span>Add<iclass="iR"></i>
</span></a>,
creates a link with an ADD request. The value of REQUEST is the name of the button the user clicks. For example, suppose you have a button with a name of CHANGE and a label Apply Changes. When a user clicks the button, the value of REQUEST is CHANGE. In section 7.6.7, you will create the following process named Add Product to the order collection.
for x in (select p.rowid, p.* from demo_product_info p where product_id=:P12_PRODUCT_ID)
loop
select count(*)
into l_count
from wwv_flow_collections
where collection_name = 'ORDER'
and c001 = x.product_id;
if l_count >= 10 then
exit;
end if;
apex_collection.add_member(p_collection_name => 'ORDER',
p_c001 => x.product_id,
p_c002 => x.product_name,
p_c003 => x.list_price,
p_c004 => 1,
p_c010 => x.rowid);
end loop;
During the process creation, you’ll select Request=Value in Condition Type and will enter ADD for Value. The ADD request in the \ tag is referencing the same expression. When a user clicks the ADD button on the web page, the URL sends the ADD request to the process along with the selected product ID using a hidden item named P12_PRODUCT_ID to be created in section 7.6.4. In turn, the process adds the product to the Current Order section. The URL generated from this code looks something like this at runtime:
f?p=18132:12:13238397476902:ADD:::P12_PRODUCT_ID:10end loop; (Line: 40)
End of FOR loop.
sys.htp.p('</tbody></table>'); (Line: 41)
Table and body closing tags.
sys.htp.p('</div>'); (Line: 42)
The closing div tag.
Display Current Order (Lines: 46-79)
This section acts as a shopping cart. The products selected by a user are
placed in this section.
sys.htp.p('<div class="Products" >'); (Line: 46)
Defines the <div> tag and utilizes the Products class referenced in rules 4-9.
sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0"
border="0">
<thead>
<tr><th class="left">Current Order</th></tr>
</thead>
</table> (Line: 47)
Displays section heading as follows in the first row of a separate table.
Declare (Line: 48)
This is a nested or child block. To nest a block means to embed one or more
PL/SQL block inside another PL/SQL block to have better control over
program’s execution.
c number := 0; t number := 0; (Line: 49)
Declared two numeric counter variables and initialized them with zero. The
variable c is used to evaluate whether any product is selected in the current
order, while the variable t stores total value for the order.
Begin (Line: 50)
for c1 in (select c001 pid, c002 i, to_number(c003) p, count(c002) q,
sum(c003) ep, 'Remove' remove
from apex_collections
where collection_name = 'ORDER'group by c001, c002, c003
order by c001)
loop (Line: 52)
APEX Collection enables you to temporarily capture one or more non-scalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user’s specific session. You can think of a collection as a bucket in which you temporarily store and name rows of information. Every collection contains a named list of data elements (or members), which can have up to 50 character properties (varchar2 (4000)), 5 number, 5 date, 1 XML type, 1 BLOB, and 1 CLOB attribute. You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.
When you create a new collection, you must give it a name that cannot exceed 255 characters. Note that collection names are not case-sensitive and will be converted to uppercase. Once the collection is named, you can access the values (members of a collection) in the collection by running a SQL query against the database view APEX_COLLECTIONS.
The APEX_COLLECTIONS view has the following definition:
COLLECTION_NAME NOT NULL VARCHAR2(255)
SEQ_ID NOT NULL NUMBER
C001 VARCHAR2(4000)
C002 VARCHAR2(4000)
C003 VARCHAR2(4000)
C004 VARCHAR2(4000)
C005 VARCHAR2(4000)
...
C050 VARCHAR2(4000)
N001 NUMBER
N002 NUMBER
N003 NUMBER
N004 NUMBER
N005 NUMBER
CLOB001 CLOB
BLOB001 BLOB
XMLTYPE001 XMLTYPEMD5_ORIGINAL VARCHAR2(4000)
Use the APEX_COLLECTIONS view in an application just as you would use any other table or view in an application, for example:
SELECT c001, c002, c003, n001, clob001
FROM APEX_collections
WHERE collection_name = 'DEPARTMENTS'
The CREATE_OR_TRUNCATE_COLLECTION method creates a new collection if the named collection does not exist. If the named collection already exists, this method truncates it. Truncating a collection empties it, but leaves it in place.
In section 7.5.12, we created a process named Create or Truncate Order Collection under the page rendering section and used the following statement to create a collection named ORDER:
apex_collection.create_or_truncate_collection (p_collection_name => 'ORDER');
In the “For C1 in” loop, we’re selecting records from the same ORDER collection. Columns from apex_collections in the SELECT statement correspond to:
Column Corresponds To
C001 – pid Product ID (9)
C002 – i Product Name (Men Shoes)
C003 – p List Price (110)
C002 - q Quantity (1)
C003 - ep Extended Price (110) This value will increase with each Add button click to accumulate total cost of a product.
sys.htp.p('<div class="CartItem"> (Line: 53)
This line references another class (CartItem) to style the actual Current Order
section.
<a href="'||apex_util.prepare_url('f?
p=&APP_ID.:12:&SESSION.:REMOVE:::P12_PRODUCT_ID:'||sys.htf.
<img src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart"
title="Remove from cart" />
</a> (Line: 54)
Remove product from the Order Collection (as shown below) where the request expression is set to REMOVE.
for x in
(select seq_id, c001 from apex_collections
where collection_name = 'ORDER' and c001 = :P12_PRODUCT_ID)
loop
apex_collection.delete_member(p_collection_name => 'ORDER', p_seq => x.seq_id);
end loop;
In HTML, images are defined with the \ tag. The \
tag has no closing tag. To display an image on a page, you need to use the src attribute.
Src stands for "source". The value of the src attribute is the URL of the image you want to display.
Syntax for defining an image:
The URL points to the location where the image is stored. The value of IMAGE_PREFIX determines the virtual path the web server uses to point to the images directory distributed with Oracle APEX. We used “delete.gif” that is displayed in front of the product name. The required alt attribute specifies an alternate text for an image, if the image cannot be displayed. When a user clicks the remove link [X] in the Current Order section, the URL sends a REMOVE request to the process along with the product ID. The DELETE_MEMBER procedure deletes a specified member from a given named collection using the p_seq => x.seq_id parameter, which is the sequence ID of the collection member to be deleted.
'||sys.htf.escape_sc(c1.i)||' (Line: 55)
Displays name of the selected product in the Current Order section.
<span>'||trim(to_char(c1.p,'$999G999G999D00'))||'</span> (Line: 56)
<span>Quantity: '||c1.q||'</span> (Line: 57)
<span class="subtotal">Subtotal:
'||trim(to_char(c1.ep,'$999G999G999D00'))||'</span> (Line: 58)
The three lines display price, quantity, and sub-total of the selected product in
the Current Order section, as shown below:</div>'); (Line: 59)
The ending div tag.
c := c + 1; (Line: 60)
This counter increments the value of c with 1 at the end of each loop. The
variable c is used to calculate number of items selected in the current order.
t := t + c1.ep; (Line: 61)
Similar to the variable c, t is also incremented to sum up extended price
(c1.ep) to calculate total order value.
if c > 0 then
sys.htp.p('<div class="CartTotal">
<p>Items: <span>'||c||'</span></p>
<p class="CartTotal">Total:
<span>'||trim(to_char(t,'$999G999G999D00'))||'</span></p>
</div>');
else
sys.htp.p('<div class="alertMessage
info" style="margin-top: 8px;">');
sys.htp.p('<img src="#IMAGE_PREFIX#f_spacer.gif">');
sys.htp.p('<div class="innerMessage">');
sys.htp.p('<h3>Note</h3>');
sys.htp.p('<p>You have no items in your current order.</p>');
sys.htp.p('</div>');
sys.htp.p('</div>');
end if;
(Line: 64-77) The condition (IF c > 0) evaluates whether a product is selected in the current order. A value other than zero in this variable indicates addition of product(s). If the current order has some items added, the label Total: along with the value is displayed, which is stored in the variable t. If no items are selected, the message defined in the else block is shown using a couple of built-in classes.
Create a hidden item in the Select Items region. When you click the Add button on Page 12 to add a product to an order, the ID of that product is stored in this hidden item using a URL specified in the PL/SQL code on line 39.
Property Value
Right-click the Wizard Buttons node and select Create Region. Enter Buttons for the Title of this region and set its Template to Buttons Container. The region will hold three buttons: Cancel, Previous, and Next. These buttons are created in the next section.
All the three buttons created in this section have one thing in common, the Action property, which is set to Submit Page. When you click any of these three buttons, the page is submitted and a corresponding branch (to be created in section 7.6.9) is fired to take you to the specified location. For example, if you click the Cancel button, the corresponding branch takes you back to the main Orders page (Page 4). Right-click the new Buttons region and select Create Button. Set the following properties for the new button:
Property Value
Create another button under the Cancel button and set the following properties:
Property Value
Create the final button under the Previous button and set the following properties:
Property Value
The two processes created in this section handle the routine to either add a product to the Current Order section or remove one from it. The add_member function references the collection (ORDER created in section 7.5.12) to populate the collection with a new product. In Table 7-1, the link defined on line 39 in the PL/SQL code forwards an ADD request, which is entertained here after evaluating the request in step 4 below.
A. Add Product to the Order Collection
declare
l_count number := 0;
begin
for x in (select p.rowid, p.* from demo_product_info p where product_id = :P12_PRODUCT_ID)
loop
select count(*)
into l_count
from wwv_flow_collections
where collection_name = 'ORDER'
and c001 = x.product_id;
if l_count >= 10 then
exit;
end if;
apex_collection.add_member(p_collection_name => 'ORDER',
p_c001 => x.product_id,
p_c002 => x.product_name,
p_c003 => x.list_price,
p_c004 => 1,
p_c010 => x.rowid);
end loop;
end;
B. Remove Product from the Order Collection
The delete_member function is just opposite to the add_member function. It is called by a link (Table 7-1 line 54), which carries a REMOVE request. Therequest is evaluated by a condition set in Step 3 below. If the request matches, the selected product is deleted from the ORDER collection.
for x in
(select seq_id, c001 from apex_collections
where collection_name = 'ORDER' and c001 = :P12_PRODUCT_ID)
loop
apex_collection.delete_member(p_collection_name => 'ORDER', p_seq => x.seq_id);
end loop;
After selecting products for an order, you click the Next button. The process defined in this section is associated with this button. The PL/SQL code specified in this process adds new customer and order information in relevant database tables using a few SQL INSERT statements. After committing the DML statement, the process truncates the ORDER collection.
Enter Place Order for the name of this new process and set its Type to PL/SQL Code. Enter the following code in the PL/SQL Code box. Also, select NEXT for When Button Pressed property. The code is stored under BookCode\Chapter7\7.6.8.txt file.
declare
l_order_id number;
l_customer_id varchar2(30) := :P11_CUSTOMER_ID;
begin
-- Create New Customer
if :P11_CUSTOMER_OPTIONS = 'NEW' then
insert into DEMO_CUSTOMERS (
CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1,
CUST_STREET_ADDRESS2, CUST_CITY, CUST_STATE, CUST_POSTAL_CODE,
CUST_EMAIL, PHONE_NUMBER1, PHONE_NUMBER2, URL, CREDIT_LIMIT, TAGS)
values (
:P11_CUST_FIRST_NAME, :P11_CUST_LAST_NAME, :P11_CUST_STREET_ADDRESS1,
:P11_CUST_STREET_ADDRESS2, :P11_CUST_CITY, :P11_CUST_STATE,
:P11_CUST_POSTAL_CODE, :P11_CUST_EMAIL, :P11_PHONE_NUMBER1,
:P11_PHONE_NUMBER2, :P11_URL, :P11_CREDIT_LIMIT, :P11_TAGS)
returning customer_id into l_customer_id;
:P11_CUSTOMER_ID := l_customer_id;
end if;
-- Insert a row into the Order Header table
insert into demo_orders(customer_id, order_total, order_timestamp, user_name)
values (l_customer_id, null, systimestamp, upper(:APP_USER))
returning order_id into l_order_id;
commit;
-- Loop through the ORDER collection and insert rows into the Order Line Item table
for x in (select c001, c003, sum(c004) c004 from apex_collections
where collection_name = 'ORDER' group by c001, c003) loop
insert into demo_order_items(order_item_id, order_id, product_id, unit_price, quantity)
values (null, l_order_id, to_number(x.c001), to_number(x.c003),to_number(x.c004));
end loop;
commit;
-- Set the item P14_ORDER_ID to the order which was just placed
:P14_ORDER_ID := l_order_id;
-- Truncate the collection after the order has been placed
apex_collection.truncate_collection(p_collection_name => 'ORDER');
end;
Create the following three branches under the After Processing node on the Processing tab. The buttons referenced in these branches were created in section 7.6.6.
Property Value
Property Value
Property Value
Test Your Work
Navigate to the Orders page using the main menu route and click the Enter New Order button. Select a customer using the Existing Customer option and click Next. Click the Add button next to Air Jordan 6 shoes to add this product to the Current Order pane. Click the Add button again for this product and see increase in Quantity and Total. Add some more products and observe the change in the Current Order section. Click the cross sign to remove a product from the Current Order section. Click Cancel to return to Page 4 without saving the order.
After adding products to the Order form, you click the Place Order button. The next page, Order Summary, comes up to show details of the placed order. In this section, you will create this page. It is the last step in the order creation wizard.
Right-click the Wizard Progress Bar node and select Create Region. Set following properties for the new region.
Property Value
Right-click the Wizard Body node and select Create Region. Set the following properties for this region. Just like section 7.6.3, you define the region as PL/SQL Dynamic Content, which is based on PL/SQL that enables you to render any HTML or text.
Property Value
PL/SQL Code
begin
for x in (
select c.cust_first_name, c.cust_last_name, cust_street_address1,
cust_street_address2, cust_city, cust_state, cust_postal_code from demo_customers c, demo_orders o
where c.customer_id = o.customer_id and o.order_id = :P14_ORDER_ID
)
loop
htp.p('<span style="font-size:16px;font-weight:bold;">ORDER #' ||sys.htf.escape_sc(:P14_ORDER_ID) || '</span><br />');
htp.p(sys.htf.escape_sc(x.cust_first_name) || ' ' ||sys.htf.escape_sc(x.cust_last_name) || '<br />');
htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />');
if x.cust_street_address2 is not null then
htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
end if;
htp.p(sys.htf.escape_sc(x.cust_city) || ', ' || sys.htf.escape_sc(x.cust_state) || ' ' ||sys.htf.escape_sc(x.cust_postal_code) || '<br /><br />');
end loop;
end;
Add another region under the Wizard Body node and set the following properties for this region. After creating this region expand its Columns node and set suitable heading for each column. This region will carry line item information.
Property Value
select p.product_name, oi.unit_price, oi.quantity, (oi.unit_price * oi.quantity) extended_price
from demo_order_items oi, demo_product_info p
where oi.product_id = p.product_id and oi.order_id = :P14_ORDER_ID
Right-click the Order Lines region and select Create Page Item. Set the following properties for the new item. The value for this item was set in the PL/SQL code defined in section 7.6.8 and was utilized in the codes defined in section 7.7.2 and in section 7.7.3 to fetch order information. Property Value Name P14_ORDER_ID Type Hidden 7.7.5 Create Region – Buttons Right-click the Wizard Buttons node and select Create Region. Enter Buttons for its Name and set its Template to Buttons Container. The region will hold the following button. 7.7.6 Create Button Right-click the new Buttons region node and select Create Button. Set the following properties for the new button: Property Value Button Name BACK Label Back To Orders Button Position Next Hot On Action Redirect to Page in this Application Target Type = Page in this application Page = 4 7.7.7 Create Trigger As the final step of this module, add the following trigger to your schema. The trigger will fire to write order total to the DEMO_ORDERS table when any order item is changed.1. From the main Oracle APEX menu, select SQL Workshop | SQL Commands. Figure 7-31
You can use the APEX_MAIL package to send an email from an Oracle Application Express application. This package is built on top of the Oracle supplied UTL_SMTP package. Because of this dependence, the UTL_SMTP package must be installed and functioning to use APEX_MAIL. Since we are using the online APEX version, this package is already configured and we can give it a test run. In this section we are going to send an order confirmation email to customers, whose emails exists in the DEMO_CUSTOMERS table. The email will contain a link to access the placed order.
Open Page 12 (Order Items) and create a new process under the Place Order process. Set the attributes for this new process as shown on the next page. The PL/SQL code for this process is provided in BookCode\Chapter7\7.8.txt file. The PL/SQL code starts with the declaration of some variables. You can use VARCHAR type for Vbody and Vbody_html variables. Passing values to these variables yield a multi-part message that includes both plain text and HTML content. The settings and capabilities of the recipient's email client determine what displays. Although most modern email clients can read an HTML formatted email, remember that some users disable this functionality to address security issues. On line 7 we fetch email address of the ordering customer, and on line 8 we store customer name in a variable. The CSS code defined on line 10 formats different parts of the email. Line 11 creates the greeting line, while line 12 forms a paragraph containing a link to the customer order. The order is displayed on Page 30 of the application. Remember, you must include a carriage return or line feed (CRLF) every 1000 characters because the SMTP/MIME specification dictates that no single line shall exceed 1000 characters. We used utl_tcp.crlf for the same purpose. Finally, the APEX_MAIL.SEND procedure sends an outbound email message. The following table describes the parameters used in this SEND procedure. Parameter Descriptionp_to (required) Valid email address to which the email is sent. For multiple email addresses, use a comma-separated list. p_from (required) Email address from which the email is sent. This email address must be a valid address. Otherwise, the message is not sent. p_body (required) Body of the email in plain text. If a value is passed to p_body_html, then this is the only text the recipient sees. If a value is not passed to p_body_html, then this text only displays for email clients that do not support HTML or have HTML disabled. A carriage return or line feed (CRLF) must be included every 1000 characters. p_body_html Body of the email in HTML format. p_subj Subject of the email. Property Value Name Send Confirmation Email Type PL/SQL Code PL/SQL Code
DECLARE
Vbody CLOB;
Vbody_html CLOB;
Vcust_email varchar2(100);
Vcust_name varchar2(100);
BEGIN
select cust_email into Vcust_email from DEMO_CUSTOMERS
where customer_id = :P11_CUSTOMER_ID;
select CUST_FIRST_NAME into Vcust_name from DEMO_CUSTOMERS
where customer_id = :P11_CUSTOMER_ID;
Vbody := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;
Vbody_html := '<html>
<head>
<style type="text/css">
body{font-family: Arial, Helvetica, sans-serif; font-size:10pt;margin:30px;
background-color:#ffffff;}
span.sig{font-size: 20px; font-weight:bold; color:#811919;}
</style>
</head>
<body>'||utl_tcp.crlf;
Vbody_html := Vbody_html || 'Hi '|| Vcust_name ||','||utl_tcp.crlf||utl_tcp.crlf;
Vbody_html := Vbody_html ||'<p> Your order has been confirmed which you can access by clicking <a href="'||APEX_UTIL.HOST_URL('SCRIPT')||'f?p='||:APP_ID|| ':30'||':0::::P30_ORDER_ID:'||:P14_ORDER_ID ||'"> here. </a></p>' ||utl_tcp.crlf;
Vbody_html := Vbody_html ||'<p> Regards,</p>'||utl_tcp.crlf;
Vbody_html := Vbody_html ||' <span class="sig">Sales Team</span><br />'||utl_tcp.crlf;
apex_mail.send(
p_to => Vcust_email,
p_from => 'sales@abc.com',
p_body => Vbody,
p_body_html => Vbody_html,
p_subj => 'Order Confirmation');
END;
Success Message Order confirmation email sent to customer Error Message There was some problem in sending email When Button Pressed NEXT When a customer clicks the link in the email, he is routed to Page 30 (after providing his credentials on the sign in page) to see his order. This page will be created by making a copy of Page 29. There are a couple of default security setting on this page that we need to change as well to allow access to the order.
I know as a beginner you might be confused with the stuff described in section 7.5 onward. I added this stuff purposely to present something that would be helpful to you in your future endeavors. However, in this section I’ll demonstrate a simpler approach to add, modify, and delete orders using just one interface.
If you are an absolute beginner, I would recommend you to skip this section for the time being. Once you get a firm grip on APEX, revert to this section to learn some beyond stuff. In this section, you learn how to loop through each record in an interactive grid to perform some kind of validation. For example, here you will prevent addition of duplicate products in a single order. Of course, you can add a composite unique key constraint on the corresponding table to prevent duplication. But, there are some scenarios where this solution doesn’t fit. For example, if you provide some free samples of a product in an invoice, you need to create two line item entries in your order screen for the same product – one with a price tag and another free. Execute the following steps to prevent product duplication in an order.1. Open Page 429 in page designer. Click the Form on DEMO_ORDERS static content region (under Wizard Body) and set it Title to Order Master.
Interactive Grid Downloads includes native PDF Printing which allows youto print PDF files directly from Interactive Grids. This feature produces a PDF file which retains Grid formatting such as highlighting, column grouping, and column breaks. Let’s go through a simple demonstration to explore this feature.
page 304/419
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz....Search.....Style Theme r......Style buttons.....Calendar..... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search, style, calendar.....Style Theme r......Style buttons.....Calendar..... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz....Search, style, calendar.....Style Theme r......Style buttons.....Calendar..... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz....Search, style, calendar.....Style Theme r......Style buttons.....Calendar..... Deploy
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal......Deploy
Consists of two steps : Export desired components to a script file and import script file into your production environment. Decide where and how app will run.
For simplicity, we will deploy app in the same workspace. Same technique is applicable to new workspace or prod env.
Up to 40x faster than Exp/Imp Data.
Very high-speed movement of data and metadata from one DB to another for a complete DB or subsets of a DB. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.
See oracle_DB18c_devsuite10g_F6i_to_apex.txt.
Import exported app f145615.sql into existing workspace you are connected to with a different WS ID.
The Developers Toolbar is used to access the application source. In this exercise, we are going to prevent users from modifying the application by suppressing the toolbar.
Thats it. You have successfully deployed your application in the same workspace. You can apply the same procedure to deploy the application to another environment.
Conclusion
Oracle APEX has come a long way from its simple beginning. Sky is the limit, you are limited by your imagination.
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL .....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
(+ f?p=4550:1:117485610537637:::::)
Page "App Builder" : Create and manage my apps and their pages :
Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL .....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
Page creation wizards - option to create a blank page and add components manually, Page Designer interface to add more controls after page creation.
When you run app requested with a URL, APEX engine relies on two processes:
You can create following types of pages for your app :
Region template controls region look, size, determines whether there is border or background color, and what type of fonts to display. Also determines standard placement for any buttons placed in region positions.
You can use regions to group page elements (such as items or buttons).
APEX supports many different region types eg Static Content, Classic Report, Interactive Report, Interactive Grid, Chart, and more.
Text Field, Textarea, Password, Select List, Checkbox... Item properties : where a label displays, how large an item is, and if item displays next to or below previous item. Page item name is eg P7_CUSTOMER_ID = custID item on page 7 (preceded P followed by number pageID).
Redirect :
In case of a redirect : nothing is uploaded to the server. If you change some items values on a page and press a button created with a redirect action, those changes will be lost.
Button options are: Icon, Text, and Text with Icon. You can place buttons either in predefined region positions or with other items in a form - see Figures 1-1, 1-3, and 1-7 in chapter 1.
Buttons control flow of DB apps, are created by right-clicking region -> select "Create Button" from context menu. By placing buttons (such as Create, Delete, Cancel, Next, Previous , and more) on your web page, you can post or process by customer provided information or you can direct user to another app pg or to another URL.
Submit a page eg to save user input in a DB tbl. When a button on a page is clicked, pg is submitted with REQUEST value that carries btnname. You can reference value of REQUEST from within PL/SQL using bind variable " :REQUEST.". By using this bind variable, you can conditionally process, validate, or branch based on which button the user clicks. You can create processes that execute when the clicks a button. And you can use a more complex condition as demonstrated in the following examples:
If :REQUEST in ('EDIT','DELETE') then ...
If :REQUEST != 'DELETE' then ...
These examples assume existence of buttons named EDIT and DELETE. You can also use this syntax in PL/SQL Expression conditions. If you name a button LOGIN, then request looking for Login fails - btnname is case sensitive.
Redirect to another URL.
Do nothing eg if buttons behavior is defined in a Dynamic Action.
Download Printable Report Query. This creates a Submit Page button and also a corresponding branch. When the button is clicked, the output is downloaded from Report Query.
Top.....Apexws_cloud.....App. builder.....Page.....Environment.....URL Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
You are not required to install any client software to develop, deploy, or run Oracle APEX applications.
Primary APEX tools :
App Builder - to create dynamic database driven web appls. Here you create and modify your applications and pages. It comprises following :
SQL Workshop - to browse your DB objects, to run ad-hoc SQL queries, allow App Developers to maintain DB objects eg tables, packages, functions, views... It is beneficial in hosted environments like apex.oracle.com where direct access to underlying schemas is not provided. It has five basic components:
Team Development - allows development teams to better manage their Oracle APEX projects by defining milestones, features, to-dos, and bugs. Features, to-dos, and bugs can be associated with specific apps and pages as necessary. Developers can readily configure feedback to allow their end-users to provide comments on app. The feedback also captures relevant session state details and can be readily converted to a feature, to-do or bug.
App Gallery - Productivity apps are a suite of business productivity applications, easily installed with only a few clicks. These solutions can be readily used as production applications to improve business processes and are fully supported by Oracle.
Oracle APEX environment has two broad categories:
page top, right
It comprises various tools to find a page, lock/unlock a page, undo/redo actions, Create menu, Utilities menu, shared components, save and run page (see tooltips).
Utilities menu has an option that lets you delete the page being displayed in your browser.
Lock icon (padlock - lokot) indicates whether a page is currently locked. Is also displayed in Page Designer as well as on App home page - Figure 2-5. This feature enables you to prevent conflicts during app development. By locking a page you prevent other developers from editing it.
page top, left
Tree nodes : regions, items, buttons, app logic (eg computations, processes, validations), dynamic actions, branches, shared components.
4 icons :
Rendering icon (B) - contains as nodes in a tree : regions, page items, page buttons, and app logic. Components defined in this section appear when a page is rendered. These components can be viewed as a tree, organized either by processing order (the default) or by component type. The first two buttons to the right of the Rendering label can be used to toggle between rendering trees. Rendering is divided in three stages :
Dynamic Actions icon (C) - Displays dynamic actions defined on this page. By creating a dynamic action, you can define complex client-side behavior declaratively without the need for JavaScript. Refer to Dynamic Actions entry in the book’s index to see its utilization in the project application.
Processing icon (D) - Use this icon to specify app logic eg :
The left iconic button to the right of the Processing label displays the components under this tab according to the servers processing order. The middle button organizes these components according to their type, while the third one provides a menu to create a new component in the selected folder.
Upper section contains three tabs: Layout, Page Search, Help. Lower pane is called Gallery and it is associated with Layout tab.
right pane
As you select component Property Editor updates to reflect current selection. Properties are organized into functional groups (Identification, Source, Layout, Appearance, and more) that describe their purpose. When you modify or add a value to a property, a colored vertical bar appears as a visual modification indicator before the property name.
Top.....Apexws_cloud.....App. builder.....Page Environm.....URL .....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy
is a legacy syntax that creates a unique URL structure that identifies address of APEX, app ID, page number, and session ID.
https://apex.oracle.com/pls/apex/f?p=4500:1000:706440515653484
In code: https://apex.oracle.com/pls/apex/f?p=&APP_ID.:1000:&APP_SESSION.:::::
https:// URL of the server / pls is indicator to use the mod_plsql cartridge
/ apex is (default) DAD (database access descriptor) name. DAD describes how HTTP server connects to DB server to fulfill HTTP request.
/ f?p= is a prefix used by Oracle APEX to route the request to the correct engine process
4500 is appID being called : 1000 is page within app to be displayed
: 440323506685863558 is session number to keep track of user’s session state
Developer Toolbar among other tools for developers contains a Session option, which shows you the current session state. Clicking it opens a window (called Session Page, shown in Figure 2-6) carrying all items and their current session values. It is useful for developers to debug pages. When you change some item value on a page and submit it, the value in the session window for that item changes to reflect the current state. Use Page, Find, and View parameters to view session state for the page. The drop-down View menu comprises Page Items, Application Items, Session State, Collections, and All options. Select an option from this list and click the Set button to refresh the Session State report.
creates a URL structure that identifies the address of APEX, app, page, and uses a standard URL hierarchy and passes parameters in a similar fashion. You can change existing apps to use Friendly URLs by editing Friendly URLs attribute in app definition :
Shared Components | Application Definition Attributes | Properties.
https://apex.oracle.com/pls/apex/POSSYS/r/4500/home?session=702685162362252
Friendly URL Syntax creates a URL with the following directory hierarchy and syntax:
https:// apex.oracle.com / pls /apex / myws/ r / 4500 / home?session=16167973992554
Where:
myws is the path_prefix which is URI path prefix used to access RESTful Services. When you create a workspace, this value defaults to workspace name. You can customize the URI path prefix by editing the Path Prefix attribute in :
Administration | Manage Service | Set Workspace Preferences | SQL Workshop
r is the router shortcut. This value is a constant and should never be changed
4500 is the application id
home is the alias of the page being displayed. If no alias is defined, the page number is displayed instead.
?session=16167973992554 identifies the session ID.
&varname. means prefix ampersand to item name and append period at its end to reference item.
:item_name means precede item name with colon.
To make your app more portable, APEX provides many features. On top of the list are the Substitution Strings that help you avoid hard-coded references in your app. Every app has its own unique ID used to identify app and it's metadata within APEX repository. When you move app from your dev. envir. to prod. envir, and if you've hard-coded app references, eg you hard-coded the application ID (101) like this: f?p=101:1:&APP_SESSION and prod. environ. already has an app ID=101, you will be forced to use a different ID, which will point all your links within app to the wrong ID.
To avoid such situations, you should always use substitution strings. You can avoid hard-coding appID by using APP_ID substitution string, which identifies ID of the currently executing app. With the substitution string, the URL looks like: f?p=&APP_ID.:1:&APP_SESSION.. Supported syntax for referencing APP_ID :
Reference Type | Syntax | Help |
---|---|---|
Bind variable | :APP_ID | |
Substitution string | &APP_ID. |
There are two ways to get a page to access value of a session state variable:
Use substitution string from within an HTML expression so :
Include a substitution string within a template to reference component values
Special substitution strings available within a template are denoted by the number symbol (#). For example: #PRODUCT_ID# - see Chapter 4 Section 4.3.2, 4.3.5, and 4.3.6.
Reference page or app items using &ITEM. syntax - all capital letters !
For example, you would refer to a page item named P7_CUSTOMER_ID in a region, a region title, an item label, or in any of numerous other contexts in which static text is used, like this: "&CUSTOMER_ID." Notice required trailing period. When the page is rendered, APEX engine replaces the value of the substitution string &CUSTOMER_ID. with the value of item P7_CUSTOMER_ID
Use one of many APEX built-in substitution strings to achieve specific types of functionality. Eg APP_ID, APP_IMAGES, APP_PAGE_ID, APP_SESSION, APP_USER, LOGIN_URL, LOGOUT_URL.
"Page Shared Components" tab top left icon (pyramid of 3) in Page Designer (App Builder -> App) displays a list of APPLEVEL common elements applied to that particular page. Shared components are only displayed in this section after you add them to a page.
Link to Edit App Definition page where you can modify your app attributes, including its name, version, and availability options. You can turn on new Friendly URLs option.
Run PL/SQL logic at a specific point from multiple pages (reports) of an app. You can apply conditions to control when process executes. Currently there are 8 different types of process that you can include in your app.
One significant is On Demand App Process. It is a special type of app process which executes when called from :
or from an Ajax call from the browser.
On Demand processes are useful eg assessing customer's outstanding balance and using that figure (value) on reports eg : customer invoice, age analysis report, customer balances report and so on.
(access=prijava, functionalities=prava)
These schemes enable us to declaratively define security for our apps quickly and easily.
Authentication is the process of establishing identity of every your app user . Most common auth. process : usrname and psw. These credentials are then evaluated either through
Authentication could involve use of digital certificates or a secure key, too. If credentials pass, user is allowed to access app. Once a user has been identified, APEX engine keeps track of each user by setting the value of built-in substitution string APP_USER.
As you create your app, you must determine whether to include auth. You can:
Choose to not require authentication. Oracle APEX does not check any user credentials. All pages of your app are accessible to all users eg public informational app website.
Select a built-in authentication scheme. Create an auth. method based on available preconfigured authentication schemes. Each scheme follows a standard behavior for authentication and session management.
APEX Accounts. These are user accounts created within and managed in Oracle APEX user repository. When you use this method, your app is authenticated against these accounts.
It utilizes DB schema accounts. This authscheme requires that DB user (schema) exist in local DB. When using this method, username and password of the DB account is used to authenticate user. Choose DB Account Credentials if having one DB account for each named user of your app is feasible (viable, practicable, executable, workable) and account maintenance using DB tools meets your needs.
HTTP Header Variable. It supports the use of header variables to identify a user and to create an Application Express user session. Use this authentication scheme if your company employs a centralized web authentication solution like Oracle Access Manager, which provides single sign-on across apps and technologies.
LDAP Directory Verification. You can configure any authentication scheme that uses a login page to use Lightweight Directory Access Protocol (LDAP) to verify the username and password submitted on the login page. App Builder includes wizards and pages that explain how to configure this option. These wizards assume that an LDAP directory accessible to your application for this purpose already exists and that it can respond to a SIMPLE_BIND_S call for credentials verification.
Application Server Single Sign-On Server. This one delegates authentication to the Oracle AS Single Sign-On (SSO) Server. To use this authentication scheme, your site must have been registered as a partner application with the SSO server.
Create custom authentication scheme.
Using this method you can have complete control over the authentication interface. To implement this approach you must provide a PL/SQL function the Application Express engine executes before processing each page request. The Boolean return value of this function determines whether the Application Express engine processes the page normally or displays a failure page. This is the best approach for apps when any of the following is true:
To control users access to specific components of your app. An auth. scheme can be specified for an entire app, page, or specific page components such as a region, button, or item. For instance, you can apply an auth. scheme to determine which menu options a user can see, or whether he is allowed to create a new order (using Create button).
Once created, LOVs are stored in LOVs repository and are utilized by page items Px_...
After creating LOV you associate it to page items such as select list, radio group, checkbox.... LOVs at app-level can be added to any page within app, and is easy to locate and update (since all LOV definitions are stored in one location).
Allows developers to create their own plug-ins to add additional functionality in a supported and declarative way. Usually, a tool like Ajax is used to add custom functionality. The con of this approach is to place the code in different locations such as within the database, in external JavaScript files, and so on. On the other hand, turning that code into a plug-in is more convenient to use and manage because the code resides in one object. With the help of open source jQuery components you can create plug-ins without generating huge amount of code manually.
Plug-ins are shared component objects that allow you to extend the functionality of item types, region types, dynamic actions, and process types. The plug-in architecture includes a declarative development environment that lets you create custom versions of these built-in objects. For example, you can create your own star rating item that allows your user to provide feedback using a one-to-five star graphic. This new item type can then be used across all your apps. The main part of a plug-in consists of PL/SQL code and can be supplemented with JavaScript and CSS code. A plug-in consists of one or more PL/SQL functions. These functions can either reside in the database (in a package or a set of functions) or be included within the plug-in.
NOTE: Plug-in OTN page (https://www.oracle.com/tools/technologies/apex-plug-ins.html) has several different plug-ins developed by APEX community.
You can use a shortcut to define a page control such as a button, HTML text, or a PL/SQL procedure. Once defined, you can invoke a shortcut using specific syntax unique to the location in which the shortcut is used. Shortcuts can be referenced many times, thus reducing code redundancy. When you create a shortcut, you must specify the type of shortcut you want to create. Oracle APEX supports the following shortcut types:
For each list entry, you specify display text, a target URL, and other attributes to control when and how the list entry displays. Once created, you can add a list to any number of pages within an app by creating a region and specifying the region type as List. You control the display of the list and the appearance of all list entries by linking list to a template. Lists are of two types:
Static Lists - When you create a static list you define a list entry label and a target (either a page or a URL). You can add list entries when you create the list (from scratch), by copying existing entries or by adding the list entries. You can control when list entries display by defining display conditions.
Dynamic Lists - are based on a SQL query or a PL/SQL function executed at runtime.
You might have seen a horizontal bar at the top of a website. The options provided on this bar help you navigate to different pages within that site. app Express provides you with a similar component called Navigation Menu. It is an effective way to navigate users between pages of an app. A navigation menu is basically a list with hierarchical entries.
When you create an app, the Create app Wizard automatically creates a navigation menu for you and populates it with one or more list entries. Types of navigation menus include Side Me
Navig. Top Menu, or Mega Menu
By default, the navigation menu is displayed as a left sidebar. Users can expand or collapse the Side Navigation Menu by clicking on the menu icon from the header. This navigation menu renders the navigation items using a tree component that enables users to expand or collapse sub items. A Top Navigation Menu displays at the top of the app. You can change how and where a navigation menu displays by editing the app User Interface Details. The Top Navigation Mega Menu template renders your app navigation in a pop-up panel that can be opened or closed from the header menu button. Users can expand or collapse a Mega Menu by clicking on the menu icon from the header. Mega menus are especially useful when you want to display all navigation items at once to your user.
A breadcrumb (A) is a hierarchical list of links rendered using a template. For example, you can display breadcrumbs as a list of links or as a breadcrumb path. A breadcrumb trail indicates where you are within the app from a hierarchical perspective. In addition, you can click a specific breadcrumb link to instantly view the page. For example, in the screen shot below you can access the app home page by clicking its breadcrumb entry (B). You use breadcrumbs as a second level of navigation at the top of each page, complementing other navigation options such as Navigation Menu and Navigation Bar.
Just like menus, lists, and breadcrumb, a navigation bar is also created to link users to various pages within an app. Typically, a navigation bar carries links such as user id, logout, feedback, help, and so on. It appears on top-right of every app page. While creating a navigation bar, you can specify an image name, label, display sequence, and target location.
The app user interface determines default characteristics of app and optimizes the display for the target environment. This is place where you define your app logo.
Instead of telling App Builder how to design and style your pages using HTML, CSS, and JavaScript code that you may not be familiar with, you only apply theme and templates you want to use and the Oracle APEX engine does the rest of the job for you.
A theme is a named collection of templates that defines the look and feel of app user interface. Each theme contains templates for every type of app component and page control, including individual pages, regions,reports, lists, labels, menus, buttons, and list of values.
APEX engine constructs the appearance of each page in a app using Templates . Templates define how pages, page controls, and page components display. Templates control the look and feel of the pages in your app using snippets of HTML, CSS, JavaScript and image icons. As you create your app, you specify templates for pages, regions, reports, lists, labels, menus, buttons, and pop-up lists of values.
Groups of templates are organized into named collections called themes.
App Builder also allows you to access themes and template mechanism so you can create new ones according to your own requirements or amend (improve) existing ones. Oracle APEX ships with an extensive theme repository. Administrators can add themes to the theme repository, as follows:
Use these two links to upload, edit, and delete static files including images, custom style sheets, JavaScript files.
Static App file can be referenced from a specific app only, whereas workspace file can be accessed by any app in workspace. Here we use Static app Files option to upload your app logo.
A report query is a printable document, which can be integrated with an app using buttons, list items, branches, or any other navigational components that allow for using URLs as targets. A report query is based on a standard SQL query. It can be downloaded as a PDF document, a Word document (RTF based), an Excel Spreadsheet (HTML based), or as an HTML file. The layout of a report query is customizable using RTF templates.
Use Report Layouts in conjunction with a report region or report query to render data in a printer-friendly format, such as PDF, Word, or Excel. A report layout can be designed using the Template Builder Word plug-in and uploaded as a file of type RTF or XSL-FO. Report regions use a generic XSL-FO layout, which is customizable.
If you want to develop apps that can run concurrently in different languages, then app Express is the right platform for this. In the Globalization interface, you can specify options such as the app Primary Language, Date/Time format, and some other options related to app globalization.
A single Oracle DB and APEX instance can support an app in multiple languages. Translating an app involves multiple steps. To translate an app developed in App Builder, you must :
Is finished this tutorial.
Is an application that highlights common design concepts. It includes dedicated pages for customers, products, and orders as well as demonstrates the use of reports, charts, calendar, map, and tree.
Interactive Grid is a page component, which is used to display data in row/column matrix. In appearance, it looks similar to an Interactive Report (used in the next chapter) and delivers all features of Interactive Report, but it also allows you to manipulate data by clicking on a cell and editing its value (like Clipper Dbedit !), which is not available in Interactive Reports. In many ways this grid looks and acts like an Interactive Report. Here are some new features and differences:
https://apex.oracle.com/pls/apex/f?p=4000:1:708714070734932::NO:RP:FB_FLOW_ID,F4000_P1_FLOW,P0_FLOWPAGE,RECENT_PAGES:19693,19693,19693 https://apex.oracle.com/pls/apex/possys/r/19693/home (+ ?session=104411212612767) Install sample application to get required tables.
App Gallery menu -> Sample Apps.Figure 5-10
Click icon Sample Interactive Grids app - functionality of new APEX Interactive Grid. Declarative features :
On App Details page, click Install App button Minimum Version APEX 20.1, Oracle DB 11.2.0.4 (newer than XE 11.2.0.2.0 !!) Released 10-MAR-2020
On Install App wizard screen, accept the default Authentication scheme (APEX Accounts) and click Next.
On the next wizard screen, click Install App button. After a while, you will see the message Application installed.
SQL Workshop main menu -> Click Object Browser and see two required tables (EBA_DEMO_IG_EMP and EBA_DEMO_IG_PEOPLE) in the left pane under Tables category.
Groups are used to associate columns together in the grid and Single Row View. Groups are added by expanding the Attributes node within Rendering tree, and right-clicking on Column Groups. Let's try this feature by executing the following steps:
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, onleave, notes, flex4 as tags
FROM EBA_DEMO_IG_EMP
Column Group Property
ENAME Identity
SAL Compensation
ONLEAVE Notes
Save your work and run the page. Column group headings can be used to reorder columns just like column headings. Play around with column reordering (using drag and drop - see E in Figure 5-9) to see how group headings are split and joined.
Interactive Grid allows you to manipulate data by clicking on a cell. When you add an Interactive Grid to a page, you specify (on Report Source wizard screen) whether it is editable–see step 4 in the previous section. If you initially turn this attribute off, you can always reverse it to make the Interactive Grid editable. Here are some points to know about editing:
Execute following steps to enable editing in the Interactive Grid you added to Page 100 in the previous section.
By default, the type of a column in an Interactive Grid is inherited from the base table. For example, the names of employees are displayed in a Text Field column type, while their salaries are shown in Number Field column type. In this exercise, you will change the default types of some columns to some other types, as follows:
A. The Job column will be presented as a Radio Group to select one from a list of distinct jobs
B. The value for the Manager column will be selected from a pop-up LOVC. The Hire Date will use a Date Picker that opens on focus
D. Display Yes/No in On Leave column
E. The Tags column will use a Shuttle type to select multiple values
With Page 100 being displayed in the Page Designer, expand the Columns node and click the JOB column. Set its Type attribute to Radio Group. When you select the radio group type, you are asked to associate a list of values to populate the item. For the List of Values Type attribute, select SQL Query and enter
SELECT DISTINCT job a, job b FROM EBA_DEMO_IG_EMP
in SQL Query box. Also turn off set Display Extra Values and Display Null Values properties – see section 5.4.2 for details. The SQL Query fetches distinct job IDs from the table and shows them in the JOB column using the radio group type.
Next, click the MGR column and change its Type to Popup LOV. Then, select SQL Query for List of Values Type and enter the following statement in SQL Query box.
SELECT ENAME as d, EMPNO as r
FROM EBA_DEMO_IG_EMP
WHERE JOB = 'MANAGER' or JOB = 'PRESIDENT' order by 1
Click the HIREDATE column. The Type attribute of this column is already set to Date Picker and this is what we want. The only attribute of this column that needs to be changed is Show (under Settings). Change it from on icon click to On focus to display the date picker when the focus is on this column.
Click the ONLEAVE column and set its Type to Switch. This will display either On or Off state for this column.
Finally, click the TAGS column. Change its Type from Textarea to Shuttle. Set List of Values Type to Static Values and enter static values (by clicking Display1, Display2 text next to the Static Values property) as shown in the following screenshot. Recall that you created a Static LOV through Shared Components interface inChapter 3 - Section 3.4.1. There you specified a pair of static Display and Return values. Here, you didn’t use the Return value, because the Return value is optional. If a Return value is not included, the return value equals the display value. Click the OK button to close the Static Values screen. Figure 5-16
Save and run the page. Click different cells under the JOB column and press F2 to see values in a radio group (A). Similarly, press F2 in the Manager column. This will display a drop down list (B) in the selected cell carrying names of president and managers. Double click the column prior to the Hire Date column, and press the Tab key. The cursor’s focus will move on to the Hire Date column and immediately the Date Picker window (C) will pop up. Keep pressing the Tab key to access the On Leave column, which will show a Yes and No switch (D). Access the Tags column, which should come up with a shuttle (E) carrying the five values defined in step 5. Using the arrow key in the shuttle, move all these values to the right pane and click the cross icon to close the shuttle. Click the Save button to write your changes to the database.
In this example, you will see how to protect rows in an Interactive Grid. For this purpose, you need to add a column named CTRL to implement a simple rule that Managers and Presidents cannot be edited or deleted. This column is then selected in the Allowed Row Operations Column property in the Attributes node.
NOTE: If you encounter the error Interactive Grid 'Column Groups' doesn't have a primary key column defined which is required for editing or in a master detail relationship, then click the EMPNO column in the Page Designer, and turn on the Primary Key property in the Source section. Save and run the page.
Another exciting feature of Interactive Grids is scroll paging (also known as infinite scrolling or virtual paging). It is enabled by setting Pagination attribute to Scroll. After enabling this attribute, the region appears to carry the entire result set but rows are rendered on demand as you scroll. When you scroll down in the Interactive Grid, the model fetches data from the server as it is needed by the view. You can even drag the scroll bar handle all the way to the bottom and then scroll up. You need a database table with lots of records to assess this feature. In this exercise, you will use EBA_DEMO_IG_PEOPLE table, which carries over 4000 records.
Interactive Grid makes it effortless to create master-detail relationships and go any number of levels deep and across. You can create all types of master-detail-detail screens with ease. In this section, I’ll demonstrate this feature.
From the SQL Workshop menu, select SQL Scripts and click the Upload button. In the Upload Script screen, click the Choose File button. In the Open dialog box, select master_detail_detail.sql file from Chapter 5 folder in the book’s source code and click Upload. In the SQL Scripts interface click the Run button appearing in the last column. On the Run Script screen, click the Run Now button. The script will execute to create four tables (MD_continent, MD_country, MD_city, and MD_population) along with relevant data to demonstrate the master detail detail feature. You can view these tables from the SQL Workshop > Object Browser interface.
Create a new page by clicking the Create Page button in the App Builder interface. This time, select the first Blank Page option and click Next. Set Page Number to 112, Name to Master Detail Detail, Page Mode to Normal, and click Next. On the Navigation Menu screen, select the first Navigation Preference to not associate this page with any sales app navigation menu entry. On the final wizard screen, click Finish.
In the Page Designer, right-click the Regions node on the Rendering tab and select Create Region. Set the following properties for the new region. This region will display data from the MD_continent table.
Property Value
Create another region under the Continents region by right-clicking the main Regions node. This region will act as the detail for the Continents region. At run-time when you select a continent, this region will display a list of countries in the selected continent. Set the following properties for this new region.
Property Value
Property Value
Property Value
Save and run the page. Click the row representing Europe (A) in the first region. As you click this row, the second region will display countries in the Europe continent. Click Germany (B) in the second region. This will refresh the third region with a list of cities in Germany. Click the Berlin city (C) to see its population (D) in the forth region.Figure 5-18
Techniques in this chapter:
Top.....Back
drop table DEMO_ORDER_ITEMS;
drop table DEMO_ORDERS;
drop table DEMO_ORDERS_ERR$;
drop table DEMO_STATES;
drop table DEMO_CUSTOMERS;
drop table DEMO_CUSTOMERS_ERR$;
drop table DEMO_PRODUCT_INFO;
drop sequence DEMO_ORDER_ITEMS_SEQ;
drop sequence DEMO_ORDERS_SEQ;
drop sequence DEMO_STATES_SEQ;
drop sequence DEMO_CUSTOMERS_SEQ_SEQ;
drop sequence DEMO_PRODUCT_INFO_SEQ;
--update DEMO_CUSTOMERS set CUST_STREET_ADDRESS2 ='aaaa bbbbb' where CUSTOMER_ID=10 ;
--SELECT CUST_STREET_ADDRESS2, CUSTOMER_ID from DEMO_CUSTOMERS order by CUST_LAST_NAME desc ;
CREATE table "DEMO_CUSTOMERS" (
"CUSTOMER_ID" NUMBER NOT NULL,
"CUST_FIRST_NAME" VARCHAR2(20) NOT NULL,
"CUST_LAST_NAME" VARCHAR2(20) NOT NULL,
"CUST_STREET_ADDRESS1" VARCHAR2(60),
"CUST_STREET_ADDRESS2" VARCHAR2(60),
"CUST_CITY" VARCHAR2(30),
"CUST_STATE" VARCHAR2(2),
"CUST_POSTAL_CODE" VARCHAR2(10),
"CUST_EMAIL" VARCHAR2(30),
"PHONE_NUMBER1" VARCHAR2(25),
"PHONE_NUMBER2" VARCHAR2(25),
"URL" VARCHAR2(100),
"CREDIT_LIMIT" VARCHAR2(2),
"TAGS" VARCHAR2(4000),
constraint "DEMO_CUSTOMERS_PK" primary key ("CUSTOMER_ID")
)
/
-- CONSTRAINT "DEMO_CUST_CREDIT_LIMIT_MAX" CHECK (credit_limit <= 5000) ENABLE,
--CONSTRAINT "DEMO_CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID") USING INDEX ENABLE,
--CONSTRAINT "DEMO_CUSTOMERS_UK" UNIQUE ("CUST_FIRST_NAME", "CUST_LAST_NAME") USING INDEX ENABLE
alter table "DEMO_CUSTOMERS" modify (
"CREDIT_LIMIT" NUMBER(9,2)
)
/
CREATE sequence "DEMO_CUSTOMERS_SEQ"
/
CREATE trigger "BI_DEMO_CUSTOMERS"
before insert on "DEMO_CUSTOMERS"
for each row
begin
if :NEW."CUSTOMER_ID" is null then
select "DEMO_CUSTOMERS_SEQ".nextval into :NEW."CUSTOMER_ID" from sys.dual;
end if;
end;
/
--update DEMO_PRODUCT_INFO set CATEGORY ='aaaa' where PRODUCT_ID=8 ;
--SELECT PRODUCT_NAME, CATEGORY, PRODUCT_ID from DEMO_PRODUCT_INFO order by PRODUCT_NAME ;
CREATE table "DEMO_PRODUCT_INFO" (
"PRODUCT_ID" NUMBER NOT NULL,
"PRODUCT_NAME" VARCHAR2(50),
"PRODUCT_DESCRIPTION" VARCHAR2(2000),
"CATEGORY" VARCHAR2(30),
"PRODUCT_AVAIL" VARCHAR2(1),
"LIST_PRICE" NUMBER(8,2),
"PRODUCT_IMAGE" BLOB,
"MIMETYPE" VARCHAR2(255),
"FILENAME" VARCHAR2(400),
"IMAGE_LAST_UPDATE" TIMESTAMP(6) WITH LOCAL TIME ZONE,
"TAGS" VARCHAR2(4000),
constraint "DEMO_PRODUCT_INFO_PK" primary key ("PRODUCT_ID")
)
/
--alter table "DEMO_PRODUCT_INFO" add column TAGS VARCHAR2(4000) ;
CREATE sequence "DEMO_PRODUCT_INFO_SEQ"
/
CREATE trigger "BI_DEMO_PRODUCT_INFO"
before insert on "DEMO_PRODUCT_INFO"
for each row
begin
if :NEW."PRODUCT_ID" is null then
select "DEMO_PRODUCT_INFO_SEQ".nextval into :NEW."PRODUCT_ID" from sys.dual;
end if;
end;
/
CREATE table "DEMO_STATES" (
--"STATE_ID" VARCHAR2(2),
"ST" VARCHAR2(30),
"STATE_NAME" VARCHAR2(30)
)
/
update DEMO_ORDERS set ORDER_TIMESTAMP = ORDER_TIMESTAMP + 3600 * 48
Formatting of the TIMESTAMP datatype with fractional seconds:
SELECT TO_CHAR(ORDER_TIMESTAMP,'DD.MM.YYYY HH24:MI:SS.FF3') ORDER_TIMESTAMP, ORDER_ID from DEMO_ORDERS order by ORDER_ID desc ;
prompt ORDER_TIMESTAMP=30.08.2020 20:18:21:000 ORDER_ID=10
update DEMO_ORDERS set ORDER_TIMESTAMP = TO_TIMESTAMP('01.09.2020 11:10:55.888','DD.MM.RRRR HH24:MI:SS.FF3') where ORDER_ID=10 ;
SELECT TO_CHAR(ORDER_TIMESTAMP,'DD.MM.YYYY HH24:MI:SS.FF3') ORDER_TIMESTAMP, ORDER_ID from DEMO_ORDERS order by ORDER_ID desc ;
rem Commit statement not applicable. All statements are **automatically committed**.
CREATE table "DEMO_ORDERS" (
"ORDER_ID" NUMBER NOT NULL,
"CUSTOMER_ID" NUMBER NOT NULL,
"ORDER_TOTAL" NUMBER(8,2),
"ORDER_TIMESTAMP" TIMESTAMP(6) WITH LOCAL TIME ZONE,
"USER_NAME" VARCHAR2(100),
"TAGS" VARCHAR2(4000),
constraint "DEMO_ORDERS_PK" primary key ("ORDER_ID")
)
/
CREATE sequence "DEMO_ORDERS_SEQ"
/
CREATE trigger "BI_DEMO_ORDERS"
before insert on "DEMO_ORDERS"
for each row
begin
if :NEW."ORDER_ID" is null then
select "DEMO_ORDERS_SEQ".nextval into :NEW."ORDER_ID" from sys.dual;
end if;
end;
/
--ALTER TABLE "DEMO_ORDERS" DROP CONSTRAINT "DEMO_ORDERS_FK"
ALTER TABLE "DEMO_ORDERS" ADD CONSTRAINT "DEMO_ORDERS_FK"
FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "DEMO_CUSTOMERS" ("CUSTOMER_ID")
/
--delete DEMO_ORDERS
CREATE table "DEMO_ORDER_ITEMS" (
"ORDER_ITEM_ID" NUMBER NOT NULL,
"ORDER_ID" NUMBER NOT NULL,
"PRODUCT_ID" NUMBER NOT NULL,
"UNIT_PRICE" NUMBER(8,2) NOT NULL,
"QUANTITY" NUMBER(8,0) NOT NULL,
constraint "DEMO_ORDER_ITEMS_PK" primary key ("ORDER_ITEM_ID")
)
/
CREATE sequence "DEMO_ORDER_ITEMS_SEQ"
/
CREATE trigger "BI_DEMO_ORDER_ITEMS"
before insert on "DEMO_ORDER_ITEMS"
for each row
begin
if :NEW."ORDER_ITEM_ID" is null then
select "DEMO_ORDER_ITEMS_SEQ".nextval into :NEW."ORDER_ITEM_ID" from sys.dual;
end if;
end;
/
ALTER TABLE "DEMO_ORDER_ITEMS" ADD CONSTRAINT "DEMO_ORDER_ITEMS_FK"
FOREIGN KEY ("ORDER_ID")
REFERENCES "DEMO_ORDERS" ("ORDER_ID")
ON DELETE CASCADE
/
ALTER TABLE "DEMO_ORDER_ITEMS" ADD CONSTRAINT "DEMO_ORDER_ITEMS_PRODUCT_ID_FK"
FOREIGN KEY ("PRODUCT_ID")
REFERENCES "DEMO_PRODUCT_INFO" ("PRODUCT_ID")
/
Click the SQL Workshop menu (A).
Select the Object Browser option (B) from the menu, which is used to review and maintain database objects (such as, tables, sequences, views, functions, triggers, and so on).
In Object Browser page, select the Tables option (C) from select list. This action will show a list of existing tables in the left pane, if there are any.
Click Create menu select list (D) , and select Table (E) from the menu list to create a new table. This will invoke a wizard named Create Table
DEMO_CUSTOMERS for table name (A), CUSTOMER_ID in the first Column Name (B), NUMBER (C) Type, Not Null (D)
CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1, CUST_STREET_ADDRESS2, CUST_CITY, CUST_STATE,
CUST_POSTAL_CODE, CUST_EMAIL, PHONE_NUMBER1, PHONE_NUMBER2, URL, CREDIT_LIMIT, TAGS
Scale column specify the number of characters each column will hold
Primary Key -> select Populated from a new sequence (A) -> Accept DEMO_CUSTOMERS_PK (B) for the Primary Key Constraint Name -> select CUSTOMER_ID (C) -> default Sequence Name or enter any other
Skipp Foreign Key and Constraints wizard screens. On the final Confirm screen, click the Create Table button. SQL button shows auto-generated SQL see above.
"Column Name","Data Type","Nullable","Default","Primary Key"
"CUSTOMER_ID","NUMBER","No"," ","1"
"CUST_FIRST_NAME","VARCHAR2(20)","No"," "," "
"CUST_LAST_NAME","VARCHAR2(20)","No"," "," "
"CUST_STREET_ADDRESS1","VARCHAR2(60)","Yes"," "," "
"CUST_STREET_ADDRESS2","VARCHAR2(60)","Yes"," "," "
"CUST_CITY","VARCHAR2(30)","Yes"," "," "
"CUST_STATE","VARCHAR2(2)","Yes"," "," "
"CUST_POSTAL_CODE","VARCHAR2(10)","Yes"," "," "
"CUST_EMAIL","VARCHAR2(30)","Yes"," "," "
"PHONE_NUMBER1","VARCHAR2(25)","Yes"," "," "
"PHONE_NUMBER2","VARCHAR2(25)","Yes"," "," "
"URL","VARCHAR2(100)","Yes"," "," "
"CREDIT_LIMIT","VARCHAR2(2)","Yes"," "," "
"TAGS","VARCHAR2(4000)","Yes"," "," "
-- data : see oracle_apex_....csv files
CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_STREET_ADDRESS1,CUST_STREET_ADDRESS2,CUST_CITY,CUST_STATE,CUST_POSTAL_CODE,CUST_EMAIL,PHONE_NUMBER1,PHONE_NUMBER2,URL,CREDIT_LIMIT,TAGS
1,John,Dulles,45020 Aviation Drive, ,Sterling,VA,20166,techies81@gmail.com,703-555-2143,703-555-8967,http://www.johndulles.com,1000,
2,William,Hartsfield,6000 North Terminal Parkway, ,Atlanta,GA,30320, ,404-555-3285, , ,1000,REPEAT CUSTOMER
3,Edward,Logan,1 Harborside Drive, ,East Boston,MA,2128, ,617-555-3295, , ,1000,REPEAT CUSTOMER
4,Frank,OHare,10000 West OHare, ,Chicago,IL,60666, ,773-555-7693, , ,1000,
5,Fiorello,LaGuardia,Hangar Center,Third Floor,Flushing,NY,11371, ,212-555-3923, , ,1000,
6,Albert,Lambert,10701 Lambert International Blvd., ,St. Louis,MO,63145,techies81@gmail.com,314-555-4022, , ,1000,
7,Eugene,Bradley,Schoephoester Road, ,Windsor Locks,CT,6096,techies81@gmail.com,860-555-1835, , ,1000,REPEAT CUSTOMER
Contains BLOB (Binary Large Objects) type (B), which is an Oracle data type that can hold up to 4 GB of data. BLOBs are handy for storing digitized information, such as images, audio, and video. This type can also be used to store document files like PDF, MS Word, MS Excel, MS PowerPoint and CSV. We are also using a TIMESTAMP type (C) to store the date when a product image is updated.
Select the Populated from a new sequence
Skip the Foreign Key and Constraints wizard screens.
PRODUCT_ID,PRODUCT_NAME,PRODUCT_DESCRIPTION,CATEGORY,PRODUCT_AVAIL,LIST_PRICE,PRODUCT_IMAGE,MIMETYPE,FILENAME,IMAGE_LAST_UPDATE
1,Air Max 2090,"Bring the past into the future with the Nike Air Max 2090, a bold look inspired by the DNA of the iconic Air Max 90.",Men,Y,1500,,image/png,1_AirMax2090.png,21-JUN-20 06.45.19.434901 AM
2,LeBron Soldier 13 Red,"LeBron works hard in the offseason, getting stronger and fine-tuning his already devastating skills. ",Men,Y,200,,image/png,2_LeBron Soldier 13 Red.png,21-JUN-20 06.45.19.733931 AM
3,LeBron Soldier 13 White,"LeBron works hard in the offseason, getting stronger and fine-tuning his already devastating skills. ",Men,Y,150,,image/png,3_LeBron Soldier 13 White.png,21-JUN-20 06.45.19.738065 AM
4,Air Max 720,"The Nike Air Max 720 goes bigger than ever before with Nike’s tallest Air unit yet, which offers more air underfoot for unimaginable, all-day comfort.",Women,Y,60,,image/png,4_Air Max 720.png,21-JUN-20 06.45.19.741109 AM
5,Air Max 270,"Nike's first lifestyle Air Max takes a leap forward with the softest, smoothest, most resilient foam yet in the Nike Air Max 270 React.",Women,Y,80,,image/png,5_Air Max 270.png,21-JUN-20 06.45.19.743820 AM
6,Air Jordan 6,Inspired by the original AJ6 - released the year MJ won his first professional championship - the Air Jordan 6 Retro maintains the iconic look with updated materials.,Women,Y,120,,image/png,6_Air Jordan 6.png,21-JUN-20 06.45.19.746745 AM
7,LeBron 17 BG,The LeBron 17 harnesses LeBrons strength and speed with containment and support for all-court domination.,Women,Y,30,,image/png,7_LeBron 17 BG.png,21-JUN-20 06.45.19.749888 AM
8,Air Max 270 Gradient,"By pulling inspiration from two icons the Air Max 180 and Air Max 93, the Nike Air Max 270 has the tallest Air unit to date.",Men,Y,125,,image/png,8_Air Max 270 Gradient.png,21-JUN-20 06.45.19.752623 AM
9,Air Max 180 Trainer,"Hi-Tech meets high impact in these durable, performance-based Nike training shoes.",Men,Y,110,,image/png,9_Air Max 180 Trainer.png,21-JUN-20 06.45.19.755284 AM
10,Jr Phantom Vision,"Wenn du auf der Suche nach Multinockenschuhen für Kinder bist, ist der kicker-Onlineshop genau das Richtige - 10112892",Kids,Y,50,,image/png,10_Jr Phantom Vision.png,21-JUN-20 06.45.19.757715 AM
Skip the Foreign Key and Constraints wizard screens.
STATE_ID,STATE_NAME
AK,ALASKA
AL,ALABAMA
AR,ARKANSAS
AZ,ARIZONA
CA,CALIFORNIA
CO,COLORADO
CT,CONNECTICUT
DC,DISTRICT OF COLUMBIA
DE,DELAWARE
FL,FLORIDA
GA,GEORGIA
HI,HAWAII
IA,IOWA
ID,IDAHO
IL,ILLINOIS
IN,INDIANA
KS,KANSAS
KY,KENTUCKY
LA,LOUISIANA
MA,MASSACHUSETTS
MD,MARYLAND
ME,MAINE
MI,MICHIGAN
MN,MINNESOTA
MO,MISSOURI
MS,MISSISSIPPI
MT,MONTANA
NC,NORTH CAROLINA
ND,NORTH DAKOTA
NE,NEBRASKA
NH,NEW HAMPSHIRE
NJ,NEW JERSEY
NM,NEW MEXICO
NV,NEVADA
NY,NEW YORK
OH,OHIO
OK,OKLAHOMA
OR,OREGON
PA,PENNSYLVANIA
RI,RHODE ISLAND
SC,SOUTH CAROLINA
SD,SOUTH DAKOTA
TN,TENNESSEE
TX,TEXAS
UT,UTAH
VA,VIRGINIA
VT,VERMONT
WA,WASHINGTON
WI,WISCONSIN
WV,WEST VIRGINIA
WY,WYOMING
Select Populated from a new sequence.
Foreign Key wizard screen :
Create a relationship between DEMO_CUSTOMERS and DEMO_ORDERS table.
Default Disallow Delete option (B) will block deletion of rows from the customers table when they are utilized in the orders master table.
Skip Constraints wizard screen.
ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME,TAGS
1,7,1890,17-APR-20 06.45.19.000000 AM,DEMO,
2,1,2380,01-MAY-20 06.45.19.000000 AM,DEMO,LARGE ORDER
3,2,1640,12-MAY-20 06.45.19.000000 AM,DEMO,
4,5,1090,14-MAY-20 06.45.19.000000 AM,DEMO,
5,6,950,24-MAY-20 06.45.19.000000 AM,DEMO,
6,3,1515,29-MAY-20 06.45.19.000000 AM,DEMO,
7,3,905,03-JUN-20 06.45.19.000000 AM,DEMO,
8,4,1060,11-JUN-20 06.45.19.000000 AM,DEMO,
9,2,730,17-JUN-20 06.45.19.000000 AM,DEMO,
10,7,870,20-JUN-20 06.45.19.000000 AM,DEMO,
192,7,180,21-JUN-20 07.03.03.717283 AM,DEMO,
191,1,800,21-JUN-20 06.52.48.105288 AM,DEMO,
211,7,100,21-JUN-20 06.57.10.978243 AM,DEMO,
212,6,210,21-JUN-20 06.59.13.144771 AM,DEMO,
213,6,500,21-JUN-20 07.00.17.533966 AM,DEMO,
251,7,155,02-DEC-20 06.29.39.463839 AM,DEMO,
231,7,1421,06-JUL-20 09.17.31.833485 AM,DEMO,
Has two foreign key references : ORDER_ID and PRODUCT_ID.
FK to DEMO_ORDERS : For this relationship you selected the Cascade Delete option (A), which simultaneously removes both parent and child records from the two tables when you delete an order.
DEMO_ORDER_ITEMS_PRODUCT_ID_FK : Select Disallow Delete for delete option.
Skip Constraint wizard screen.
"ORDER_ITEM_ID","ORDER_ID","PRODUCT_ID","UNIT_PRICE","QUANTITY"
"500","1","1","50","10"
"501","1","2","80","8"
"502","1","3","150","5"
"503","2","1","50","3"
"504","2","2","80","3"
"505","2","3","150","3"
"506","2","4","60","3"
"507","2","5","80","3"
"508","2","6","120","2"
"509","2","7","30","2"
"510","2","8","125","4"
"511","2","9","110","2"
"512","2","10","50","2"
"513","3","4","60","4"
"514","3","5","80","4"
"515","3","6","120","4"
"516","3","8","125","4"
"517","3","10","50","2"
"518","4","6","120","2"
"519","4","7","30","6"
"520","4","8","125","2"
"521","4","9","110","2"
"522","4","10","50","4"
"523","5","1","50","3"
"524","5","2","80","2"
"525","5","3","150","2"
"526","5","4","60","3"
"527","5","5","80","2"
"528","6","3","150","3"
"529","6","6","120","3"
"530","6","8","125","3"
"531","6","9","110","3"
"532","7","1","50","2"
"533","7","2","80","2"
"534","7","4","60","2"
"535","7","5","80","2"
"536","7","7","30","3"
"537","7","8","125","1"
"538","7","10","50","3"
"539","8","2","80","2"
"540","8","3","150","3"
"541","8","6","120","1"
"542","8","9","110","3"
"543","9","4","60","4"
"544","9","5","80","3"
"545","9","8","125","2"
"546","10","1","50","5"
"547","10","2","80","4"
"548","10","3","150","2"
"561","192","7","30","6"
"620","231","8","125","10"
"560","191","2","80","10"
"549","211","10","50","2"
"550","212","7","30","7"
"551","213","1","50","10"
"640","251","7","30","1"
"641","251","8","125","1"
"580","231","8","111","1"
"581","231","7","30","2"
https://blog.foex.at/an-introduction-to-single-page-application-development-in-oracle-apex/
APEX has many unique characteristics that you won’t find in any other framework. 6 characteristics sets it apart from every other development framework :
it runs within the Oracle DB, it's at the same level of your data negating need for a middle tier (Java need not apply). It's perfect for data centric apps. You have full access to all DB capability, and as of 18c Oracle DB packs a very long comprehensive set of features.
it's meta data driven. Using SQL you can query everything that is designed on your page, or in your app, and you can do this at the point of page rendering or in every AJAX callback. You can even generate apps/pages/regions etc. using the same API's the APEX builder uses to create your apps. The power and solution capability this gives you as a developer is not widely publicised and in our opinion is really underrated.
is serverside generation, you can programatically output HTML, CSS and Javascript on the fly. This can be on page render or for AJAX callbacks. The benefits of server generated Javascript is not widely publicised either. Again it can reduce your client side foot print drastically as well as reducing client side coding complexity.
we can build visual appealing applications really fast with APEX, and they are super easy to deploy and maintain.Thanks to the universal theme and Theme Roller you can achieve a modern look and feel whilst adhering to corporate colour conventions. APEX has an extensive and mature PLSQL API, and also built-in REST support thanks to ORDS (Oracle REST Data Services).
low code design makes it easy for beginners to become productive, whilst it provides full control for experts.
licensing cost, if you don't have a data foot print above 12GB you can use 18c XE, ORDS, and APEX for free. You can also deploy this on cloud providers that can charge as little as $5 a month for hosting. So for people not using Oracle yet there is no entry barrier for adopting APEX. You could even use REST to access data in remote databases if your data requirement exceeds 12G. There are solutions to keeping costs to a minimum for small businesses.
Lastly there's also productivity apps that come with it like Quick SQL which is a markdown editor that can quickly build your data model and also generate a dummy dataset. You can also quickly build prototypes using APEX, comparable in time to using a wireframe tool. The difference being you have a functional application to preview, making the choice to use APEX that much easier.
SPA Framework | MPA Framework | |
---|---|---|
1. | ✔ Client Side Rendering | Serverside Rendering |
2. | ✔ Partial Page Loading | Full Page Loading |
3. | ✔ Lazy Loading | |
4. | ✔ Lazy Rendering | |
5. | ✔ AJAX Centric | |
6. | ✔ - | Page Submits |
Currently the majority of APEX plugin developers are using it for single functionality use cases like nested reporting, multiple file upload, new LOV items... but you can take this a step further and plug-in an existing SPA Javascript framework and provide a reusable declarative set of regions, items, processes, and dynamic actions that are designed to work together to form a single page application design approach to building your application. This is what is FOEX Plugin Framework for Oracle APEX.
for c1 in
(
select page_title, help_text
from apex_application_pages
where page_id = :P10061_PAGE_ID
and application_id = :APP_ID
)
loop
if c1.help_text is null then
sys.htp.p('No help is available for this page.');
else
if substr(c1.help_text, 1, 3) != '<p>' then
sys.htp.p('<p>');
end if;
sys.htp.p(apex_application.do_substitutions(c1.help_text));
if substr(trim(c1.help_text), -4) != '</p>' then
sys.htp.p('</p>');
end if;
end if;
end loop;
MD to HTML converters on inet
- https://www.tutorialspoint.com/online_markdown_editor.php or https://markdowntohtml.com/
- or https://www.browserling.com/tools/markdown-to-html (many converters)
- or files convert : https://products.aspose.app/pdf/conversion/md-to-html to many formats
- Links not working : http://demo.showdownjs.com/ (no HTML source)
NOT WORKING : https://daringfireball.net/projects/markdown/dingus or https://pandoc.org/try/
Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy