Wednesday, September 25, 2013

Creating RESTful API's with the APEX Listener

In REST you model the resources, not the actions. So you define a GET /apex/oow13/hr/employees instead of a GET /apex/oow13/GetAllEmployees. With REST you have six methods (with the most common associated database actions: GET (select), HEAD (select), OPTIONS (to get a list of methods supported by the resource), PUT (merge or update), DELETE (delete), POST (insert).
You can manage the REST resources from either within SQL Developer or APEX. Within SQL Developer the resources are grouped into Modules (like PL/SQL packages) with a URI prefix (like /hr) and within that Resource Templates.
As an example you can define a GET request ( select * from emp where empno = :id ), then the URI Template of the resource will be something like employees/{id}. When you call the URL (like http://localhost:8888/apex/oow13/hr/employees/12345), this will result in a JSON string (or XML or CSV) containing the employee data of employee 12345.
Within a query you can use "special" columns - starting with a $ - that will return a hyperlink within the JSON object.
The listener supports CORS (Cross Origin Resource Sharing) for all public services. So you're not restricted to using URI's within the same domain and can create mashups using data from different domains. Protected resources are not CORS enabled by default, however you can specify a whitelist of origins that are allowed to call that service.
In SQL Developer you can also assign "Privileges" for mehods - so you can secure your DELETE operations from your SELECT operation. The autorization is implemented as an "oauth2" call. See the APEX Listener documentation for more details on that.

Mobile Development with APEX 5.0

In APEX 5.0 a Tablet User Interface will be introduced. The idea is not that you will end up with three user interfaces (Desktop, Tablet and Mobile), but use te Tablet UI for desktop usage as well. You might even want to use the Tablet UI - as an common "average - for all devices.
The plans are to include something like Themeroller into APEX 5.0. So you can 100% skin your application within APEX. 
APEX 5 will include the jQuery Mobile features that are available on the moment that 5 ships. Because the current version of jQM (1.3) contains features as panels, reflow tables and column toggle (the current version of APEX ships version 1.2 - and "upgrading" to a higher version of jQM will not work for 100%). A new CSS Calender component will be available with more flexibility, so it supports drag and drop on mobile devices.
jQM 1.4 will have some theme improvements: One theme (swatch A), a flat look, switching from icons to SVG - so the images will look better on high resolution screens. Also tabs and content containers are foreseen. And these new features will be exposed to APEX as well.

In 5.0 the menu is created in a slide-in panel (instead of a list on the home page). So it's by default accessible from all pages. "Responsive Table" will be one of the reporting options, with a "Reflow Table" as Table Mode. Then the report is laid out vertically or horizontally - dependent on the screen size. "Column Toggle" us another Table Mode. Then you specify your "priority columns". Then lower columns will be dropped (or hidden) from the report, dependent on the screen size. However you can pick the columns you want to show or hide during runtime as well.

All looks very promising ... but we have to wait until 2014 for general availability!


Monday, September 23, 2013

Oracle Database 12c Real Application Security and APEX

Oracle Real Application Security (RAS) applies security policies at the database layer. So those policies are applied to the data and is not relying on the security built in into an application (like VPD). Security is applied to direct connections. Policies are stored in an "Identity and Policy Store".
As an example an employee can see some public information of other employees and all his own data. A manager can see all data of all his employees. So you need both column and row level security. To accomplish the row level security RAS uses "Data Realms". And privileges - like viewSSN and viewSalary - will be assigned to columns. Each Data Realm ha an associated ACL with grants. So a policy is a collection of Data Realms and associated ACLs. 
A user is granted a role. A role is a combination of policies. And this works for all applications that access this data, either APEX, SQL*Plus or whatever.
In APEX you have to create Authorization Schemes that query the RAS ACL's using the ORA_CHECK_ACL function. The good news is, RAS is a no-cost option of the 12c EE database.
See 12c Oracle Security Documentation for all details.
The RAS Application Policy Management APEX application that was used in the demo looks very handy for managing the policies in a (more) user friendly way. This application might be available for download from OTN at some later stage. It probably won't be shipped as a packaged application as it requires an EE license.

Sunday, September 22, 2013

OOW 2103 : Get your own Database Instance in the Cloud

Today the "Database as a Service" has been announced. Not to confuse with the "Database Platform as a Service" that was announced last year and is available since early this year. With the "platform" service, you get one schema within a database. With the "database" service, you get ... a database - either 11gR2 or 12cR1. And you have access to the operating system etc. So in fact you get your own VM with an Oracle database pre-installed.
And within this service, there are three levels defined:
1. Basic - The DB instance is managed by yourself.
2. Managed - The DB instance is managed by Oracle. Backup and recovery, patching and upgrade, all taken care of.
3. Premium Managed - Like Managed, but in a RAC environment, so more highly available. 
I haven't seen any SLA's yet - as you would especially in a Premium Managed Service. Prices are also still unclear. But it is offered on a monthly subscription basis. 
The good news is, all ACE DIrectors will get their own (Basic) Database as a Service to try things out!

OOW 2013 : The new Oracle In-Memory Database

Today the new In-Memory technique for the Oracle database has been introduced. This is not something like TimesTen(+) or so, but a new way of querying data. The concept is like this: All tables that are marked for keeping data in-memory, will be "stored" using both row and column format. The good old row format will be used for the regular OLTP systems, while the column format is used for queries. To speed up those queries, the column format will be held in-memory - and in-memory only. Only the row format goes to disk, just like nowadays. 
To keep all data in sync, the in-memory data will be marked stale during a DML option that affects that data. Therefore the overhead added to DML statements is very small. Upon request the in-memory data will be updated. 
This in-memory data, a.k.a. the "Column Store" is an optional component of the SGA. So you have to switch it on. And you can pick which tables, which partitions, which columns and whether you want it distributed or not (over different RAC nodes) should be kept in-memory. 
The columnar data is loaded in compressed format. So it just takes a fraction of the space that the same total number of rows use (as usually a lot of columns contain the same values and can be more compressed). And this column store can / will be indexed as well.
So you can have an OLTP and a Data Warehouse / Reporting on the same structures. And depending on your query and data , the optimiser will either use the buffer cache or the column store. So the optimiser is "fully aware" of the in-memory database.
The In-Memory database is planned as an option (so additional licensing required) for the 12.1.0.2 release of the databae - expected next calendar year.

Thursday, September 19, 2013

My OOW 2013 Expectations

Redwood Shores, Thursday September 19. 5:30 AM. 
5:30 AM? So I must be in the "wrong" timezone.... Just like most European based Oracle ACE Directors in this hotel I woke up early. Not as early as some others - I noticed tweets from around 2:30 - but nevertheless early.
After a decent flight with some sleep and a few movies we arrived at San Francisco airport yesterday around noon. We were already warned by the flight attendant about the long lines for immigration, but this wasn't quite what we expected:
Usually the line starts somewhere in the big hall but this was about 200 metres before the hall! So expectations were set at a 3 hour waiting time ... After one hour we arrived in the hall and then it showed that half of the hall wasn't used for the queues - which is a good thing, because then there are less people in front of you as you night expected - but also half of the booths weren't used. And that was what caused the long delay in the first place. Probably due to the government expense cut (a.k.a. sequestration). So all in all it took about 1.5 hours. One advantage : You don't have to wait for luggage - that's waiting for you!

Today is the first day of the ACE Director briefing. In these two days the announcements done at Open World will be revealed to us and discussed with the Oracle Product Managers / Directors and such. Also Thomas Kurian will stop by to tell us where Oracle will be heading and to answer the question from the audience (I guess it'll be around 75 people).
When the show starts we will be under NDA, so we can't tell / blog / tweet anything about until Larry or another Oracle presenter has officially announced it.

So right now I can only guess... I expect some of the Exadata features will be available for "regular" databases as well. So more compression options for instance. I expect more "in memory" database features will be announced as well as more Big Data / NoSQL stuff. Maybe even an appliance? Like a Oracle Big Data Appliance (OBDA)? With all the Hadoop, MapReduce, NoSQL and database stuff in one piece? "Engineered to work together"? We will see ....
Last year the motto was : Cloud, Mobile. Social.
Cloud... Oracle did release the Oracle Cloud early this year and will extend their offerings. Mobile ... We did get more mobile Fusion Apps, ADF Mobile and APEX Mobile, So that's covered as well. Social...  Oracle did announce the Oracle Social Network last year. Never heard anything from that since! Did you?