Tuesday, 29 September 2015

Generating JSON from SQL cursor

It appears there will be a good 1001 uses for the APEX_JSON package in APEX 5, here is one of them.

Previously I had an AJAX PL/SQL callback that returned a relatively simple JSON string, using the LISTAGG technique described by Lucas here.
  l_json varchar2(32767);
  select listagg('{"id":"'||version||'","content":"'||version||'.x"}',',')  within group (order by null) json
  into l_json
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version

end getGroups;
Now it can be further simplified by taking LISTAGG out of the equation, which can be a problem for larger data sets:
  c sys_refcursor;
  open c for 
    select version    as id
       ,version||'.x' as content
  from (
    select distinct substr(version,1,1) version
    from  apex_timeline
    order by version

The output differs only slightly
[ { "ID":"1" ,"CONTENT":"1.x" } ,{ "ID":"2" ,"CONTENT":"2.x" } ,{ "ID":"3" ,"CONTENT":"3.x" } ,{ "ID":"4" ,"CONTENT":"4.x" } ,{ "ID":"5" ,"CONTENT":"5.x" } ]

A few extra spaces in the apex_json version. If lower case required for JSON attributes then use double quotes around column aliases, ie: select version as "id"

Oracle 12c JSON APIs seem to be all about validating and deconstruction, while this API has a bunch of overloaded apex_json.write() modules, including support for CLOBs.

Craig Sykes demonstrated how this could be done dynamically with a simple page.

Review: The Martian

I think we have a new genre of films for those consumers yearning for science and exploration.

First Gravity took us on a cinematographic journey into low earth orbit.
Then Interstellar took us on an inception style ride.
If you liked these you may enjoy Europa Report, which has a 2001 feel to it.

Now we have The Martian, which only a few years ago was still in the imagination of write Andy Weir. The book has an amazing backstory, you should have a listen.
Thank you 20th Century Fox

This review from Kirstin Acuna nailed the comparison to Castaway (another of my favourite movies), but I'd like to comment on some of her conclusions.

Biggest obstacle: Science.

Let's think about the target demographic for a moment, shall we? Audiences have been yearning for this style of moview, and good, sensical dialogue should not be compromised for the lowest common denominator. Have a read of this parody [cached] regarding if sports was reported like science, then come back to reconsider.

Book vs Movie

I haven't read the book yet, but I would agree with Kirstin in that you should watch the movie first. I don't know how the book ends, but the cinema took me on an amazing journey, and I can't wait to go deeper in the book.
I didn't come out of movie bummed out with disappointment like Sphere, though perhaps in that case it shows movies just can't always translate to the screen.

Comparisons to Interstellar.

Movies will be compared to other movies all the time. You know the amazing thing about The Martian? No bad guy, no agenda, just the "love letter to science".

See it in 2D.

I beg to differ, see it in 3D. We don't need scenes constructed simply to satisfy an intense 3D experience. The Martian does an amazing job of taking you to the planet, go experience it in 3D to help with that journey. I also recommend sitting near the front where the screen fully immerses your peripheral vision, though I recommend taking a few things to cover the little arm rest lights we had in our cinema.


Watch this movie, I can't recommend it enough.
Thanks to my colleague for getting me pre-screen tickets - I think I will be seeing it again before too long to help my brain process it all!
Also, hang around for the credits. Not only does it start perfectly, the credits blend nice and neat.

Tuesday, 15 September 2015

APEX 5 Change Workspace Authentication

APEX has provided the ability to authenticate your application users against an LDAP server for quite some time.

APEX 5 now provides us the ability to change how we log into the development builder itself, and it's surprisingly easy.

Recently I modified our APEX 5 sandpit to authenticate against LDAP, so we can use our Windows passwords logging into the APEX Development Builder - one less password to manage.

Database ACL

I didn't need to apply an ACL to get this working in 11g APEX 4.2.1, but I did in 12c & 11g APEX 5 instances.

  l_acl       VARCHAR2(100) := 'ldapacl.xml';
  l_desc      VARCHAR2(100) := 'LDAP Authentication for SAGE';
  l_principal VARCHAR2(30)  := 'APEX_050000'; -- upper case
  l_host      VARCHAR2(100) := 'your-ldap-domain.com.au';
  -- Create the new ACL.
  -- Also, provide one starter privilege, granting the schema the privilege to connect.
  dbms_network_acl_admin.create_acl(l_acl, l_desc, l_principal, TRUE, 'connect');

  -- Now grant privilege to resolve DNS names.
  dbms_network_acl_admin.add_privilege(l_acl, l_principal, TRUE, 'resolve');

  -- Specify which hosts this ACL applies to.
  dbms_network_acl_admin.assign_acl(l_acl, l_host);


You can check what you have already with
select * from dba_network_acl_privileges where acl like '%ldap%'

Instance Security

First log into the INTERNAL workspace and head to Instance Security settings. Under the authentication section you'll see a list of available schemes, all you need to do is change the 'current' scheme.
Instance Security Settings

Scheme Settings

The declarative settings are just like those for your application. Getting the LDAP string correct for your environment can be tricky.

At our site I had one that worked for some of us, but not others. Then I found since we're using a Microsoft LDAP we can use the simple format of domain\%LDAP_USER%
LDAP authentication attributes

Note the message on the right hand side.
NOTE: Even for external authentication schemes (e.g.HTTP Header Variable), you have to make sure that users exist as developers or administrators in the workspaces. Otherwise, Application Express will not be able to verify which workspace a user is allowed to work in.
Now the login process authenticates against LDAP, but it still uses APEX user settings to determine authorisation.
ie - what type of user are they? administrator, developer etc.

Internal Workspace Users

Since level of access is still deferred to APEX accounts, don't forget to define workspace users specifically for the INTERNAL workspace that match the LDAP username.
INTERNAL Workspace user list
It's unlikely you will have an ADMIN account in your LDAP server, which is a good thing.

Rolling Back

If you leave your INTERNAL session logged in you can use another browser (or private session) to test authentication and revert the current scheme if necessary.

Alternatively, as described by the warning when applying the scheme change, you restore the authentication from SQL Developer using an API.

You'll need administration privileges:
exec apex_instance_admin.set_parameter('APEX_BUILDER_AUTHENTICATION','APEX')

Workspace Logon Page

Don't forget to remind your developers what's going on, perhaps via a login message.

APEX 5 login message

The login message here is a running joke with our transient DBAs.

I've drafted a post where you can include JavaScript in the login message to make modifications to the login page, just like in prior versions.

Monday, 31 August 2015

APEX 5 Verify Theme Subscription

I was attempting to replicate the navigation bar example from the Opportunity Tracker application and I noticed my application was missing icons from the sub list dropping off the username.
Icons in submenu when visible
Here are the relevant User Interface settings for the Navigation Bar.

User Interface settings
It turns out in newly created application with Theme 42 the following is used as the sublist entry template
<li data-current="true" data-icon="#ICON_CSS_CLASSES#"><a href="#LINK#">#TEXT_ESC_SC#</a></li>

In my application that was created a few weeks ago uses
<li><a href="#LINK#">#TEXT_ESC_SC#</a></li>

So this is when I thought it might be worth giving the Verify Theme Refresh option a go.
Sure enough a bunch of templates, including the Navigation Bar, was "Out of Sync" instead of "Match"
Verify Theme Subscription

I followed the on-screen instructions as the documentation suggested, and waited with baited breath as to what to do next...

Nothing! A message came back saying my theme was synchronised and every template matched the theme master that lives in the Oracle domain somewhere.

I tried the Verify theme option previously but it does not seem to operate well in apex.oracle.com
ORA-00040: active time limit exceeded - call aborted
Not sure if this is a shared server thing or something is actually wrong.

At the end of the day this shows an advantage of running the Universal Theme - updates and corrections can come from Oracle at any time.

I guess we'll learn any disadvantages as time goes by, such as any JavaScript or CSS changes that affect your usage of the relevant template.

Monday, 24 August 2015

APEX 5 Source Used attribute - tolerating less mistakes

If you've upgraded to APEX 5 and find yourself with an error complaining about the 'Source Used' attribute:
then you should thank APEX 5 for finding (and no longer tolerating) a logic bug in your application.

APEX 5 - correcting your applications

Track down that field and set the Source Used attribute to 'Always, replacing any existing value in session state'.

On a side note, setting the application's Compatability Mode to 4.2 will also fix the problem, but in this case certainly not the appropriate solution. But this means by setting this attribute to 5.0 on your existing applications can help find these problems in the first place.

Friday, 21 August 2015

Inaugural Developer Choice Awards

Can you think of anyone worthy of being recognised in the following areas?

Developer Choice Awards Categories
Today is the last day for nominations, so visit the website and nominate the first person you think of - right now!

From mid next month you will have a chance to vote for the finalists.

It looks like Oracle are trying really hard to be more involved with the community, so I think good on them for this initiative.

Monday, 17 August 2015

APEX 5 Changing region type

APEX 5 change region type
If only to further emphasise some of the fundamental rebuilds APEX 5 must have undertaken, look at the ability to switch region types.

Prior to APEX 5.0, we could migrate a Classic report to an Interactive Report, but not the reverse. And don't even think about switching to another region type.

Just bear in mind the tweaks and adjustments you'd probably lose as you toggle around. I think it speaks more about how even more the APEX toolset fits in the RAD category, and how much neater the backend must be to make this happen.

This flexibility certainly helps a step in our course where we ask students to create a classic report. No matter how clear the instruction, there's always someone who creates an interactive report. This will make it easier to switch back!

Also note the "Legacy Calendar" type. This is what helps makes APEX backwards compatible, ensuring version upgrades go more smoothly by minimising the internal changes to applications. Though the same can't be said for plug-ins, where you'll probably where most of your upgrade pain.