Jeff Kemp

Subscribe to Jeff Kemp feed Jeff Kemp
Oracle Database: Get it Right
Updated: 1 month 1 week ago

Hiding HTML when downloading an Interactive Report

Thu, 2019-05-23 02:33

A common requirement is to format data for display in an Interactive Report, for example showing an icon, making part of the data a clickable link or a button, or showing it in different colours.

The problem with embedding formatting in the data for the report is that it is not only used for display in the web page; it is also used for the Download option, causing user confusion when HTML code is exposed in the generated CSV file.

If the logic for the formatting is not data-driven, the solution is to enter the HTML in the HTML Expression attribute on the report column definition. The HTML Expression is used only when displaying the data in the interactive report; the underlying column data is used for the Download. The underlying column data is also used to generate the filter list for the column (if Column Filter Type is “Default Based on Column Type”).

If the logic for the formatting is data-driven, a simple solution is to generate the HTML in an underlying column in the query. However, you don’t want this HTML appearing in the Download CSV, so what you can do is generate the HTML in an additional, hidden column in the report; then use the hidden column in the HTML Expression attribute. This works because the HTML Expression attribute is allowed to refer to any column in the query, even hidden ones.

For example, here is a query with a generated column that determines an icon to be displayed within the “job” column:

select ename,
       job,
       job
       || ' <span class="fa '
       || case when mgr is null then 'fa-gear' else 'fa-user' end
       || '"></span>' as job_html
from emp

The following column attributes are set:

  • JOB: HTML Expression = #JOB_HTML#
  • JOB_HTML: Type = Hidden Column

When the report is run, we see the icons displayed:

When the report is downloaded as CSV, the Job column is plaintext as desired:

Escaped strings and PL/SQL Dynamic Actions

Mon, 2019-04-08 02:50

I had a simple form where the user can select a vendor from a select list (using the Select2 plugin), and the form would retrieve the latest purchase details based on a simple string search of that vendor’s name – for most cases. Sometimes, however, it failed to find anything even though I knew the data was there.

There was a dynamic action that fires on Change of the list item that executes a PL/SQL procedure and retrieves the Total Amount and the Memo:

transaction_pkg.get_suggestion
  (vendor_name => :P5_VENDOR_NAME
  ,total_amt   => :P5_AMOUNT /*out*/
  ,memo        => :P5_MEMO); /*out*/

This didn’t work in some cases where a vendor name included special characters, such as &. This is because the item had Escape special characters set to the default (Yes), which is good practice to protect against Cross-Site Scripting (XSS) attacks. Therefore, the value sent by the dynamic action to my PL/SQL procedure had the special html characters escaped, e.g. “Ben & Jerry’s” was escaped to “Ben &amp; Jerry&#x27;s“. I believe APEX uses the apex_escape.html function to do this.

Usually, I would try to rework my code to send a numeric ID instead of a string; but in this particular case the data model does not have surrogate keys for vendors (it’s just a free-text field in the transactions table) so I want to use the name.

If I was doing this properly, I would fix the data model to make vendors first-class entities, instead of using a free-text field. This would allow using a surrogate key for the vendor list and this escaping behaviour would no longer be a problem.

Another alternative here is to use the latest transaction ID as a surrogate key for each vendor; but then I would need to modify the form to translate this back into a vendor name when inserting or updating the table; and this would add unnecessary complexity to this simple form, in my opinion.

Instead, before sending this string to my procedure, I’ve chosen to unescape the data. To do this, I add a call to utl_i18n.unescape_reference:

transaction_pkg.get_suggestion
  (vendor_name => utl_i18n.unescape_reference(:P5_VENDOR_NAME)
  ,total_amt   => :P5_AMOUNT /*out*/
  ,memo        => :P5_MEMO); /*out*/

This converts the escaped data back into a plain string, and my simple data matching procedure now works as expected.

Form Regions – Superclass/Subclass

Tue, 2019-04-02 22:02

The new Form Region feature introduced in Oracle APEX 19.1 is a big improvement to the way that single-record forms are defined declaratively.

In prior versions of APEX, you were effectively limited to a single DML load process and a single DML submit process, and all the items on the page that were linked to a table column were required to be from only a single table. If you wanted to do anything more complicated than that, you had to either split your form into multiple pages or get rid of the DML processes and hand-code the PL/SQL yourself.

The new components/attributes for Form Regions are:

  • Region type “Form” which defines the Source Table (or view, or SQL Query, or PL/SQL returning a SQL query, or Web Source) and associated semantics.
  • Item Source attribute that associates each item with a particular Form Region. The item can optionally be marked as “Query Only” and/or as a “Primary Key”.
  • Pre-render process type “Form – Initialization” that is associated with a Form Region.
  • Page process type “Form – Automatic Row Processing (DML)” that is associated with a Form Region. This can be used to change the Target for the form region (default is the Source Table) to a different table or view, or to replace the DML processing with your custom PL/SQL. You can also modify other semantics such as Prevent Lost Updates and row locking.

If you generate a single-record form page APEX will generate all the above for you and you can then customise them as you need.

Now, I want to create a slightly more complex form – one based on two tables that are related – “Transaction Lines” and “Reserve Lines” have the same Primary Key, and a referential integrity constraint linking them. They act like a “super class / subclass”, where one table (“Reserve Lines”) is optional. This data model eliminates the need to have lots of nullable columns in a base table.

Data model

Here’s the form for editing a single “Transaction Line”:

Form with “Reserve” unticked

The form shows the associated “Reserve Line” if it exists:

Form with “Reserve” ticked

If the “Reserve” checkbox is ticked, the Reserve details region is shown and the user can enter the Reserve Line attributes.

I need the page to perform the following actions:

  1. For a new record, if the user leaves “Reserve” unticked, only a Transaction Line should be inserted.
  2. For a new record, if the user ticks “Reserve”, both a Transaction Line and a Reserve Line should be inserted.
  3. For an existing record, if the user ticks or unticks “Reserve”, the Reserve Line should be inserted or deleted accordingly.

If you want to see this example in action, feel free to try it out here:
https://apex.oracle.com/pls/apex/f?p=JK201904&c=JK64 (login using your email address if you want). The starting page is a blank transaction; click “Create”, then click “Add Line” to open the transaction line page.

The page is composed of two Form Regions. Note that in this example I’ve laid out all the items for each form within its Form Region, but this is not strictly necessary.

To do this, I built the form in the following manner:

  1. Created a Form Region for the Transaction Line with standard Initialize form and Automatic Row Processing (DML) processes.
  2. Created a second Form Region for the Reserve Line with the standard processes (but customised later).
  3. Added the “P2_RESERVE_Y” checkbox item, not linked to any source column. Added a dynamic action to show/hide the Reserve details region if it is ticked/unticked.
  4. Added a “set up reserve line” PL/SQL Code process immediately after the Initialize form Transaction Line and just before the Initialize form Reserve Line process:
  • Server-side Condition Type = Rows returned
  • SQL Query =
    select null from reserve_lines where line_id=:P2_LINE_ID
  • PL/SQL Code:
:P2_RESERVE_LINE_ID := :P2_LINE_ID;
:P2_RESERVE_Y := 'Y';

5. Change the Automatic Row Processing (DML) for the Reserve Line region:

  • Target Type = PL/SQL Code
  • PL/SQL Code to Insert/Update/Delete:
case when :P2_RESERVE_Y is null then

  delete reserve_lines where line_id=:P2_RESERVE_LINE_ID;

when :P2_RESERVE_Y is not null
and :P2_RESERVE_LINE_ID is null then

  insert into reserve_lines
    (line_id,asset,target_amt,target_year)
  values
    (:P2_LINE_ID,:P2_ASSET,:P2_TARGET_AMT,:P2_TARGET_YEAR )
  returning line_id into :P2_RESERVE_LINE_ID;

when :P2_RESERVE_Y is not null
and :P2_RESERVE_LINE_ID is not null then

  update reserve_lines
    set asset=:P2_ASSET
       ,target_amt=:P2_TARGET_AMT
       ,target_year=:P2_TARGET_YEAR
  where line_id=:P2_RESERVE_LINE_ID;

else
  null;
end case;

6. Add a special handler to delete the reserve line if the user clicks the Delete button (this needs to be executed prior to the Process form Transaction Line to avoid a FK violation).

This solution is not quite as “low-code” as I’d like; it would be simpler to call a TAPI here instead of hardcoding the DML statements. The reason we need custom PL/SQL here is that when the user clicks the “Create” or “Save” buttons (which are associated with the SQL Insert and Update actions, respectively), we often need to translate this into a different DML action (insert, update, or delete) for the Reserve Lines table.

Amend commit message in SourceTree

Sun, 2019-03-31 21:10

If you’re using Atlassian SourceTree with a git repository and you do a local commit but then realise the message was incorrect, you can amend it before you push it to remote. To set this up, you can create a “Custom Action” in SourceTree:

  1. Tools -> Options -> Custom Actions
  2. Click Add
  3. Set Menu caption, e.g. “Amend commit message”
  4. Select “Open in a separate window” and unselect “Run command silently”
  5. Set Script to run to “git.exe” including path
  6. Set Parameters to “commit –amend”

Now, whenever you want to correct the message on your latest commit, you right-click on the commit, and select “Custom Actions” -> “Amend commit message“. A terminal window will open with a vi editor open with the current commit message at the top of the file (there will also be some instructions with # at the start of each line; you can leave them alone).

If you know vi, you’ll know what to do. Here’s a quick guide:

  • To start editing, press “i” and then edit the message (on the first line of the file).
  • To delete everything on the line, press ESC, followed by “ddO” (this will delete the line and then return you to edit mode to type the new message)
  • To save and quit, press ESC, followed by “:wq
  • To quit without making any changes, press ESC, followed by “:q!

Oracle Workspace Manager + APEX + VPD

Fri, 2019-03-15 20:24

This is an article I wrote quite some time ago and put “on ice” until I completed the client project it was related to. However, for unrelated reasons the project was significantly delayed and later rebooted with an updated set of requirements, which no longer require Oracle Workspace Manager. This means I’m unable to add a triumphant postscript saying “this was a great success and it’s being used to this day”. Instead, I’m adding this preamble to say “this was a very interesting feature of Oracle I learned but didn’t quite get to use, but hopefully someone will find it useful”.

Oracle Workspace Manager (OWM) is a feature of the Oracle database that was built back in the 8i days and installed in Oracle by default since 9i. As described by Tim Hall, it allows multiple transactionally consistent environments to exist within one database.

Confusing Terms
Don’t confuse OWM with Oracle APEX workspaces, or with Analytic Workspace Manager for Oracle OLAP.

OWM allows the developer to take a leap over the complexities involved in a number of use cases, such as:

  • Savepoints – a snapshot of a point in time
  • Workspaces – a private area for modifications that are not visible to normal users
  • Row History – store a history of every change for every record
  • Valid Time – support date/time range validity for each record

The Row History use case is similar to using Flashback Query which is a more modern feature of the database; however, since it can be enabled or disabled individually for each table, it may require less storage space to support querying back as far as the user would like in time.

The Valid Time use case allows your users to set a date/time range on each record; multiple versions of a unique row can be created with non-overlapping date ranges; updates can be done within the context of a given date/time range, which will cause rows that span the boundary of the range to be split into multiple versions. I haven’t tried this myself but it looks like a powerful feature.

Note: the default workspace for a user session is called “LIVE“, and the default savepoint is “LATEST“.

One example of using savepoints is that a user could create a savepoint, make changes, go back and view the database as of the savepoint, and can rollback all changes to a savepoint. They can also run an API command (dbms_wm.SetDiffVersions) to generate a differences report which shows all the inserts, updates, and deletes that have occurred since a savepoint.

An example of using workspaces is where a user could create one or two workspaces, each representing a different scenario. The user can jump back and forth between the scenarios and the “live” workspace (which is the default). They can edit any of these workspaces. Changes made by other users to the “live” workspace may, optionally, be automatically propagated into a workspace. Similarly to savepoints, the user can get a differences report between any workspace (including the “live” workspace). In addition, the user can create and view savepoints and additional workspaces within a workspace – OWM maintains a hierarchy of workspaces.

If the user is not happy with their scenario, they can simply delete the workspace and the live data is not affected.

If they are happy with their changes in a workspace, they can choose to do a Merge – which attempts to effect all the inserts, updates and deletes that were made in the workspace to its parent workspace (e.g. the “live” workspace if that was the source). If any change to a row would conflict with a change that another user made to the same row in the parent workspace, the merge stops and the user may be prompted to resolve the conflicts (i.e. for each row, the user can decide to refresh their workspace with the updated data in the parent, or they can force their workspace’s change onto the parent).

I suspect OWM was once known (or internally referred to) as “Long Transactions” or something like that, probably because of its user workspace and merging features. You can see the remnants of this old name in the documentation – many of the document URLs start with “long”. Also, note the title of this slide deck by an Oracle product manager: “Long Transactions with Oracle Database Workspace Manager Feature”.

The features of OWM only affect the tables that you explicitly Enable Versioning on via the API (DBMS_WM).

Limitations/Restrictions of Workspace Manager

If you are looking into using OWM, you must read the Intro to Workspace Manager in the docs. Make sure to review the restrictions that apply, such as:

  • Each version-enabled table must have a primary key.
  • If a parent table is version-enabled, each child table must also be version-enabled (but the opposite does not apply).
  • Referential integrity constraints MUST refer to the primary key in the parent table.
  • Primary key values in a parent table cannot be updated.
  • Only row-level triggers are supported – no per-statement triggers.
  • SQL MERGE statements are not allowed (attempts to do so results in “ORA-01400 cannot insert NULL into WM_VERSION”).
  • RETURNING clause is not supported (for INSERT or UPDATE statements).
  • Row-level security policies (VPD) are not enforced during workspace operations (such as Merge Workspace, and Rollback to Savepoint).
  • Materialized Views on a version-enabled table can only use the REFRESH COMPLETE method; e.g. no FAST or ON COMMIT.
  • Table names cannot be longer than 25 characters.
  • Column names cannot be longer than 28 characters.
  • Trigger names cannot be longer than 27 characters.
  • Some reserved words and characters apply, e.g. column names cannot start with WM$ or WM_.
  • Most DDL operations cannot be run directly on a version-enabled table (see below).

There are some other restrictions, so make sure to review the intro document carefully and consider the ramifications of each limitation for your particular situation.

I’ve used this simple script to do some basic checks on a table before I enable it for versioning: check_table_for_owm.sql

Database schema changes made by OWM

Internally, when you version-enable a table (e.g. MYTABLE), OWM makes a number of changes to your schema as follows:

  1. Renames MYTABLE to MYTABLE_LT, for internal use only
  2. Adds a number of grants on the object to WMSYS and WM_ADMIN_ROLE
  3. Adds some extra OWM-specific columns (with names prefixed with WM_) and indexes to MYTABLE_LT
  4. Creates the view MYTABLE, for use by your application for querying and DML
  5. Creates an INSTEAD OF trigger (owned by WMSYS) on the view to handle DML
  6. Creates some other triggers on any parent tables for referential integrity
  7. Creates one or two other tables (MYTABLE_AUX, and sometimes MYTABLE_LCK) for internal use only
  8. Creates some other views (MYTABLE_CONF, MYTABLE_DIFF, MYTABLE_HIST, MYTABLE_LOCK, MYTABLE_MW) for application use where needed
  9. Converts any triggers and VPD policies on the table to metadata and then drops them; these are managed by OWM from then on

Based on the grants that OWM gives to WMSYS (such as ON COMMIT REFRESH and QUERY REWRITE) I think the OWM tables are materialized views.

To change the structure of a version-enabled table, e.g. adding/modifying/removing a column, constraint, or trigger, the following steps must be done:

  1. Call dbms_wm.BeginDDL('MYTABLE');
  2. Make the changes to a special table called MYTABLE_LTS
  3. Call dbms_wm.CommitDDL('MYTABLE');

Note that this will fail if the table happens to have any constraints or indexes that are longer than 26 characters – so keep this in mind when naming them.

One of the most pleasing features of OWM is that it is relatively idiot-proof; if you try to perform an operation that is not supported, OWM will simply raise an exception (e.g. “ORA-20061: versioned objects have to be version disabled before being dropped“) instead of leaving you with a mess to clean up. Therefore it’s generally safe to test your scripts by simply running them and seeing what happens. For example, try doing some damage to the underlying tables or views by changing columns or dropping them – OWM will stop you. I would never do this sort of experiment in a production environment, of course!

Column comments on a table that becomes version-enabled are not migrated automatically to the view. They do remain on the renamed table (MYTABLE_LT). You can add comments to the view columns (and you don’t need to execute the BeginDDL/CommitDDL procedures for this); the same applies to the TABLE_LT table as well. Note, however, that if you remove the versioning from the table the view is dropped along with its comments, so if you later re-enable versioning you might want to re-apply the column comments as well.

To copy/synchronize the column comments from the underlying MYTABLE_LT table to the MYTABLE view, I use this script: sync_comments_lt_to_view.sql.

Implementing Workspace Manager

For guidance on how to take advantage of Oracle Workspace Manager in an APEX application, I watched this video by Dan McGhan. He demonstrates it in a javascript application but the real work is all done on the database in PL/SQL, which can be used by any application environment including APEX.

These are some of the API calls that I’ve used to build OWM features into my application:

set up a table for scenariosdbms_wm.EnableVersioning('MYTABLE', 'VIEW_W_OVERWRITE');create a scenariodbms_wm.CreateWorkspace('SCENARIO1');view a scenariodbms_wm.GotoWorkspace('SCENARIO1');create a snapshotdbms_wm.CreateSavepoint('SCENARIO1', 'SCENARIO1_SP1');rollbackdbms_wm.RollbacktoSP('SCENARIO1', 'SCENARIO1_SP1');merge a scenario to its parentdbms_wm.MergeWorkspace('SCENARIO1', create_savepoint=>false, remove_workspace=>true);drop a scenariodbms_wm.RemoveWorkspace('SCENARIO1');go back to livedbms_wm.GotoWorkspace('LIVE');compare two scenariosdbms_wm.SetDiffVersions('SCENARIO1', 'SCENARIO2', onlyModified=>true);analyze conflicts prior to mergedbms_wm.SetConflictWorkspace('SCENARIO1');

“You just can’t tell about the future.”

APEX and VPD

My APEX application already uses VPD (or Row Level Security) to provide a multi-tenant environment for a wide range of users. Groups of users are assigned to one or more Security Groups; when they login they are assigned to one Security Group at a time which is set in a Global Application Context. A VPD policy has been applied to most tables like this:

function vpd_policy
  (object_schema in varchar2
  ,object_name in varchar2
  ) return varchar2 is
begin
  return q'[security_group_id=sys_context('CTX','SECURITY_GROUP_ID')]';
end vpd_policy;

The Application Context is associated with the APEX session’s Client Identifier (e.g. JBLOGGS:16630445499603) so each page request will be executed within the correct VPD context. A procedure is called from the Post-Authentication Procedure Name on the authentication scheme which sets the SECURITY_GROUP_ID context variable.

For more info: Converting an APEX Application to Multi-Tenant

Workspace Manager and VPD

According to the docs, OWM can work along with VPD. However, you need to be aware of a few considerations:

  • Row-level security policies are not enforced during workspace operations, including MergeWorkspace and RollbackToSP.
  • Row-level security policies must be defined on a number of views, not just the view for the version-enabled table.
  • Don’t apply policies to the underlying tables created by OWM.

You can add VPD policies to a table prior to version-enabling it and these will be handled correctly by OWM when you version-enable it. However, if I need to add VPD policies after a table has been version-enabled, this is the code I run:

declare
  target_table varchar2(30) := 'MYTABLE';
begin
  for r in (
    select view_name
    from user_views
    where view_name in (target_table
                       ,target_table||'_LOCK'
                       ,target_table||'_CONF'
                       ,target_table||'_DIFF'
                       ,target_table||'_HIST'
                       ,target_table||'_MW')
  ) loop
    begin
      dbms_rls.add_policy
        (object_name     => r.view_name
        ,policy_name     => 'security_policy'
        ,policy_function => 'security_pkg.security_policy'
        ,update_check    => true
        ,static_policy   => true);
    exception
      when others then
        if sqlcode != -28101 /*policy already exists*/ then
          raise;
        end if;
    end;
  end loop;
end;

It adds the policy to the base view, as well as the five specific associated views (mytable_LOCK, mytable_CONF, mytable_DIFF, mytable_HIST and mytable_MW). Similar code may be used to alter or drop policies.

Note: with relation to VPD policies, the documentation doesn’t specifically mention the Multi-Workspace (_MW) view. As far as I can tell, however, this is required – because when you version-enable a table, a VPD policy on a non-version-enabled table will be applied by Workspace Manager to this _MW view as well as the other views.

Since a user’s changes within a workspace will be restricted to data for their Security Group, a MergeWorkspace works just fine – even though technically the merge occurs across the entire table ignoring the VPD policy, the only changes we expect are those for the user’s Security Group.

However, Savepoints are different; like workspaces, they are a snapshot of the entire table; if you issue a RollbackToSP, it will ignore any VPD policies and rollback all data to the selected savepoint. This means that you can’t use RollbackToSP to give users the ability to rollback their data to a snapshot, while isolating their changes to their VPD context. For this reason I don’t use RollbackToSP in my application.

All together – OWM, VPD and APEX

The users of my application need to be able to create scenarios for people in their Security Group to make proposed changes, see how the changes affect the reports, and merge those changes back to the “live” version. To facilitate this, I create a table to list the workspaces, and apply the VPD policy to it so that users can only view and work with workspaces for their Security Group.

create table scenarios (
  scenario_id         number
    default to_number(sys_guid()
                     ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
    not null
 ,scenario_name       varchar2(200) not null
 ,wm_workspace        varchar2(30) not null
 ,security_group_id   number
    default sys_context('CTX','SECURITY_GROUP_ID')
    not null
 ,constraint scenario_pk primary key (scenario_id)
 ,constraint scenario_uk unique (wm_workspace)
);

Each scenario has an internal ID (in this case, a surrogate key generated from a GUID), a user-specified name, and a name for the workspace. I didn’t use the user-entered name for the workspace name because (a) it must be unique across the database, and (b) it is limited to 30 characters.

After a user clicks the “Create Scenario” button and enters a name, the page process calls the following procedure (within the package security_pkg associated with the context):

procedure create_scenario
  (scenario_name  in varchar2
  ,keep_refreshed in boolean := false) is
  l_wm_workspace varchar2(30);
begin

  -- workspace name must be unique and &amp;lt;=30 chars
  l_wm_workspace := to_basex
   (to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
   ,36);

  -- record the new scenario
  insert into scenarios (scenario_name, wm_workspace)
  values (create_scenario.scenario_name, l_wm_workspace);

  -- create the workspace
  dbms_wm.CreateWorkspace
    (workspace   => l_wm_workspace
    ,isrefreshed => keep_refreshed
    ,description => scenario_name
                 || ' ('
                 || sys_context('CTX','SECURITY_GROUP')
                 || ')'
    ,auto_commit => false);

  -- reset the savepoint, if required
  dbms_session.clear_context
      (namespace => 'CTX'
      ,attribute => 'WM_SAVEPOINT'
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

  -- go to the workspace on the next page view
  dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => 'WM_WORKSPACE'
      ,value     => l_wm_workspace
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

end create_scenario;

Note: the to_basex function is based on code copied from this proposed OraOpenSource addition.

In my APEX application Security Attributes, I have the following:

Initialization PL/SQL Codesecurity_pkg.init_apex_session;Cleanup PL/SQL Codesecurity_pkg.cleanup_apex_session;

The procedures called above are as follows:

procedure wm_init is
begin
  dbms_wm.GotoWorkspace(nvl(sys_context('CTX','WM_WORKSPACE')
                           ,'LIVE'));
  dbms_wm.GotoSavepoint(nvl(sys_context('CTX','WM_SAVEPOINT')
                           ,'LATEST'));
end wm_init;

procedure init_apex_session is
begin
  wm_init;
end init_apex_session;

procedure cleanup_apex_session is
begin
  dbms_wm.GotoWorkspace('LIVE');
  dbms_wm.GotoSavepoint('LATEST');
end cleanup_apex_session;

The effect of this is that for each page request, the user’s selected workspace and/or savepoint is activated, or if they have not yet chosen a workspace or savepoint, the “live” workspace and “latest” savepoint is selected (which are the defaults). At the end of each page request, the session is reset to the “live” workspace and “latest” savepoint.

“That makes two of us.”

Create a Snapshot

Here is my code to create a snapshot, using the Workspace Manager SavePoint feature:

procedure create_snapshot
  (snapshot_name in varchar2) is
  l_wm_workspace varchar2(30);
  l_wm_savepoint varchar2(30);
begin

  -- savepoint name must be unique and &amp;lt;=30 chars
  l_wm_savepoint := to_basex
   (to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
   ,36);

  l_wm_workspace := nvl(sys_context(CTX,'WM_WORKSPACE'), 'LIVE');

  -- record the snapshot
  insert into snapshots
    (snapshot_name
    ,wm_workspace
    ,wm_savepoint)
  values
    (create_snapshot.snapshot_name
    ,l_wm_workspace
    ,l_wm_savepoint);

  -- create the savepoint
  dbms_wm.CreateSavepoint
    (workspace      => l_wm_workspace
    ,savepoint_name => l_wm_savepoint
    ,description    => snapshot_name
                    || ' ('
                    || sys_context(CTX,'SECURITY_GROUP')
                    || ')'
    ,auto_commit    => false);

end create_snapshot;

Go to a Scenario

This sets the context for the user’s session so that subsequent page requests will load the specified Workspace. Any DML the user performs on version-enabled tables will be private to the workspace.

procedure goto_scenario (scenario_name in varchar2) is
  l_wm_workspace varchar2(30);
begin

  -- retrieve the workspace name for the given scenario
  select s.wm_workspace into l_wm_workspace
  from scenarios s
  where s.scenario_name = goto_scenario.scenario_name;

  -- reset the savepoint, if required
  dbms_session.clear_context
      (namespace => 'CTX'
      ,attribute => 'WM_SAVEPOINT'
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

  -- go to the workspace on the next page view
  dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => 'WM_WORKSPACE'
      ,value     => l_wm_workspace
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

end goto_scenario;

View a Snapshot

This sets the context for the user’s session so that subsequent page requests will be viewing the specified Savepoint. The version-enabled tables will be read-only; any DML on them will raise an exception.

procedure goto_snapshot (snapshot_name in varchar2) is
  l_wm_workspace varchar2(30);
  l_wm_savepoint varchar2(30);
begin

  -- retrieve the details for the given snapshot
  select s.wm_workspace
        ,s.wm_savepoint
  into   l_wm_workspace
        ,l_wm_savepoint
  from snapshots s
  where s.snapshot_name = goto_snapshot.snapshot_name;

  -- set the workspace and savepoint on the next page request

  dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => 'WM_WORKSPACE'
      ,value     => l_wm_workspace
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

  dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => 'WM_SAVEPOINT'
      ,value     => l_wm_savepoint
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

end goto_snapshot;

Go to Live

When the user is in a Scenario, and they wish to go back to “Live”, they can click a button which executes the following procedure. Their next page request will go to the LATEST savepoint in the LIVE workspace.

procedure goto_live is
begin

  dbms_session.clear_context
      (namespace => 'CTX'
      ,attribute => 'WM_WORKSPACE'
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

  dbms_session.clear_context
      (namespace => 'CTX'
      ,attribute => 'WM_SAVEPOINT'
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

end goto_live;

Go to Latest

When the user is viewing a Snapshot, and they wish to go back to “Latest” (so they do DML, etc.), they can click a button which executes the following procedure. This works regardless of whether they are in the Live workspace or viewing a scenario. Their next page request will go to the LATEST savepoint.

procedure goto_latest is
begin

  dbms_session.clear_context
      (namespace => 'CTX'
      ,attribute => 'WM_SAVEPOINT'
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

end goto_latest;

Compare two Scenarios

When the user wishes to analyze the differences between two scenarios, or between a scenario and the Live workspace, there is a two-step process:

  1. The user selects two Scenarios (or the “Live” workspace) from some select lists; the return value of these select lists should be the wm_workspace column from the table, or 'LIVE' for the Live workspace.
  2. The user clicks a button to execute the find_diffs1 procedure (see below).
  3. The page has a report on the view mytable_DIFF.
procedure find_diffs1
  (wm_workspace1 in varchar2
  ,wm_workspace2 in varchar2) is
begin

  dbms_wm.SetDiffVersions
    (workspace1   => wm_workspace1
    ,workspace2   => wm_workspace2
    ,onlyModified => true);

end find_diffs1;

Compare Snapshots

When the user wishes to analyze the differences between two snapshots (in any workspace), or between the latest data and a snapshot, a similar process is used:

  1. The user selects two Snapshots (or the “Latest” snapshot for either the Live workspace or a given Scenario); the return values of these select lists should be the wm_workspace and wm_savepoint, e.g. wm_workspace||':'||wm_savepoint.
  2. The user clicks a button to execute the find_diffs2 procedure (see below).
  3. The page has a report on the view mytable_DIFF.
procedure find_diffs
  (workspace_savepoint1 in varchar2
  ,workspace_savepoint2 in varchar2) is
  l_sep1 number := instr(workspace_savepoint1,':');
  l_sep2 number := instr(workspace_savepoint2,':');
begin

  dbms_wm.SetDiffVersions
    (workspace1   => substr(workspace_savepoint1, 1, l_sep1-1)
    ,savepoint1   => substr(workspace_savepoint1, l_sep1+1)
    ,workspace2   => substr(workspace_savepoint2, 1, l_sep2-1)
    ,savepoint2   => substr(workspace_savepoint2, l_sep2+1)
    ,onlyModified => true);

end find_diffs;

Merge a Scenario

In my system, I don’t allow users to create scenarios within scenarios, although this is possible with Workspace Manager. If the user wishes to merge all changes in a Scenario to Live, they click a button to execute the following procedure:

wm_error_55 exception;
wm_error_56 exception;
pragma exception_init(wm_error_55, -20055);
pragma exception_init(wm_error_56, -20056);

procedure merge_scenario is
  l_wm_workspace varchar2(30);
begin

  l_wm_workspace := sys_context('CTX','WM_WORKSPACE');

  goto_live;
  wm_init;

  -- merge the workspace
  dbms_wm.MergeWorkspace
    (workspace        => l_wm_workspace
    ,remove_workspace => true
    ,auto_commit      => false);

  -- delete the scenario
  delete scenarios s
  where s.wm_workspace = merge_scenario.scenario_id;

exception
  when wm_error_55 or wm_error_56 then
    -- unable to merge due to conflicts

    -- go back into the workspace
    dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => 'WM_WORKSPACE'
      ,value     => l_wm_workspace
      ,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));

    -- caller should redirect user to the "resolve conflicts" page
    raise e_merge_conflict;

end merge_scenario;

This will fail with an exception if there are any conflicting changes in the Live workspace, e.g.:

  • a record was updated in the scenario, but was deleted in Live
  • a record was updated or deleted in the scenario, but also updated in Live
  • a record was inserted in the scenario, but another record with the same PK was inserted in Live

If the workspace had been created with the isrefreshed option, the changes being made in Live will be automatically copied to the workspace so these conflicts should be minimised; however, conflicts cannot always be avoided. To give the user the ability to analyze the conflicts, you would perform the following steps:

  1. Call dbms_wm.BeginResolve(workspace => sys_context('CTX','WM_WORKSPACE'))
  2. Show a report that queries the view mytable_CONF
  3. Allow the user to select an action for each record in conflict – either “PARENT” (keep the parent, i.e. discard the change in the scenario) or “CHILD” (keep the child, i.e. discard the change made in Live).
  4. For each record, call dbms_wm.ResolveConflicts (see example below).
  5. COMMIT
  6. Call dbms_wm.CommitResolve(workspace => sys_context('CTX','WM_WORKSPACE'))
  7. Call merge_scenario again.

The ResolveConflicts procedure takes a where clause that identifies the row (or rows) to mark as resolved. In my case, I just call it with the id for each record the user chose:

for r in (
  ...query on something, e.g. an APEX collection...
  ) loop

  dbms_wm.ResolveConflicts
    (workspace    => sys_context('CTX','WM_WORKSPACE')
    ,table_name   => 'MYTABLE'
    ,where_clause => 'id=' || r.id
    ,keep         => r.action /*'PARENT' or 'CHILD'*/);

end loop;

If the conflicts are across multiple tables, the UI will probably be a bit more complicated. You’d have to resolve conflicts on all the affected tables before the Merge can succeed.

“Don’t let a white fence and a promotion end the world for you.”

Delete a Scenario

Deleting a scenario uses RemoveWorkspace:

procedure delete_scenario
  (scenario_name in varchar2) is
  l_wm_workspace varchar2(30);
begin

  -- retrieve the workspace name for the given scenario
  select s.wm_workspace into l_wm_workspace
  from scenarios s
  where s.scenario_name = delete_scenario.scenario_name;

  -- get out of the workspace
  goto_live;
  wm_init;

  -- delete the workspace
  dbms_wm.RemoveWorkspace
    (workspace   => l_wm_workspace
    ,auto_commit => false);

  delete scenarios s
  where s.wm_workspace = l_wm_workspace;

end delete_scenario;

Delete a Snapshot

Deleting a snapshot uses DeleteSavepoint:

procedure delete_snapshot
  (snapshot_name varchar2) is
  l_wm_workspace varchar2(30);
  l_wm_savepoint varchar2(30);
begin

  -- retrieve the details for the given snapshot
  select s.wm_workspace
        ,s.wm_savepoint
  into   l_wm_workspace
        ,l_wm_savepoint
  from snapshots s
  where s.snapshot_name = delete_snapshot.snapshot_name;

  -- get out of the snapshot/scenario:
  goto_latest;
  wm_init;

  -- delete the savepoint
  dbms_wm.DeleteSavepoint
    (workspace      => nvl(l_wm_workspace,'LIVE')
    ,savepoint_name => l_wm_savepoint
    ,auto_commit    => false);

  delete snapshots s
  where s.wm_savepoint = l_wm_savepoint;

end delete_snapshot;

Row History

One of the requirements of my application was to show a report of the entire history of edits to each record in a table. Since I’m already going to version-enable this table, it makes sense to take advantage of the Row History feature of Oracle Workspace Manager.

When you version-enable a table, OWM creates a view called MYTABLE_HIST which includes all the columns of the table, plus the following columns: WM_WORKSPACE, WM_VERSION, WM_USERNAME, WM_OPTYPE, WM_CREATETIME, and WM_RETIRETIME. By default, when you version-enable a table, OWM keeps only a minimal set of history in order to support other features in use, such as snapshots. In order to retain a complete history of changes to the record, enable versioning with the View Without Overwrite history option:

begin
  dbms_wm.EnableVersioning('MYTABLE'
    ,hist => 'VIEW_WO_OVERWRITE');
end;

This stops OWM from overwriting the history of changes to each record, so it can be queried via the _HIST view.

Now, I wanted to expose the contents of this view to my users, and for each history record show Who did it and When. “When” is easily answered by WM_CREATETIME, which is a TIMESTAMP WITH TIME ZONE. “Who” would normally be answered by WM_USERNAME, but since we’re in APEX, this will always be 'APEX_PUBLIC_USER' which is not very useful. Therefore, I have an ordinary column in my table called DB$UPDATED_BY which is set by the following ordinary trigger:

create trigger MYTABLE$TRG
  before update on MYTABLE
  for each row
begin
  :new.db$updated_by :=
     coalesce(sys_context('APEX$SESSION','APP_USER')
             ,sys_context('USERENV','SESSION_USER'));
end MYTABLE$TRG;

This means my report will show the actual APEX username of who created or updated the record.

The WM_OPTYPE column in the _HIST view will be 'I' (inserted), 'U' (updated), or 'D' (deleted). I can translate this code into user-friendly values for reporting purposes, but there is one problem: when a row is deleted, the _HIST table knows the username who deleted the record, but we never recorded the APEX username. Our db$updated_by column wasn’t changed, so it simply shows whoever last updated the records before it was deleted.

To solve this, we have two options:
(1) Write the application to do a quick no-change UPDATE on each row immediately before deleting it; then do some fancy footwork in the view to show this as a “Delete” operation.
(2) Don’t allow the application to execute actual DELETEs – in other words, use a “soft-delete” design.

I’ve gone with option (2) which is what the users needed anyway – they needed to be able to Delete and Undelete records at will, so I’ve only granted INSERT and UPDATE on the table and the application will translate a request to “DELETE” into an update of a “deleted” flag on the table. A request to Undelete a record is simply another update to set the “deleted” flag to NULL. All reports in the application have a predicate to filter out any deleted records unless the user is viewing the “recycle bin”. In addition, if the user is viewing a deleted record, I put the form into “Readonly” mode – the only thing they can do is Undelete it (thereafter, they can modify the record if they wish).

I’ve created the following view in order to show the history of changes to a record:

create or replace view mytable_hist_vw as
select x.id, x.name, etc.
      ,l.wm_createtime as op_timestamp
      ,case
       when x.wm_optype = 'U'
        and x.deleted_ind = 'Y'
        and lag(x.deleted_ind,1)
            over (partition by x.id
                  order by x.wm_createtime) is null
       then 'Deleted'
       when x.wm_optype = 'U'
        and x.deleted_ind is null
        and lag(x.deleted_ind,1)
            over (partition by x.id
                  order by x.wm_createtime) = 'Y'
       then 'Undeleted'
       when x.wm_optype = 'I' then 'Created'
       when x.wm_optype = 'U' then 'Updated'
       when x.wm_optype = 'D' then 'Deleted permanently'
       end as op_desc
      ,case
       when x.wm_optype = 'D'
       then x.wm_username
       else x.db$updated_by
       end as op_by
from   mytable_hist x

I interpret an WM_OPTYPE of 'D' as “Deleted permanently”. This might occur if we do a delete directly on the table, e.g. via a data fix run directly on the database. In this case I report the username according to the wm_username column since we won’t have the APEX user name.

Data Dictionary Views

OWM provides a number of data dictionary views:

Versioned tablesuser / all / dba_wm_versioned_tablesConstraints on version-enabled tablesuser / all_wm_constraintsTriggers on version-enabled tablesuser / all_wm_tab_triggersVPD policies on version-enabled tablesuser / all_wm_policiesWorkspacesuser / all / dba_workspaces

The Future

According to Bill Beauregard (Long Transactions with Oracle Database Workspace Manager Feature), we can expect to see some enhancements to OWM in the near future, including:

  • 128 character workspace names
  • Relaxed limitations on table, column and index name length
  • Support for invisible indexes, virtual and identity columns

Related

Oracle Documentation

Did you recognise all the TV shows referenced in this article?

Interactive Grid: Custom Select List on each row

Tue, 2018-02-27 22:19

I had a column in an editable interactive grid based on a Select List, which takes valid values from a table that supports “soft delete” – i.e. records could be marked as “deleted” without affecting existing references to those deleted records.

The SQL Query for the LOV was like this (my example is a view on a reference table of school “year levels”):

select name, id from year_levels_vw
where deleted_ind is null
order by sort_order

The problem is that if a year level is marked as deleted, the select list will not include it due to the where clause; since Display Extra Values is set to “Yes”, the item on the page will instead show the internal ID which is not very useful to the user. Instead, I want to show the name but appended with a string to show it has been deleted:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
order by deleted_ind nulls first, sort_order

So now the select list shows the name, even if it has been deleted. However, once users start using this system and they delete some year levels, each select list will include all the deleted values, even if they will never be used again. We’d prefer to only include a deleted value IF it is currently used by the record being viewed; otherwise, we want to omit it from the list.

If this was an APEX item in a single-record edit form, I’d simply change the SQL Query for the LOV to:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
where deleted_ind is null or id = :P1_FROM_YEAR_LEVEL_ID
order by deleted_ind nulls first, sort_order

This way, the select list will only include the deleted year level if the underlying item was already set to that deleted ID. But we are now using an Interactive Grid – there is no page item to refer to.

The method I’ve used to solve this is to take advantage of the Cascading LOV feature in order to allow the query to refer to the value of the column. The SQL Query for the LOV on my Interactive Grid is:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
where deleted_ind is null or id = :FROM_YEAR_LEVEL_ID
order by deleted_ind nulls first, sort_order

Now, we need to make sure that “FROM_YEAR_LEVEL_ID” is available to the query, so we need to put it in the Items to Submit attribute. To make this attribute available, however, we must set Cascading LOV Parent Column(s) to something; I set it to the PK ID of the table, or some other column which doesn’t get changed by the user and isn’t actually referred to in the LOV Query.

Now, records not referring to a deleted value show only valid values:

And records that refer to a deleted value include the deleted value in the list, as desired:

It should be noted that the design of the select list means that the user is allowed to save changes to the record while preserving the reference to the deleted year level. This is desired, in this case; if it wasn’t, however, I’d consider putting a validation on the page to stop the record being saved unless the user changes it to a valid value.

P.S. Concerned parents should note that this example was just testing the ability to delete a record from a database, and I’d just like to make it totally clear that there are no plans by the department to eliminate year 7 from schools. Honest!

ADDENDUM (19/3/2018):

There are two known issues:

  1. If the item is the child of a Cascading LOV, when the parent item is changed, APEX automatically clears out any value in the child before rendering the list of values – which means the column value submitted will be NULL – which means the “deleted” items disappear from the list immediately. This means the user will not be allowed to save the record with a reference to a deleted value from the list.
  2. The column filter list of values is empty – this is due to a known bug in APEX [Doc ID 2289512.1 FILTER NOT WORKING IN INTERACTIVE GRID WITH CASCADING LOV][thanks to Dejan for alerting me to this]

AUSOUG Connect 2017 – see you in Perth

Thu, 2017-10-12 09:13

I’ll be presenting a talk about some APEX applications I’ve been building gradually over the past five years at the AUSOUG Connect 2017 conference in Perth, Western Australia on 29 November.

My hope is that you’ll get some ideas on how best to set up the overall architecture for your APEX applications that may need to serve more than one customer – whether different organisations or different business groups within your organisation.

Although it’s just one day, there’s a lot of good content packed in for developers, DBAs and eBusiness professionals so you really don’t want to miss it!

For more info

Site migrated

Wed, 2017-10-04 20:14

I’ve recently migrated this site to a new host. Most of the content has come across unscathed but there are some remaining issues I’m working on. If you notice anything amiss, PLEASE let me know by email to errata@jk64.com – it will be greatly appreciated!

PayWay interface

Mon, 2017-08-28 21:22

This is just to document how my system (which was built quite a few years ago) handles credit card payments via PayWay. The interface is rather dated and the way my system uses it is a bit hackish but for what it’s worth:

Step 1. On the server, generate a unique transaction reference, e.g. id123456

Step 2. On the server, request a token via HTTP Post:

https://www.payway.com.au/RequestToken

Data (all one line):

biller_code=my-vendor-code
&merchant_id=my-merchant-id
&payment_reference_text=Our+Reference
&payment_reference=id123456
&payment_amount_text=Amount
&payment_amount=123.45
&receipt_address=customer@example.com
&username=my-payway-username
&password=my-payway-password
&payment_alert=bookkeeper@example.com
&return_link_url=https://example.com/return/gamesfestid12345
&return_link_redirect=true
&information_fields=CustomField1,CustomField2
&CustomField1=foo
&CustomField2=bar

Make sure all the values are url encoded.

Some of the fields are optional. The “information_fields” is a list of custom attributes that you can add if you want.

Step 3. Read the response

The response data will be something like this:

token=xyz1234567890

If it doesn’t start with “token=”, I interpret it as an error message.

Step 4. On the client, redirect browser to this URL:

https://www.payway.com.au/MakePayment?biller_code=my-vendor-code&token=xyz1234567890

Step 5. After the user comes back to your site (or in a separate server job) you have to query PayWay’s system to find out the result of the transaction, using the payment reference you generated earlier. This is a REST-style interface.

http://api.payway.com.au/rest/v1/transactions/search-customer?customerNumber=id123456

This returns a JSON document listing one or more transactions for the given reference, including transactionid (e.g. 12345678901234567890), status, transactionType, paymentAmount, and settlementDate.

Step 6. For each transaction, query PayWay’s system again to get all the details, using the transactionid retrieved earlier.

http://api.payway.com.au/rest/v1/transactions/12345678901234567890

This returns a JSON document with attributes including: receiptNumber, status, responseCode, responseText, transactionType, customerName, customerEmail, principalAmount, creditcard.cardScheme, creditCard.cardNumber, creditCard.expiryDateMonth, creditCard.expiryDateYear, creditCard.cardholderName, transactionDateTime, settlementDate, declinedDate, isRefundable

My system keeps track of the payment references generated in step #1, and periodically requeries PayWay to get the results (and I give up after about a day or two – this happens if the customer never completes the transaction).

Note that for all this to work, you have to first get the bank to enable both interfaces for your client – the token interface on http://www.payway.com.au) as well as the REST-style interface on api.payway.com.au. It took quite a few emails and phone calls before it was all working.


Filed under: Other

Apex Plugin: Password Strength Estimator

Tue, 2017-08-15 23:26

I needed a simple password strength prompt for users when they need to create or change their password on my website. After a bit of Googling I found the “Low-Budget Password Strength Estimator” which is supposedly used by Dropbox, so you know it’s good :)

This simple javascript library runs entirely within the client’s browser, and when presented with a candidate password, gives a score from 0 (very poor) to 4 (very good). It can also return extra feedback, including a warning message for poor passwords, as well as suggestions for making a password more secure.

preview-verypoor

So I’ve created a very simple Dynamic Action plugin (try the demo here) that allows you to add this functionality to any item on your page. You can specify a minimum length for the password, and can override the default messages for each score. You can also select whether or not the feedback warnings or suggestions are shown.

preview-poor

It seems to catch a lot of poor passwords, including ones comprising common words and names, and ones involving a simple sequence or repetition.

preview-good

preview-strong

Obviously it’s only really useful for password entry fields; but don’t use it on your Login page!

Download from: https://github.com/jeffreykemp/jk64-plugin-passwordstrength


Filed under: APEX, Oracle Tagged: APEX, passwords, plug-ins

Apex Reports: One Link, Multiple Destinations

Wed, 2017-08-02 01:22

Every Interactive Report has an optional set of “Link” attributes that allow you to specify the location where the user should be redirected if they click a link next to a record in the report. You can choose “Link to Custom Target” and use the Link Builder to easily specify the target application, page, item values to pass, and other attributes.

linkbuilder1

What if the report combines different entities, and you need to direct the user to a different page depending on the type of entity? Or, if you need to direct the user to a different page with different attributes depending on the status of the record?

One method is to generate the URL in the report query using apex_page.get_url (Apex 5+) or apex_util.prepare_url (Apex 4 or earlier), or (God forbid) you could generate the url yourself using string concatenation.

A more declarative solution is to instead use Apex page redirects. This solution involves the following:

  1. Add some hidden items to the page to store the parameters for each target page;
  2. Add a Branch to the page for each target page; and
  3. Add a Request to the link to signal the page that a redirect has been requested.

Here’s an example. My page 550 has an interactive report which combines TI records with TRQ records (both of which have a very similar structure). If the user clicks on a TI record they should be redirected to p510 with the corresponding TI_ID, and if they click on a TRQ record they should be redirected to p305 with the corresponding TRQ_ID.

Here’s the link attributes for this report:

linkbuilder2

Notice that the page now redirects back to itself with the request set to “GOTO_TARGET”, along with the IDs required. My report query has been constructed so that every record will only have a TI_ID or a TRQ_ID, never both at the same time; so the link will ensure that only one of the P550_GOTO_xxx_ID values will be set.

The page then just needs two Branches: one for each target. Conditions on each branch ensures they only get activated if the request has been set, and the branch is selected based on which “GOTO ID” item has been set:

branch1

branch2

For a normal navigation to this report (e.g. from another page), the request should be blank (or some other value) so none of the Branches should be triggered.

For a relatively simple scenario like this, I like the declarative approach. Each branch can take advantage of the full range of Apex features such as the link builder, security conditions, build options, etc.

Note: this method works just as well for Classic report links as well.

The thing to be mindful of is that the order of the branches, and the condition on each branch, must be carefully selected so that the correct branch is activated in each situation. I’ve shown a simple example which works because I have ensured that only one of the ID parameters is set at the same time. If a record has both IDs, the condition on the first branch “GOTO_TARGET (TI_ID)” will evaluate to True and it will be used, regardless of what GOTO_TRQ_ID was set to.

If there were numerous potential destination pages, with a large number of parameters to pass, I might choose the apex_page.get_url method instead.

Related


Filed under: APEX Tagged: APEX, tips-&-tricks

Using LetsEncrypt on Amazon Linux

Thu, 2017-07-27 22:00

sslapexFor a number of years now I’ve been using LetsEncrypt to provide free SSL certificates for the Apex applications I provide. These certificates last for 90 days and are renewed automatically by a simple script on my server.

By the way – if you’re not already using https for your public-facing Apex applications, you should, okay – even if your site doesn’t have data entry.

Each LetsEncrypt certificate can cover multiple subdomains. They don’t currently support wildcard domains (e.g. *.example.com) but they are planning to add this next year (2018).

To install LetsEncrypt I ran the following on my Amazon Linux instance (note – this is my web server, not my database server):

cd /opt
git clone https://github.com/letsencrypt/letsencrypt
cd letsencrypt
./letsencrypt-auto -v --debug

(when prompted at the last step I typed “c” to cancel the subsequent steps)

It wasn’t easy at first because I got a number of errors which I’d google (or search the community forum) and eventually find reasonable answers. I’ve had to reinstall a number of times, as the OS is patched regularly and certbot is updated from time to time.

I use Apache to provide about a dozen virtual hosts and therefore the automated installation option didn’t work for me. Instead, I’ve got lines like these in each VirtualHost:

<VirtualHost *:443>
 ServerName subdomain.mydomain.com
 ServerAlias subdomain.mydomain.com
 SSLEngine on
 SSLCertificateFile "/etc/letsencrypt/live/mydomain.com/cert.pem"
 SSLCertificateKeyFile "/etc/letsencrypt/live/mydomain.com/privkey.pem"
 SSLCertificateChainFile "/etc/letsencrypt/live/mydomain.com/chain.pem"
 ...
</VirtualHost>

To register a certificate I used the following command as root (all one line):

/opt/letsencrypt/letsencrypt-auto
certonly --webroot -w /var/www/html
-d mydomain.com,www.mydomain.com,sub1.mydomain.com,sub2.mydomain.com

This generates all the keys and certificates and stores them locally. No private keys ever leave the server. This command is using SAN to combine multiple subdomains in one certificate. I run this command again separately for each domain.

To renew all my certificates I run the following command as root:

/opt/letsencrypt/letsencrypt-auto renew -n --no-self-upgrade
service httpd restart

This will automatically skip any certificates that are not yet due to expire. I’ve put the above script in a file which is run by cron on a monthly basis.

0 20 1 * * /path-to-script/renewall.sh

To get usage info on the options:

/opt/letsencrypt/letsencrypt-auto --help

Since it’s free, one cannot expect support from LetsEncrypt directly if there are issues; however, there is an active LetsEncrypt support community which can be helpful at times.

But it’s certainly made a big difference to my bottom line, and provided a bit of peace-of-mind to my users.

Related


Filed under: Other Tagged: amazon-web-services, letsencrypt, server-config, SSL

Compiling views: when the FORCE fails you

Sun, 2017-04-02 20:01

Darth-Vader-selfieThe order in which your deployment scripts create views is important. This is a fact that I was reminded of when I had to fix a minor issue in the deployment of version #2 of my application recently.

Normally, you can just generate a create or replace force view script for all your views and just run it in each environment, then recompile your schema after they’re finished – and everything’s fine. However, if views depend on other views, you can run into a logical problem if you don’t create them in the order of dependency.

Software Release 1.0

create table t (id number, name varchar2(100));
create or replace force view tv_base as
select t.*, 'hello' as stat from t;
create or replace force view tv_alpha as
select t.* from tv_base t;

desc tv_alpha;
Name Null Type
---- ---- -------------
ID        NUMBER
NAME      VARCHAR2(100)
STAT      CHAR(5)

Here we have our first version of the schema, with a table and two views based on it. Let’s say that the tv_base includes some derived expressions, and tv_alpha is intended to do some joins on other tables for more detailed reporting.

Software Release 1.1

alter table t add (phone varchar2(10));
create or replace force view tv_alpha as
select t.* from tv_base t;
create or replace force view tv_base as
select t.*, 'hello' as stat from t;

Now, in the second release of the software, we added a new column to the table, and duly recompiled the views. In the development environment the view recompilation may happen multiple times (because other changes are being made to the views as well) – and nothing’s wrong. Everything works as expected.

However, when we run the deployment scripts in the Test environment, the “run all views” script has been run just once; and due to the way it was generated, the views are created in alphabetical order – so tv_alpha was recreated first, followed by tv_base. Now, when we describe the view, we see that it’s missing the new column:

desc tv_alpha;
Name Null Type
---- ---- -------------
ID        NUMBER
NAME      VARCHAR2(100)
STAT      CHAR(5)

Whoops. What’s happened, of course, is that when tv_alpha was recompiled, tv_base still hadn’t been recompiled and so it didn’t have the new column in it yet. Oracle internally defines views with SELECT * expanded to list all the columns. The view won’t gain the new column until we REPLACE the view with a new one using SELECT *. By that time, it’s too late for tv_alpha – it had already been compiled, successfully, so it doesn’t see the new column.

Lesson Learnt

What should we learn from this? Be wary of SELECT * in your views. Don’t get me wrong: they are very handy, especially during initial development of your application; but they can surprise you if not handled carefully and I would suggest it’s good practice to expand those SELECT *‘s into a discrete list of columns.

Some people would go so far as to completely outlaw SELECT *, and even views-on-views, for reasons such as the above. I’m not so dogmatic, because in my view there are some good reasons to use them in some situations.


Filed under: SQL Tagged: development, SQL

Show greyscale icon as red

Tue, 2017-03-14 03:03

I have an editable tabular form using Apex’s old greyscale edit link icons:

greyscale-icons

The users complained that they currently have to click each link to drill down to the detail records to find and fix any errors; they wanted the screen to indicate which detail records were already fine and which ones needed attention.

Since screen real-estate is limited here, I wanted to indicate the problems by showing a red edit link instead of the default greyscale one; since this application is using an old theme I didn’t feel like converting it to use Font Awesome (not yet, at least) and neither did I want to create a whole new image and upload it. Instead, I tried a CSS trick to convert the greyscale image to a red shade.

I used this informative post to work out what I needed: https://css-tricks.com/color-filters-can-turn-your-gray-skies-blue/

WARNING: Unfortunately this trick does NOT work in IE (tested in IE11). Blast.

Firstly, I added a column to the underlying query that determines if the error needs to be indicated or not:

select ...,
       case when {error condition}
       then 'btnerr' end as year1_err
from mytable...

I set the new column type to Hidden Column.

The link column is rendered using a Link-type column, with Link Text set to:

<img src="#IMAGE_PREFIX#e2.gif" alt="">

I changed this to:

<img src="#IMAGE_PREFIX#e2.gif" alt="" class="#YEAR1_ERR#">

What this does is if there is an error for a particular record, the class "btnerr" is added to the img tag. Rows with no error will simply have class="" which does nothing.

Now, to make the greyscale image show up as red, I need to add an SVG filter to the HTML Header in the page:

<svg style="display:none"><defs>
  <filter id="redshader">
    <feColorMatrix type="matrix"
      values="0.7 0.7 0.7 0 0
              0.2 0.2 0.2 0 0
              0.2 0.2 0.2 0 0
              0   0   0   1 0"/>
  </filter>
</defs></svg>

I made up the values for the R G B lines with some trial and error. The filter is applied to the buttons with the btnerr class with this CSS in the Inline CSS property of the page:

img.btnerr {filter:url(#redshader);}

The result is quite effective:

greyscale-colorize

But, as I noted earlier, this solution does not work in IE, so that’s a big fail.

NOTE: if this application was using the Universal Theme I would simply apply a simple font color style to the icon since it would be using a font instead of an image icon.


Filed under: APEX Tagged: APEX, CSS, tips-&-tricks

A nice Descending Index Range Scan

Thu, 2017-03-09 20:06

I’ve been aware of some of the ways that Oracle database optimises index accesses for queries, but I’m also aware that you have to test each critical query to ensure that the expected optimisations are taking effect.

I had this simple query, the requirement of which is to get the “previous status” for a record from a journal table. Since the journal table records all inserts, updates and deletes, and this query is called immediately after an update, to get the previous status we need to query the journal for the record most recently prior to the most recent record. Since the “version_id” column is incremented for each update, we can use that as the sort order.


select status_code
from (select rownum rn, status_code
      from   xxtim_requests$jn jn
      where  jn.trq_id = :trq_id
      order by version_id desc)
where rn = 2;

The xxtim_requests$jn table has an ordinary index on (trq_id, version_id). This query is embedded in some PL/SQL with an INTO clause – so it will only fetch one record (plus a 2nd fetch to detect TOO_MANY_ROWS which we know won’t happen).

The table is relatively small (in dev it only has 6K records, and production data volumes are expected to grow very slowly) but regardless, I was pleased to find that (at least, in Oracle 12.1) it uses a nice optimisation so that it not only uses the index, it is choosing to use a Descending scan on it – which means it avoids a SORT operation, and should very quickly return the 2nd record that we desire.

index_scan_range_descending.PNG

It looks quite similar in effect to the “COUNT STOPKEY” optimisation you can see on “ROWNUM=1” queries. If this was a much larger table and this query needed to be faster or was being run more frequently, I’d probably consider appending status_code to the index in order to avoid the table access. In this case, however, I don’t think it’s necessary.


Filed under: SQL Tagged: oracle12c, SQL

IRs with Subscriptions that might not work

Thu, 2017-02-23 20:31

If you have an Interactive Report with the Subscription feature enabled, users can “subscribe” to the report, getting a daily email with the results of the report. Unfortunately, however, this feature doesn’t work as expected if it relies on session state – e.g. if the query uses bind variables based on page items to filter the records. In this case, the subscription will run the query with a default session state – Apex doesn’t remember what the page item values were when the user subscribed to the report.

This is a query I used to quickly pick out all the Interactive Reports that have the Subscription feature enabled but which might rely on session state to work – i.e. it relies on items submitted from the page, refers to a bind variable or to a system context:


select workspace, application_id, application_name,
page_id, region_name, page_items_to_submit
from apex_application_page_ir
where show_notify = 'Yes'
and (page_items_to_submit is not null
or regexp_like(sql_query,':[A-Z]','i')
or regexp_like(sql_query,'SYS_CONTEXT','i')
);

For these reports, I reviewed them and where appropriate, turned off the Subscription feature. Note that this query is not perfect and might give some false positives and negatives.


Filed under: Oracle

Quick Pick in Apex Report

Mon, 2017-01-30 02:08

I have an Interactive Report that includes some editable columns, and the users wanted to include some “quick picks” on these columns to make it easy to copy data from a previous period. The user can choose to type in a new value, or click the “quick pick” to quickly data-enter the suggested value.

example-report-quickpick

Normally, a simple page item can have a quick pick by setting the Show Quick Picks attribute on the item. This is not, however, available as an option when generating Apex items in a report.

To do this, I added code like the following to my report query:

SELECT ...
      ,APEX_ITEM.textarea(5,x.ytd_comments
         ,p_rows => 1
         ,p_cols => 30
         ,p_item_id => 'f05_'||to_char(rownum,'fm00000'))
       || case when x.prev_ytd_comments is not null
          then '<a href="javascript:$(''#'
            || 'f05_' || to_char(rownum,'fm00000')
            || ''').val('
            || apex_escape.js_literal(x.prev_ytd_comments)
            || ').trigger(''change'')">'
            || apex_escape.html(x.prev_ytd_comments)
            || '</a>'
          end
       as edit_ytd_comments
FROM my_report_view x;

This results in the following HTML code being generated:

html-report-quickpick

In the report definition, the EDIT_YTD_COMMENTS column has Escape Special Characters set to No. This runs a real risk of adding a XSS attack vector to your application, so be very careful to escape any user-entered data (such as prev_ytd_comments in the example above) before allowing it to be included. In this case, the user-entered data is rendered as the link text (so is escaped using APEX_ESCAPE.html) and also within some javascript (so is escaped using APEX_ESCAPE.js_literal).

So, if the data includes any characters that conflict with html or javascript, it is neatly escaped:

html-quickpick-bad

And it is shown on screen as expected, and clicking the link copies the data correctly into the item:

example-quickpick-bad

This technique should, of course, work with most of the different item types you can generate with APEX_ITEM.

Recommended further reading:


Filed under: APEX Tagged: apex-5.0, javascript, tips-&-tricks

Powerless Javascript

Thu, 2017-01-19 19:37

I was writing a small javascript function, part of which needed to evaluate 10 to the power of a parameter – I couldn’t remember what the exponentiation operator is in javascript so as usually I hit F12 and typed the following into the console:

10**3

jspower

Wrote and tested the code, checked in to source control. Job done.

A few days later we deployed a new release that included dozens of bug fixes into UAT for testing. Soon after a tester showed me a screen where a lot of stuff wasn’t looking right, and things that had been working for a long time was not working at all.

Developer: “It works fine on my machine.”

After some playing around on their browser I noted that it seemed half of the javascript code I’d written was not running at all. A look at their browser console revealed two things:

  1. they are using Internet Explorer 11
  2. a compilation error was accusing the line with the ** operator

The error meant that all javascript following that point in the file was never executed, causing the strange behaviour experienced by the testers.

A bit of googling revealed that the ** operator was only added to javascript relatively recently and was supported by Chrome 52 and Edge browser but not IE. So I quickly rewrote it to use Math.pow(n,m) and applied a quick patch to UAT to get things back on track.

I think there’s a lesson there somewhere. Probably, the lesson is something like “if you try drive-by javascript coding, you’re gonna have a bad time.”


Filed under: APEX Tagged: exponentiation, javascript, lesson-learned

Target=_Blank for Cards report template

Sat, 2016-12-24 02:20

cardsreport.PNGI wanted to use the “Cards” report template for a small report which lists file attachments. When the user clicks on one of the cards, the file should download and open in a new tab/window. Unfortunately, the Cards report template does not include a placeholder for extra attributes for the anchor tag, so it won’t let me add “target=_blank” like I would normally.

One solution is to edit the Cards template to add the extra placeholder; however, this means breaking the subscription from the universal theme.

As a workaround for this I’ve added a small bit of javascript to add the attribute after page load, and whenever the region is refreshed.

  • Set report static ID, e.g. “mycardsreport”
  • Add Dynamic Action:
    • Event = After Refresh
    • Selection Type = Region
    • Region = (the region)
  • Add True Action: Execute JavaScript Code
    • Code = $("#mycardsreport a.t-Card-wrap").attr("target","_blank"); (replace the report static ID in the selector)
    • Fire On Page Load = Yes

Note: this code affects all cards in the chosen report.


Filed under: APEX Tagged: APEX, javascript, jQuery, tips-&-tricks

Report Link: Save before Navigation

Sun, 2016-10-30 23:46

“We always click ‘Apply Changes’, then we click the button we actually wanted” – a user

Typically an Apex report with an “Open” or “Edit” icon will simply do an immediate navigation to the target page, passing the ID of the record to edit. When the user clicks this link, the page is not submitted and the user is instead redirected.

It’s easy to quickly build large and complex applications in Apex but it’s also very easy to build something that confuses and disorients your users. They only need one instance when something doesn’t work how they expected it to, and they will lose trust in your application.

For example: if most buttons save their changes, but one button doesn’t, they might not notice straight away, and then wonder what happened to their work. If you’re lucky, they will raise a defect notice and you can fix it. More likely (and worse), they’ll decide it’s their fault, and begrudgingly accept slow and unnecessary extra steps as part of the process.

You can improve this situation by taking care to ensure that everything works the way your users expect. For most buttons in an Apex page, this is easy and straightforward: just make sure the buttons submit the page. The only buttons that should do a redirect are those that a user should expect will NOT save the changes – e.g. a “Cancel” button.

For the example of an icon in a report region, it’s not so straightforward. The page might include some editable items (e.g. a page for editing a “header” record) – and if the user doesn’t Save their changes before clicking the report link their changes will be lost on navigation.

To solve this problem you can make the edit links first submit the page before navigating. The way I do this is as follows (in this example, the report query is on the “emp” table:

    1. Add a hidden item P1_EDIT_ID to the page
      • Set Value Protected to No
    2. Add something like this to the report query (without the newlines):
      'javascript:apex.submit(
        {request:''SAVE_EDIT_ROW'',
         set:{''P1_EDIT_ID'':''' || emp.rec_id || '''}
        })' AS edit_link
      • Set this new column to Hidden Column
    3. Modify the edit link Target:
      • Type = URL
      • URL = #EDIT_LINK#
    4. Add a Branch at point “After Processing”
      • Set the Target to the page to navigate to
      • Set the item for the record ID to &P1_EDIT_ID.
      • Set the Condition to the following PL/SQL Expression:
        :REQUEST='SAVE_EDIT_ROW' AND :P1_EDIT_ID IS NOT NULL
      • Make sure the branch is evaluated before any other branches (at least, any others that might respond to this request)
    5. Modify any existing Processing so that the request SAVE_EDIT_ROW will cause any changes on the page to be saved.

You can, of course, choose different item names and request names if needed (just update it in the code you entered earlier). For example, to make it work with the default Apex DML process you might need to use a request like “APPLY_CHANGES_EDIT_ROW”.

Now, when the user makes some changes to the form, then clicks one of the record Edit links, the page will first be submitted before navigating to the child row.

Adding buttons to Apex pages is easy. Making sure every last one of them does exactly what the user expects, nothing more, and nothing less, is the tricky part!


Filed under: Oracle Tagged: tips-&-tricks

Pages