Wednesday, March 18, 2020

Seek where you can help. Now.



I have a simple request of the global Oracle APEX Community, please:

Seek where you can help.  Now.

These are interesting times we're living in, unlike anything I've witnessed in my lifetime. And people are rapidly understanding that they have many information needs, which they needed fulfilled yesterday.  I said this earlier about Brexit and I see the same thing unfolding for COVID-19.  People need information management applications that they've never even dreamed of, but they need them immediately.  This is a classic case of opportunistic applications.  And who better to understand how to design and deliver applications in hours or days than the global Oracle APEX community?  That's you!

If you're on social media, you may have seen Oracle APEX used in a number of interesting scenarios.  Here are a few I'm aware of:

  • In China, Buling Zhang the director of IT at a hospital, wrote an APEX app to gather information about patients (travel history, symptoms, etc.).  He subsequently shared this app with 6 other hospitals.
  • In United Arab Emirates, Muhammad Rehan Javed wrote an APEX application to track attendance.  It was developed in 2 hours and rolled out to 10,000 users.  As he said, it "facilitated in avoiding contact with biometric devices".
  • In the United Kingdom, Dave Waghorn and his team at University Hospital Southampton developed APEX apps in 3 days to help support the handling of COVID-19.

I'm sure you're aware of many more.  I encourage you to find where you can help...not tomorrow, but today.

We have offered our team's services to a couple large organizations, and I strongly encourage you to do the same.  Be proactive about it.  Don't wait for someone to approach you.  Contact your ministry of public health or clinic or hospital at the national, state or local level.  Tell them what you can do and help them manage the information needs they're being overrun with.  Better yet, show them what you can do.  You are talented and experienced to help solve problems exactly like these.

This isn't about Oracle or APEX or even the community.  I'd be satisfied if they never knew what you used to provide a solution for them.  This is about helping people who desperately need your help and expertise, and they are currently unaware of you and what you can deliver.  Please contact someone today.

Saturday, March 07, 2020

How to show dates in a given time zone when the value is stored in a DATE data type

Firstly, this post has nothing to do specifically with APEX.  It has everything to do with date conversions in Oracle SQL and how to convert them to a different time zone.  This emanated from a request from our Senior Vice President Michael Hichwa, who asked exactly this question:

"How can I show dates in a given timezone when the date value is stored in a DATE datavtype?  I would like display a calendar in my time zone not GMT.  I don’t want to change my default data type in my table and the CREATED column to be a time stamp with local time zone; I want to keep it as DATE."

I typed out a reply in email tonight, and I thought I would share this here, as I imagine this answer has general applicability.

Two things are necessary to make this happen.
  1. Firstly, you must know the time zone of the server where you're running at.  On a system like apex.oracle.com, it's UTC, but this can be confirmed via to_char(systimestamp, 'TZR').  This is important, because this would tell you the correct implicit time zone for your system-generated DATE values like SYSDATE.  If your program hasn't done any other ALTERs of the time zone in your session, then this should be equal to SESSIONTIMEZONE.
  2. Once you have the base time zone (which you know is the implicit time zone of all of your system-generated data values), you can now easily convert this to another time zone via AT TIME ZONE.

The syntax is:
from_tz(cast({date_value} as timestamp), to_char(systimestamp,'TZR')) at time zone {preferred_time_zone}
What this says:
  • Convert your date to a timestamp using CAST
  • Use FROM_TZ to convert it to a timestamp with time zone, and specify the time zone region of the server
  • Use AT TIME ZONE to convert it to your preferred time zone

Here's a good example on apex.oracle.com, to take SYSDATE and convert it to my US East Coast time zone:
select from_tz( cast( SYSDATE as timestamp ), to_char(systimestamp, 'TZR')) at time zone 'US/Eastern', to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') from dual
I executed this just now and it gave me the results:

07-MAR-20 09.06.45.000000 PM US/EASTERN

03/08/2020 02:06:45


which makes perfect sense.  The SYSDATE is already tomorrow (Sunday, March 8), but converting it to my local time zone, I got 9:06 PM on March 7, which was exactly the time of day in Ohio when I was testing this.

If I were designing a new application, I would most often take advantage of the time zone-aware data types like TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.  But most people store dates as type DATE, and this is an easy way to convert it to another time zone.

Monday, December 09, 2019

The Global Oracle APEX Community Delivers. Again.


Oracle was recently recognized as a November 2019 Gartner Peer Insights Customers’ Choice for Enterprise Low-Code Application Platform Market for Oracle APEX.  You can read more about that here.

I personally regard this a distinction for the global Oracle APEX community.  We asked for your assistance by participating in these reviews, and you delivered.  Any time we've asked for help or feedback, the Oracle APEX community has selflessly and promptly responded.  You have always been very gracious with your time and energy.

I was telling someone recently how I feel the Oracle APEX community is unique within all of Oracle, but I also find it to be unique within the industry.  It is the proverbial two-way partnership that many talk about but rarely live through their actions.  We remain deeply committed to our customers' personal and professional success - it is a mindset which permeates our team.  We are successful only when our customers and partners are successful.

Thank you to all who participated in the Gartner Peer Insights reviews - customers, partners who nudged their customers, and enthusiasts.  You, as a community, stand out amongst all others.  We are grateful for you.

Tuesday, November 12, 2019

ā·pěks 10 Years Later



Exactly 10 years ago today, I wrote a succinct blog post with the intent of clarifying how to properly pronounce and abbreviate Oracle APEX.  I decided to use the phonetic spelling, ā'pěks, to avoid all ambiguity with the pronunciation.  Was I successful?

  • I still encounter many people who spell this Apex (and not the correct APEX)
  • I routinely hear people pronounce this as ah·pěks or ap·ěks (and not the correct ā'pěks)

Obviously, we still have a ways to go.  However, this hasn't been a complete loss.  With many thanks to the global APEX community, this simple phonetic spelling has resulted in:
  • stickers
  • hoodies
  • umbrellas
  • 3D-printed keychains
  • cutting boards
  • bottle openers
  • coffee mugs
  • challenge coins
  • T-shirts
  • fan shop
...and more.  And did I say stickers?

What I especially love is that all of this was created by the Oracle APEX community.  Instead of Oracle providing merchandise and branding for Oracle APEX, the community embraced this and ran with it themselves.  This has been wonderfully organic and authentic, and completely community-driven.

Going forward, if you come across someone who misspells or mispronounces Oracle APEX, please feel free to direct them to this blog post.  It is:

Oracle APEX

and it's pronounced ā·pěks.

Saturday, November 02, 2019

My Personal Thanks to the Chicago Police Department - the First Real Proving Ground for Oracle APEX


In 2001, the Chicago Police Department took a chance on APEX.  And with all thanks to them for the opportunity they provided us, Oracle APEX is what it is today.  We owe them a big debt of gratitude.  Let me explain.

As many people know, the genesis of Oracle APEX was an internal development project that began in 1999, to build a Web-based HTML calendar for use by Oracle employees.  My manager, Mike Hichwa, was the inventor of Oracle Web DB.  And when faced with the assignment of creating a new HTML calendar application for the company, the choices were a) WebDB, b) a lovingly hand-crafted PL/SQL application from scratch, or c) a yet-to-be-created application metadata framework (using Mike's lessons learned from WebDB).  We went with the latter, and Mike began the creation of the APEX framework while I developed a calendar application which was "programmed" to use this new framework.  Mocked and doubted by many at Oracle, we went live with the first production application in 3 months, rolled out to thousands of employees.  Having Tom Kyte to help us was instrumental in our success.

Over the next 18 months, we evolved this framework and created a number of other internal applications.  We thought we were ready to offer this framework for customers to use.  But one of the best things happened for APEX at that time.  When Larry Ellison was visiting New York City, Mike traveled to meet with him and brief him on the state of the framework, as well as Mike's aspirations to offer this framework as another tool from Oracle.  The advice offered by Larry to Mike - prove the framework with 30 real-world customers before you consider taking this live.  Invaluable guidance.

In 2001, Mike and I had an internal meeting in Chicago with Oracle Consulting.  The back-end information system for the Chicago Police Department (CPD), the Criminal History Record Information System (CHRIS), was written in Oracle Forms.  It had been developed over many years, and was a joint effort between Oracle Consulting and the Chicago Police Department.  The purpose of this meeting, at the time, was to discuss possible alternatives to the next state of CHRIS.  This meeting was ultimately precipitated by the estimated hardware requirements to run the next version of Oracle Forms.  They had estimated that the backend database server requirements alone would require 4 very large and very expensive new Sun Enterprise 10000 servers.  This was a lot of money to be spent on hardware with effectively no net gain in functionality for their end users.  We proposed APEX ("Flows", at the time), and they went with it.

Over a period of more than a year, a number of today's APEX product development team members worked directly, onsite, with Oracle Consulting and Chicago Police Department to move the functionality of their Oracle Forms applications to APEX.  It wasn't a 1-to-1 mapping, and it required a level of application and UI redesign.  But we were able to capitalize on the existing data structures and business logic, already present in the database.  The Oracle Forms applications and APEX apps were able to easily co-exist, because they were built on the same foundation.  There were also new systems developed as part of this effort, named CLEAR.  You can still read about CLEAR from this article from 2004.

This entire exercise was hugely beneficial to us.  We thought we were ready to go to market.  But once we dug into the requirements of a large-scale enterprise system like CHRIS, it uncovered many significant gaps in the functionality of the APEX framework.  Fortunately, we owned the framework and were able to simultaneously fill those functional gaps in APEX.  As a simple example, at the time there was no way to manage vectors of information in APEX session state.  This real-world requirement resulted in today's APEX collections.  When you own the framework and you are concurrently building the app, you can do anything!

Scalability was another concern.  While the original calendar application we wrote for Oracle had more than 25,000 users, let's face it - the use of a calendar is occasional throughout the day.  Contrast this with CHRIS, which had more than 10,000 total users, the vast majority who would interact with CHRIS frequently throughout the day.  The heavy concurrent usage of these applications provided us numerous opportunities to tune and optimize the APEX execution engine.  And talk about mission-critical applications - "business" slows to a crawl if you can't look up information about a person or log evidence.  And when business slows to a crawl, public safety is jeopardized.

Fast forward to 2019, and here we are with a large global community of hundreds of thousands of developers.  There are dedicated conferences, stickers, bloggers, videos, meetup groups, awards, books, podcasts, webinars, hosting providers, cloud services, partners & consulting companies, and thousands upon thousands of real-world successes from around the globe.  Much of our success can be traced to this proving ground, which was afforded us by the Chicago Police Department.

The purpose of this blog post is simple - I wish to offer my personal, sincere thanks to the Chicago Police Department for the gamble they took on us.  There was no true guarantee that APEX was going to exist beyond a "skunkworks" project, but they still forged ahead, given some assurances from Oracle and the alternatives.  They banked on us and they won.  Their real-world use cases stretched us and the technology in ways we had never imagined.  We learned so many valuable lessons during this project, and all of it resulted in a much more scalable, hardened, proven system by the time APEX was first offered as an Oracle Database feature in 2004.  We will forever be grateful to them.

For the record, these internal systems still run on Oracle APEX today, and are used by thousands of Chicago Police Department employees every day.  Now that is longevity, and a great investment.  Amidst today's rapid technology churn, this remains an extraordinary success story.


Patch by City of Chicago - http://www.publicsafetypatches.org/IL/Police/, Public Domain, Link

Friday, June 28, 2019

Should the Oracle APEX Community Care About Autonomous Database?



This past week, Oracle announced the availability of Oracle APEX, SQL Developer Web and Oracle REST Data Services on Oracle Autonomous Database.  If you're in the APEX community, should you care?  I say "absolutely yes!", but not for the reasons you might suspect.

Autonomous Database is strategic to Oracle.  Just read the transcript from the recent Oracle quarterly earnings conference call and it will be obvious to you.  Autonomous is an advancement in technology that has significant investment from Oracle and very real benefits for customers.  It's a clear market differentiator - I do truly believe this, it's not merely my marketing spin.  And now, with the addition of Oracle APEX & SQL Developer Web & Oracle REST Data Services, I think this combination of technologies provides even more capabilities to this platform and even greater differentiation.  What other service provides elastic, autonomous capabilities, application design and proven low code application development, out-of-the-box?  Did I mention that this also happens to include the world's most popular database, Oracle Database?

The benefits of low code application development are real.  And Low Code + Autonomous Database is the ideal combination.  Low code is about reducing costs, delivering faster, with greater consistency, and being usable by a broader range of skill sets.  Some of the benefits of Autonomous Database are equivalent - less cost, instant availability, usable by others who may not be world-class experts.  It has been a long multi-year confluence of events that has brought us together here.

The APEX community is the envy of others at Oracle.  Even people who aren't APEX fans recognize the APEX community's passion.  But where did this come from?  Do people really get excited about a tool?  No.  They get excited about what they can do with a tool - how it helps them deliver a solution, and be successful.  A carpenter doesn't get passionate about his dual-slide compound miter saw because it's a cool tool.  He gets satisfaction about what he can actually do with that tool versus a hand saw.  When you get a pay raise or praise or a promotion because of what you've been able to deliver with APEX and ORDS and Oracle Database, that's a reason to get excited!  And I think that is ultimately the real story behind the enviable, tangible energy in the APEX community.  Countless people have had many great successes with this combination of technologies, and success begets success.

Let's say you're in the APEX community, you saw this announcement about APEX on Autonomous, but you're not interested in cloud.  Or, as Andre de Souza so eloquently stated on Twitter, "I know it’s big news, just does not affect 99,9% of current #orclapex developers I’m guessing."  Should you care?  I say yes, and here's why.  The great APEX community that I mention above, which has been so successful with APEX & ORDS & Oracle Database over the years, has become very large across the globe, and with not a lot of help from Oracle.  Make no mistake - Oracle does invest in APEX, millions of dollars every year.  But I still come across Oracle Database customers who have simply never heard of APEX.  This is because there has not been much promotion from Oracle marketing or public relations or even sales.  All of this is about to change.  Why?  Because APEX is on Autonomous Database, and Autonomous Database is strategic to Oracle.  You will probably see more communication and discussion from Oracle about APEX than probably the last 20 years combined.  Low code resonates with customers, APEX is proven, and everyone has application development needs.

How does this benefit someone in the APEX community?  Simple:

  1. Awareness and interest will rise by people who have never heard about APEX before, both existing on-premises customers and net new customers.
  2. There will be greater demand for APEX and database development talent.  If you have experience with APEX, with a proven track record of delivering solutions with APEX, you're a very attractive person.  Perhaps the rate you charge has now gotten a bit higher.  You'll certainly gain upward mobility.
  3. You'll no longer have to introduce someone to APEX for the very first time, or counter the claim that "it's not strategic."
  4. As our friends from Explorer UK say, with APEX, they "develop cloud ready applications".  And you've been doing this for years.  Don't be afraid to make this claim.  When and if you're ready for cloud, you're already out of the gate.  The same APEX apps you developed on-premises run and look and feel exactly the same in the cloud.  Who has been developing cloud-ready apps for years?  You!

So.  Even if you're not into "cloud" but into APEX, this announcement and these capabilities on Autonomous Database has material impact on you and everyone else in the APEX community.  Your skills and experience will become more valued, and we should expect the market and interest and demand to grow.

Everything is not perfect, and we on the APEX team still have a lot of very hard work ahead of us.  But these are exciting times and it's what we've labored on for the past 20 years, to get to this point.  For those who have been with the APEX community for so many years, congratulations!  You've bet on the right horse.  Just fasten your seat belt.

Saturday, February 23, 2019

APEX World 2019: Het gaat weer los!



The very first and still the largest Oracle APEX-focused conference in the world, APEX World 2019, is happening again this year on March 25/26 2019 in Rotterdam, Netherlands.  Incredibly, it's the 10th year for this conference, and like the global Oracle APEX community, it's still growing!

If you've never been to a user group conference, then you should consider attending one this year, and APEX World is an excellent choice.  There is something very organic and authentic about user group conferences.  It is a collection of enthusiasts, professionals, students, partners, business leaders, citizen developers, and full-stack developers, all there for a common purpose.  The APEX community is unique, certainly at Oracle, and probably within the industry too.  You will find this common spirit of sharing and camaraderie ever-present at APEX World.  It helps that the Dutch are naturally kind people, too (and very direct). ;)

This year at APEX World, there are a number of important additions, including:

  • A business seminar "The Future of APEX"
  • A separate track with real customers presenting their real success stories.  This is ideal for someone who is curious about APEX and the large variety of problems being solved with APEX today
  • Numerous deep-dive sessions for those who are experienced APEX developers
  • A special student track
  • Workshops for students from academies/ universities so they can build their first Low Code Oracle APEX app

There is honestly something for everyone.  And the vast majority of sessions will be in English, so there's no reason not to attend.  Did I mention that the Dutch are super nice?

I am honored to attend APEX World 2019, and I will be there, along with product development team superstars Hilary Farrell, Shakeeb Rahman and Jason Straub.  Personally, I relish the opportunity to engage with our many customers and partners, understand what you're doing, understand what your pain points are, and get your advice how we can help you in the future.  The APEX team is invested in your success.  We look forward to seeing you there!