Monday, January 15, 2018

The top 5 reasons why you should submit an abstract for APEX at the Great Lakes Oracle Conference (GLOC)

APEX Developer Day at Great Lakes Oracle Conference 2017

The Northeast Ohio Oracle User's Group (NEOOUG) is easily one of my favorite user groups on the planet.  They've been graciously hosting me at their user group events since 2004 (when I first gave a demonstration on Oracle HTML DB 1.5!).  They are a large, active and passionate user group.  In the past 14 years, I've seen them grow from simple user group events to "Training Days" at the Cleveland State University campus to a nicely sized regional conference named Great Lakes Oracle Conference.

If you're into Oracle APEX, either on-premises, or in the Oracle Cloud, I encourage you to submit an abstract to speak at the Great Lakes Oracle Conference.  Here are my top 5 reasons why you should strongly consider this:
  1. There is a real hunger for Oracle APEX content at this conference.  There are countless customers in the immediate region who use Oracle APEX.  Last year, they had the first ever Oracle APEX Developer Day in advance of the conference, and it was sold out (100 attendees)!
  2. It's the largest Oracle user's conference in the Midwest US.  It draws people from all over Ohio, Michigan, Indiana, Kentucky and Pennsylvania.  There will be over 500 attendees at the conference in 2018.
  3. The Great Lakes Oracle Conference routinely gets world-class speakers from all over the world, both Oracle employees and Oracle ACEs.  As a speaker, you would be able to attend any session in any track.
  4. There are numerous tracks at the Great Lakes Oracle Conference, including APEX, Oracle Applications, Business Intelligence, DBA, Database Developer and Data Warehousing.
  5. Cleveland, Ohio is on the North Coast of the US.  There, you can visit Great Lakes Brewing Company, Market Garden Brewery, Platform Beer Company,  and the Rock & Roll Hall of Fame.

I come across so many people who say "why would anyone want to hear me talk about that?"  From case studies to lessons learned to best practices in your environment, it's all interesting and valuable.  Not everyone who attends the APEX sessions at GLOC are experts, so entry-level sessions are also welcome!

I encourage you to submit an abstract today.  The deadline for abstract submission is February 2, 2018.

Tuesday, January 09, 2018

Can you use Oracle Database Unified Auditing with Oracle APEX?

A customer asked me this morning:

"Can we monitor the table access from an APEX application?"

This is a security-minded customer, who was also interested in the use of Oracle Database Vault to help protect unauthorized access to personally identifiable information which would be maintained in their database tables.  The only access to these tables would be through their APEX applications (for now), and they wanted to be able to monitor access to these tables.  While Oracle offers a very robust, enterprise solution in this problem domain named Oracle Audit Vault and Database Firewall, auditing via the native database functionality would be sufficient for now.

This blog post is not intended to be a complete treatise on the subject of database auditing in the Oracle Database, there is already a lot of documentation on this topic.  This blog post is a simple proof that auditing can be enabled to track accesses from an APEX application and how to see the audit log entries.

  1.   I created a simple APEX application based upon an updated version of the TASKS CSV file, used in this 2015 Oracle Magazine article.  This was done in the JOEL_DB schema.  The Interactive Report on page 1 is based upon the query:

    select id, project, task_name, start_date, end_date, status, assigned_to, cost, budget
     from tasks
    

    The application looked like:




  2. I created an audit policy named JOEL_TASKS_POLICY and enabled it using the following SQL:

    create audit policy joel_tasks_policy actions 
        select on joel_db.tasks, 
        update on joel_db.tasks, 
        delete on joel_db.tasks, 
        insert on joel_db.tasks;
    

    Note:  This is Oracle Database 12cR2.  Before beginning this step, I ensured that the database was configured for Unified Auditing.

  3. I ran the following SQL statement to validate existence of the audit policy:
    SQL> 
    
    select audit_option, object_schema, object_name 
      from audit_unified_policies 
     where policy_name = 'JOEL_TASKS_POLICY';
    
    AUDIT_OPTION  OBJECT_SCHEMA  OBJECT_NAME
    DELETE        JOEL_DB        TASKS
    INSERT        JOEL_DB        TASKS
    SELECT        JOEL_DB        TASKS
    UPDATE        JOEL_DB        TASKS
    

  4. I stopped and restarted Oracle REST Data Services (ORDS).  This is important, because the policy will only take effect for database sessions established after the audit policy was enabled.  Since ORDS maintains a pool of database sessions, I needed to nuke the existing sessions and establish new ones.

  5. I ran the page a few times in my APEX application using the Interactive Report.

  6. As a DBA, I queried the database view UNIFIED_AUDIT_TRAIL using the following query:
    select dbusername, action_name, event_timestamp, scn, 
           object_schema, object_name, sql_text, sql_binds, client_identifier 
      from unified_audit_trail 
     where unified_audit_policies = 'JOEL_TASKS_POLICY' 
       and object_schema = 'JOEL_DB' 
     order by event_timestamp desc
    



    and voila!  You can easily see that the SELECT statements against the TASKS table are properly being audited, complete with any bind values used in the query.  The username of the database session is APEX_PUBLIC_USER as expected, because this is what the database sessions in the ORDS pool are connected as.  In the CLIENT_IDENTIFIER column, you can see that the name of the authenticated user to the APEX application along with the APEX session identifier are also recorded.

Does this give you everything you wish?  Probably not.  I'd like to know the APEX application ID, the page number, the actual parsing schema, and more.  But using the APEX session ID and correlating it with entries in the APEX_ACTIVITY_LOG database view, I can find so much more about this activity.  There are also many other columns in the UNIFIED_AUDIT_TRAIL database view which are used in conjunction with Oracle Database Vault and Real Application Security, and which would capture still more information about access of this underlying database object.

A great benefit of using database auditing instead of simply implementing "poor-man's" logging in the APEX application is that any access will be recorded from any application, not just the APEX app.



Friday, December 08, 2017

How do I create a responsive Rich Text Editor in Oracle APEX?

I was in a video call this morning with a great customer from England (and by the way, this customer is in the process of transforming the healthcare industry across the UK).  They asked me a very simple question:

How do I create a responsive Rich Text Editor item on a page?

Simple question and answer, right?  Well, you'd be wrong.  While we pride ourselves on the responsive user interfaces that you can easily create with Oracle Application Express (APEX), unfortunately, the item type of Rich Text Editor is not responsive, out of the box.

So - I did what all smart people do, and I reached out to the Oracle APEX Development team, in this case, the ever-intelligent Carsten Czarski.  And in a few minutes, he showed me exactly what I needed to do.

  1. Open up Application Builder, and in Page Designer, edit the page with the Rich Text Editor item.  In my example, my Rich Text Editor page item name is P3_RESUME.
  2. Navigate to the attributes of the Rich Text Editor item, and in the Advanced section, enter the following code in the "JavaScript Initialization Code" attribute:
    function (o) {
        o.width = $("#P3_RESUME").closest(".t-Form-inputContainer").width() - 5;
        o.height = 300;  // Specify your desired item height, in pixels
        return o;
    }
    This code determines the width of the region container of the item, subtracts 5, and returns the object initialized to this size.  This will take care of the Rich Text Editor when the page is initially displayed. But it won't handle the case when the browser is resized. To handle that case, we'll need to add a dynamic action.
  3. Click the Dynamic Actions sub-tab in Page designer (the lightning bolt)
  4. Select Events in the upper-left, right-click your mouse and choose "Create Dynamic Action".
  5. In the attributes, enter "Resize" for Name, and select "Resize" for the Event.
  6. Select the True action of the dynamic action (it should be "Show").  Change the Action to "Execute JavaScript Code".
  7. In the Code attribute, enter the code:
    CKEDITOR.instances.P3_RESUME.resize( $("#P3_RESUME").closest(".t-Form-inputContainer").width() - 5, 300);
    
    This is an absolute reference to the Rich Text Editor item on the page, named P3_RESUME. And like the code before, this will determine what the width is of the container of the item, subtract 5 from it, and invoke the resize() method of the Rich Text Editor (CK Editor) element.
That's all there is to it!



Obviously, this item type (like all others) should be responsive, out of the box.  And Carsten is looking at this for the next version of APEX.  In the meantime, if you're using Universal Theme with Oracle APEX 5.1, all it takes is a tiny amount of JavaScript to get a responsive Rich Text Editor.

Thursday, December 07, 2017

Errors when downloading a file on page submit in Oracle Application Express 5.1 or later...

Recently, Sharon Kennedy from our team approached me for some help with file download in Oracle Application Express (APEX).  Sharon is the primary developer of Oracle Live SQL (among many of her other responsibilities), and she wanted to initiate a file download in a page process, after page submission.  Since I've done this 100 times in APEX applications, should be easy, right?

Back in 2014, I wrote a short blog post showing how to generate a link to download a file from a BLOB stored in a table.  But this problem was slightly different.  The application flow was:

  1. In Oracle Live SQL Administration, an administrator would click the button "Download Oracle Content"
  2. The page would then be submitted, and a PL/SQL page process would fire, which would query all of the static scripts and tutorials from Live SQL, zip them up using APEX_ZIP, and initiate a file download.

However, when the button was clicked, the page would be submitted, no file download would be initiated, and the following error was displayed on the page:


Error: SyntaxError: Unexpected token r in JSON at position 0



After spending more than an hour debugging the Live SQL application, I resorted to a simpler test case.  I created a trivial application with a button on the first page, which would submit and invoke the PL/SQL page process:

declare  
    l_file_blob blob;  
    l_file_name apex_application_files.filename%type;  
    l_file_mimetype apex_application_files.mime_type%type;  
begin  
    select blob_content, mime_type, filename into l_file_blob , l_file_mimetype , l_file_name from apex_application_files where id = 2928972027711464812;     
    sys.owa_util.mime_header( l_file_mimetype , false );  
    sys.htp.p('Content-Disposition: attachment; filename="' || l_file_name ||'"');  
    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_file_blob ));  
    sys.owa_util.http_header_close;  
    sys.wpg_docload.download_file( l_file_blob );  
    -- Stop page processing  
    apex_application.stop_apex_engine ;  
end;  


With my test case, it was exactly the same error encountered, the meaningless error message of "Error: SyntaxError: Unexpected token r in JSON at position 0".

I finally gave up and contacted Patrick Wolf on the APEX product development team, who helped me solve this problem in one minute.  Granted...Patrick was both the creator of the problem and the creator of the solution!

To resolve this problem:

  1. Open the page in Page Designer in Application Builder
  2. Edit the page attributes
  3. In the Advanced section of the page properties on the right hand side of Page Designer, change "Reload on Submit" to "Always" (changing it from "Only for Success" to "Always")
That's it!



Setting "Reload on Submit" to "Always" will POST the page and render the result using the behavior as it was in APEX 5.0 and earlier.  In APEX 5.1, if Reload on Submit is set "Only for Success" (the default), it will use the new optimized page submission process, and expect a specifically formatted JSON result returned from the APEX engine.  Obviously, when I employ a page process which overrides the HTP buffer and emit binary content (instead of a well-formed JSON result), the libraries on the page don't know how to deal with that, and thus, results in this obtuse "Unexpected token r..." message.

Tuesday, October 17, 2017

If you're in Panama, Colombia, Ecuador, Paraguay, Brazil or Argentina, Oracle APEX is coming to you!


In the first part of November, my colleague David Peake and I are taking the Oracle APEX & Oracle Database Cloud message to a number of Oracle user groups and communities who are graciously hosting us as part of the Oracle Developer Tour Latinoamérica.  These are countries for which there is growing interest in Oracle Application Express, and we wish to help support these groups and aid in fostering their growing APEX communities.

The dates and locations are:
  1. Panamá, November 1, 2017
  2. Colombia, November 2-3, 2017
  3. Ecuador, November 7, 2017
  4. Paraguay, November 8, 2017
  5. Brasil, November 9-10, 2017
  6. Argentina, November 13-14, 2017
You should consider attending one of these conferences if:

  • You're a CIO or manager, and you wish to understand what Oracle Application Express is and if it can help you and your business.
  • You're a PL/SQL developer, and you want to learn how easy or difficult it is to exploit your skills on the Web and in the Cloud.
  • You come from a client/server background and you want to understand what you can do with your skills but in Web development and Cloud development.
  • You're an Oracle DBA, and you want to understand if you can use Oracle Application Express in your daily responsibilities.
  • You know nothing about Oracle Application Express and you want to learn a bit more.
  • You're experienced with Oracle Application Express and you want to learn what's in the future for Oracle APEX.

If you have any interest or questions or concerns (or complaints!) about Oracle Application Express, and you are nearby, we would be very honored to meet you in person and assist in any way we can.  We hope you can make it!

P.S. I couldn't make it to Panamá, but David will be there, so please join him.

Thursday, October 05, 2017

A Great APEX Developer is.....a Full Stack Developer!



I've heard these phrases from customers:

  • Where can I find Oracle APEX developers?  
  • I'm having a tough time filling a vacancy for an APEX developer.  
  • I'm getting pushback from my own management because they say Java developers are much easier to find than APEX developers.

The last comment was from Peter, a potential customer, who recently posted this on Twitter.

Make no mistake about it.  If you go to indeed.com or dice.com or monster.com, you're going to find far more openings for .NET or Java developers than Oracle APEX developers.  But I would like to take a step back and ask - what makes a really great Oracle APEX developer?  Well, a great APEX developer is competent in:

  1. The Oracle Database (installation, setup, tuning, monitoring)
  2. Data modeling
  3. SQL (and SQL tuning)
  4. PL/SQL
  5. User Interface Design
  6. Responsive Design
  7. HTML
  8. CSS
  9. JavaScript
  10. Web applications
  11. Web servers
  12. Web security
  13. Network security
  14. Server administration
  15. Continuous integration
  16. Unit testing
  17. Application Express (APEX) development

...and probably a few more not even listed here.  It's an amazing list of very specific and relatively complicated technologies.  Don't be alarmed if you consider yourself a rockstar developer with APEX and you are competent in less than half of these areas.  I can't really think of anyone on our own Oracle APEX Development team who is intimately familiar with all of these technologies.  A few experts in the APEX community can truly claim to be proficient in all of these areas, but they're finite in number.  If you look at this list of technology areas, you will quickly conclude:

If you're looking for a great APEX developer, you're really looking for a full-stack developer

...and those are pretty rare in the industry.  Let's look at a couple examples.

It's easier to find Java developers than it is APEX developers.  Sure, I agree.  But it's a common misconception that because someone knows .NET or Java (or PHP or Python or Ruby or JavaScript or whatever) that they are competent in elegant and attractive user interface design.  Just because you know a programming language that is commonly used in Web applications has no correlation to you having the ability to create rich and elegant UI.  This is a very rare skill, and one I see even some experienced APEX developers wrestle with.

As another example, I heard a manager say that he was going to pursue building an application with Oracle JET because it's easier to find JavaScript developers.  We on the APEX team love Oracle JET and want Oracle JET to succeed (so much so, that we've integrated Oracle JET to produce the data visualizations in APEX 5.1!).  But this manager's statement was a bit misguided, because 1) your knowledge of JavaScript doesn't mean you'll immediately know how to use Oracle JET, you'll need to learn it, and 2) what about the rest of the stack?  Is a JavaScript developer good at data modeling?  Are they competent in authoring and tuning SQL?  Do they know when it's preferable to use PL/SQL in their SQL and when not to?  Or is that someone else's job?

My advice when you're looking for APEX developers - determine what is most important to you, and what skills and technologies you need the most assistance with.  APEX development is ultimately Oracle Database development (Smart DB development, as some would say), and experience with SQL and PL/SQL and data modeling is always relevant.  If you have a subpar data model and poor SQL, your application will suffer regardless of front-end technology.  If you have no knowledge of Web applications and you are rich in Oracle Database development talent, then focus instead on those who bring Web application development experience to the table.  I've found that the customers who "get it" the most are those who have done large-scale Web app development in another language or technology.  They are the ones who can appreciate the framework of APEX and how it enables them to focus on the business problem at hand, instead of focusing on all of the necessary minutiae typically associated with modern Web app development.

When reviewing potential candidates, don't focus exclusively on APEX experience.  APEX can be on the wish list of skills, but someone adept at database development or Web development can usually pick up APEX relatively quickly.

Oracle Database application development with APEX and Oracle REST Data Services (ORDS) continues to grow, for both on-premises applications and the cloud.  Industry analysts are responding to more and more inquiries about APEX than ever before.  Countless colleges and universities are taking advantage of the free Oracle APEX curriculum and the free offering by Oracle Academy, helping to familiarize thousands of students on appdev with the Oracle Database & APEX.  The Oracle APEX community is vibrant and growing.  There's a reason why APEX skills are in high demand.

P.S.  If you've stumbled upon this blog post and have no idea what Oracle APEX even is, I suggest you review the Simple Guide to Oracle Application Express.

Thursday, September 14, 2017

The Insurance of Oracle APEX - Can Your Low Code Framework Do This?

Oracle Application Express (APEX) runs wherever the Oracle Database runs.  I've said these words to thousands of customers and partners for the last 15 years.  So why should you care?  Simple.  Peace of mind.

Last evening, Amazon Web Services tweeted the following announcement:

"Use Amazon RDS for Oracle with Oracle APEX versions 5.0.4 & 5.1.2 to build applications within your web browser."


Now, one might think I'm daft for even mentioning AWS on my blog, but I think this presents an opportunity to emphasize two very important points about Oracle APEX:

  1. While I believe that Oracle Cloud is superior for running your Oracle APEX applications, if for some reason you need to not run it on the Oracle Cloud at some later date, you have that choice.
  2. Oracle APEX is not exclusively a cloud-only solution.  You can run it in the Oracle Cloud (my preference), you can run it on AWS, you can use a number of other hosting providers, you can run it with other infrastructure providers, but you can also choose to run it on-premises.  And you can change your mind among these options whenever you wish.  This is very powerful.

Oracle Application Express is a part of every Oracle Database Cloud Service from Oracle, including:

There are other cloud services coming from Oracle later this year which will also include and feature Oracle APEX.

If you think that the Oracle Database is only good for storing data (i.e., a "bit bucket"), you'd be wrong.  If you're unfamiliar with Oracle APEX, please check out this Simple Guide to Oracle APEX.  From bespoke applications in the Oracle Cloud & on-premises to Oracle ERP Cloud PaaS Extensions using Oracle APEX, our customers and partners are really killing it with the Oracle Database and Oracle APEX.


P.S.  Kudos to Amazon for referencing it as Oracle APEX and not Oracle Apex!