APEX – Dynamic Application Version Numbers

tech16logo

At this years Conference APEX Roundtable on Tuesday one of the presenters mentioned that they were having an issue with having the Application Version reset dynamically.

I have used a simple method for doing this and thought I’d add it here in case it is useful to others.

The following steps are needed.

  1. Create a database table to hold version number information. If you have multiple environments (e.g. Dev, Test, Production) then having the table accessible to all may help with reporting your application versions across the estate.
  2. Build a simple trigger on the table.
  3. Build a PL/SQL Package to manage the table. (In future I would like to make a REST service available to manage and return the version details)
  4. Define 1 or 2 Application Items (The second is for the environment)
  5. Define 1 or 2 Application Processes to set the above Application Items
  6. Set the Application Version attribute
  7. Add record(s) to the table

End Result

The end point of the above steps will be something like this.

Image showing version text as "Ver: 17.9.650"

Not very exciting, but useful.

Step 1: Create Database table

CREATE TABLE "DEMO_APPLICATION_VERSIONS"
 ( "APPLICATION_VERSION_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE NOT NULL ENABLE, 
 "APPLICATION_ID" NUMBER,
 "APPLICATION_ENVIRONMENT" VARCHAR2(240),
 "VERSION" VARCHAR2(240),
 "VERSION_REASON" VARCHAR2(400),
 "VERSION_CREATED_DATE" TIMESTAMP (6) DEFAULT systimestamp,
 "VERSION_CREATED_BY" VARCHAR2(240),
 CONSTRAINT "DEMO_APPLICATION_VERSIONS_PK" PRIMARY KEY ("APPLICATION_VERSION_ID")
 USING INDEX ENABLE
 )
/

CREATE UNIQUE INDEX "DAV_UK01" ON "DEMO_APPLICATION_VERSIONS" ("APPLICATION_ID", "APPLICATION_ENVIRONMENT", "VERSION_CREATED_DATE")
/

CREATE UNIQUE INDEX "DAV_UK02" ON "DEMO_APPLICATION_VERSIONS" ("APPLICATION_ID", "VERSION_CREATED_DATE", "APPLICATION_ENVIRONMENT")
/

APPLICATION_ID: (Mandatory) APEX Application ID

APPLICATION_ENVIRONMENT: (Optional) Text string holding the Environment this version is deployed in. (If you only have one Environment, set this to PRODUCTION.)

VERSION: (Mandatory) This the Text of your version.  E.g. 17. 9.650.

VERSION_REASON: (Optional) Why this version deployed.  May relate to JIRA Stories/bugs, Ticket numbers as required.

VERSION_CREATED_DATE and VERSION_CREATED_BY: System generated.

Step 2: Build the Trigger

create or replace trigger demo_application_versions_trg
 before insert or update or delete on demo_application_versions
 referencing new as new old as old
 for each row
declare
 l_user varchar2(240) ;
 l_systimestamp timestamp(6) := systimestamp ;
 e_application_id_missing exception ;
 e_update_not_permitted exception ;
 e_deletion_not_permitted exception ;
begin

 if inserting then

 if :new.application_id is null then
 raise e_application_id_missing ;
 end if ;

 :new.version_created_by :=
 nvl(v('APPUSER_ID'), sys_context('userenv','CURRENT_USER'));

 :new.version_created_date := l_systimestamp ;

 :new.version :=
 case when :new.version is null then
 'UNDEFINED' else
 :new.version end ;

 :new.application_environment :=
 case when :new.application_environment is not null
 then :new.application_environment
 else demo_versioning_pkg.c_application_environment
 end
 ;

 elsif updating then
 raise e_update_not_permitted ;
 elsif deleting then
 raise e_deletion_not_permitted ;
 end if ;

exception
 when e_application_id_missing then
 raise_application_error(-20500, 'Application identifier not present.') ;
 when e_update_not_permitted then
 raise_application_error(-20501, 'Application version reference cannot be changed.') ;
 when e_deletion_not_permitted then
 raise_application_error(-20502, 'Application version records cannot be deleted') ;
 when others then
 raise ;
end ;
/

Step 3: Build the PL/SQL Package

create or replace package demo_versioning_pkg
 as

c_application_environment constant 
              demo_application_versions.application_environment%type 
              := 'PRODUCTION' ;

function add_new_version(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type,
 p_version in demo_application_versions.version%type,
 p_version_reason in demo_application_versions.version_reason%type
) return demo_application_versions.application_version_id%type ;

procedure add_new_version(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type,
 p_version in demo_application_versions.version%type,
 p_version_reason in demo_application_versions.version_reason%type,
 p_application_version_id in out demo_application_versions.application_version_id%type
) ;

function get_latest_version(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type
) return demo_application_versions.version%type ;

function get_version_history(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type
) return clob ;


end demo_versioning_pkg ;

/

… and the body

create or replace package body demo_versioning_pkg
 as

function add_new_version(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type,
 p_version in demo_application_versions.version%type,
 p_version_reason in demo_application_versions.version_reason%type
) return demo_application_versions.application_version_id%type
is

 l_application_version_id demo_application_versions.application_version_id%type ;

begin

 insert into demo_application_versions
 (application_id
 , application_environment
 , version
 , version_reason)
 values (
 p_application_id
 , nvl(p_application_environment, demo_versioning_pkg.c_application_environment)
 , p_version
 , p_version_reason
 )
 returning application_version_id into l_application_version_id
 ;

 return l_application_version_id ;

end add_new_version ;

procedure add_new_version(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type,
 p_version in demo_application_versions.version%type,
 p_version_reason in demo_application_versions.version_reason%type,
 p_application_version_id in out demo_application_versions.application_version_id%type
)
is

 l_application_version_id demo_application_versions.application_version_id%type ;

begin

 l_application_version_id := add_new_version(
 p_application_id => p_application_id
 , p_application_environment => p_application_environment
 , p_version => p_version
 , p_version_reason => p_version_reason
 )
 ;

 p_application_version_id := l_application_version_id ;

end add_new_version ;

function get_latest_version(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type
) return demo_application_versions.version%type
is

 l_version demo_application_versions.version%type ;

begin
 with recent_versions
 as ( select application_id
 , application_environment
 , max(version_created_date) as most_recent_date
 from demo_application_versions
 group by application_id, application_environment
 )
 select version as l_version
 into l_version
 from recent_versions rv
 inner join demo_application_versions dav
 on (dav.application_id = rv.application_id
 and
 dav.application_environment = rv.application_environment
 and
 dav.version_created_date = rv.most_recent_date
 )
 where dav.application_id = p_application_id
 and dav.application_environment = nvl(p_application_environment, demo_versioning_pkg.c_application_environment)
 ;

 return l_version ;

exception
 when no_data_found then
 raise_application_error(-20503, 'No version data available for Application ID ' ||
 p_application_id ||
 ', Environment '|| p_application_environment
 )
 ;
 when others then
 raise_application_error(-20504, 'Attempt to retrieve latest version for Application '||
 case when p_application_environment is null then
 null
 else p_application_environment||' '
 end ||
 'ID '||p_application_id||' resulted in unexpected error "'||SQLERRM||'"') ;

end get_latest_version ;

function get_version_history(
 p_application_id in demo_application_versions.application_id%type,
 p_application_environment in demo_application_versions.application_environment%type
) return clob
is

 l_version_history clob ;

 cursor c_versions is
 select version, version_reason, version_created_date, version_created_by
 from demo_application_versions
 where application_id = p_application_id
 and application_environment =
 nvl(p_application_environment, demo_versioning_pkg.c_application_environment)
 order by version_created_date desc
 ;

begin

 null ; -- Work in Progress!

end get_version_history ;

end demo_versioning_pkg ;

/

Step 4: Create the two Application Items

demoversionapplicationitems

Set both items to have Protection Level “Restricted – May not be set from browser” and Scope “Application”

Step 5: Create the Application Processes

First Process

Name: setCurrentEnvironment

Sequence: 1

Point: On New Instance (new session)

PL/SQL Code:

BEGIN
 :ENVIRONMENT := 'DEMO' ;  - Derive/Set this however you need.
END ;

Second Process

Name: setCurrentVersion

Sequence: 100

Point: On New Instance (new session)

PL/SQL Code:

begin
  :CURRENT_APPLICATION_VERSION := 'Ver: '||
               demo_versioning_pkg.get_latest_version(
                             p_application_id          => v('APP_ID'),
                             p_application_environment => :ENVIRONMENT
               ) 
  ;

end ;

 

Step 6: Set the Application Version attribute

From the Application Properties page, set the Version field to be

&CURRENT_APPLICATION_VERSION.

Step 7: Add an Application Version record

As part of your deploy script call either the package function or procedure add_new_version.

Supply the appropriate values.

Step 7: Try it out

APEX Coffee House Utilities

The page shows the User Name and Password required.

The only change that I have made to the above code is that the Application Process setCurrentVersion is fired “On Load: Before Header (page template header).

This will allow you use the “Add New Version” page to test the utility.

I hope that some of the ideas behind this are of interest.

Stewart

Advertisements

Oracle JET MOOC – Week 3 completed

So, completed successfully and passed the final test so I’ve passed the course. 🙂   

Next efforts will be some practice to consolidate the learning.

Week 3 was interesting in a different way to the previous 2 weeks as it covered how JET takes it’s place in the world of enterprise applications including sections on  Accessibility, Internationalisation, use of Cloud Services, Security.  Also covered were Testing, Packaging and Building. 

Oracle JET MOOC

Just starting week 3 of the MOOC (Oracle Massive Open Online Course: Build Applications with Oracle JET and NetBeans).

First 2 weeks have been really good and a bit of a challenge.  The challenge part is a positive – it’s fun learning cool new stuff. And the Oracle JavaScript Extension Toolkit, also known as Oracle JET or just JET, is cool, fun and a seriously capable set of utilities.

I’m really looking forward to trying to use the kit in anger!

First though, I need to complete week 3 and, hopefully, succeed in this weeks test.

If you might be interested in finding out more on JET, check out http://www.oracle.com/webfolder/technetwork/jet/index.html. Also, check out Using JET with PeopleSoft