Easier, Better, Faster JSON in ORACLE 12c Release 2

Oracle Database supports for JSON from their version of 12.1.0.2. It is managing JSON documents stored in CLOBs or VARCHAR2s. Most of the time it is easy to process data if you store your JSON data in relational tables. But it's become hard again if you want to retrieve your data in JSON format. Then the option is to implement your own JSON generator which is not easy. 

So in 12.2 Oracle offers a whole host of options to help you get the job done. Now you can generate the JSON from SQL. Following new functions will help you to write SQL that returns relational table data in JSON format.

• JSON_object
• JSON_objectagg
• JSON_array
• JSON_arrayagg

JSON_object

Use to create series of key-value pair documents. i.e the output has curly braces {}.

JSON_array

These functions take a list of values and return it as an array. i.e in square brackets []. 

For each row in the input, the non-agg versions of these functions output a row. The agg versions combine multiple rows into a single document or array. Let's look at an example.

Say you're using the classic students and courses tables. For each department, you want a JSON document that contains:

• The course name
• An array of its students

• Each element of this array should be its own document, listing the student's name and their contact number. 

For example:

{ 

"course": "programming", 

"students": [   

  {     

     "name": "Dimuthu,Rathnayake",

      "contact": "0716586402"

  },   

  {     

      "name": "Kalpa,Wijerathne",

      "contact": "0765932762" 

   } 

  ]

}

How do you create this using the new functions? Let's work from the inside out:

• First, you need a document for each student. This has two attributes, name and contact. Pass these into a JSON_object call.
• Then you need to turn these into an array. So wrap the JSON_object in a JSON_arrayagg. Group by the course to split out the students for each one into a separate array.
• Finally, you have a document per course. So you need another JSON_object with a course and students attributes. The values for these are the course name and the results of the JSON_arrayagg call in the previous step.

Put it all together and you get:

select json_object (

          'course' value c.course_name,

           'students' value json_arrayagg (

              json_object (

                'name' value first_name || ',' ||last_name,

                'contact' value contact_no )))

from uni.course c, uni.students s, uni.contacts con

where c.course_id = s.course_id

and s.contact_id = con.contact_id

group by c.course_name;

You will get the above JSON output

New Features in Oracle Database 12.2 (Oracle SQL and PL/SQL Improvements)

Enhancing CAST Function With Error Handling

The existing CAST function is enhanced to return a user-specified value in the case of a conversion error instead of raising an error.
CAST lets you convert built-in data types or collection-typed values of one type into another built-in datatype or collection type. Previously it returned an error if the conversion is not supported. Now it returns the default value which is defined by the user.

Eg: - 

SELECT CAST('N/A' AS NUMBER DEFAULT '0' ON CONVERSION ERROR) FROM DUAL;

New SQL and PL/SQL Function VALIDATE_CONVERSION

The new function, VALIDATE_CONVERSION, determines whether a given input value can be converted to the requested data type.

Eg: - 

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE) FROM DUAL;

Enhancing LISTAGG Functionality 

LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality is added for managing situations where the length of the concatenated string is too long.

Eg:- 

SELECT col_1, LISTAGG(col_2, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY col_2) AS students
FROM   table;

Approximate Query Processing

This release extends the area of approximate query processing by adding approximate percentile aggregation. With this feature, the processing of large volumes of data is significantly faster than the exact aggregation. This is especially true for data sets that have a large number of distinct values with a negligible deviation from the exact result.
Approximate query aggregation is a common requirement in today's data analysis. It optimizes the processing time and resource consumption by orders of magnitude while providing almost exact results. Approximate query aggregation can be used to speed up existing processing.

Materialized Views: Real-Time Materialized Views

Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables and are considered stale. Using materialized view logs for delta computation together with the stale materialized view, the database can compute the query and return correct results in real time.

For materialized views that can be used for query rewrite all of the time, with the accurate result being computed in real time, the result is optimized and fast query processing for best performance. This alleviates the stringent requirement of always having to have fresh materialized views for the best performance.

Materialized Views: Statement-Level Refresh

In addition to ON COMMIT and ON DEMAND refresh, the materialized join views can be refreshed when a DML operation takes place, without the need to commit such a transaction. This is predominantly relevant for star schema deployments. 
The new ON STATEMENT refresh capability provides more flexibility to the application developers to take advantage of the materialized view rewrite, especially for complex transactions involving multiple DML statements. It offers built-in refresh capabilities that can replace customer-written trigger-based solutions, simplifying an application while offering higher performance.

New Features in Oracle Database 12.2 (Application Development)

Oracle has improved their database in several ways in their new version.

Application Development

Database Development Productivity Tools Enhancements

• All New Calendar

This new calendar component includes built-in support for Month, Week, Day, and Agenda views, and is much easier to customize. It is based on the popular FullCalendar library and supports drag and drop, time-based events, and is even responsive. The ability to easily define duration-based events and restyle the calendars makes the new calendar very popular with both developers and end-users.

Oracle Application Express release 5.0 calendars now support duration-based events and are significantly easier to style.

• Improved Application Builder Design

The new design also provides improved accessibility and keyboard support, more intuitive page layouts, and many other enhancements.

• Interactive Reporting

Interactive reports are completely rebuilt in Oracle Application Express release 5.0 to enhance both developer and end-user capabilities. New capabilities include the ability to define multiple reports on a single page, column pivot, fixed headers, and modernized actions. You can also restyle interactive report regions using Cascading Style Sheets (CSS) in a similar manner to other regions within Oracle Application Express.

• Mobile Enhancements

You can now build reports that display all of your data on any mobile device by using reflow table or column toggle. Reflow table wraps each column or changes the display to allow multiple lines on very small screens. Column toggle enables you to specify the most important columns to view and those columns that should be hidden, as necessary, on smaller screens. Panels are now incorporated into mobile applications and are used to display navigation menus.

• Modal Dialog Pages

Now you can easily define modal and non-modal pages, complete with the ability to use standard page processes. You no longer need to manually edit a page using JavaScript. Instead, set the display type and the appropriate template and let Oracle Application Express take care of the rest.

• Packaged Applications

Oracle Application Express release 5.0 includes a broad collection of point solutions called packaged applications. These are Application Express applications that you can use out-of-the-box and that are supported by Oracle Database. Examples include Project Tracking, Survey Builder, Meeting Minutes, and Group Calendar. There are 19 productivity applications in all. Additionally, there are 16 sample applications that are used to showcase the features of Oracle Application Express, from Sample Charts to Sample Data Loading. Release 5.0 even includes a sample application that demonstrates the powerful spatial capabilities that are present in every Oracle Database.

• Page Designer

Page Designer is a modern, intuitive, and exceedingly powerful browser-based Integrated Development Environment (IDE). As a completely new IDE that is designed to greatly improve developer productivity, Page Designer enables you to very quickly develop and maintain your Application Express applications. Page Designer features a better visual representation of your application pages and provides an entirely new way of quickly developing pages by using intuitive drag and drop. The enhanced code editor provides SQL and PL/SQL validation with inline errors, auto-completion, syntax highlighting, search and replace with regex support, complete with undo and redo support.

• Universal Theme

Universal Theme is a new user interface for your applications for Oracle Application Express release 5.0. It is a simpler, yet more capable theme that eliminates excessive templates and enables customization using the built-in Theme Roller and Template Options. The Universal Theme enables developers to build modern, responsive, and sophisticated applications without requiring expert knowledge of HTML, Cascading Style Sheet (CSS), or JavaScript.

• User Interface Enhancements

With the development of Universal Theme, there are also several enhancements to the handling of themes and templates. Oracle Application Express release 5.0 includes features such as Theme Subscriptions, Template Options, and Theme Styles. These features give you more granular control over your templates and the HTML that the Application Express engine produces. It is easier to fully control your application user interface.

Binding a PL/SQL index-by table to SQL

Before 12.1, you could invoke a function with a collection actual, or select from a collection, but

• The type had to be defined at schema level
• Therefore it had to be a nested table or a varray
• A non-scalar payload had to be an ADT

Now in 12.1

• The type can be defined in a package spec – can be index by pls_integer table
•  The payload can be a record – but the fields must still be SQL datatypes

Example: 

binding an IBPI to a PL/SQL function in SQL

Example:

   binding to the operand of the table operator