1

Oracle APEX 5.2 / 18.1 - The Golden Nuggets

Dietmar Aust
Opal-Consulting, Köln
www.opal-consulting.de
About Dietmar. 2

•  Dipl.-Inform. Dietmar Aust, Freelance Consultant

•  Master's Degree in Computer Science (MSCS)

•  Building Oracle based Web Applications since 1997

•  Portal, Forms, Reports, OWA Toolkit, now APEX!

•  1997-2000: Consultant at Oracle Germany

•  Since 09/2000: Freelance Consultant, Since 2006 – APEX only!

•  Author of the JasperReportsIntegration toolkit

•  http://www.opal-consulting.de/tools

•  2015 Database Developer of the year in the ORDS category

 3

Feature Analysis
How to find the new features? 4

https://apexea.oracle.com/

Documentation (BETA)
https://docs.oracle.com/database/apex-18.1/

Actually compute the delta

between different APEX
versions:
-  APEX Dictionary
-  Packages
Wie wurde analysiert? 5
How to find the new features? 6

In Debug-Mode (logged in as developer) APEX uses the

uncompressed Javascript Files (Source Code)
How to find the new features? 7
 How to find the new features? 8

Pre Definition in User-Interface > Concatenated Files

5.2 Sample: Oracle APEX Page Designer, Application 4000, Page 4500
Sample Application will be made available 9
 10

Architecture
Additional x11-x20 … Parameters in AJAX calls 11

The big archtitecture change was in APEX 5.1 => JSON processing

Small change in 5.2: Additional Parameters for AJAX calls x11-x20

Javascript on client PL/SQL on server side

 12

Application Builder
Resource Links 13

New Resource Links on APEX Workspace Homepage

•  Shortcuts on Community Site

•  Twitter, Youtube, LinkedIn, Facebook

Great idea … but everybody has different needs

Custom Resource Links 14

Customizing the Workspace or Instance Announcement

(HTML is only allowed here, not in the news items)
Custom Resource Links 15

Customizing the Workspace Announcement

Spotlight Search 16

Powerful Search Feature (like on the Mac) to search for

•  Workspace and application areas and navigates there (Packaged
Apps, SQL Workshop, Shared Components, ...)
•  Applications
•  Pages
•  Application content
•  within pages, etc.

Context dependent (Workspace, Application, Page Designer)

Spotlight Search 17
Spotlight Search 18

How to figure out the right keyboard shortcut in your language?

CTRL-’ on an English layout

… what is the representation of ‘ in a different language?

CTRL-ä on a German layout

Compatibility Mode 19

The compatibility mode changes the behaviour of the APEX engine

at runtime

=> balancing new features /

security / stricter checks and
easy upgrades

=> No Changes in APEX 5.2

 Default Schema 20

Pre
5.2
Set default schema for user in workspace
 Default Schema 21

Pre
5.2 Default schema is used:
–  SQL Workshop
–  Installation of packaged applications
User Interface Attributes > Collapsed by Default 22

Universal Theme starts with left navigation side collapsed

User Interface Attributes > "Built with APEX" Footer 23

Universal Theme has a new footer

Can be turned off in

User Interface Attributes
 24

Application Building
New Authentication Scheme 25

New Authentication Scheme

–  Social Sign-In

Authentication Provider
New Authentication Scheme 26

About OAuth2 and its flow

https://developers.google.com
/identity/protocols/OAuth2
New Authentication Scheme 27

Example: Steps to authenticate with Google

1.  Register oauth2 client with Google

2.  Create web credentials in APEX
3.  Create authentication scheme and use credentials
New Authentication Scheme 28

1.  Register client with Google

Register client: https://console.developers.google.com/
New Authentication Scheme 29
New Authentication Scheme 30

2.  Create web credentials in APEX

New Authentication Scheme 31

3.  Create authentication scheme and use credentials

New Authentication Scheme 32

Scopes
https://developers.google.com/identity/protocols/
googlescopes#google_sign-in
Switching Authentication Schemes at Runtime 33

Why use multiple authentication schemes at runtime?

Switching Authentication Schemes at Runtime 34

Step 1 – make apex login current scheme

Step 2 - Enable switch in session for the Google scheme

Switching Authentication Schemes at Runtime 35

Step 3 - Add button to login page and use a URL to switch

to the Google scheme

Request variable:
APEX_AUTHENTICATION=<Scheme Name>

f?p=&APP_ID.:1:&APP_SESSION.:APEX_AUTHENTICATION=Google
Switching Authentication Schemes at Runtime 36

Post Authentication Handler – change username

-- derive new username from :APP_USER
case :app_user
when '[email protected]' then
l_new_app_user := '[email protected]';
else
-- will not change
l_new_app_user := l_orig_app_user;
end case;

-- set user for this session
apex_custom_auth.set_user( p_user => l_new_app_user );
Switching Authentication Schemes at Runtime 37

Post Authentication Handler – get scope attributes

-- get attribute by name
apex_json.get_varchar2( p_path => 'id_token' );
apex_json.get_varchar2( p_path => 'access_token' );

-- or loop over all attributes
l_idx := apex_json.g_values.first;

while (l_idx is not null)
loop
l_value := apex_json.g_values(l_idx);
:p2_user_info := :p2_user_info ||
get_output_str( p_idx => l_idx, p_value => l_value );
l_idx := apex_json.g_values.next(l_idx);
end loop;
Switching Authentication Schemes at Runtime 38

Post Authentication Handler – make additional request

-- second request, to retrieve additional user information
c := apex_web_service.make_rest_request (
p_url => 'https://www.googleapis.com/oauth2/v3/userinfo',
p_http_method => 'GET',
p_credential_static_id => 'GOOGLE' );

apex_json.parse(c);
Application Access Control 39

Management of Roles and User / Role

Mappings as part of the application

Why?
•  Convenience, almost every application
needs it
•  Used for blueprint applications
Application Access Control 40
Application Access Control 41
Application Access Control 42

Security Setting to define the meaning of “ROLE”

Application Access Control 43

This makes a difference when using Authorization

Schemes
Application Access Control 44

Summary
•  Roles are exported with the application
•  User / Role Mappings are NOT
•  Typically use local user table for
additional details (email, first name, …)
•  API provided: APEX_ACL
•  Data dictionary views for viewing and modifying the
data (INSERT, UPDATE, DELETE on
APEX_APPL_ACL_USERS)
Application Settings 45

Maintain application specific configuration

settings, e.g. Environment, Email servers,
...

Why?
•  Application and Source Code should be identical
between environments, specific go into config table
Application Settings 46

On Upgrade Keep Values => respect the value upon

upgrade
Email Templates 47

Manage Email
Templates
as part of
the application
Email Templates 48
Email Templates 49

Send email via API

begin
apex_mail.send (
p_to => '[email protected]',
p_template_static_id => 'TEST1',
p_placeholders => '{'||
' "CUSTOMER_NAME":' || apex_json.stringify( 'Dietmar' )||
' ,"ITEMS_ORDERED":' || apex_json.stringify( 6 )||
' ,"MY_APPLICATION_LINK":' || apex_json.stringify( '' )||
' ,"ORDER_DATE":' || apex_json.stringify( '' )||
' ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( '' )||
' ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( '' )||
' ,"SHIP_TO":' || apex_json.stringify( '' )||
'}' );
end;

Email Templates 50
Email Templates 51

Just generate the HTML via the API

declare
l_subject varchar2( 4000 );
l_html clob;
l_text clob;
begin
apex_mail.prepare_template (
p_static_id => 'ORDER',
p_placeholders => '{ "ORDER_NUMBER": 5321, "ORDER_DATE": "01-Feb-2018",
"ORDER_TOTAL": "$12,000" }',
p_subject => l_subject,
p_html => l_html,
p_text => l_text );
end;
Email Templates 52

Manage Email Templates as part of

the application

Good separation of Templates and

data in placeholders

Easy to use

No complex logic supported

 53

Items and Regions

Tree Region - Enhancements 54

•  The Advanced: JavaScript Initialization Code attribute is

supported.
•  JSDoc for Tree Region, easier to customize the tree
•  Support Dynamic Action Event: Selection Change [Tree].
•  Advanced Config option (nodeSelector) to support checkbox
selection.
•  Tree region now supports the apex.region API => but AJAX
REFRESH is still not possible!
•  Copy selection to clipboard.
'Text with Autocomplete' item type using Oracle JET
55

Re-Implementation of the item type „Text with

Autocomplete“ based on Oracle JET

APEX 5.1 APEX 18.1

'Text with Autocomplete' item type using Oracle JET
56

Advantages:
•  Improved accessibility
•  Lazy loading with cache
•  Minimum search characters
Responsive Rich Text 57

Responsive Rich Text editor - automatically adjusts to the

region width.
Responsive Rich Text 58

Feature can be turned off in Shared Components >

Component Settings

Initially turned off for imported

/ upgraded applications
 59

RESTful Services
(providing)
RESTful Services (providing) 60

SQL Workshop Integration upgraded to use ORDS APIs

New
Schemas Schemas Features
§  APEX_180100 §  ORDS_METADATA only here

§  APEX_LISTENER §  ORDS_PUBLIC_USER
§  APEX_REST_PUBLIC_USER
RESTful Services (providing) 61

“Old” RESTful services still available – READ ONLY

RESTful Services (providing) 62

“Old” RESTful services still available – READ ONLY

RESTful Services (providing) 63

New RESTful Services use API for ORDS_METADATA

RESTful Services (providing) 64

New RESTful Services use API for ORDS_METDATA

Features:
•  More capable template handlers
•  Autorest enablement for tables,
views and procedures
•  OAUTH2 support
•  Open API 2.0 (Swagger) Support
•  ...
RESTful Services (providing) 65

Integration with Swagger to generate doc

•  http://douggault.com/2018/03/19/swagger-doc-from-
apex-ords-restful-workshop
•  SWAGGER UI 2.0+ Server (based on Node.js)
•  https://swagger.io/download-swagger-ui/
RESTful Services (providing) 66

APEX Instance Administration

RESTful Services (providing) 67
 68

SQL Workshop
SQL Workshop > QuickSQL 69

QuickSQL is now integrated, used to be a packaged app

SQL Workshop > QuickSQL 70
SQL Workshop > Sample Datasets 71

Sample Datasets
SQL Workshop > Sample Datasets 72

Sample Datasets – very good to help with training and

troubleshooting
 73

Packaged Applications
 APEX_PKG_APP_INSTALL 74

Pre
5.2 Install, upgrade and delete packaged applications on the command
line:
declare
l_app_id number;
begin
-- Install
l_app_id := APEX_PKG_APP_INSTALL.install(
p_app_name => 'Sample Trees',
p_authentication_type => 'NATIVE_APEX_ACCOUNTS',
p_schema => 'DAUST_DE' );
end;
APEX_PKG_APP_INSTALL 75

Great for automatically installing all packaged applications after a

patch to look for gems or upgrade all applications at once.

Currently (not yet) officially supported but used by Oracle in the

cloud.

Data Dictionary View APEX_PKG_APPS

Install Packaged Applications 76

Allow „Modify Other Applications“ in security settings

 77

PL/SQL APIs
PL/SQL APIs 78

APEX_ACL add_user_role
Application Access Control has_user_any_roles
has_user_role
remove_all_user_roles
remove_user_role
replace_user_roles

APEX_APP_SETTING get_value
Application settings set_value
PL/SQL APIs 79

APEX_APP_BUILDER_API delete_page_item
Generate Pages, Reports, etc. delete_region
Mainly used in Data Reporter,
not (yet) officially supported

APEX_APPLICATION_INSTALL get_no_proxy_domains
Application settings get_remote_server_base_url
get_remote_server_https_host
get_remote_server_ords_tz
set_remote_server
PL/SQL APIs 80

APEX_COLLECTION create_collection
Collection handling (Parameter p_truncate_if_exists)

APEX_CREDENTIAL clear_tokens
Public API for Secure Credentials set_persistent_credentials
(basic auth / oauth2) set_persistent_token
set_session_credentials
set_session_token

APEX_JWT decode
Handling von JSON Web Tokens encode
validate
PL/SQL APIs 81

https://blogs.oracle.com/apex/apex-181-early-adopter-2-rest-services-and-plsql

APEX_EXEC add_column
SQL Execution (mostly add_filter
remote), especially for plugin- add_order_by
developer add_parameter
close
execute_plsql
execute_remote_plsql
execute_web_source
get_anydata
get_clob
get_column
…
PL/SQL APIs 82

5.1.4
APEX_EXPORT get_application
Replacement for wwv_flow_utilities. get_workspace_files
export_application_to_clob get_feedback
get_workspace

APEX_MAIL prepare_template
Mail handling send_mail (support template)

APEX_SESSION attach
Session utils create_session
testing in sqlplus / sqlcl delete_session
running apex_exec in sqlplus / sqlcl detach
PL/SQL APIs 83

APEX_UTIL delete_feedback
Utilities delete_feedback_attachment
get_timeframe_lov_data
reply_to_feedback
set_parsing_schema_for_request
Multitenant applications
Switching datasets (different
schemas)

prepare_url (Parameter p_plain_url)

 84

Data Dictionary
APEX_WORKSPACE_GROUPS 85

New columns application_id and application_name,

„true“ workspace groups now: where application_id is
null
APEX_WEBSERVICE_LOG 86

https://jastraub.blogspot.de/2018/03/apex-181-new-feature-
logging-web.html
Questions? 87

Opal Consulting Email / Website

Zum Tilmeshof 11 [email protected]
50859 Köln www.opal-consulting.de
Germany
 88

Backup
Universal Theme light / dark navigation 89
Create Feature Page 90

Add „Blueprint“ features to your app AFTER creation