The Power of the Oracle APEX Repository Patrick Wolf, Sphinx IT Consulting DOAG SIG APEX for Advance

1
The Power of theOracle APEX RepositoryPatrick
Wolf, Sphinx IT ConsultingDOAG SIG APEX for
Advanced, 31-May-2007
2
About Sphinx IT Consulting

• Founded 1993
• Creates mission-critical individual software
• Consulting for Databases Java Middleware
• Specialized in Oracle
• More information at http//www.sphinx.eu/

About Patrick Wolf

• 13 years of Oracle experience with SQL, PL/SQL
  and Oracle Forms
• Solution Architect at Sphinx IT Consulting
• Run an APEX Blog http//inside-apex.blogspot.com
  
• Author of the ApexLib Frameworks
  http//apexlib.sourceforge.net/
• Author of the Oracle APEX Builder Plugins
• Contact mail is patrick.wolf_at_sphinx.at

3
Agenda

• The Oracle APEX Repository
• Part 1 - The APEX Dictionary Views
• Simple queries
• Automatic documentation creation
• Monitoring APEX Applikations
• Create generic code using the repository
• Part 2 Directly manipulating the APEX
  Repository
• Do mass updates in the Repository
• Create new Page Items, ...
• QA

4
The Oracle APEX Repository

• Oracle APEX stores all meta-data of an
  application in a repostiory in the database
• Schema FLOWS_xxx (eg. FLOWS_030000)
• Table prefixed with WWV_FLOW_
• This tables are not public.
• Starting with version 2.2 there is a view access
  layer.
• Prefix of the views is APEX_
• Since 3.0 there is an user interface for simple
  queries. Can be found at Utilities/APEX Dictionary

5
The APEX Dictionary Views

• Returns a readable form of the internal APEX
  Repository tables
• Hide the generic/old data model
• Lookups for Foreign-Keys
• Translate interne Enumerations into English
• Contains statistic counters eg. Number of child
  elements
• Checks access privileges (you only see your
  applications)
• Connected as schema user All applications of the
  workspaces where the schema is assigned to.
• Connected as SYS, SYSTEM, FLOWS_xxx All
  applications
• Other users nothing
• The Views are public and can therefore be used in
  APEX applications or in external scripts.

6
The APEX Dictionary Views
7
The APEX Dictionary Views
Select Tree view
8
The APEX Dictionary Views
Hierarchical representation
9
The APEX Dictionary Views
Documentation of each column
10
The APEX Dictionary Views
11
The APEX Dictionary Views
Dont forget thequote!
To execute pressGo button at thebeginning of
thepage
12
The APEX Dictionary Views

• Divided into 3 areas
• Application-Meta-Data
• APEX_APPLICATION
• Monitoring data about the application
• APEX_WORKSPACE_ACCESS_LOG
• APEX_WORKSPACE_ACTIVITY_LOG
• APEX_WORKSPACE_LOG_SUMMARY
• APEX_WORKSPACE_CLICKS
• APEX_WORKSPACE_SESSIONS
• Workspace-Meta-Data
• The reminding APEX_WORKSPACE views. For example
  APEX_WORKSPACE_APEX_USERS, ...

13
The APEX Dictionary Views

• What are usage examples for the views?
• A few examples
• Quality checks a coding styleguides,
• Dynamic generation of Site-Maps
• Automatic documentation of the application
• Automatic monitoring of the application
• Some nice page view charts
• For the creating of generic code
• And so on...

14
Quality check 1

• Dont use the xxx Label with Help Label
  Template for Page Items.

SELECT APPLICATION_NAME , PAGE_ID ,
ITEM_NAME , DISPLAY_AS ,
ITEM_LABEL_TEMPLATE FROM APEX_APPLICATION_PAGE_I
TEMS WHERE ITEM_LABEL_TEMPLATE LIKE 'Label with
Help' AND DISPLAY_AS ltgt 'Hidden'
ORDER BY APPLICATION_NAME , PAGE_ID
, ITEM_NAME
15
Quality check 2

• Are all labels left aligned?

SELECT APPLICATION_NAME , PAGE_ID ,
ITEM_NAME , DISPLAY_AS ,
LABEL_ALIGNMENT FROM APEX_APPLICATION_PAGE_ITEMS
WHERE LABEL_ALIGNMENT ltgt 'Left' AND
DISPLAY_AS ltgt 'Hidden' ORDER BY
APPLICATION_NAME , PAGE_ID , ITEM_NAME
16
Quality check 3

• Has a help text been entered for all Page Items
  where the xxx Label with Help Template has been
  used?

SELECT APPLICATION_NAME , PAGE_ID ,
ITEM_NAME , DISPLAY_AS FROM
APEX_APPLICATION_PAGE_ITEMS WHERE
ITEM_LABEL_TEMPLATE LIKE 'Label with Help'
AND DISPLAY_AS ltgt 'Hidden' AND
ITEM_HELP_TEXT IS NULL ORDER BY
APPLICATION_NAME , PAGE_ID , ITEM_NAME
17
Dynamic Site-Maps

• Create a automatically generated Site-Map for
  your application.
• Tag relevant pages with SITEMAP in the page
  comment.

SELECT PAGE_ID , PAGE_TITLE ,
PAGE_GROUP FROM APEX_APPLICATION_PAGES WHERE
APPLICATION_ID APP_ID AND
INSTR(PAGE_COMMENT, 'SITEMAP') gt 0 ORDER BY
PAGE_TITLE

• Create SQL Report with above SQL statement.
• For column PAGE_TITLE, change Link Text to
  PAGE_TITLE and Page to PAGE_ID
• Set column PAGE_ID to Hidden.
• Page Groups could be used to get a hierarchical
  representation in a tree view

18
Automatic Documentation

• Wouldnt it be nice to have a diagram of the Page
  Flow of an application? Which page calls which
  page
• Like JDeveloper, where there is a Page Flow
  editor for JSF applications, but which has be
  manually mantained
• Not like other development environments where the
  necessary data is stored somewhere in XML files
  or in Java/ code, we have all the necessary data
  accessible with simple SQL queries!
• With the help of Graphviz an open source tool,
  the graphical diagram generation is quite easy.

19
Page Flow Generator

• Analyses the meta-data of an application, like
  Pages, Branches, Buttons, Links- in Report
  Columns, HTML Regions and SQL Statements.
• Creates a dependency model in the memory and
• creates for each Page Group (to keep them small)
  its own diagram.
• The complicated part was the Graphviz syntax.
• The Page Flow Generator is part of the ApexLib
  Frameworks.

20
(No Transcript)
21
Monitoring APEX Applications 1

• Wouldnt it be cool if the administrator
  automatically gets notified if an error occurs in
  the application?
• APEX provides the view APEX_WORKSPACE_ACTIVITY_LOG
  and the column ERROR_MESSAGE for such a purpose.
• A DBMS_JOB can be used to check it periodically.
• See blog entry http//oraclequirks.blogspot.com/20
  07/05/simple-pager-for-apex-30.html
• And with http//inside-apex.blogspot.com/2007/05/s
  ending-sms-to-mobile-phone.html you can set it up
  to send a SMS.

22
Monitoring APEX Applications 2

• How to detect hackers trying to break into your
  application?
• The view APEX_WORKSPACE_ACCESS_LOG logs each
  login attempt with the following data
• Timestamp
• Usernamen
• IP address
• Result Successful/Failed

23
Monitoring APEX Applications 3

• How can you detect if the response time of you
  application decreases?
• The view APEX_WORKSPACE_ACTIVITY_LOG logs each
  page access and the column ELAPSED_TIME tells you
  how long APEX needed to generate the page.
• Combined with historical data and the number of
  users currently accessing the system you can
  estimate a trend of your response time.
• The view can also be used to analyze the
  navigation behavior of your users. Also see
  column THINK_TIME for the time the user waited
  till opened another page.
• Or create a query which returns the pages which
  are never called.
• And so on

24
Create Generic Code 1

• Ever had the problem that you had a page where
  the Page Items are very similar and number
  VALUE1, VALUE2, and where you had to create the
  same validation for all of them? Boring job,
  isnt it?
• There is also a way to do it with less effort!

BEGIN FOR rITEM IN ( SELECT ITEM_NAME
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE APPLICATION_ID APP_ID AND
PAGE_ID PAGE_ID AND
ITEM_NAME LIKE 'P'PAGE_ID'_VALUE'
) LOOP IF V(rITEM.ITEM_NAME) IS NOT
NULL THEN APEX_Util.set_sessio
n_state ( p_name gt
rITEM.ITEM_NAME , p_value gt 1
) END IF END IF END
25
Create Generic Code 2

• Ever needed a cascading/hierarchical LOV?
• Have you implemented Carl Backstoms
  (http//carlback.blogspot.com/) AJAX example for
  each LOV?
• Isnt all required information available in the
  meta-data of the Page Item? The WHERE clause of
  the LOV contains all dependencies!
• Why dont we use it and stop duplicating code and
  information about the dependencies?
• Thats what the Open Source ApexLib Framework
  (http//apexlib.sourceforge.net/) does!
• Short demonstration of the framework.

26
APEX Dictionary Views - Conclusio

• Are a powerful tool which you can use to enhance
  your application of your development process.
• There a lot of possible scenarios where you can
  use them, you just have to come up with some good
  ideas.
• Use the APEX Dictionary View Browser to get used
  to them!
• Available since Oracle APEX Version 2.2.

27
Manipulating the APEX Repository

• Ever had the requirement to make a bulk update in
  your application? For example to change the Label
  Template to without Help for all Page Items.
• Or create with your own code Page Items, Your
  own wizard.
• Oracle APEX doesnt offer an official tool to
  fulfill above requirements.
• But if already all the meta-data of the
  application are in the database
• But the result of doing a direct update on the
  repository tables can really be surprising. The
  application isnt accessible anymore in the
  builder!
• So how do it the right way?

28
Manipulating the APEX Repository

• BACKUP, BACKUP, BACKUP!!!
• Make a backup/export of the FLOWS_XXX schema
  before you do any manipulation of the repository
  tables! At least do an export of your
  application!
• Use the following tips at your own risk !

29
Manipulating the APEX Repository

• The manipulation is down connected as FLOWS_xxx
  (eg. FLOWS_030000).
• The tables are prefixed with WWV_FLOW
• The best way to identify the tables and columns
  is to have a look at the APEX Dictionary Views.
• Create a reference record. Eg. With the new Label
  Template
• Set APEX context with WWV_Flow_API.set_security_gr
  oup_id
• What is the Security Group ID?
• Is equal to the Workspace ID and can be get with
  Apex_Util.get_security_group_id(Workspacename)

30
Manipulating the APEX Repository

• Example to initialize the APEX context.

DECLARE vSecurityGroupId NUMBER BEGIN
vSecurityGroupId Apex_Util.find_security_group_
id ( p_workspace gt
'SPHINX_SXSD' ) IF
vSecurityGroupId 0 THEN
RAISE_APPLICATION_ERROR ( -20111
, 'Workspace not found! Do not issue updates'
) END IF --
WWV_Flow_Api.set_security_group_id(vSecurityGroupI
d) WWV_Flow.g_user 'PWOLF' END
31
Manipulating the APEX Repository

• Example which sets the Label Template from
  Optional Label with Help to Optional Label on
  all Page Items on page 4.

UPDATE WWV_FLOW_STEP_ITEMS SET
ITEM_FIELD_TEMPLATE 6.61528317798547E18 WHERE
FLOW_ID 106 AND FLOW_STEP_ID
4 AND ITEM_FIELD_TEMPLATE
7.22798225629576E18
FLOW_STEP_ID equal to Page Id
FLOW_ID equals to Application Id
32
Manipulating the APEX Repository

• What can I do if my application isnt accessible
  after an update anymore?
• Check the SECURITY_GROUP_ID in WWV_FLOWS.
• If its 0, initialize the APEX context and set
  the SECURITY_GROUP_ID in WWV_FLOWS again.

UPDATE WWV_FLOWS SET SECURITY_GROUP_ID NULL
WHERE FLOW_ID 106
NULL is used by intention!
33
Manipulating the APEX Repository

• How can we create new Page Items, ?
• APEX provides the public(!) but undocumented
  package WWV_Flow_Api
• Can be called from a package in the application
  schema or from FLOWS_XXX.
• There are a lot of examples, just view the export
  file of an application.
• WWV_Flow_Api.g_id_offset is used during import to
  get unique IDs -gt not required in our case.
• The next free ID is returned by
  WWV_Flow_Id.next_val.

34
Manipulating the APEX Repository

• The following session initialization steps are
  required if you are executing the code outside of
  an APEX session. For example when you are
  connected to FLOWS_XXX.
• Initialize APEX context as done for an update
  execute the following code.

BEGIN SELECT VALUE INTO
WWV_Flow_Api.g_nls_numeric_chars FROM
NLS_SESSION_PARAMETERS WHERE PARAMETER
'NLS_NUMERIC_CHARACTERS' EXECUTE
IMMEDIATE 'ALTER SESSION SET
NLS_NUMERIC_CHARACTERS''.,''' --
WWV_FLOW.g_browser_language 'en'
WWV_Flow_Api.g_id_offset 0 END
35
Manipulating the APEX Repository

• Just create a new Page Item with the following
  example code.

DECLARE vPageItemId NUMBER BEGIN
WWV_Flow_Api.set_version (
WWV_Flow_Api.g_compatable_from_version )
WWV_Flow.g_flow_id 100 -- Application Id
-- vPageItemId WWV_Flow_Id.next_val
-- WWV_Flow_Api.create_page_item ( p_id
gt vPageItemId , p_flow_id
gt WWV_Flow.g_flow_id ,
p_flow_step_id gt 4 , p_name
gt 'P4_TESTIT' ... ) END
36
Manipulating the APEX Repository - Conclusio

• BACKUP, BACKUP, BACKUP!!!
• For updates, do a direct update on the tables.
• Always create a Reference record.
• Do inserts with the WWV_Flow_Api package.
• Application export file has good example.

37
Any questions?