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.

declare
  l_json varchar2(32767);
begin
  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
  );

  sys.htp.prn('['||l_json||']');
end getGroups;

Now it can be further simplified by taking LISTAGG out of the equation, which can be a problem for larger data sets:

DECLARE
  c sys_refcursor;
BEGIN
  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
  );

  apex_json.write(c);
END;

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"}]
[ { "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.

Conclusion

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.

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.

DECLARE
  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';
BEGIN
  -- 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);

  COMMIT;
END;
/

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.