On Controlling Use of Minified Code in APEX

Today I encounted an issue during regression testing for 5.1. An older application that doesn't use the Universal Theme, and hasn't really been touched since 4.x, complained about the SkillBuilders modal page plugin.

The solution seemed pretty clear upon reading through this forum post - just update the relevant jquery.colorbox.js file to the latest version, no problem.

I just updated the minified version on my dev instance, jquery.colorbox-min.js, but saw no effect. After checking the code in my relevant plugin package (since all our plugin PL/SQL goes into packages), I saw it wasn't actually using the minified version, and it got me thinking.

apex_javascript.add_library(
      -- p_name      => 'jquery.colorbox-min',
      p_name      => 'jquery.colorbox',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

Quite some time ago I remember learning about the #MIN# substitution string. The idea is you include files using my.file#MIN#.js, and it only used the minified code when you weren't in debug mode. (That double negative is just for Eddie.)

Item help when adding File URLs

My initial thought was to add it myself, based on the value of debug.

apex_javascript.add_library(
      p_name      => 'jquery.colorbox'||case when v('DEBUG') = 'NO' then '-min' end,      
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

Only to quickly realise the apex_javascript.add_library API has a dedicated parameter for such a task, though it expects dot notation in the filename, not a dash: my.file.min.js

apex_javascript.add_library(
      p_name      => 'jquery.colorbox',
      p_check_to_add_minified => true,
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

What surprised me though was looking through the other plugins. I couldn't find any in my current data source that used the p_check_to_add_minified parameter, they all just expected its use.

Do plugin developers not bother with p_check_to_add_minified?

Perhaps the newer plugins at apex.world use the parameter, if so I'll stand corrected.

Or perhaps there's little value for its use in our data driven pages, once the plugin is up and running. Any APEX plugin authors care to chime in?

Re-evaluating APEX Authorization Schemes

Authorization schemes in Oracle APEX are used to control access to page, buttons, and all sorts of other components.

In my experience, these are best defined at a privilege level, where the same privilege could be allocated to multiple business roles, but that's for another post.

In this post, I want to mention a cool API function called apex_authorization.reset_cache, which helps control the behaviour of these authorization schemes.

Preface

While googling something else I stumbled across an interesting function called apex_util.reset_authorizations, only to find it was deprecated in 5.1, replaced with the same (but renamed) function in another package.

APEX_UTIL was getting big, and even though the team was trying to manage it, 5.1 was the first time I noticed it reduce the number of procedures. Too many procedures mean some gold nuggets get lost, perhaps until they're moved to a more specific package.

I'd say we're all guilty of putting a procedure in the inappropriate package or letting a "utils" package grow too big. Me probably more than many, but there's been a lot of clean-up in 5.1. If there's any documentation you read about 5.1, let it be the release notes.

I also found the function mentioned in an old email I marked for blogging about because of an OTN forum post I was listening to. Not because of the original topic, but another deprecated/renamed procedure mentioned within.

Authorization Evaluation Point

The default evaluation point for authorization schemes is once per session. This means the first time APEX comes across a component protected by an authorization scheme, it will evaluate it and remember the result.

Authorization Scheme Evaluation Point

This default option is best for performance, but if you want to afford your users the ability to pick up changes in authorization without needing to log out then back in, then you can use one of the other options.

Re-setting authorization schemes at runtime

A cool API exists called apex_authorization.reset_cache. The documentation states

"it resets the authorization caches for the session and forces a re-evaluation when an authorization is checked next".

This means you can provide a button to the user that would clear the slate for all authorization scheme outcomes in that session and force APEX to re-evaluate any authorisation schemes it encounters.

This will be handy for me because it offers a chance to do this on demand at runtime, instead of once or 'all the time'. It also does the job across multiple applications that share authentication.

This is one of quite a few library functions now available in 5.1, in fact, this particular one has been around in some form since 4.2, but buried in apex_util.reset_authorizations.

Under the hood

If you want to see the interactions in the underlying APEX table, this query will help.

select s.*, a.application_id, a.authorization_scheme_name
from apex_050000.wwv_flow_session_authz$ s
join apex_application_authorization a
  on a.authorization_scheme_id = s.authorization_id
where session_id = 16678957299354

See, it's not magic. Just clever.

References

apex_authorization.reset_cache
Providing Security through Authorization
APEX 5.1 Release Notes

Generating JSON from SQL cursor

It appears there will be a good 1001 uses for the APEX_JSON package in APEX 5, here is one of them.

Previously I had an AJAX PL/SQL callback that returned a relatively simple JSON string, using the LISTAGG technique described by Lucas here.

declare
  l_json varchar2(32767);
begin
  select listagg('{"id":"'||version||'","content":"'||version||'.x"}',',')  within group (order by null) json
  into l_json
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version
  );

  sys.htp.prn('['||l_json||']');
end getGroups;

Now it can be further simplified by taking LISTAGG out of the equation, which can be a problem for larger data sets:

DECLARE
  c sys_refcursor;
BEGIN
  open c for 
    select version    as id
       ,version||'.x' as content
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version
  );

  apex_json.write(c);
END;

The output differs only slightly
[{"id":"1","content":"1.x"},{"id":"2","content":"2.x"},{"id":"3","content":"3.x"},{"id":"4","content":"4.x"},{"id":"5","content":"5.x"}]
[ { "ID":"1" ,"CONTENT":"1.x" } ,{ "ID":"2" ,"CONTENT":"2.x" } ,{ "ID":"3" ,"CONTENT":"3.x" } ,{ "ID":"4" ,"CONTENT":"4.x" } ,{ "ID":"5" ,"CONTENT":"5.x" } ]

A few extra spaces in the apex_json version. If lower case required for JSON attributes then use double quotes around column aliases, ie: select version as "id"

Oracle 12c JSON APIs seem to be all about validating and deconstruction, while this API has a bunch of overloaded apex_json.write() modules, including support for CLOBs.

Craig Sykes demonstrated how this could be done dynamically with a simple page.

Managing APEX using APIs

You can still manage much of your APEX workspace from the command line - whether that be SQL*Plus, SQL Developer, or some other tool of choice.

They key thing is, not everything needs to be point and click - that feels a little important.

I like to frequently blow away & re-create a bunch of users, workspaces & applications in one foul swoop. I'd like to share a little of what I've done since it was a topic of discussion recently at the Perth APEXposed.

To use the APIs below, your schema will require the APEX_ADMINISTRATOR_ROLE, described by Martin here.

To create & define all the training accounts, I use the following procedure in a package I compile in my parsing schema.
Privileges are a little different when encapsulated in a package, so direct EXECUTE access on APEX_INSTANCE_ADMIN would be required to avoid the infamous PLS-00201. Permissions such as CREATE USER would also be required.

PROCEDURE define_users
  (p_nbr_users  NUMBER)
IS
  lc_user  VARCHAR2(10);
BEGIN
  << define_users >>
  FOR i IN 1..p_nbr_users LOOP

    lc_user := 'train'||i;

    << drop_user >>
    DECLARE
      e_no_user exception;
      pragma exception_init(e_no_user, -1918);
    BEGIN
      EXECUTE IMMEDIATE 'DROP USER '||lc_user||' CASCADE';
    EXCEPTION WHEN e_no_user THEN
      NULL;
    END drop_user;
    EXECUTE IMMEDIATE 'CREATE USER '||lc_user||' IDENTIFIED BY '||lc_user;

    EXECUTE IMMEDIATE 'ALTER USER '||lc_user||' QUOTA 100M ON users';
    EXECUTE IMMEDIATE 'GRANT CONNECT TO '||lc_user ;

    EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TYPE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO '||lc_user;

  END LOOP define_users;
END define_users;

All my other procedures just encapsulate the following steps and example API calls. I highly recommend specifying formal parameter names as the signature to these APIs often between APEX versions.

My procedure to define my workspace makes calls

APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(lc_user,'N','N');

Create the workspace

APEX_INSTANCE_ADMIN.ADD_WORKSPACE
  (p_workspace_id   => ln_workspace_id
  ,p_workspace      => lc_user
  ,p_primary_schema => lc_user
  ,p_additional_schemas => '')

A new call required for 4.1.1, found by Dimitri, announced by Patrick

apex_instance_admin.enable_workspace;

Set the context regarding which workspace

apex_util.set_security_group_id(p_security_group_id => ln_workspace_id);

Create a number of users, with various developer privileges.

APEX_UTIL.CREATE_USER(
        p_user_name                     => 'ADMIN'
       ,p_web_password                  => /* my attempt to keep generic accounts passwords unmentionable */
       ,p_email_address                 => 'username@sample.com.au'
       ,p_developer_privs               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'
       ,p_default_schema                => lc_user
       ,p_allow_access_to_schemas       => lc_user
       ,p_change_password_on_first_use  => 'N');

Then before installing an application, you need to set the workpace and schema

APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID(p_workspace_id);
APEX_APPLICATION_INSTALL.SET_SCHEMA(p_schema);

Then you can either nominate the application id or have one generated

APEX_APPLICATION_INSTALL.SET_APPLICATION_ID(p_app_id);
-- or
APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;

You may also choose to set such properties as application name and alias

APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME (p_app_name);
APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(p_app_alias);

Then you generate an offset for all the IDs in the export script

APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

If you're creating multiple applications for one workspace, all you need each time is

APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;
APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

To execute these APIs among calls to import previously exported applications in SQL*Plus (wasn't that a mouthful), I used

set define '^'
accept my_app DEFAULT 'C:\my_everything\f_123_example.sql'

-- set app context
exec apex_application_install.generate_application_id;
exec apex_application_install.generate_offset;
-- install app from export
@^my_app
-- rinse and repeat

And that's essentially how I do it, just bundled up to suit my needs. It'll be pretty easy for you to grab what you need and sort out your own scripts.

Scott