Kscope16 travel day (drive to Chicago)

Shane and I drove from Hibbing, MN to Chicago, IL via Milwaukee.  Nice trip.  Seeing the Milwaukee port and bridge was neat.  It seemed that there were less tolls coming into Chicago from the North.

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).