tag and utilizes the Products class referenced in rules 4-9.
sys.htp.p('
(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('
(Line: 53)
This line references another class (CartItem) to style the actual Current Order
section.
(Line: 54)
```
\
tag creates a link with a REMOVE request. This time, it usesproduct ID from the collection. In section 7.6.7 (B), there is a process named
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.
'||trim(to_char(c1.p,'$999G999G999D00'))||' (Line: 56)
Quantity: '||c1.q||' (Line: 57)
Subtotal:
'||trim(to_char(c1.ep,'$999G999G999D00'))||' (Line: 58)
The three lines display price, quantity, and sub-total of the selected product in
the Current Order section, as shown below:'); (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('
Items: '||c||'
Total:
'||trim(to_char(t,'$999G999G999D00'))||'
');
else
sys.htp.p('
');
sys.htp.p('

');
sys.htp.p('
');
sys.htp.p('
Note
');
sys.htp.p('
You have no items in your current order.
');
sys.htp.p('
');
sys.htp.p('
');
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.
### 7.6.4 Create Hidden Item
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
1. Name P12_PRODUCT_ID
2. Type Hidden
### 7.6.5 Create Region to hold Buttons
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.
### 7.6.6 Create Buttons
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
1. Button Name CANCEL
2. Label Cancel
3. Button Position Close
4. Action Submit Page
Create another button under the Cancel button and set the following properties:
Property Value
1. Button Name PREVIOUS
2. Label Previous
3. Button Position Previous
4. Button Template Icon
5. Icon fa-chevron-left
6. Action Submit Page
Create the final button under the Previous button and set the following properties:
Property Value
1. Button Name NEXT
2. Label Place Order
3. Button Position Next
4. Button Template Text with Icon
5. Hot On
6. Icon fa-chevron-right
7. Action Submit Page
### 7.6.7 Create Processes
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
1. On Page 12, expand the Pre-Rendering node (on the Rendering tab) and create a process under Before Header node.
2. Enter Add Product to the ORDER Collection for the name of this new process and set its Type to PL/SQL Code.Figure 7-28
3. Enter the following code in the PL/SQL Code box. Locate this code under BookCode\Chapter7\7.6.7A.txt file.
```
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;
```
4. In Server-side Condition section, set Type to Request=Value, and enter ADD in the Value property box.
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.
1. Create another process under the previous one. Name it Remove Product from the ORDER Collection and set its Type to PL/SQL Code.
2. Enter the following code in the PL/SQL Code property box. Get this code from BookCode\Chapter7\7.6.7B.txt file.
```
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;
```
3. In Server-side Condition section, set Type to Request=Value, and enter REMOVE in the Value property box.
### 7.6.8 Create Process - Place Order
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.
1. On the Processing tab, create a new process under the Processing node.Figure 7-29
2. 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;
```
### 7.6.9 Create Branches
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
1. Name Go To Page 14
2. Type (under Behavior) Page or URL (Redirect)
3. Target Type = Page in this Application Page = 14
4. When Button Pressed NEXT
Property Value
1. Name Go To Page 4
2. Type (under Behavior) Page or URL (Redirect)
3. Target Type = Page in this Application Page = 4
4. When Button Pressed CANCEL
Property Value
1. Name Go To Page 11
2. Type (under Behavior) Page or URL (Redirect)
3. Target Type = Page in this Application Page = 11
4. When Button Pressed PREVIOUS
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.
## 7.7 Create Order Summary Page - Page 14
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.
1. Create one more Blank Page.
2. Complete the first wizard step as show in the following figure and
click Next.
3. On the Navigation Menu screen, set Navigation Preference to
Identify an existing navigation menu entry for this page, and set
Existing Navigation Menu Entry to Orders. Click Next.
4. Click Finish to end the wizard.
5. Click the root node (Page 14: Order Summary) and set Dialog
Template to Wizard Modal Dialog.
### 7.7.1 Create Region – Order Progress
Right-click the Wizard Progress Bar node and select Create Region. Set following properties for the new region.
Property Value
1. Title Order Progress
2. Type List
3. List Order Wizard
4. Template Blank with Attributes
5. List Template (under Attributes node) Wizard Progress
### 7.7.2 Create Region – Order Header
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
1. Title Order Header
2. Type PL/SQL Dynamic Content
3. 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('
ORDER #' ||sys.htf.escape_sc(:P14_ORDER_ID) || '');
htp.p(sys.htf.escape_sc(x.cust_first_name) || ' ' ||sys.htf.escape_sc(x.cust_last_name) || '
');
htp.p(sys.htf.escape_sc(x.cust_street_address1) || '
');
if x.cust_street_address2 is not null then
htp.p(sys.htf.escape_sc(x.cust_street_address2) || '
');
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) || '
');
end loop;
end;
```
### 7.7.3 Create Region – Order Lines
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
1. Title Order LinesType Classic Report
2. Location Local Database
3. Type SQL Query
4. SQL Query
```
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
```
### 7.7.4 Create Item
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
2. In the SQL Commands interface, enter the code for the new trigger
named DEMO_ORDER_ITEMS_AIUD_TOTAL, as illustrated in
the following figure, and hit the Run button. The trigger will be
created and you will see a confirmation on the Results tab. The
code for this trigger is available in BookCode\Chapter7\7.7.7.txt
file.Figure 7-32
Complete Testing
Congratulation! You have completed the most tiresome but interesting
chapter of the book in which you learned numerous techniques. Now you are
in a position to test the whole work you performed in this chapter.
1. Select Orders from the main navigation menu and then click the
Enter New Order button.
2. Select New Customer.
3. Fill in the New Customer form using your own name, address,
and so on. Click Next to proceed.
4. On the Select Items page add some products to the Current Order
pane.
5. Click the Place Order button to see the Order Summary page, as
illustrated in figure 7-33.Figure 7-33 Order Summary Page
NOTE: You might encounter a primary key violation message (ORA-00001:
unique constraint (DEMO_ORDERS_PK) violated) while creating first
product record. This is because the Sequence object for this table is created
with an initial value of 1. Keep clicking the Place Order button unless the
record is saved.
6. Click the Back To Orders button in the Order Summary page to
return to the orders main page. The newly created order will appear
in the orders list.
7. Click the number of the new order to modify it in Order Details
page (Page 29). Try to add or remove products on this page and
save your modifications.8. Also, try the delete operation by deleting this new order.
## 7.8 Sending Email from Oracle APEX Application
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.
1. 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 := '
'||utl_tcp.crlf;
Vbody_html := Vbody_html || 'Hi '|| Vcust_name ||','||utl_tcp.crlf||utl_tcp.crlf;
Vbody_html := Vbody_html ||'
Your order has been confirmed which you can access by clicking here.
' ||utl_tcp.crlf;
Vbody_html := Vbody_html ||'
Regards,
'||utl_tcp.crlf;
Vbody_html := Vbody_html ||'
Sales Team'||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.
2. Open Page 29 in Page Designer. From the Create menu, select
Page as Copy – see section 7.5. Enter 30 for New Page Number
and Customer Order for New Page Name. On the Navigation
Menu screen, select Identify an existing navigation menu entry
for this page, and select Orders for Existing Navigation Menu
Entry.
3. Open Page 29 (Order Details) and click the root node. Scroll down
to the Security section, and set Page Access Protection attribute to
Unrestricted. This value is set for a page that is requested using a
URL, with or without session state arguments, and having nochecksum.
4. Delete all six buttons and their regions from Page 30.
5. Next, click the P30_ORDER_ID page item located under Wizard
Body | Order #&P30_ORDER_ID. region. Set Session State
Protection under Security to Unrestricted. By setting this value the
item's value can be set by passing the item in a URL or in a form
and no checksum is required in the URL.
All is set. Modify a customer record by entering your email account. Create a
new order for this customer. After clicking the Place Order button on the
second wizard screen, you will see the message “ Order confirmation email sent to
customer. ” Log out from the application. After a while, you will receive an
order confirmation email in your email account. Click the “ here ” link in the
email that will take you to the application login page. Immediately after
providing your credentials, the copied order details page (Page 30) will
appear on your screen displaying the order you just entered.
NOTE: If you get “Your session has expired. Please close this dialog and
press your browser's reload button to obtain a new session.” message, then
open Page 30, click its root node, and set Page Mode to Normal.
## 7.9 A More Simple Approach
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.
1. Execute all the steps mentioned in section 7.2 to create the two
master and details pages. In step 4, set number of the Master Page
to 404, and number of the Details Page to 429.
NOTE: Make the Interactive Grid visible on the Order Details page (Page
429), as instructed at the end of section 7.2.
2. Open Page 404 and execute the instructions provided in section
7.3.1. In step 5 of section 7.3.1, set Page and Clear Cacheproperties to 429 to point to the correct page number and set the
Name property to P429_ORDER_ID. Skip the optional report
sections (spanning from 7.3.2 to 7.3.4) at this stage to preserve
some time.
3. Set the following attributes for the CREATE button. Note that
previously this buttons was used to initiate the order wizard by
calling Page 11. Here, we are calling Page 429 to directly enter a
new order.
Property Value
Button Name CREATE
Label Enter New Order
Button Template Text with Icon
Hot On
Icon fa-chevron-right
Action Redirect to Page in this Application
Target
Type = Page in this Application
Page = 429
Clear Cache = 429
4. Save Page 404.
5. In the Page Finder box, enter 429 and press the Enter key to call
Page 429 in the Page Designer.
6. Click the root node (Page 429: Order Details) and set the Page
Mode property to Modal Dialog. Set Width, Height, and Maximum
Width properties to 900, 800, and 1200, respectively. Also, set
Dialog Template (in the Appearance section) to Wizard Modal
Dialog.
7. Edit the following items individually and set the corresponding
properties shown under each item. The customer ID item, which
was displayed as Display Only item in the previous method, will
now be rendered as a Select List carrying the names of all
customers. The SQL query defined for the Select List automatically
shows the correct customer name when you navigate from oneorder to another.
P429_CUSTOMER_ID
Property Value
Type Select List
Label Customer
Type (List of Values) SQL Query
SQL Query
select cust_first_name ||' '|| cust_last_name d, customer_id r
from demo_customers
P429_USER_NAME
Property Value
Type Select List
Label Sales Rep
Type (List of Values) SQL Query
SQL Query
select distinct user_name d, user_name r
from demo_orders
union
select upper(:APP_USER) d, upper(:APP_USER) r
from dual
order by 1
Display Extra Values Off
Display Null Value Off
Help Text Use this list to change the Sales Rep associated with the order.
8. In the Region Buttons node, set Button Position property to Edit
for GET_PREVIOUS_ORDER_ID and GET_NEXT_ORDER_ID
buttons to place them on top of the region.
9. Click the Order Details interactive grid region. Set its Source Type
to SQL Query, and replace the default query with the one that
follows:
select oi.order_item_id, oi.order_id, oi.product_id,
pi.product_name, oi.unit_price,
oi.quantity, (oi.unit_price * oi.quantity) extended_price
from DEMO_ORDER_ITEMS oi, DEMO_PRODUCT_INFO piwhere oi.ORDER_ID = :P429_ORDER_ID
and oi.product_id = pi.product_id (+)
10. Under the Columns node, edit the following columns using the
specified properties and values.
Column Property Value
PRODUCT_ID
Type
Heading
Alignment
Type (LOV)
List of Values
Display Null Value
Select List
Product
left
Shared Components
Products With Price
Off
PRODUCT_NAME Type Hidden
QUANTITY
Width (Appearance)
Type (Default)
PL/SQL Expression
5
PL/SQL Expression
1 (sets 1 as the default q
EXTENDED_PRICE
Type
Heading
Alignment
Column Alignment
Format Mask
Query Only ( Source )
Display Only
Price
right
right
$5,234.10
On
11. Right-click the Wizard Buttons node and select Create Region. Set
Title of the new region to Buttons and Template to Buttons
Container. In Regions Buttons node, click the Cancel button and
set its Region property (under Layout) to Buttons. Set this region
for Delete, Save, and Create buttons, too. This action will place all
the four buttons under the Buttons region.
12. Open Page 429 in the Page Designer. On the Processing tab make
sure that the Process form Form on DEMO_ORDERS sits before
the Order Details - Save Interactive Grid Data process.
13. Click the Save Interactive Grid Data process and switch its Type
from Interactive Grid - Automatic Row Processing (DML) to
PL/SQL Code. Enter the following code in the PL/SQL Code box.
In this code, you specify SQL Insert, Update, and Delete statements
to manually handle the three operations for the Interactive Griddata. The :APEX$ROW_STATUS is a built-in substitution string,
which is used to refer to the row status in an Interactive Grid. This
placeholder returns the status of C if created, U if updated, or D if
deleted for the currently processed interactive grid row. Enter "The
DML operation performed successfully" in the Success Message
box. Similarly, enter "Could not perform the DML operation" in
the Error Message box, and save your work.
begin
case :APEX$ROW_STATUS
when 'C' then
insert into DEMO_ORDER_ITEMS
(order_item_id, order_id, product_id, unit_price, quantity)
values (null, :P429_ORDER_ID, :PRODUCT_ID, :UNIT_PRICE,
:QUANTITY);
when 'U' then
update DEMO_ORDER_ITEMS
set product_id = :PRODUCT_ID,
unit_price = :UNIT_PRICE,
quantity = :QUANTITY
where order_item_id = :ORDER_ITEM_ID and order_id =
:ORDER_ID;
when 'D' then
delete DEMO_ORDER_ITEMS
where order_item_id = :ORDER_ITEM_ID and order_id =
:P429_ORDER_ID;
end case;
end;
NOTE: All four input items in the Order Master section on Page 429 are
rendered as floating elements (see Template property under Appearance
section) in which the label is displayed inside of the input item, and it
automatically shrinks once the input field has a value.
Test Your Work
Click the Enter New Order button (A) on Page 404. Select a customer (B)
and pick an order date (C). Click the Edit button (D) in the Order Details
region. With a product appearing in the first row (E) along with its default
quantity (G), enter some value in the Unit Price column (F), and click the
Create button (H). The order will be saved and you will see the success
message. On the Order Master page, click the order number you just saved,
and then click Add Row (I) to add some more products. Just select aproduct, enter some value in the Quantity column, and click Save. The
modified order will be saved as well. Try to remove a product from this order
using the Delete Rows option in the Row Actions menu. Finally, click the
Delete button on the Order Details page to test order deletion. You’re done!
Figure 7-34 Order Master and Detail Pages
## 7.10 Looping Through Interactive Grid
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.
2. Click the Order Details interactive grid region and enter ORDER
for its Static ID attribute (under Advanced). The ORDER static id
will be used as the ID for the interactive grid region, which is
useful in developing custom JavaScript behavior for the region, as
you will see later in this exercise.
3. Right-click the Items node under the Order Master static content
region (under Wizard Body) and select Create Page Item. Set the
following attributes for this new item. It is a hidden item that will
store 0 (as default) or 1 behind the scene. The value 1 in this item
means that there are some duplicate products in the order. This
evaluation will be done by a validation – Check Duplicate Product.
Property Value
Name P429_PRODDUP
Type Hidden
Value Protected Off
Type (under Source) Null
Type (under Default) Static
Static Value 0
4. Expand the Columns node (under the Order Details region), and
set the following attributes for PRODUCT_NAME column:
Property Value
Type Text Field
Heading Product Name
5. Switch to the Dynamic Actions tab. Right-click the main Events
node and select Create Dynamic Action. Set the following
attributes for this dynamic action. The dynamic action will execute
a JavaScript code that will be fired before submitting the page. The
JavaScript code is defined as a custom function – chkDUP() in step7.
Property Value
Name Check Duplicate Product
Event Before Page Submit
Click the Show node (under True) to set the following attributes:
Action Execute JavaScript Code
Code chkDUP()
6. Create another dynamic action. This time right-click the Change
node and select Create Dynamic Action from the context menu.
Set the following attributes for this dynamic action, which is being
created to fetch product name when a user selects a different
product in the Order Details interactive grid.
Property Value
Name Fetch Product Name
Event Change
Selection Type Column(s)
Region Order Details
Column PRODUCT_ID
Click the Show node (under True) to set the following attributes:
Action Execute PL/SQL Code
PL/SQL Code
select product_name into :PRODUCT_NA
from DEMO_PRODUCT_INFO
where product_id = :PRODUCT_ID;
Items to submit PRODUCT_ID
Items to Return PRODUCT_NAME
7. On the Rendering tab, click the root node - Page 429: Order
Details. Scroll down to the Function and Global Variable
Declaration section and append the following JavaScript function
after the existing code:
function chkDUP() {
var record;
var prodDUP=0;//Identify the particular interactive grid
var ig$ = apex.region("ORDER").widget();
var grid = ig$.interactiveGrid("getViews","grid");
//Fetch the model for the interactive grid
var model = grid.model;
//Select all rows
ig$.interactiveGrid("getViews").grid.view$.grid("selectAll");
//Fetch selected records
var selectedRecords = grid.view$.grid("getSelectedRecords");
for (idx1=0; idx1 < selectedRecords.length; idx1++) {
record = model.getRecord(selectedRecords[idx1][0]);
prodcode1 = model.getValue(record,"PRODUCT_NAME");
for (idx2=0; idx2 < selectedRecords.length; idx2++) {
record = model.getRecord(selectedRecords[idx2][0]);
prodcode2 = model.getValue(record,"PRODUCT_NAME");
if (prodcode1 == prodcode2 && idx1 != idx2) {
prodDUP=1;
break;
}
}
if (prodDUP == 1) {
break;
}
}
$s("P429_PRODDUP",prodDUP);
if (prodDUP == 1) {
alert("Duplication of product occurred - "+prodcode2);
}
}
The function is called from the Check Duplicate Product dynamic
action before the page is submitted. Initially the function identifies the
Order Details interactive grid through its static ID. Then, after fetching
the interactive grid's model, all rows in the interactive grid are selected.
The function then initiates a FOR loop, which loops through every
record in the interactive grid. In every loop, value from the Product
Name column is stored (in prodcode1 variable) and then compared with
another variable in an inner FOR loop. If a duplicate is found, the
duplicate switch is turned on – prodDUP=1. If the switch is turned on,
you see the client-side message specified in the alert function.
8. The JavaScript function in the previous step alerts you of duplicateproducts. After the alert, the page is submitted and the order is
saved with duplication. A server-side validation must also be
created to prevent this situation. On the Processing tab, right-click
the Validations node and select Create Validation. Set the
following attributes for the new validation, which evaluates the
value of P429_PRODDUP hidden page item when either Save or
Create buttons are clicked. If the value of this item is zero, the
order is processed. If it is set as 1 by the chkDUP function, an error
message is fired. Note that if a validation passes the equality test,
or evaluates to TRUE, then the validation error message does not
display. Validation error messages display when the validation fails
the equality test, or evaluates to FALSE, or a non-empty text string
is returned. Subsequent processes and branches are not executed if
one or more validations fail evaluation.
Property Value
Name Check Duplicate Products
Type (under Validation) Item = Value
Item P429_PRODDUP
Value 0
Error Message Duplicated product found – cannot proceed
further
Display Location Inline in Notification
Server-side Condition section
Type Request is contained in Value
Value SAVE,CREATE
Save and run the module. Create a new order. Initially the Product
column in the interactive grid defaults to Air Jordan 6. Select a different
product to fire the dynamic action and fetch the product name in the
Product Name column. Add another row and select the same product on
the new row. Input unit price in both rows and click Create. First, you
will get the client-side product duplication message from the JavaScript
function followed by the error message defined in the validation.
## 7.11 Interactive Grid Native PDF Printing
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.
1. Open the Orders Interactive Report page (Page 4) in Page
Designer.
2. Right-click the Orders interactive report region, and select
Duplicate from the context menu. A copy of this region will be
created.
Figure 7-35
3. Click the new Orders region, and change its Type property in the
Identification section from Interactive Report to Interactive Grid.
4. Click the Attributes node under the new Orders region. In the
properties pane, scroll down to Download section and ensure that
PDF option (under Download | Formats) is checked. The checkeddownload formats can be utilized by users to download the
currently displayed columns in the interactive grid.
5. Save and run the page. The page should now have two regions.
Scroll down a bit to see the interactive grid region. From the
interactive grid’s Actions menu, select Format | Control Break.
6. On the Control Break dialog, select Order Month from the
Column list and click Save.
Figure 7-36
7. Next, select Actions | Format | Highlight. Set the following
parameters in the Highlight dialog. Once you hit Save in the
Highlight dialog, rows with 1000 or greater amount in the Order
Total column will be highlighted.Figure 7-37
8. Click the Actions menu again and select Download. In the
Download dialog, select PDF and other options as illustrated in the
following figure and click the Download button. The output of the
interactive grid will be downloaded as a PDF to your device.Figure 7-38
The following figure illustrates the downloaded PDF. As you can see both
highlight and control break formattings are preserved in the PDF.Figure 7-39
Summary
Here are the highlights of this chapter:
Master Detail – You learned how to implement Master Detail page
feature to handle data in two relational tables and went through theauto-generated page components added by the wizard to
transparently manage the order processing module.
Interactive Report – Created an interactive report and learned how
to alter the report layout by applying highlighting, sorting, and
using aggregate functions. You also applied Control Breaks to
group related data and added Chart and Group By Views.
Primary, Public, and Alternative Interactive Report – You created
three variants of the interactive report and went through the
concepts behind these variants.
Wizard Steps – Learned how to create wizard-like interfaces to
perform related tasks in a sequence.
Copy Page Utility – The chapter provided a shortcut to utilize an
existing page with all functionalities using a different number and
for a different scenario.
Oracle APEX Collection – You learned how to use collections to
store and retrieve rows and columns information in session state.
Custom Processes and Dynamic Actions – In addition to the auto-
generated components and processes, you learned how to manually
add your own processes and other components.
Using HTML in PL/SQL Code – You used PL/SQL to have more
control over dynamically generated HTML within a region.
Using CSS in Oracle APEX Pages – You applied styling rules to
give the page a more professional look.
Simple Approach – Besides the advance techniques, you also
learned how to create this module using a simple approach.
Looping through Interactive Grid – In the final section of this
chapter you learned how to loop through interactive grid records.
You usually execute this procedure when you need to perform some sort of validation on the data in an interactive grid prior to
storing it in your database.
# 08. Graphical Reports & Mobile Integration
page 304/419
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....**Graph. & Mobile**.....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)
# 09. Produce Advance Reports
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....**Adv. Rep**.....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)
# 10. Authorization - Managing Users & App Access
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....**Authoriz**.....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)
## 11.1 Faceted Search
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz)....**Search**.....[Style Theme r.](#themeroll).....[Style buttons](#style_buttons).....[Calendar](#calendar).....[ Deploy](#deploy)
## 11.2 Theme Roller – Style Your Application
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search, style, calendar](#searchstylecal).....**Style Theme r.**.....[Style buttons](#style_buttons).....[Calendar](#calendar).....[ Deploy](#deploy)
## 11.3 Styling Buttons
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz)....[Search, style, calendar](#searchstylecal).....[Style Theme r.](#themeroll).....**Style buttons**.....[Calendar](#calendar).....[ Deploy](#deploy)
## 11.4 Manage Events via Calendar component
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz)....[Search, style, calendar](#searchstylecal).....[Style Theme r.](#themeroll).....[Style buttons](#style_buttons).....**Calendar**.....[ Deploy](#deploy)
# 12. Deploy
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....**Deploy**
## 12.1 About App Deployment dev to production environment
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.
1. **No Deployment**: dev environment becomes prod environment and nothing is moved to another computer. In this option **users are provided with URL** to access app.
2. **App**: if target computer is already running production Ora DB with all objects. You export app and import it into target DB.
3. **App and Table Structures**: you create two scripts, one for your app and another for DB tbl structures using **Generate DDL utility** in SQL Workshop.
4. **App and DB obj. with Data**: you deploy your app along with all DB objects using **data pump utility**
5. **Individual Components**: With dev phase going on, you can supplement your deployment plan by exporting only selected components.
## 12.2 Export App
https://docs.oracle.com/en/database/oracle/application-express/20.1//htmdb/managing-application-backups.html
For simplicity, we will **deploy app in the same workspace**. Same technique is applicable to new workspace or prod env.
1. Sign in to APEX and edit (click) Sales Web Application.
2. Click Export/Import icon. On the ensuing page, click Export icon.
4. In "Choose App" section, set App to Sales Web App, and click Export button.
5. A file something like **f145615.sql** will be saved in Download folder under My Doc or in another folder specified in your browser.
## 12.3 Data pump utils DB 20.1c expdp/impdp (since 10g)
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**.
## 12.4 Import App
Import exported app **f145615.sql into existing workspace you are connected to with a different WS ID**.
1. Go to App Builder interface and click Import icon.
2. On the Import screen, click the Choose File button and select exported file (f145615.sql). For File Type, select **DB App, Page or Component** Export and click Next.
3. After a while a message The export file has been imported successfully will appear. The status bar at the bottom of your screen
will show progress during the upload process. Click Next to move on.
4. On the **Install screen**, select default value for Parsing Schema. Set **Build Status** to Run and Build App, Install As Application to Auto Assign New Application ID, and click the Install Application button. After a short while, the application will be installed with a new ID for you to give it a test-run.
5. On the Install page, click the Run Application button. You will encounter an error saying “ You are not authorized to view this application, either because you have not been granted access, or your account has been locked. Please contact the application administrator. ” Application users are not exported as part of your application. When you deploy your application you will need to manually manage your user to role assignments. Roles are exported as part of an application export and imported with application imports. Execute the following step to cope with this error.
6. On the Install page, click the Edit Application button. Go to Shared Components, and click Application Access Control in the Security section. Using the Add User Role Assignment button (A) add the three users as shown in the following figure. In Shared Components, click Security Attributes. Click the Authorization tab, and set Authorization Scheme to No application authorization required (A). Apply the change. Now you will be able to access the application.
## 12.5 Prevent App Modification and Remove Developers Toolbar
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.
1. Open the new application you just imported in the designer interface.
2. Click Shared Components.
3. Click the Globalization Attributes link (under Globalization).
4. Click the Definition tab.
5. Click the Availability tab, set Build Status to Run Application Only, and click Apply Changes.
6. Go to the App Builder interface and see that the new application doesnt have the Edit link. Click the Run button and provide yoursign in credentials. Note that the Developer Toolbar has disappeared as well.
7. To make the application editable again, select App Builder | Workspace Utilities | All Workspace Utilities (A), as illustrated in the following figure. On the Workspace Utilities page, select Build and App Status (B) from the right sidebar. On Build Status and Application Status page, click the application ID in the first report column, change the Build Status to Run and Build Application, and apply the change.
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](#top).....[Back](#goAPEXConcepts)
## 2.2 Applications, App. builder
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....**App. builder**.....[Page](#page).....[Environm](#environm).....[URL](#url) .....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)
1.
## https://apex.oracle.com/pls/apex/
(+ f?p=4550:1:117485610537637:::::)
2. button "Sign in" opens same URL, page "App Builder" :
Page "App Builder" : Create and manage my apps and their pages :
1. "**Create**" icon functionality :
1. New App based on **tables** you select or by providing a valid **SQL**
2. New App from File :
**Load** Data Wizard appears to load definitions & data in : CSV, XLSX, XML, TXT or JSON
or Copy and Paste **column delimited** data.
After loading data into DB tbl, **wizard creates some app. pages based on new tbl**
3. **"App Gallery" -> "Productivity Apps"** eg proj. mngmnt, surveys, shared calendars, and tracking apps, can be installed, run, and removed.
2. "**Import**" icon
3. "**Dashboard**" icon
4. "**Workspace**" Utilities icon
## 2.3 Page eg App. builder -> click my "Sales Web app" icon
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....**Page**.....[Environm](#environm).....[URL](#url) .....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#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**:
1. **Show Page** is page rendering process, runs **when you request page** using a URL. It assembles all the page attributes including regions (stacked canvases), items, and buttons into a viewable HTML page. When you request a page using a URL, the engine is running Show Page.
2. **Accept Page** performs page processing **when you submit page**. It performs computations, validations, processes, and branching.
1. APEX engine is running **Accept Page** then performing page **processing** during which it
2. saves submitted values in session cache
3. and then performs any computations, validations, or processes.
You can create following **types of pages** for your app :
1. **Blank Page** Creates a page without any built-in functionality
2. Report Used to present a SQL query in a formatted style
1. Interactive Report
2. **Interactive Grid**
3. Classic Report
3. Form
1. Editable Interactive **Grid**
2. **Report with Form**
4. **Master Detail**
1. Stacked
2. Side by Side
3. Drill Down
4. Plug-ins
5. Chart
6. Tree
7. Calendar
8. Data Loading
## 2.4 Region, Items, Buttons
### 1. Region (stacked canvas) serves as container for content
**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.
### 2. Items
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).
### 3. Buttons to submit page or to take users to another page
Redirect :
1. within the same site where a user submits a page, the Oracle APEX engine executes some processes associated with a particular button and uploads the page's item values to the server - see Figure 1-3 in chapter 1
2. or to a different site
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**.
#### Buttons are used to:
1. **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.
2. Take user to another page within the same app with optional additional properties for **resetting pagination**, setting request value, clearing cache, and setting item values on the target page.
3. Redirect to another URL.
4. Do nothing eg if buttons behavior is defined in a Dynamic Action.
5. 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.
## 2.5 APEX Web-based app Development Environment to build web apps
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....**Environment**.....[URL](#url) [Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)
You are not required to install any client software to develop, deploy, or run Oracle APEX applications.
Primary APEX **tools** :
1. **App Builder** - to create dynamic database driven web appls. Here you create and modify your applications and pages. It comprises following :
1. Create: new apps. See section 2.3.2
2. Import: entire Oracle APEX apps developed somewhere else, along with related files.
3. Dashboard: metrics about apps in your workspace eg: Developer Activity, Page Events, Page Count by App, Most Active Pages...
4. Workspace Utilities: Most significant is **Export** app and component metadata to **SQL script file format** that you can import on the same or another compatible instance of APEX.
2. **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:
1. Object Browser: to review and maintain database objects (tables, views, functions, triggers...).
2. SQL Commands: to run SQL queries.
3. SQL Scripts: to **upload and execute** script files.
4. Utilities: eg Query Builder, Data Workshop, Generate DDL, Schema Comparison...
5. RESTful Services: to define Web Services using SQL and PL/SQL against DB
3. **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.
4. **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:
1. **Development** Environment: Here you have complete control to build and test your applications.
2. **Runtime** Environment: After completing **development** and **testing** phase, you implement your apps in a **production** environment where users can only run these applications and do not have the right to modify them.
## 2.6 Page Designer : App Builder -> Sales app -> some page
1. **page top, right**
### 2.6.1 Page Designer toolbar (A)
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.
2. **page top, left**
### 2.6.2 Tree Pane contains four icons (were tabs) and tree nodes
Tree nodes : regions, items, buttons, app logic (eg computations, processes, validations), dynamic actions, branches, shared components.
4 icons :
1. **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 :
1. pre-Rendering stage **pre**liminary **computations** are performed
2. main rendering stage comprises **regions and its components**
3. **Post**-Rendering stage also carries **computations** that occur after rendering a page.
2. **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.
3. **Processing icon** (D) - Use this icon to specify app logic eg :
1. **Computations** are APEX's declarative way of **setting an item's values** on page. These are units of logic used to **assign session state to items** and are executed ** at the time page is processed**.
2. **Validation** is **server-side** mechanism designed to check and validate quality, accuracy, and consistency of page submitted data, prior to saving it into DB. If a validation fails, further processing is aborted by the server and existing page is redisplayed with all inline validation errors.
3. **Processing** are **logic controls used to execute DML** (Data Manipulation Language) **or PL/SQL**. Processes are executed **after page is submitted** - typically when user clicks button.
4. **Branches** enable you to create logic controls that **determine how user navigates through app**.
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.
4. **Page Shared Components icon** (E) - Displays shared components associated with this page. The list on this tab gets populated automatically when you use shared components on a page.
3.
### 2.6.3 Central Pane has two sections
Upper section contains three tabs: Layout, Page Search, Help. Lower pane is called **Gallery** and it is **associated with Layout tab**.
1. Layout (F) is a **visual representation of the regions, items, and buttons** that define a page. You can add new regions, items and buttons to a page by selecting them from Gallery at the bottom of the page, drag, drop.
2. Page Search (G) - to search all **page metadata** including regions, items, buttons, dynamic actions, columns, and so on.
3. Help (H) displays **help text for properties (purpose) in Property Editor (right pane)**. Click a property in the Property Editor and then click Help tab (in the Central pane). As you move from one property to next in property editor, Help tab displays it's help text.
4. **right pane**
### 2.6.4 Property Editor (prop, value) for current component in either Tree View or Layout tab
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.
## 2.7 APEX URL Syntax
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page) [Environm](#environm).....**URL** .....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)
### f?p URL Syntax
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**.
### Friendly URL Syntax (APEX release >= 20)
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:
1. 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
2. r is the router shortcut. This value is a constant and should never be changed
3. 4500 is the application id
4. home is the alias of the page being displayed. **If no alias is defined, the page number is displayed instead**.
5. ?session=16167973992554 identifies the session ID.
## 2.8 Substitution Strings (&varname.) and Bind Variables (:item_name)
&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:
1. Use **bind variable** to reference the variable from **within SQL or PL/SQL** code,
2. Use **substitution string** from **within an HTML expression** so :
## Using Substitution Strings
1. 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.
2. **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**
3. 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.
[Top](#top).....[Back](#shared)
## 3.1 Shared (common, appglobal) Components across all app pages eg menu options
"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.
## Shared comp. list 1. APP LOGIC SECTION :
### 3.1.1 App Definition Attributes
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.
### 3.1.2 Application Processes
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 :
1. page-level On Demand process
2. 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.
## Shared comp. list 2. SECURITY SECTION
### 3.1.3 Authentication Schemes
(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
1. built-in APEX Auth. scheme
2. or using a custom scheme with more control.
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:
1. 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**.
2. 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.
3. 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.
4.
### DB Account Credentials authentication scheme
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.
5. 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.
6. 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.
7. 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.
8. 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:
1. Database authentication or other methods are not adequate
2. You want to develop your **own login form** and associated methods
3. You want to control security aspects of session management
4. You want to record or audit activity at the user or session level
5. You want to enforce session activity or expiry limits
6. Your **app consists of multiple apps that operate seamlessly (eg, more than one app ID)**
### 3.1.4 Authorization Schemes
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).
## Shared comp. list 3. OTHER COMPONENTS
### 3.1.5 LOVs (Lists of Values) are defined by running LOV wizard - static and dynamic
Once created, LOVs are stored in LOVs repository and are utilized by page items Px\_...
1. static LOV displays and returns predefined values such as Yes and No
2. dynamic list is populated using SQL query
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).
### 3.1.6 Plug-Ins framework - was introduced in Oracle APEX 4.0
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.
### 3.1.7 Shortcuts to avoid repetitive coding of HTML or PL/SQL functions
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:
1. PL/SQL Function Body
2. HTML Text
3. HTML Text with Escaped Special Characters
4. Image
5. Text with JavaScript Escaped Single Quotes
6. Message
7. Message with JavaScript Escaped Special Quotes
## Shared comp. list 4. NAVIGATION
### 3.1.8 Lists - collections of links
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.
### 3.1.9 Navigation Menu
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.
### 3.1.10 Breadcrumb
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.
### 3.1.11 Navigation Bar List
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.
## Shared comp. list 5. USER INTERFACE
### 3.1.12 User Interface Attributes
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.
### 3.1.13 Themes and Templates
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:
1. Workspace administrators can create themes that are available to all developers within the workspace. Such themes are called workspace themes.
2. Instance administrators can create public themes by adding them to the Oracle APEX Administration Services. Once added, these public themes are available to all developers across all workspaces in an instance.
3. Apps you create with the Create app Wizard use Universal Theme. Universal Theme - 42 features a responsive design, versatile UI components and enables developers to create web apps without extensive knowledge of HTML, CSS, or JavaScript. Responsive design enables you to design web pages so that the layout fits the available space regardless of the device on which page displays (eg desktop computer, laptop computer, tablet, or smartphone).
## Shared comp. list 6. FILES
### 3.1.14 Static App and Workspace resource Files (img, CSS, JS)
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**.
## Shared comp. list 7. DATA SOURCES
## Shared comp. list 8. REPORTS
### 3.1.15 Report Queries
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.
### 3.1.16 Report Layouts
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.
## Shared comp. list 9. GLOBALIZATION
### 3.1.17 Globalization Attributes
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.
### 3.1.18 Translate app
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 :
1. map the primary and target language
2. seed and export text to a translation file
3. translate the text
4. apply the translation file
5. publish the translated app.
[Top](#top).....[Back](#gosampleIG)
### App Gallery "Sample Database Application#
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.
## 5.6 IG : Learn IG - install "Sample Interactive Grids" app
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:
1. Rows are fixed height and columns have a specific width that can be adjusted by dragging the border between column headers (G) or with Ctrl+Left/Right keys when the column header has keyboard focus.
2. Columns can be reordered with drag and drop (dragging the handle (E) at the start of a column heading) or with Shift+Left/Right keys when the column header has keyboard focus.
3. Columns can be sorted using the buttons (F) in the column heading or by using Alt+Up/Down key combination. Use the Shift key to add additional sort columns.
4. Columns can be frozen using the Freeze button (D) in the column heading pop-up menu. For example, to freeze the customers’ name column (on Page 2), click the Name column heading. A pop-up menu will appear with four options: Hide (A), Control Break (B), Aggregate (C), and Freeze (D). Select Freeze. Drag the border between the Name and Address columns (F) toward right to expand the Name column.
5. By default the toolbar and column headings stick to the top of the page and the footer sticks to the bottom when scrolling.
6. By default pagination uses a "Load More" button.The grid is keyboard navigable with a focused cell and current selected row (single selection by default).
7. Toolbar includes a Reset button by default, which restores all the report settings to their defaults.
### IG : Install Sample Interactive Grids app
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.
1. App Gallery menu -> **Sample Apps**.Figure 5-10
2. Click icon **Sample Interactive Grids app** - functionality of **new APEX Interactive Grid**. Declarative features :
1. Reporting capabilities
2. Pagination options
3. Editing capabilities
4. Advanced techniques
3. 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
4. On Install App wizard screen, accept the default Authentication scheme (APEX Accounts) and click Next.
5. On the next wizard screen, click Install App button. After a while, you will see the message Application installed.
6. 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.
### 5.6.1 IG : Column Groups in IG (Interactive Grid)
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:
1. Create a new page in your Sales app by clicking the Create Page button. Select the Report option in the first wizard screen, followed by the Interactive Grid option on the next screen.
2. Enter 100 for Page Number, Column Groups for Page Name, set Page Mode to Normal, Breadcrumb to Breadcrumb, Parent Entry to No Parent Entry, Entry Name to Column Groups, and click Next.
3. Select the default Navigation Preference Do not associate this page with a navigation menu entry, because this page is not associated with our sales app. Click Next.
4. On Report Source screen, keep default Off value of Editing Enabled, set Source Type to SQL Query, and enter the following SQL Statement in Enter a SQL SELECT Statement text area.
```
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, onleave, notes, flex4 as tags
FROM EBA_DEMO_IG_EMP
```
5. Click Create button to complete the page creation process.
6. In Page Designer, under Column Groups region (in the Rendering tree), right-click the Attributes node, and select Create Column Group (A) from the context menu. In the Properties pane, set the Heading attribute for this new group to **Identity** (cols emp name till dep).
7. Repeat step 6 to create **two more groups**. Enter **Compensation** (cols salary, commission) and **Notes** for their headings. The three column groups should look like (B).
8. Under the Columns Group region, expand the Columns node. Click the **EMPNO column and set its Type to Hidden**.
9. Set appropriate column headings, as shown in Figure 5-11.
10. Use the following table to associate each column with a group created in steps 6 & 7. To establish this association, click any column (ENAME, for example), scroll down to the Layout section, and set the Group property as follows:
Column Group Property
1. ENAME Identity
1. JOB Identity
2. MGR Identity
3. HIREDATE Identity
2. SAL Compensation
1. COMM Compensation
**DEPTNO Identity**
3. ONLEAVE Notes
1. NOTES Notes
2. TAGS Notes
11. 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.
### 5.6.2 IG : Editing Data in IG (Interactive Grid)
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:
1. Normally the grid is in navigation mode where arrow keys move from cell to cell. To enter edit mode, press the Edit button (A).
2. Alternatively, double-click a cell or press either the Enter key or F2 key in a cell.
3. To exit edit mode, press the Edit button (A) again or press the Escape key in a cell.
4. Use the Delete key on your keyboard to delete the selected rows.
5. Use the Insert key to add a row.
6. Second column (B) is a menu. It allows you to perform actions on the selected row such as Delete or Duplicate. Use the Revert Changes option from this menu to revert a record marked for deletion.
7. Editing is also supported in Single Row View. All edits are stored locally until you press the Save button (C). If you try to leave the page while there are unsaved changes you will be notified.
8. Any action that causes refreshing the data such as changing a filter or sorting will warn if there are unsaved changes. Pagination does
not affect changes.
Execute following steps to enable editing in the Interactive Grid you added to Page 100 in the previous section.
1. Click the Attributes node (A) under the Column Group region and turn on the Enabled attribute (B). Make sure all three operations (C) are also enabled. Figure 5-14
2. Scroll down to the Toolbar section to ensure that the Show property is turned on and the two toolbar buttons (Reset and Save) are also enabled. Reset removes any customizations, such as filters, column width, ordering, and so forth, and reloads the report definition from the server. Save will only save changes made to this interactive grid, without needing to save the whole page. The save button will be displayed only when the interactive grid is editable and the end user has the authorization to add, update, or delete records.3. After making these changes, save and run the page. Notice that the row selector (D) and the Selection Actions menu (B) columns (in Figure 5-13) are added automatically. A process named Save Interactive Grid Data is also added to the Processing tab with an Interactive Grid - Automatic Row Processing (DML) type process to perform DML processing for you without writing any SQL code. This process is added by default when an Interactive Grid is made editable. Play around with the interactive grid by adding, modifying, and deleting rows.
### 5.6.3 IG : Changing Column Type
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
1. 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.
2. 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
```
3. 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.
4. Click the ONLEAVE column and set its Type to Switch. This will display either On or Off state for this column.
5. 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
6. 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.
### 5.6.4 IG : Protecting Rows in Interactive Grid
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.
1. With Page 100 being displayed in the Page Designer, click the Column Groups region and amend the SELECT statement as follows (the amendment is shown in bold):
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,onleave,notes, flex4 as tags,
**case when JOB = 'MANAGER' or JOB = 'PRESIDENT' then ''**
**else 'UD' end as CTRL**
FROM EBA_DEMO_IG_EMP
2. After amending the SQL query you will see the CTRL column under the Columns node. Click this column and set its Type to Hidden. Also turn on the Query Only property (under Source). For explanation, see Chapter 7 section 7.4.2.
3. Click the Attributes node under the Column Groups region and set Allowed Row Operations Column (in the Edit section) to the CTRL column.4. Save the page and run it. Click the Edit button. Rows that cannot be edited or deleted are grayed out (A) in the edit mode.
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.
### 5.6.5 IG : Scroll Paging
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.
1. Create a new page using the instructions mentioned earlier in Section 5.6.1. Set Page Number to 111, set Page Name to Scroll Paging, and enter the following SELECT statement. Rest of the page properties will remain the same. SELECT name, country, rating FROM EBA_DEMO_IG_PEOPLE
2. In the Page Designer, click the Attributes node under the Scroll Paging region. Set Type under the Pagination section to Scroll and turn on the Show Total Row Count.
3. Save and run the page and scroll down using your mouse wheel to test this amazing feature. You will see total number of records at the bottom of the Interactive Grid.
### 5.6.6 IG : Master Detail. And Detail. And Detail...
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.
1. 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.
2. 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.
3. 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**
1. Title Continents
2. Type (under Identification) Interactive Grid
3. Type (under Source) SQL Query
4. SQL Query SELECT * FROM MD_continent
After entering the SQL query, click anywhere outside the query box. Expand the Columns node under this region. Click the CONTINENT_ID column. Set its Type to Hidden and turn on the Primary Key property (under Source). You must define a primary key column for an interactive grid region, which is required to establish a master detail relationship.
4. 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**
1. Title Countries
2. Type (under Identification) Interactive Grid
3. Type (under Source) SQL Query
4. SQL Query SELECT * FROM MD_country
Expand the Columns node under the Countries region. Click the COUNTRY_ID column. Set its Type to Hidden and turn on the Primary Key property (under Source). You set the Primary Key property to Yes, because this region will act as a master for the Cities region created in the next step. Now, associate this detail region to its master (Continents). Click the Countries region and set the Master Region property (under Master Detail) to Continents. This should be set when this region is the detail region in a master-detail relationship with another region on the page. For the master-detail relationship to work correctly, you must also select the column(s) in the detail region, which are foreign keys to the master region, by setting the Master Column property. Click the CONTINENT_ID column (a foreign key) in the Countries region. Set its Type property to Hidden and Master Column (under Master Detail) to CONTINENT_ID, which references the same column in the master region.
5. Create another region and place it under the Countries region. This region will show a list of cities when you select a country from its master region. Set the following properties for this region:
**Property Value**
1. Title Cities
2. Type (under Identification) Interactive Grid
3. Type (under Source) SQL Query
4. SQL Query SELECT * FROM MD_cityMaster Region Countries
Expand the Columns node under the Cities region. Click the CITY_ID column. Set its Type to Hidden and turn on the Primary Key property (under Source). Click the COUNTRY_ID column in this region. Set the Type of this column to Hidden and Master Column to COUNTRY_ID to point to the same column in the Countries region.
6. Create the last region to display population of a city.
**Property Value**
1. Title Population
2. Type (under Identification) Interactive Grid
3. Type (under Source) SQL Query
4. SQL Query SELECT * FROM MD_population
5. Master Region Cities
Expand the Columns node under the Population region. Click the POPULATION_ID column. Set its Type to Hidden. Since this is the last region, you do not need to specify this column as a primary key. However, you have to set a couple of properties for the CITY_ID column in this region to associate it with its master. Click the CITY_ID column, set its Type property to Hidden and Master Column to CITY_ID. That’s it!
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
### Summary of 5.6 IG :
Techniques in this chapter:
1. Declaratively created **report and form pages and linked them** together
2. Placed **form input items using 12 columns grid layout**
3. Changed default **type of an item and used LOV**
4. Created **validations** to prevent customer record deletion with existing orders and to check customers credit limits.
5. Used a dynamic action to **automatically refresh page**
6. Used various features of **new Interactive Grid**
7. Learned how to **change types of columns**
8. Got hands-on exposure to **Master Detail Detail feature**.
[Top](#top).....[Back](#goDBobjects)
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
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;
### prompt 1. DEMO_ CUSTOMERS
```
--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;
/
```
### prompt 2. DEMO_ PRODUCT_ INFO
```
--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;
/
```
### prompt 3. DEMO_ STATES (no PK)
```
CREATE table "DEMO_STATES" (
--"STATE_ID" VARCHAR2(2),
"ST" VARCHAR2(30),
"STATE_NAME" VARCHAR2(30)
)
/
```
### prompt 4. DEMO_ ORDERS
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
```
### prompt 5. DEMO_ ORDER_ ITEMS
```
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")
/
```
[Top](#top).....[Back](#goDBobjectsData)
### tbl01 DEMO_CUSTOMERS
1. Click the SQL Workshop menu (A).
2. 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).
3. 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.
4. 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
5. 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
6. Scale column specify the number of characters each column will hold
7. 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
8. 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
```
### tbl02 DEMO_PRODUCT_INFO
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
```
### tbl03 DEMO_STATES no PK (51 row)
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
```
### tbl04 DEMO_ORDERS
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,
```
### tbl05 DEMO_ORDER_ITEMS
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"
```
## Why would I want to build a Single Page Application in APEX?
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 :
1. 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.
2. 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.
3. 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.
4. 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)**.
5. low code design makes it easy for beginners to become productive, whilst it provides full control for experts.
6. 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.
# Help region
```
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) != '
' then
sys.htp.p('
');
end if;
sys.htp.p(apex_application.do_substitutions(c1.help_text));
if substr(trim(c1.help_text), -4) != '
' then
sys.htp.p('');
end if;
end if;
end loop;
```
___
[Top](#top)
>### MD to HTML converters on inet
1. **https://www.tutorialspoint.com/online_markdown_editor.php** or https://markdowntohtml.com/
2. or https://www.browserling.com/tools/markdown-to-html (many converters)
3. or files convert : https://products.aspose.app/pdf/conversion/md-to-html to many formats
4. Links not working : http://demo.showdownjs.com/ (no HTML source)
NOT WORKING : https://daringfireball.net/projects/markdown/dingus or https://pandoc.org/try/
>https://www.w3schools.com/html/html_symbols.asp
>https://www.degraeve.com/reference/specialcharacters.php
[Top](#top).....[Apexws_cloud](#Apexws_cloud).....[App. builder](#app_builder).....[Page](#page).....[Environm](#environm) .....[URL](#url).....[Sales](#sales).....[SHARED C.](#shared)......[Rep.List](#rep_list).....[Ord.WizList](#ord_wiz_list).....[Top. Navig](#top_navig).....[LOVs](#lov)....[IMGs](#img)
.....[Home p.](#home).....[Buttons](#buttons).....[PgStyles](#pgstyles).....[Cust](#cust).....[Prod](#prod).....[Order](#order).....[Graph. & Mobile](#gra_mob).....[Adv. Rep](#advrep).....[Authoriz](#authoriz).....[Search Style Cal.](#searchstylecal).....[ Deploy](#deploy)