Health IT week 2015

Health IT week is October 5-9, 2015.

www.healthitweek.org

on-center.blogspot.com

I look forward to creating a few blog posts during the week.  We are an EHR vendor and have a unique perspective in this area.

SYS_GUID()

In Oracle PL/SQL, SYS_GUID is a built in function which returns the Global Unique Identifier (GUID) for a row in a table.  It accepts no arguments and returns a RAW value of 16 bytes.  A GUID is a sequence of characters that are supposed to be globally unique. In other words, they should never appear more than once regardless of the circumstances. You would normally use GUIDs when you need absolutely unique values that involve multiple database instances or multiple networks. The algorithm that Oracle uses is somewhat faulty in that the values are unique but they are not random (or even pseudo-random).
Source: http://psoug.org/definition/SYS_GUID.htm

Example:

select sys_guid() from dual;  evaluates to 717A66D4915946D59152CC7FD4F517CC (in this instance).

select rawtohex(sys_guid()) from dual; evaluates to CCDE794E51F8442F99505E5EFF83FCA3 (in this instance).

One place this can be useful is in the generation of CCR, CCD, or CDA documents, where an unique identifier is required within the XML.

Another place this can be useful is as a default value for a primary key column.  However, I prefer to use a sequence and a table trigger, so the primary keys are nice, sequential and clean.

NVL vs COALESCE

Recently, I attended Kscope15 (see the blog posts in June 2015), an annual convention by Oracle Development Tools User Group.

During one of the sessions by Scott Wesley (Evidence-Based APEX: Building Better Practices), it was explained that COALESCE was better on several fronts than NVL.  Since we use NVL quite a bit at IRCS, Inc, I thought I'd compile a post about the subject.

NVL is an Oracle specific command, introduced in the 80s before there were any standards.
COALESCE is a more modern function that is part of the ANSI-92 standard.  COALESCE was introduced in Oracle 9i.

NVL (if the first parameter is null, the second parameter is returned, otherwise if the first parameter is any other value other than null, it is returned unchanged):
select NVL(null, 0) from dual; evaluates to 0
select NVL(1, 0) from dual; evaluates to 1

COALESCE (the first non-null value is returned):
select COALESCE(null,0) from dual; evaluates to 0
select COALESCE(1,0) from dual; evaluates to 1

OK, different commands, same answer.  In the case of evaluating two values, they are synonyms, except:
1) COALESCE stops evaluation whenever it finds the first non-null.
2) NVL always evaluates both arguments.
So performance wise, COALESCE potentially only has 1/2 the work to do.

However, there is more to COALESCE.  COALESCE accepts two or more parameters and returns the first non-null value in a list.  If all parameters contain null values, it returns null.
select coalesce(null,null,0) from dual;  evaluates to 0 (as it is the first non-null value evaluated).
select coalesce(null,1,2) from dual;  evaluates to 1 (as it is the first non-null value evaluated).
select coalesce(null,null,null,null,null,null,1,2,3,4,5) from dual;  evaluates to 1 (as it is the first non-null value evaluated).

COALESCE pitfalls:
NVL is tolerant of differing types, for example:
select NVL('Hello',1) from dual;  evaluates to 'Hello'
Where
select COALESCE('Hello',1) from dual;  evaluates to ORA00932: inconsistent datatypes; expected CHAR got NUMBER....

So be careful in the use of COALESCE.  This is specifically true in APEX, where all page items are in reality VARCHAR2.  If you are lazy and you are depending on implicit conversion, weird, inconsistent things can happen.  Its always best to convert your page items to a known type before you use them.

A good example of the performance difference between NVL and COALESCE is available here

A good description of NULL-related functions available within Oracle is available here

KScope15 travel day (coming home)

This was another rough travel day for me.  Left my hotel at 5:30am and arrived in Hibbing at 11:30pm.

5:30am-6:00am Uber from my hotel to FLL
8:29am-9:42am Silver Airways 68 (Saab 340)
2:18 layover in Orlando
12:00pm-1:43 US Airways 2063 from MCO to CLT (Airbus A321)
1:07 layover in Charlotte, NC
2:50pm-4:37pm US Airways 1855 from CLT to MSP (Airbus A320)

Drive home to Hibbing, MN arriving at 11:30pm

On the FLL to MCO flight:
Spoke with a manager of a 250 physician group who was flying to Orlando for a meeting with United Health Care.  We spoke of meaningful use, EHRs, and insurance companies.

On the MCO to CLT flight:
I sat next to an inspiring actress and writer from Hoboken, NJ and had a interesting and rambling conversation.  She looks just like a young Angie Harmon.

On the CLT to MSP flight:
I sat next to Ross, who was returning to Minneapolis from Boston, where he was attending Red Hat training, focusing on Docker. 

KScope15 day 4 (06/25/2015)

Thursday Deep Dive (9:00am-11:00am) Scott Wesley Using CSS and Applying jQuery in your Web Applications: A Guide for PL/SQL Developers
Key points:

• jQuery is a query language that can select information from the DOM or the contents of the web page.  It can also manipulate it.
•  Enhancements for touch screens.
• Touchpunch.furf.com.
• Enlarge date pickers.
• touch row instead of press button.
• Use your browser tools.
•  Use inspect element.
• See the slides, there is LOTS of information in them.

Sessions I wish I attended:
Peter Raganitsch Worst Practices: Top 10 Common Mistakes in APEX Applications 
Peter Raganitsch Interactive Reports in APEX: Watch out for the Pitfalls
Common APEX Responsive Design Issues and How to Correct Them
Whats Hiding in the APEX Packaged Applications
APEX and the world of D3
Tackling Time Troubles: How Oracle Implements Datetime Data Types
SQL Developer Tips and Tricks

KScope15 day 3 (06/24/2015)

Session 11 (8:30am-9:30am) Martin D'Souza How to Build an APEX Plugin
Key points:

• One can build his own plug-ins.
• Personally, I'm happy we are using the FOEX suite of plugins, as they have done all the hard work.
• Interesting to know how its done.

Session 12 (9:45am-10:45am) Cary Millsap Oceans, Islands, and Rivers: The Mistake Everyone Always Makes with Trace Files
Key points:

• The presentation explained Oracle's extended SQL trace data.
• See Joel Kallman's comment "Folks who really excel with APEX do tracing analysis and understand where each millisecond is spent when rendering a page."

Session 13 (11:15am-12:15am) Rafael M Rodriguez and Rodolfo Rodriguez APEX+RESTFul+OAuth2: The Future Is Here
Key points:

• Showed us what they did, but not how they did it.
• Can easily see how one could create a native mobile smart phone app that utilizes REST services for data access.

Session 14 (1:45pm-2:45pm) Ashley Chen Hands-On Training: Get up to Speed with Oracle REST Data Services
Key points:

• I installed the Oracle DB developer VM (Ashley had a few flash drives that worked great).
• I was able to install ORDS 3.0.0.
• I was able to create GET/POST/PUT/DELETE web services (using Oracle's standard) on any table I wanted to.

Session 15 (3:00pm-4:00pm) Kris Rice Intro to Oracle REST Data Services
Key points:

• See slides, lots of information, but the Hands-On Training immediately before that was the most important to me.

Session 16 (4:15pm-5:15pm) Francis Mignault The Objects of My Affection: Deploying Your APEX Applications and Using Supporting Objects
Key points:

• Dev/Test/Prod configurations
• Dev configuration
• Debug ON.
• Tracing mode ON.
• DB schema available to developers .
• Test, UAT configuration
• copy of production.
• Debug ON.
• DB schema available to DBA only.
• Tracing mode ON.
• Prod configuration
• Debug OFF.
• Tracing mode OFF.
• Run application ONLY with runtime install of APEX.
•  Secured with no outside access.
• Development best practices
• Naming conventions are important.
• Source control standards.
• Document explaining MTT (move to test) and MTP (move to production)
• Use packages
• Use application modules are separate applications (faster delivery, easier deployment).

KScope15 day 2 (06/23/2015)

Session 5 (8:30am-9:30am) Dimitri Gielis How to make APEX Print through node.js
Key points:

• Node.js is a server that runs in Javascript
• Demo of officegen, an XML generator which runs on node.js.
• Demo of APEX Office Print, AOP (produce DOCX,PPTX,PDF from within APEX)
• It is possible using javascript to print directly to a printer

Session 6 (9:45am-10:45am) Matt Nolan FOEX Vendor Presentation: Build complex app in APEX 5.0 with ease, using FOEX and the new page designer
Key points:

• Thanks Matt for showing us how awesome FOEX is.  It is really neat to see how fast you can develop a very rich page with FOEX.

Session 7 (11:15am-12:15am) Martin D'Souza Top 10 APEX API
Key points:

• OraOpenSource.com contains great APEX utilities.
•  sleep/pause, stop_apex_engine
• string_to_table (useful for delimited lists)
• api.oracleapex.com

Session 8 (2:00pm-3:00pm) Alex Nuijten and Alan Arentsen APEX, Node.js, and HTML5: Magic!
real time process control with APEX5, and a Lego Mindstorms running Linux and Node.js

Key points:

• It really is neat to see what you can do with APEX, Linux, and node.js

Session 9 (3:30pm-4:30pm) Tim Austwick and Nathan Catlow APEX Security: Hack Demos and Security Best Practices
Key points:

• Should be running ORDS2.0.8+.
• Should be running APEX 4.2.6+.
• SSL should be enabled and should only be allowing HTTPS.
• Should define security inclusion list (f,p,z,wwv_*, apex*).
• Buttons and processes must have the same conditions as the page.
• Avoid substitution syntax, as this is exploitable, even with code commented out.
• Avoid dynamic SQL statements.
• Escape your HTP.P calls.
• Safest to not even have the APEX builder installed in a production environment.
• APEXSEC is an application which helps find vulnerabilities in your application.

Session 10 (4:45pm-5:45pm) Dietmar Aust Setting Up the Oracle APEX Listener (Now ORDS) for Production Environments
Key points:

• Reference architecture = Apache HTTPD 2.4 reverse proxy - AJP - Tomcat 8 (ORDS 3) - JDBC - Oracle DB (APEX 5.0).
• The WORK is done IN THE DATABASE.
• The webserver and PL/SQL gateway don't need alot of processing power, most of the time idle.
• Install in a layered approach, testing along the way to make sure each layer works.
• Configure ORDS in standalone mode, then TEST it.
• Deploy ORDS to Tomcat, then TEST it.
• Configure Apache HTTP server as a proxy to Tomcat, then TEST it.
• Must optimize the connection pool.
• Monitor: select username, status, count(*) count from v$session where username LIKE '%APEX%' OR username LIKE '%ORDS%' group by username, status;
• Can test the tomcat installation http://localhost:8080/i/apex_version.txt.
• See slides, as they are full in reference material.

KScope15 day 1 (06/22/2015)

Session 1 (8:30am-9:30am) Paul Broughton APEX and the Printing Problem: Solved?
Key points:
APEX 5.1 will have printing upgrades.
We are using the best printing option, PL-jrxml2pdf.  We are able to use an IDE report writer and it fits right into APEX/FOEX.

Other options:

• BI Publisher.
• PL/PDF.
• Node.js.
• CSS changes (must create a dedicated page to print), use @media print.
• ORDS.
• Print Server Configuration.
• XLS-FO Editors (www.altova.com/XSL-FO, sourceforge.net/projects/wysiwyg-edit/)
• Jasper Reports.
• PL-jrxml2pdf.

Session 2 (9:45am-10:45am) Scott Wesley Evidence-Based APEX: Building Better Practices
Key points:

• Use COALESCE instead of NVL.
• Use Administration - Monitor Activity - Most Viewed Pages over All Applications to prioritize which pages to tune (simplistic).
• Use Administration - Monitor Activity - Page Views by Weighted Page Performance to prioritize which pages to tune (considers how long the page takes to load).

Session 3 (11:30am-12:30am) Dick Dral Developing Single-Page Applications in APEX
I found a diamond in this presentation when he demonstrated mobile speech to text and specifically how he was able to parse the conversational speech and place each important part of the sentence in each specific field.   I plan to use this same methodology in a future Vireo mobile application.

Key points:

• SPA = Single Page Application
• Performance and performance only, as you eliminate all page refreshes.  Page refreshes take a lot of time, certainly through thin lines.  This is interesting, as this is how we already develop most pages in Vireo (using FOEX).
• Interesting mobile time entry by drawing the arms of the clock

Session 4 (2:00pm-3:00pm) Tim Gorman Hands-On Training: Oracle Database Security: Frustrating the Patient Intruder
I was not prepared for this presentation, as I had not previously downloaded the developer VM (6GB).  Tim did have a few flash drives, but none of them worked.


APEX Open Mic Night (8:00pm-10:00pm)
It was great to see other developers applications.  One never has a good perspective on your own application until you experience other applications.  Next Kscope I plan to demonstrate Vireo, as I believe everyone would have been interested to see the combination of features it brings to the table.

It was great to see two other FOEX applications.  Thank you to Niels de Bruijn for showing off his family tree application, which is available free-of-cost.  That night I downloaded and installed it on my Oracle XE/APEX 5 server at home.

KScope15 Symposium (06/21/2015)

I attended the APEX Sunday Symposium, what follows are the comments I thought important:
each presentation title is a link to the ODTUG content page.

8:30am-9:30am Joel Kallman APEX Episode 5: A New Frontier
#letswreckthistogether means to destroy the perception (even inside Oracle) that APEX is not for business critical applications. Good examples of this are APEX5, Oracle Cloud (APEX, DBaaS), and frankly our own application, Vireo.

Key point:

• Folks who really excel with APEX do tracing analysis and understand where each millisecond is spent when rendering a page.

A great quote:
"What does any business application deal with?  DATA!  That is the core.  That is what drives the business.  Applications come and go.  Data is forever.  Where does the data live?  In the database.  The database is the core.  The database has been that since the 80's.  Is still that.  Focus on the core.  Design for the core.  Leverage the core."
- Billy Verreynne

9:30am-10:30am Patrick Wolf Need for Speed: Page Designer
APEX5 reduces the number of steps to either create items or revise existing items.

Key points:

• While developing in a team, it is important to lock the page you are working on.  That way you eliminate the issue of two or more people revising the same page.
• APEX help menu displays all the keyboard shortcuts that are available.
• Undo works all the way back to the last save point. 
• Bulk updates, bulk deletes are new options in APEX5 page designer.
• Code editor helps you format and CNTRL-space gives you autocomplete options.  You also have the ability to highlight many rows at once and comment them all out.
• Page search can now use regular expressions such as ^P7_CUSTOMER_ID$ which means begins and ends with P7_CUSTOMER_ID.  Page search now searches the page in memory so its very fast.
• We should get some formal training on APEX5 page designer, as there are lots of little options to be aware of.

The Oracle APEX team clearly had fun messing around with DNS (apexdbcloud.beer).

11:00am-12:00pm Shakeeb Rahman Interstellar: The Universal
Shakeeb discussed the new universal theme, theme 42.

Key points:

• Responsive, Versatile, and Customizable.
• A new cards widget is available.
• Includes a new icon library and one may load your own icon library.
• Theme 42 actually has less template options than other older themes, but it is far more capable.
• Theme roller enables the theme to be customizable.  Vista theme style roughly matches the ADF theme Alta.
• With a bit of custom CSS one can make your login screen transparent with the background blurred.
• If you don't modify theme 42, one can update it with a new version from Oracle very quickly.

What is a good Javascript and CSS reference?


1:00pm-2:00pm Marc Sewtz The Fifth Element: HTML5+APEX5 Mobile
APEX5 supports mobile development, but it does depend on an always on internet connection.
To support all devices, one really needs 3 different applications:

1. Desktop application based on FOEX.
2. Desktop/mobile theme based on vanilla APEX and universal theme APEX5.
3. jQuery mobile themed mobile first application (jQuery 1.3,1.4 actually looks like a native iOS or Android application).  jQuery has themeroller (themeroller.jquerymobile.com).

Reflector can be used to see an application emulated in iPhone, iPad, Android, etc.
In our world, with the variety of devices we all use, it is important to think "mobile first".  We (at IRCS) are there yet, but we are definitely trending that direction.

javascript:location:href='f?p=.....'

2:00pm-3:00pm Anthony Rayner The Matrix Reloaded: Interactive Reports
Key points:

• Modernized to include accessibility improvements and keyboard shortcuts.  WCAG2.0.
• Cleaner and leaner.
• Style is more CSS based and looks more modern.
• Client side is modernized.

 Pivot View is a new option, spreadsheet killer in my opinion.

3:15pm-4:15pm David Peake The Prestige: Converting to APEX 5.0 Universal Theme
There is a bookmarklet available to help with the conversion
http://apex.oracle.com/ut

Key points:

• Reset all regions.
• No.
• Run bookmarklet and migrate using it.
• Switch themes them delete the old theme.
• Update the login page (login button should be in the NEXT position).
• Use the packaged application as good examples.

I need a video of this, as this was WAY too much content, WAY to fast.

ODTUG KScope15

June 21, 2015

I have arrived at KScope15, the primary convention for Oracle focused developers.  It is great to attend a convention aimed squarely at who I am and what I do.  I'm looking forward to meeting many folks who do exactly what I do (both on the database side and the user interface side).

KScope15 travel day (on to South Florida)

Left Hibbing, MN at 8:00am in a car and arrived at FLL at 11:30pm.

I used Uber for the first time.  Thanks to Jorge Rimblas for the recommendation.

8:00am - 12:00pm Drive from Hibbing, MN to MSP

1:53pm - 4:48pm Mesa Airlines 3740 from MSP to IAH (Embraer 175)

7:00pm - 10:33pm United Airlines 461 from IAH to FLL (Airbus A320)

Uber from FLL to my hotel (Beachwalk Hotel)

While flying from Minneapolis (MSP) to Houston (IAH) the route took us over eastern Oklahoma and eastern Texas.  Considering they had a 100 year spring (too much water) and Tropical Storm Bill had just dropped 6-8" of rain, its not surprising the amount of water I saw on the ground.  Most river valleys were completely full of water.

New to blogging

After reading blogs like these for years:
http://www.thatjeffsmith.com/
http://tkyte.blogspot.com/
http://www.grassroots-oracle.com/

I have decided to create one myself, more to record common problems that I face in the hope that I can help other folks as they have helped me.  But also to make it easier for myself to find answers.