Versioning Improvements in APEX5

If you’ve browsed my blog before you’ll know I had grave concerns about the APEX export process mangling my code and impacting my version control (just in the exported format, that’s what you can manage with svn or git)

So, I’m currently attending KScope14 and taking some time to explore exports with APEX5.0EA has been a very rewarding experience!

5.0 wraps your code in wwv_flow_t_varchar2 which accepts an array of varchar2s — but each line is intelligently split on the newlines as provided in your code.

Example 5EA App export In Editor

Example 5EA App export In Editor

Presumably, there must be a limit to the line length, but with a hasty experiment I’ve verified that it’s a fairly generous 250 characters.  In the example below, I’ve inserted a line of almost 1500 asterisks and re-exported my file.

Same export, but with massively long line added

Same export, but with massively long line added

So, for anyone interested in version control of APEX apps, this is a great step.  One line can no longer interfere with the exported format of subsequent lines, which will drastically reduce false positives in diffs, and make for at least one more sane developer.

Posted in APEX (Application Express), Change Management, Uncategorized, Versioning

Import Problems with Application Express 4.2.2

While upgrading from APEX 4.1 to 4.2.2 we ran into a surprising error during application imports.  Near the end of the import we’d get a unique constraint violation on APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK, while I’m sure we’re not the only shop to run into this, it doesn’t seem very common.  As pointed out by Dave in his OTN post deleting the application prior to import allows the import to run successfully and without the unique constraint exception.  Subsequent installs still required a full delete/install, so getting the 4.2.2 version of the export installed once doesn’t fix the underlying issue.

After harassing the rest of the team to ensure nobody had fiddled directly with the flows tables I set about trying to find a more sustainable solution.  The constraint in question is created from a functional index whose definition was foreign to me

CREATE UNIQUE INDEX APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK 
 ON APEX_040200.WWV_FLOW_WORKSHEET_RPTS (
   CASE WHEN "SESSION_ID" IS NULL THEN "WORKSHEET_ID" END , 
   CASE WHEN "SESSION_ID" IS NULL THEN "APPLICATION_USER" END , 
   CASE WHEN "SESSION_ID" IS NULL THEN "CATEGORY_ID" END , 
   CASE WHEN "SESSION_ID" IS NULL THEN "NAME" END 
);

I believe this creates a composite index where each column is a simple function consisting of one CASE statement, and include a unique constraint on that index.  Supposing this means the complete tuple must be unique I set out to find two interactive reports with the same WORKSHEET_ID which could cause a collision.  For all my searching I couldn’t find anything that might cause a unique constraint violation in my export.

But I did happen across a page that contains two interactive reports with mutually exclusive conditions on the report regions (The big problem with multiple interactive reports in APEX comes from the JavaScript, not the APEX rendering).  Here’s what the two IR on the page looked like in apex_040200.wwv_flow_worksheet_rpts

multiple_ir_on_page

So, since the worksheet_ids are unique the CASE tuples should be unique.  This didn’t look like it could be the problem, but since nothing else was working I tried re-factoring the page to use a single IR and embed the conditional logic in the interactive report definition.  After stripping the page back to one IR, the export/import worked perfectly!  So, we’re back to being able to install the app in place with minimum disruption.

If you run into this error, I hope this approach works for you too.  But, I still don’t understand how this exception could be thrown here.  The part of the import that kicks off the exception is in application/deployment/buildoptions.sql and is a call to wwv_flow_api.post_import_process, which is of course obfuscated — it could be manipulating ids to be functionally dependent on the page_id, or using an internal api to raise a reserved exception, or just demonstrating my ignorance of how composite functional indexes work!

Posted in APEX (Application Express), Weird Bugs

Long Code Blocks in Exports

A while back, I posted optimistically that I’d have some suggestion for handling long code blocks in APEX application exports.  I’m sorry to report that I haven’t come up with a solution, but I’ll try to present the problem clearly and suggest some ways that it might be fixed.  If you look at a page_#####.sql file created by the export splitter you can see that a common pattern is to take the text blocks you’ve defined, whether SQL, PL/SQL, HTML, JavaScript, help text or page comments and split your text into a series of concatenation statements.  New lines get represented as unistr(‘\000a’) and the string builder enforces limits on statement size, meaning your text will be split in difficult to predict ways.

Let’s consider this as an example: Read more ›

Posted in APEX (Application Express), Change Management, Work

Unique Constraint Violations On Import

Cleaning up your application before attempting to use version control is definitely a good idea!

As we were getting everything setup, we started having Unique Constraint Violations during import (running $base/install.sql with SQL*Plus).  After some investigating it became clear that our application’s themes were a bit of a mess.   We had multiple themes in the application, which I’m sure isn’t considered a best practice.  The themes were 101. Initialized and 3. Business Look.

What happened was, theme 101 defined a Page Template called Login, as did theme 3.  During the export split, it creates Read more ›

Posted in APEX (Application Express), Change Management

Automating APEX Export for SVN

The Problem with Manual Exports

A traditional manual export of an APEX application performed through the Application Builder isn’t arduous but it can be a bit of a hassle when it comes to tying into your version control system.  For me the biggest problems are ensuring consistent export settings, and the fact that the export is monolithic — identifying changes can be a real challenge.  So my short term goal right now is automating the export and split process, making our application a first class citizen in for version control and change management. Read more ›

Tagged with: , ,
Posted in APEX (Application Express), Change Management, Work

Starting up Version Control

Background

After we finally decommissioned our old employee records system, it took a while to get the team up and running on subversion (svn).  I think its clear that source control is very important for any development process, but I get the feeling there are a lot of Oracle developers out there who aren’t using it. I can tell you this:  Leading the charge on my team’s move from manually managed (and occasionally forgotten) snapshots to svn has been one of the most rewarding experiences I’ve had since joining the team. Read more ›

Posted in APEX (Application Express), Change Management, Work

First Post!

A while back I had a performance development meeting with my immediate supervisor, and we agreed that it might be a good thing if I spent some time creating and maintaining a blog.  I argued that it would provide added documentation, and help open communication channels with the community.

Today I found myself facing an interesting (and not new) problem — how can I better incorporate our Application Express (APEX) applications into our overall change management process?

Over the next couple of days, I hope to talk about the inherent challenges, and present what we’re doing about this problem.

Posted in APEX (Application Express), Change Management, Work
Alan Warren

Alan Warren

These days I develop software using the Oracle Database, and Application Express (APEX). Outside of work I enjoy cottaging, trivia, and billiards. I'm a long time resident of Guelph, Ontario Canada.

View Full Profile →

Subscribe

Enter your email address to subscribe to this blog and receive notifications of new posts by email.