Create Wizard-Based Order Processing Module in Oracle Application Express
Part II of IV
Section 6.10 – Page 12
After selecting an existing customer or creating a new one, the next step in the Order Wizard is to add items to the order. In this section, you learn many techniques about how to display products and add them to the cart like the ones available on the web.
Section 6.10.1 – On this page the following tasks are performed:
a. Page template set similar to Page 18.
c. Added Cascading Style Sheet (CSS) in HTML header section to position and style page elements. Check out this blog post which shows How to Use CSS in Oracle APEX.
Section 6.10.2 – In this section a PL/SQL Dynamic Content region is created to display the following information:
a. Customer information: This region displays name and other details of the selected customer.
b. Products: Displays all products from the products table.
c. Current order: This area hold items selected in the current order along with price, quantity, and totals.
Section 6.10.3 – Create a List region named Order Progress to hold Order Wizard list similar to 6.6.3 and 6.8.4.
Section 6.10.4 – Create three buttons CANCEL, PLACE ORDER and PREVIOUS. For the Place Order button, select a condition type Exists (SQL query returns at least one row) and type select 1 from apex_collections where collection_name = 'ORDER'. This condition ensures that the current order has at least one item selected to proceed further. Also note that the button Place Order becomes visible only after adding a product to the Current Order pane. See Display Current Order in the post “Using HTML in PL/SQL” for details on APEX_COLLECTION.
Section 6.10.5 – This section demonstrates the use of hidden items in Oracle APEX. The first hidden item, P12_PRODUCT_ID, is created and used in subsequent sections to Add/Remove members from Apex_Collections. The other one, P12_CUSTOMER_NAME, is used in the following computation section. You also set the attribute Value Protected to No. Specifying Yes will prevent the hidden value from being manipulated when the page is posted.
Section 6.10.6 – A computation section is created under Page Rendering to display complete customer name. It is merely added to the application to show the use of computation in Oracle APEX. The actual customer profile is displayed on the page using PL/SQL code Display Customer Information under section 6.10.2 in conjunction with CSS.
Section 6.10.7 – This section comprises two PL/SQL processes Add product to the order collection and Remove product from the order collection based on P12_PRODUCT_ID defined in section 6.10.5. The referenced collection name ORDER was created in Page 11 - section 6.6.7. Condition Types are set to ADD and REMOVE respectively. These two expressions refer to the ADD and REMOVE requests sent through the <a> tag in Display Products PL/SQL block under section 6.10.2 (see page 113/114 in the book). After receiving the request, the appropriate process is run to ADD/REMOVE product to/from the Current Order section.
Section 6.10.8 – Place Order is the final process in this page that is invoked when Place Order button is pressed. This PL/SQL block writes data to relevant database tables using page items and Order collection. The collection ORDER is truncated after the data is inserted using the statement: apex_collection.truncate_collection(p_collection_name => 'ORDER');
Section 6.10.9 – Create two branches in this page one each for Place Order and Cancel buttons. When the Place Order button is clicked, the wizard switches forward to Order Summary page (Page 14) and moves back to Order Master page (Page 4) when the Cancel button is pressed.
Section 6.12 – Page 20
In order to provide product details to the user before placing orders, you create a popup page that open within the existing order page to display product's image, description, category, and price. A popup window doesn’t need to have either a breadcrumb or a tab associated to it; therefore, none of these options were selected during the page creation wizard.
Section 6.12.1 – Modify attribute of the newly created page and set template to Popup to open it in an existing one.
Section 6.12.2 – Create a SQL Report region to hold product image. Using a SQL statement fetch the image of the selected product based on P20_PRODUCT_ID (a hidden page item created in section 6.12.5). Following is the SQL statement and explanation of the few Oracle expressions used in it:
select decode(nvl(dbms_lob.getlength(product_image),0),0,null,'<img src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',product_id)||'" />') image
where product_id = :P20_PRODUCT_ID
DECODE Function: In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement. It compares expression to each search value one by one. If the exprssion is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null. In the above statement, the Decode function assesses if the returned value of product image is zero, store null to the result. The following syntax and example of the Decode function elaborates it further.
decode( expression , search , result [, search , result]... [, default] )
Example of Decode Function:
The IF-THEN-ELSE statement for the above Decode function would be:
IF customer_id = 1 THEN
result := 'A';
ELSIF customer_id = 2 THEN
result := 'B';
ELSIF customer_id = 3 THEN
result := 'C';
result := 'D';
In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered. The syntax for the NVL function is:
NVL( string1, replace_with )
Example of NVL Function:
Select NVL(customer_state, 'N/A') from customers;
The NVL function in the above statement evaluates that if the customer_state column is not null return the value it contains. Otherwise, return the ‘N/A’ string.
DBMS_LOB Package: LOBs, or Large OBjects, are Oracle's preferred way of handling and storing non-character data, such as mp3s, videos, pictures, etc., and long character data. Binary large objects, or BLOBs, and character large objects, or CLOBs, can store up to terabytes of data - much more than the paltry 4000 bytes permitted in a varchar2 column.
GETLENGTH(): It is a PL/SQL Functions of the DBMS_LOB package to read or examine internal and external LOB values and get the length of the LOB value. Type the following SQL statement in SQL Plus or another utility:
Select product_id, dbms_lob.getlength(product_image) Length from demo_product_info order by product_id;
Output of DBMS_LOB.GETLENGTH Function:
IMG and SRC: In HTML, images are defined with the <img> tag. The <img> 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:
<img src="url" alt="some_text"/>
The URL points to the location where the image is stored.
APEX_UTIL.GET_BLOG_FILE_SRC: See section 6.4.2 in Part IV of this blog post.
Section 6.12.3 – Create another SQL Report region to display product’s text information (name, description, category, and list price).
Section 6.12.5 – Create a hidden item named P20_PRODUCT_ID to act as a parameter for the SQL queries mentioned in the preceding sections.