Monday, October 15, 2018

Export from APEX 5.1 after you have upgraded to APEX 18.2

I just wrapped up an upgrade for one of the development environments from APEX 5.1 to APEX 18.2 when the phone rang:

I need to export some changes I was doing in APEX 5.1, an issue has suddenly changed from low priority to high priority. The work in only in DEV, and I don't have a recent export!!  Is APEX 18.2 backwardly compatible with APEX 5.1?
 I wouldn't like to try exporting from APEX 18.2 back to APEX 5.1 - that's fraught with danger.
But it is possible to do an export from an old APEX schema provided you haven't dropped the schema, here's how..

variable name     varchar2(255)
variable contents clob
declare
    l_files apex_050100.wwv_flow_t_export_files;
begin
    l_files   := apex_050100.wwv_flow_export_api.get_application(p_application_id => &APPLICATION_ID. );
    :name     := l_files(1).name;
    :contents := l_files(1).contents;
end;
/
set feed off echo off head off flush off termout off trimspool on
set long 100000000 longchunksize 32767
col name new_val name
select :name name from sys.dual;
spool &name.
print contents
spool off
Surprisingly easy!!!
So now the developer imports the application into the TEST environment, which is still APEX 5.1, finalizes the change and make an emergency release to promote to UAT and PROD.

Monday, October 1, 2018

ORA-28014: cannot drop administrative users

Just so I don't forget :)

SQL> drop user apex_180100 cascade;

Error starting at line : 1 in command -
drop user apex_180100 cascade
Error report -
ORA-28014: cannot drop administrative users

SQL> alter session set "_oracle_script"=true;

Session altered.


SQL> drop user apex_180100 cascade;

User APEX_180100 dropped.


Tuesday, January 9, 2018

APEXExportSplitter deprecated in Oracle APEX 5.1.4

According to the Oracle APEX 5.1.4 patchset notes the APEXExportSplitter Java utility is deprecated.

That's not a bad thing, because it's simply been included into the APEXExport utility as an extra command option.

Better yet, it's been bundled into the Oracle SQLcl 17.4 utility.

In the screenshot above you can see the following commands run from within SQLcl.

!sql -version
This issues a host command to show the SQLcl version being used.


apex
Typing apex lists the apex applications owned by the current user.


!mkdir test
This issues a host command to create a new directory.


cd test
Within SQLcl change to the newly created directory.


apex export -applicationid 200 -skipExportDate -split
The bit we are interested in; the command to export an application from the database, and split it into seperate files in one statement.


!ls -l
Proving the f200.sql file was created, and then split into components in the f200 directory.

To see the full syntax available simply type "apex export" to view the help.

cardiac_own@orcl> apex export
    -applicationid:    ID for application to be exported
    -workspaceid:      Workspace ID for which all applications to be exported or the workspace to be exported
    -instance:         Export all applications
    -expWorkspace:     Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified
    -expMinimal:       Only export workspace definition, users, and groups
    -expFiles:         Export all workspace files identified by -workspaceid
    -skipExportDate:   Exclude export date from application export files
    -expPubReports:    Export all user saved public interactive reports
    -expSavedReports:  Export all user saved interactive reports
    -expIRNotif:       Export all interactive report notifications
    -expTranslations:  Export the translation mappings and all text from the translation repository
    -expFeedback:      Export team development feedback for all workspaces or identified by -workspaceid to development or deployment
    -expTeamdevdata:   Export team development data for all workspaces or identified by -workspaceid
    -deploymentSystem: Deployment system for exported feedback
    -expFeedbackSince: Export team development feedback since date in the format YYYYMMDD
    -expOriginalIds:   If specified, the application export will emit ids as they were when the application was imported
    -split:            Split the exported file
    -splitFlat:        Split with no directory strucure
    -splitUpdate:      Generate update.sql file while splitting
    -splitNoCheckSum:  Overwrite all files

 Application Example:            apex export -applicationid 31500
 Workspace Example:              apex export -workspaceid 9999
  Instance Example:               apex export -instance
 Export All Workspaces Example:  apex export -expWorkspace
 Export Feedback to development environment:
         apex export -workspaceid 9999 -expFeedback
 Export Feedback to deployment environment EA2 since 20100308:
         apex export -workspaceid 9999 -expFeedback -deploymentSystem EA2 -expFeedbackSince 20100308
Happy new year! Now, I've got some command scripts to update...

Tuesday, April 11, 2017

Auto-dismiss APEX Universal theme success messages

In the APEX 5.1.1 Universal Theme demonstration application, a code snippet is included showing how to automatically dismiss Success messages via JavaScript using the apex.theme42.configureSuccessMessages API.
Trouble is it only works in some circumstances, as can be seen in screenshot from a demonstration application below.


You can try the demo application to observe the issue, and a solution that works right now. You can download the application, which probably works on earlier versions of the Universal theme - I haven't tested it.

Wednesday, March 1, 2017

APEX 5.1 - Goodbye Component View

In Oracle APEX 5.1 it's possible to remove the Component View from the Page Designer.

From the Page Designer open the browsers console window and type the following code:

$("#componentView,[aria-controls='componentView']").remove();pageDesigner.storeTabsLayout();
This is a per user preference, and persists across APEX sessions.

For more details see John Snyders' blog, I've just repeated it here so I don't forget.

Wednesday, January 4, 2017

Oracle ACE Alumnus - so long, and thanks for all the fish

The Oracle ACE program has morphed once again, with ACEs at all levels now required to fill out their contributions in the ACE Program app (written in Oracle APEX of course).

The level of detail includes:
  • type of activity (presentation, book, article, blog, forum activity, tweets etc.)
  • substantiation (conference links, presentation links, book ISBN etc)
  • analytics where relevant (blogs, tweets)

Meaning the Oracle ACE program website is out of date:
What expectations does Oracle have of an Oracle ACE?
The Oracle ACE designation is given to individuals as recognition of his/her past contributions.
Oracle's only future expectation is that the recipient maintains his/her level of community activity.
Currently to maintain an ACE status requires 200 points of activity for a year.
I did look at entering my activity, but quickly decided it just wasn’t worth the time and effort to do so.

Here is how I calculated what I would need to do to generate 200 points using my typical activities:

A new presentation takes about 100 hours to write and prepare.
A decent blog post takes about 2-4 hours to write, a cynical one to rort the system takes a few minutes.

2 presentations (2*100 hours) presented 3 times (20 points each)             => 200 hours, 120 points
16 blogs (16*3 hours) (5 points each, unless mention "Cloud" for 15 points ) =>  64 hours,  80 points

So roughly:
6 weeks to produce content (spread over months of evenings and weekends)
1 week  to present content (travel, accommodation)

Direct cost to present content (lost income, travel and accomodation expenses) => 2 weeks salary
Indirect cost to family and work/life balance - incalculable.

Plus the time taken to record activities; all this effort for something that is just a nice accolade.

I don’t get any financial benefit for presenting, either directly or indirectly, it’s just my way of giving back to the Oracle community and sharing my insights. Similarly with blogging, I only blog when I’ve got something worth sharing.

I believe the ACE program contributions criteria will have the following impacts:
  • large reduction in the number of ACEs (probably the intent)
  • a number of disenchanted and disgruntled ex-ACEs
  • rules driven behaviour to maintain status at the cost of quality/integrity
  • ACEs predominantly coming from consulting/training firms with a direct financial incentive.

Overall I think I’m better off to not bother with the ACE program, hence I've now converted to an Oracle ACE Alumnus.
Instead I'll contribute when I have something worth sharing,  and use the time/money recouped with my wife and family.

So in the words of Douglas Adams, "so long, and thanks for all the fish".


Monday, January 2, 2017

Always read the latest documentation

Something that stuck in my mind from a presentation I saw several years ago was:

Always read the latest documentation, even if you're not using that version

- Connor McDonald

 So just before Christmas APEX 5.1 was released, giving me some new APEX 5.1 documentation to read over the break. Yeah - "get a life" I hear you thinking.

Where I'm currently working we're already pretty current (Oracle 12.1 database, APEX 5.0), so I'm hoping I can convince the DBA to upgrade to APEX 5.1 reasonably soon. For me the case to upgrade is pretty simple - the application was built in 5.0 using declarative development as much as possible, has legacy features switched off, and only went live a few weeks ago, so not much data or users just yet.

Obviously reading the latest documentation is completely relevant in this situation, but why would you read the latest documentation if you're unlikely to upgrade for some time?

Here's a couple of reasons:

  • documentation improves with new releases just like code does, so you will often see better examples, improved content, or features that were previously un(der)documented.
  • by knowing whats in newer versions of APEX, you can make better decisions on how to build your application and thus future proofing todays solutions
  •  APEX 5.1 has quite a few deprecated features and desupported features, so knowing what they are allows you to start pro-actively removing them from your application now, even before an official application upgrade project kicks off.
I'm sure you can think of more/better reasons, so now you have no excuse - get reading!!

Thursday, May 7, 2015

APEX 5.0 Universal theme migration guide


Looking for information on migrating to the APEX 5.0 Universal Theme?

There's no place better to start than the APEX 5.0 Universal theme migration guide

Wednesday, March 25, 2015

Oracle APEX - Performance tuning sequences


I've been recently reading about performance issues for sequences with cache values set too low.

  • Daniel Morgan www.morganslibrary.org, covers it in his ACE Director Performance Tuning Bootcamp presentation
  • Tom Kyte says "you would be amazed what setting a sequence cache via alter sequence to 100,000 or more can do during a large load -- amazed."
  • Simon Pane from Pythian goes into great detail in a blog post performance issues with the sequence nextval call. He also show to detect when sequences are impacting performance.

With APEX 5.0 nearing general release, I thought it would be interesting to see what the cache value was set to for WWV_SEQ. In case you don't recognize the sequence, it's used by the APEX engine as part of a calculation to generate globally unique ids for APEX items.

A quick check on apex.oracle.com reveals the APEX team have been thinking about it also:

select sequence_owner, cache_size, last_number
  from all_sequences
 where sequence_name = 'WWV_SEQ';

SEQUENCE_OWNERCACHE_SIZELAST_NUMBER
APEX_0500001006939179
APEX_04020020719290506

Interesting to see the cache size was set to the default value of 20 in APEX 4.2 and is now set to 100 in APEX 5.0. That's the "sweet spot" identified in Simon Panes blog also.

So, if you have an active Oracle APEX 4.2 instance, it may be worthwhile investigating..

Monday, February 2, 2015

APEX 5 EA 3 - Universal theme

Oracle APEX 5 early adopter 3 just came out.

I've been taking the Universal Theme Sample Application for a quick test drive.


I'm truly impressed by what I've seen so far, and a little sad at the same time..

Oracle APEX developers typically come from a database development background, so have strong database design and PL/SQL skills, with relatively few of them having strong web design skills.

In some respects, this has held back the adoption of Oracle APEX in organisations. Developers have been able to quickly build useful and functional web applications using the provided themes with relative ease.

Functional Lada
Sexy Veyron Super Sport
Trouble is "functional" websites don't evoke the same emotional experience as a "sexy" website, just like a functional car like a Lada doesn't evoke the excitement and desire of a super car like a Veyron (pinched from Joel Kallmans' blog).

Out there in "user-land" functional and practical websites don't engage our customers in the same way a visually appealing and well thought out interface does. The visual appeal of any website plays a vital role in user satisfaction. A visually appealing website give the user a positive first impression, and builds the desire to work with it.

Now thanks to the Universal Theme, having a "sexy" website is within the reach of every APEX developer. It looks fantastic out of the box, chock full of the latest UI components and highly configurable with little or no knowledge of CSS. With Oracle APEX 5.0 everyone can build web applications that are both functional and sexy.

To the APEX development team, I tip my hat to you and say very well done.

So why do I feel a little sad; it's always been a fun putting together a great UI for an application to transform it from functional to appealing. I won't get to do that so much now, it looks great already.

Thursday, February 27, 2014

Oracle APEX Listener is no more..

Just noticed the Oracle APEX Listener is no more.. it's now known as Oracle Rest Data Services



Notable items on the website:
  • Can be used for Application Express or any Oracle web-toolkit application
  • Apache Tomcat support is back
  • Default context path has changed from /apex to /ords, but you can change it back to /apex easily.

Tuesday, September 17, 2013

APEX 4.2.3 is out now.

Just noticed APEX 4.2.3 is available, with patch release notes.

Haven't had much time to look at it yet, but noticed Twitter Bootstrap 3.0 is included.

Saturday, June 29, 2013

Why go to conferences


KScope13 has just wrapped up, and it's been a really enjoyable conference. I've learnt a lot, caught up with some friends, and had lots of fun also.

I thought I'd share some of the reasons I go to conferences.


  1. To learn something new
    I always try to find at least one or two things that will justify the time/expense of attending a conference to my employer. If you bring back something that solves an issue you have in your business, or a new approach which improves the way you work, or solves a performance bottleneck, then you've justified the trip to your manager. So hopefully next year, you'll get to go again.
  2. To validate the way you work
    Often due to the specialized nature of your work, even in large organizations, there may only be a few people in your organization doing what you do. So even when your part of a large IT team, there's really very few people you can bounce ideas off, or discuss in depth the approach your taking to solve a business problem.
    It's really nice to be able to talk to like minded people, or hear from the experts that the issues and challenges you face, are the same ones they face. And perhaps more importantly, the approach you've taken to solve those issues is both sensible, and similar to what other people are also doing.
  3. To network with others in person
    These days with online forums we're often communicating with people from all around the world. It's really nice to meet them face to face. I've been fortunate to have met several members of the Oracle team, as well quite a few high profile presenters in person. It constantly amazes me how much personality comes across in emails and forum posts, and when you finally meet them, you feel like you already know them.
    Sharing a meal and a few drinks (sometimes quite a few) at conference events really builds those relationships. That can really help when you hit a roadblock, and need to ask for help; it's also nice when you can pay that forward by helping someone else out also.
  4. To raise your profile
    In these uncertain times you never quite know what the future is going to hold for you. So getting your name known in the Oracle community may have longer term benefits; it may open new opportunities for you.
  5. To give something back
    I've been volunteering and presenting at conferences for over 10 years now; it's my way of giving something back to the Oracle community, sharing my knowledge and helping in a small way to make sure the Oracle community is a strong and vibrant community.
So that's some of the my reasons why I go to conferences, please feel free to comment and add your reasons.

Sunday, January 6, 2013

Never write down your New Year resolutions

It's a brand new year, and I'm back from a short holiday, refreshed, re-invigorated and ready to make  some positive changes in 2013.

Now you should never write your New Year's resolutions down, particularly where somebody else might see it.

After all, 88% of all resolutions end in failure, so do you really want to leave a record to remind yourself at the end of the year of all the things you promised to do, and never managed to achieve?

Despite that, here's my list of personal goals for the year.

1. Start playing pennant squash again  

Late last year I started playing squash socially against one of the young guys at work after not touching a racquet in more than 10 years. While I was left barely able to walk, and redder than a ripe cherry by the end of the first match - I really enjoyed it. We've played several times since, each time the quality of the match has noticeably improved and I'm running around the court with much less effort.

We are getting pretty serious now, both armed with new racquets, and gearing up to play in a competition in March. Between now and the competition start we need to join a club, play a few grading games and get match fit. To that end, I've bought a pedometer and am trying to do 10,000 steps a day - squash is the only sport I know of that specifically covers vomiting in the rules.

2. Rebuild the AUSOUG website

This is something I volunteered to do, and have been working in most evenings over the last year, but not published yet. The aim is to go live in late February.

So far, I've built a content management system in APEX to maintain the website, produce eBulletins, schedule events, process conference paper submissions and the like. All that has been straight forward.

The website itself is built using Oracle Portal 10.1.4 which is much more challenging (read annoying). It's quite amazing that there are so many similarities with APEX, yet it is such a dog of a product.

3. Write an article or two for a User Group magazine

It's a real challenge facing all the Oracle user groups these days to get people to submit articles. So I'm going to do my bit - what about you?

If you're interesting in submitting an article just email me at the address below, I'll pass it on to the AUSOUG editor. We share articles with UKOUG and ODTUG, so you can get published all around the world.

4. Do a series of blogs on integrating Twitter Bootstrap into APEX

I've been using Bootstrap with APEX 4.0 (and also Oracle Portal) for about a year now, and am keen to see how much easier it is to implement in the latest APEX 4.2.1 release, which now supports grid based layouts.

Topics I plan to blog on:
  • Basic integration into page templates
  • Specific templates like standard reports, possibly IRR styling
  • List templates combined with Bootstrap components (Navbar, toggable tabs)
  • Plugin integration (AJAX enabled carousel)
  • whatever else I encounter along the way.
Anyway, that's enough resolutions from me - hope you have a great year.

Tuesday, October 23, 2012

Responsive Design in Oracle APEX - not just APEX 4.2

One of the hottest topics in web design in 2012 has been Responsive Design - the ability for a web page to dynamically re-size to fit on a desktop, tablet or mobile phone.

The reason it is such a hot topic is simple; there is no such thing as a standard screen size any more. You can't even pick a common denominator such as 1000 pixels wide and say "that will cover most computers, from laptops up to the massive dual screen desktop you told your boss you couldn't be without".

Sometime in the near future mobile devices will out-number desktops. According to which source you read it's somewhere between 2014 and 2016. Which means you should start factoring it into your applications today.

I'm really excited that APEX 4.2 supports declarative responsive design, but it may be a while before I get to  use it. In the mean time, there's no reason why you can't build it into your applications in older APEX versions today. It just requires a little more thought and ingenuity.

That's part of what I'm presenting on at the AUSOUG 2020 Foresight Conference in Perth next week.

Here's a couple of screenshots from a very simple responsive application written in APEX 4.0.


And here's the same page on a mobile phone

What's that charming expression about "eating your own dog food".

Thursday, May 31, 2012

Loading images into Oracle XDB

There are a number of different ways to load images (and other filetypes) into Oracle XDB. You can use FTP, WebDAV or PL/SQL.

For APEX installations using the Embedded PL/SQL Gateway (EPG) images are loaded onto the server, and then loaded into XDB using PL/SQL script apxldimg.sql. With a couple of minor edits to change destination folders, the apxldimg.sql script can be re-purposed for your own applications.

The script creates a database directory, and then reads a XML file (default is imagelist.xml) to identify the directories to create and files to upload.

To generate your own imagelist file, you can use the following Windows batch script from a command window directing the output to a filename.

   imagelist.bat > imagelist.xml


@echo off
@REM *****************************************************************
@REM * File:    imagelist.bat
@REM * Author:  Mark Lancaster May 2012
@REM * Purpose: For APEX applications using the Embedded PL/SQL Gateway.
@REM             Generate XML list of directories and files suitable
@REM             for loading into Oracle XDB.
@REM             
@REM             Refer APEX file "apxldimg.sql" for example usage.
@REM
@REM             Direct output to a file e.g.  imagelist.xml
@REM *****************************************************************

setlocal enabledelayedexpansion

echo ^<upload^>
echo     ^<directories^>

REM list directories with relative path

for /F "tokens=*" %%c in ('dir /ad /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<directory^>!relpath!^<^/directory^>
)

echo     ^<^/directories^>
echo     ^<files^>

REM list files with relative path and leading slash

for /F "tokens=*" %%c in ('dir /a-d /ogn /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<file^>^/!relpath!^<^/file^>
)

echo     ^<^/files^>
echo ^<^/upload^>

Monday, October 3, 2011

Not happy Jan - follow up

A while ago I posted Not Happy, Jan! about the changed behavior of list templates for hierarchical lists.

The changed behavior in APEX 4.1 meant my atypical hierarchical template which produced JSON  instead of HTML lists no longer worked.

I've re-implemented the template to handle APEX 4.1. To see it in action, and how it works go to http://apex.oracle.com/pls/otn/f?p=PLAYPEN:2025


Tuesday, September 27, 2011

Dynamic Actions - Changed behavior in APEX 4.1

APEX 4.1 Dynamic Actions have a changed behavior over 4.0 version when executing a Static Assignment or JavaScript Expression against multiple elements.

Reading that back makes it sound sufficiently obscure that it may not be an issue for you. Hopefully not, but let's run through the changed behavior, the reasons behind it, how to detect it and how to resolve it.

Firstly, some background.
As part of a demo on dynamic actions, I set the value of 3 items using a dynamic action, as shown below:
apex_4_0.png

This worked perfectly fine until I upgraded to 4.1, where the first items' value was set, and the remaining item values are set to null.

Comparing the Dynamic Actions JavaScript for setValue (JavaScript Expression) code for 4.0 to 4.1 reveals why the change occurs.

apex_dynamic_actions_4_0:
setValue : function(){
        ...snipped...
    
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lResult = eval(lJavaScriptExpression);
            if (lResult){
                this.affectedElements.each(function() {
                    $s(this, lResult);
                });
            }
        }
    },
apex_dynamic_actions_4_1:
setValue : function(){
        ...snipped...
    
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lJSExpression = eval(lJavaScriptExpression);
            this.affectedElements.each(function(i){
                // Set the value to the first affected element, null the rest.
                lValueToSet = (i===0?lJSExpression:'');
                $s(this, lValueToSet, null, lSuppressChangeEvent);
            });
        }
    },
As the comment indicates, the changed behavior is by design.

Reasons behind the change
Anthony Rayner from the APEX development team was kind enough to explain the reasoning behind the change:
This is indeed changed behaviour as you describe, introduced in 4.1. I don't know if you've seen this already, but we added a section to the release notes on just this. I have copied this below for your convenience:

4.9 Dynamic Action Set Value with Multiple Affected Elements

In dynamic actions, prior to Application Express 4.1, it was possible to use the Set Value action to set multiple Affected Elements, but only to the same value, not different values. In Application Express 4.1, we introduced the following new capabilities to set multiple values from dynamic actions:
  • Action 'Set Value' > Set Type 'SQL Statement' - You can now define a SQL Query to return between 1 and 100 columns, which is used to set the 'Affected Elements' in the order defined (1st column is used to set 1st affected element, and so on). Previously, this only supported querying for 1 column, but could be used to set multiple affected elements to that same 1 column value.
  • Action 'Execute PL/SQL Code' - A new 'Page Items to Return' attribute that sets any page item values on the page to their updated values in session state.
With the introduction of some built-in options to retrieve multiple values, Application Express no longer supports the ability to retrieve 1 value that could then be used to set multiple page items. This was done for simplicity and usability. If you had defined a 'Set Value' dynamic action that set multiple Affected Elements to the same value, here is how you can fix this, according to the 'Set Value' dynamic action's 'Set Type':
  • Static Assignment - Define additional 'Actions' for each Affected Element, setting them all to the same value.
  • JavaScript Expression - Define additional 'Actions' for each Affected Element, setting them all to the same value.
  • SQL Statement - Alter your SQL Statement such that it selects the same column value, with different column aliases for each Affected Element.
  • PL/SQL Expression - Consider changing this over to use the 'Execute PL/SQL Code' action type, in conjunction with the 'Page Items to Return' attribute. The 'PL/SQL Code' would need to ensure the page item's value is updated in session state, such that 'Page Items to Return' returned the updated value.
  • PL/SQL Function Body - Consider changing this over to use the 'Execute PL/SQL Code' action type, in conjunction with the 'Page Items to Return' attribute. The 'PL/SQL Code' would need to ensure the page item's value is updated in session state, such that 'Page Items to Return' returned the updated value.

So really this change was prompted by the introduction of support for setting multiple different values from 1 'Set Value > Set Type (SQL Statement)'. This introduced a 'positional' notation of sorts for this set type, whereby affected elements map, by position to values returned from the SQL Statement. We felt with this introduction, that it would be simpler not to continue to support the setting of multiple affected elements to the same value, but rather that if this is desired, that one of the above techniques should be used instead, depending on set type. We felt this in the long run was simpler and more intuitive and that hopefully this would have minimal impact when upgrading from 4.0.
Hmm, that makes the reasoning behind the change a lot clearer. I really like the idea of being able to use a single dynamic action to update a bunch of fields at once.

For example, you could change the shipping method for goods delivery. Using a Dynamic Action with a "PL/SQL Code" action type you could update the shipping charges, subtotal amount, expected delivery date and terms & conditions all at once. Nice!

How to detect if this impacts you
Running the following query in 4.0 or 4.1 will identify any dynamic actions that are affected:

select a.workspace
      ,a.application_id
      ,a.application_name
      ,a.page_id
      ,a.page_name
      ,a.attribute_01
      ,a.dynamic_action_name
      ,a.action_name
      ,a.action_sequence
      ,a.dynamic_action_event_result
      ,a.affected_elements
  from APEX_APPLICATION_PAGE_DA_ACTS a
 where a.action_code = 'NATIVE_SET_VALUE'
   and a.attribute_01 in ('JAVASCRIPT_EXPRESSION','STATIC_ASSIGNMENT')
   and a.affected_elements_type = 'Item'
   and instr(a.affected_elements, ',') > 0;


Adjusting for the changed behavior
In my example I already have the value I want to assign, the most appropriate alternative here is to use a JavaScript Expression. Instead of defining a single Dynamic Action to affect multiple items, just create multiple actions each updating a single item as shown below.


Sunday, September 11, 2011

APEX CSS Repository: How to include background images easily using Data URIs

One of the challenges with loading CSS files into the APEX CSS Repository is dealing with references to background images.
In the screenshot from my demo site, I'm using a series of background images for the toolbar items.

Typically when you are hosting your own web-server, your web assets (images, CSS and JavaScript files) are loaded onto the web-server.
The CSS file will contain references to background images like:
.ico-add {
    background-image: url( ../images/icon/add.gif ) !important;
}
.ico-delete {
    background-image: url( ../images/icon/delete.gif ) !important;
}

The CSS rules reference the background-image by specifying a relative path to the image location.

If you loaded the CSS file into the APEX CSS Repository, you need to adjust the path to the image location to  use an absolute path like:
.ico-add {
    background-image: url( /ux/playpen/resources/images/icon/add.gif ) !important;
}
.ico-delete {
    background-image: url( /ux/playpen/resources/images/icon/delete.gif ) !important;
}

Adding a path alias to your web-server config to resolve the path on the server completes the task:
<Context path="/ux"       docBase="C:\playpen\web\ux" reloadable="true" crossContext="true" />

It's more complicated on Hosted APEX websites
On a hosted APEX website like apex.oracle.com you don't have access to the web-server so you need to load the files into the APEX Repository. Let's also assume you don't have access to 3rd party web-server to host the files either :)

So now your images, JavaScript and CSS files are loaded into the Repository and the page templates have been updated to include the JavaScript and CSS files by using the #APP_IMAGES# or #WORKSPACE_IMAGES# substitution tags:

<html>
<head>
    <title>#TITLE#</title>
    #HEAD#
    <link rel="stylesheet" href="#WORKSPACE_IMAGES#playpen.css" type="text/css">
</head>
Ideally, you would like to use the #APP_IMAGES# or #WORKSPACE_IMAGES# substitution tags within the CSS file:
.ico-add {
    background-image: url( #WORKSPACE_IMAGES#add.gif ) !important;
}
.ico-delete {
    background-image: url( &WORKSPACE_IMAGES.delete.gif ) !important;
}
But APEX 4.1 and earlier versions do not substitute either the hash tag or substitution variable alternatives shown.

This leaves two less desirable options:
  • embed the stylesheet directly in the page template, which resolves the substitution tags but adds overhead to every page, or,
  • replace the #WORKSPACE_IMAGES# reference with it's resolved name in the CSS file
    e.g. #WORKSPACE_IMAGES#add.gif becomes http://apex.oracle.com/pls/otn/wwv_flow_file_mgr.get_file?p_security_group_id=441224701954687600&p_fname=add.gif
    This makes the application less transportable.

Data URIs to the Rescue
Data URIs provide a way for data to be included inline in a web page as though it was an external resource. The data URI format RFC 2397 is pretty simple:
data:[<MIME-type>][;charset=<encoding>][;base64],<data>

Encoding our previous example looks like:
.ico-add {
    background-image: url(data:image/gif;base64,R0lGODlhEAAQAOZ3AHe8YHO6XDSBLIe4cF+eUKDZlq/fppfVi6vUnLTZparUnW61WFaYSJbUi4/CfTmFMH2+Z73ktpbTiXeuY0CJNnnBZNTrz4e4cbXbqI7MhHzDa7jdr1OWRZXUimyqWkKNNrvcr3i0ZIm+eIrCec7pyVCZQXy6ZKbQloXNeZPNiKDZlWirU3DBY0KLOODy3LbdrESMOrLbqInIf42+e53YknG+X3rGa2WrVYHMdXS3XLfgr7PZpZfMhTSCLMXowJTTirfaqrTapqXbm2+1WLXfrKbPlm+1WX25a3WsYT2INGajVmuqWH6yaXyxZzeEL0OPOHqvZUeOPLPdqYO1bZnRi5jMinm9bsvmw37Hb6TOlGmlWH25an28Zn7BabTdqobJejyHMozLgV2cTY7Sg5LFgICzatzx2K/fpX+5bXS3W5jMh4rPfYbJfNfu0oS2bmqsW77itleeRXK5W5jLhpfHhXC4Wf///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAHcALAAAAAAQABAAAAengHeCg4SFhnduZU0Th4MXWVcWJBtoWocDIC4RQgVnPiMEhQNAZjQpGQ0dYzpHDIR0bSo/MnZ2KBpYRHGDUy8GYWxWtW8AAVRkUYJMcAdftc92dV0KLYJQUms40LV1EAhJgkhVEjYsN9x1cjwzD4MrXhU1ANwLOUFPhEpbMcV1RkNpdnjoUUhMCAxquJiYk2CJgEMcSjg4UUTEh4eN7sCgAMZJxo+DAgEAOw==) !important;
}
.ico-delete {
    background-image: url(data:image/gif;base64,R0lGODlhEAAQAOZwAPaEbPqdi7dKK+6Sj+BpavGdlvCMd/eTfvmSfvnLxPm5r9pkYfJ3Y99qavqjke2TjupcUMZVQsxbTPWmofi2qONuY8tZSbxQMviMdvSOgbxLMNFeU+l/eOl7cL5PNsFNM/3c2PNzXfupnPiUf/qah/q6rulbT/KtqvzHuupmXOqDff3b1PCRf89hU8RQNNpjX+R4cfivpOZaUu5mUvCFcNZgVPjDvvWjn/aroverocxSPPqTf/SinvN5Y++GfuljW/iGcPqrneNrYeh6deBlXPzUzfSgnuJwZrhKKuZWTOJ7dcBTOvaCaPCDe+6MgddiXN1qaPBkVu6KdfnTzPGcluZYTNxkY7pLLtBPPt5qZ8dXRfWvpb5TONddUfOno71RNeZWUO6EffeRfrhOLttpZPWsofGOevS0qttWS+ZZTtljXeddWe5qXuxfVOlZTf///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAHAALAAAAAAQABAAAAeigHCCg4SFhnBQVgtqh4MNDzZTCWdHG4cEJyAlDgEiKB0ShQReKyQsZiMIGBQVWoQqRQEHUgC1AD0xWINZW0EGbcDBDBkcS4JkCjs0b8zNbmwFHoIvOUBMzc4pVBeCT05iIW7i4yZNSleDaDhRM+NuVTI3LoQtQmVtEG5pSWA8NUiFLBCZ4OPHmjBGugg4FEHHkAEDYHxY2AgOly8axlTcOCgQADs=) !important;
}

While it looks much more complicated and bloated, Data URIs provide a performance benefit because they reduce the number of HTTP requests, allowing pages to load faster.


Most modern browsers support data URIs:
  • Firefox 2+
  • Opera 7.2+ - data URIs must not be longer than 4100 characters
  • Chrome (all versions)
  • Safari (all versions)
  • Internet Explorer 8+ - data URIs must be smaller than 32k
For IE6 and IE7 you need to use a separate browser dependent CSS file, but the rapid decline in usage for these legacy browsers will remove the issue over time.

How to encode Data URIs in your CSS file
I'm using CSSEmbed by Nicholas C. Zakas, a Java command line utility that reads in a CSS file, identifies the images referenced within, converts them to data URIs, and outputs the resulting style sheet.
The basic syntax is:

java -jar cssembed-x.y.z.jar -o <output filename> <input filename>
To learn more about the utility see Nicholas's blog on Automatic data URI embedding in CSS files.

Saturday, August 20, 2011

Oracle Real World Performance Day

I've just got back from attending the InSync11 Oracle Real World Performance Day held in Sydney, Australia.

Absolutely inspiring day, with 3 renowned Oracle performance experts in Tom Kyte, Andrew Holdsworth & Graham Wood sharing the stage the entire day, giving their insights from the developer, dba and system architect perspectives.

The format was very conversational, with a discussion about the principles behind a topic, examples the presenters have encountered and then demonstrations of how changing values or coding approaches impacts performance. There is nothing quite like seeing a live demonstration of row by row processing compared with set based processing, or how setting open_cursors too high can bring down performance.

Materials from the presentations are available:

The day started with a data warehouse story of an ETL performance issue, with the networking team, dba and BI teams in a room, each saying their piece of the puzzle was fine; the database was idling, network was performing fine and the ETL process hadn't changed, but overall performance had recently degraded...

Deja vu, at this point a DBA colleague from work and I looked across at each other - we had been it this exact position a few months earlier. The discussion from the experts around this was insightful; if only they had been present when we were tearing our hair out trying to solve the issue.

The day only got better from there on. Tom even mentioned the APEX 4.0 and Library Cache Latch Contention issue in Oracle DB 11.2.0.2.

If you get the opportunity to attend a Real World Performance Day as they tour the globe, I highly recommend it.