The usage of the term converged database to describe Oracle Database development capabilities made it even more clear that Oracle Database supports natively all modern data types, analytics, and the latest development paradigms in one product. Using the same database to store all types of data keeps the system simple. In addition there is a deep integration with other Oracle Database features including but not limited to security, availability, performance, partitioning,GoldenGate, RAC, Exadata, etc. You can change how you store specific entities and attributes without switching databases. Data held in the Oracle Database can be directly accessed via SQL, without the need to convert it into an intermediary form. Combining data in different formats is easy – all you need to do is join!  And all of these capabilties are free in all database editions in Oracle Cloud Infrastructure and on-premises.

In addition there is a secure, performant HTTPS access for your Oracle Database called Oracle REST Data Services (ORDS). Oracle REST Data Services (ORDS) bridges HTTPS and your Oracle Database. The mid-tier Java application ORDS provides a Database Management REST API, SQL Developer Web, a PL/SQL Gateway, SODA (Simple Oracle Document Access) for REST, and the ability to publish RESTful Web Services for interacting with the data and stored procedures in your Oracle Database.

You can also build an Oracle Database application with an interactive user interface, Oracle Application Express (APEX). Oracle APEX is a rapid web application development platform for the Oracle Database. Oracle APEX requires a JavaScript-enabled browser and supports the current and prior major release of Google Chrome, Mozilla Firefox, Apple Safari, and Microsoft Edge. You assemble an HTML interface or application on top of database objects with wizards or direct input. Each application is a collection of linked pages using tabs, buttons, or hypertext links. You can manage, manipulate, and display the data in a local Oracle Database or an external data source using REST-based APIs. You will find more information on that here.

If you want to experiment with these features or with an other Oracle technology, try Oracle LiveLabs or Oracle Live SQL.
Oracle LiveLabs gives you access to Oracle’s tools and technologies to run a wide variety of labs and workshops. The LiveLabs workshops allow you to step through examples live on your own Oracle Cloud tenancy, a free tier account or a LiveLabs Cloud account. The workshops can be found here. You can easily filter on dífferent categories such as level, role or focus area. 
Use Oracle Live SQL if you want to develop, expand or share your SQL and PL/SQL skills in Oracle Database. Your access to your assigned schema is temporary and the schema will be initialized and recycled for others after a period of inactivity. To save your work permanently, you will need to save your session as a script. Saved scripts can be replayed, annotated, edited, shared and downloaded.

In the following sections you will find information about Oracle Database capabilities that may help developers build applications with different data models (*). The content is based on a presentation (the slide deck can be downloaded here) I recently put together to explain Oracle Database Application Development technologies.

If you want to learn how to setup and configure a Docker environment providing these features, read the recent posting Oracle Database Development with Docker from my colleague Witold Swierzy who describes the required steps including our free Oracle Database 21c Express Edition (XE), Oracle Application Express (APEX) and Oracle REST Data Services (ORDS).

The following infographic demonstrates the usage of 8 scenarios in one Oracle Database.
Oracle Converged Database

Interested in the benefits of using Oracle converged database? In the blog posting What is a Converged Database? from Maria Colgan, you will find the video with Juan Loaiza explaining the benefits you get from this. 


Putting your text to work with Oracle Text 

Do you need to search, and analyze text and documents stored in the Oracle Database, in files, and on the web? Oracle Text can perform linguistic analysis on documents, as well as search text using a variety of strategies including keyword searching, fuzzy queries, Boolean operations, pattern matching, mixed thematic queries, HTML/XML section searching, and so on. It can render search results in various formats including unformatted text, JSON, HTML with term highlighting, and original document format. Oracle Text supports multiple languages and uses advanced relevance-ranking technology to improve search quality.
Oracle Text can be used from any programming language which has a SQL interface, meaning just about all of them. Enabling text searching within your applications is quite straightforward. 

Example
Create a table with standard SQL.

create table text_demo (id number primary key, name varchar2(100), description varchar2(4000));

Insert some rows.

insert into quickstart values (1, 'US', 'John Doe');
insert into quickstart values (2, 'GB', 'John Smith'); 
insert into quickstart values (3, 'NZ', 'Peter Smith-Smith');
commit;

If you look for the name Smith you may use the following query.

select * 
from quickstart 
where country = 'GB' or upper(full_name) like '%Smith%';

The database must read every ‘full_name’ field and scan it, because there is no index. Not a problem here for 3 entries, but certainly a problem if there are millions of records. So better create a word-based Oracle Text index on the ‘full_name’ column using the following syntax.

create index full_name_index on quickstart(full_name) indextype is ctxsys.context;

Now you can search again with the Oracle Text CONTAINS operator.

select * from quickstart where country = 'GB' and contains (full_name, 'smith') > 0;

More information

Tamper resistent Blockchain Tables

Blockchain table is a specialized table type that provides application-transparent protection from fraud by other users right in Oracle Database. It permits insert-only operations, with no updates and other modifications allowed, and restricts deletions. To further increase tamper-resistance, the rows are chained by storing previous row’s hash in the current row, which enables users to verify any modifications. And by signing and verifying data, they can detect provider fraud (trust but verify).  There’s no new infrastructure needed – the feature is delivered as part of Oracle Database. The use of the tables can be transparent to existing applications, and developers can keep current architecture and programming model, using SQL, PL/SQL, JDBC, and other ways to access the tables.
If it is only about the unchangeability of the tables, you may use the immutable table type instead. Immutable tables represent a “trimmed down” form of the blockchain table type and do not possess the safety mechanisms such as hashing and signatures. 
Immutable tables and blockchain tables are 21c new features that have been backported to 19c with Release Updates (RU) – immutable tables in 19.11 and blockchain tables in 19.10. 

Example
This example creates a blockchain table in your schema. Rows cannot be deleted until 16 days after they were inserted. The blockchain table can be dropped only after 1 day of inactivity.

create blockchain table test_blockchain (test varchar2(128), t number)
no drop until 1 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";    

More information

XML Based Development (XML DB)

Oracle XML DB provides full support for all of the key XML standards, including XML, Namespaces, DOM, XQuery, SQL/XML and XSLT. By providing full support for XML standards, Oracle XML DB supports native XML application development. Application developers are able to use XML-centric techniques to store, manage, organize, and manipulate XML content stored in the database. Oracle XML DB also supports the SQL/XML standard, which allows SQL-centric development techniques to be used to publish XML directly from relational data. With Oracle XML DB, you get all the advantages of relational database technology plus all the advantages of the W3C and industry XML  standards (like XBRL). Oracle XML DB provides advanced features such as support for the W3C’s XQuery-Update and XQuery Full-Text standards.

Example
Create a table to store XML documents using the xmltype data type and insert some xml documents.

Note: The complete code is provided in the Live SQL tutorial Oracle XML DB : Storing and Processing XML Documents.

create table purchaseorder of xmltype;

begin 
for i in 1..100 loop 
insert into purchaseorder values 
('<PurchaseOrder><Reference>ACABRIO-'||i ||'PDT</Reference><Actions><Action><User>ACABRIO-' ||i ...

In the next query you access fragments of an XML document.

select xmlquery('/PurchaseOrder/Reference' passing object_value returning content)
from purchaseorder where rownum <= 5;

With the next query you may search for an XML document.

select t.object_value.getclobval()
from purchaseorder t
where xmlexists('/PurchaseOrder[Reference/text()=$REFERENCE]' passing object_value, 'ACABRIO-1PDT' as "REFERENCE" );

More information

JSON Based Development

Oracle Database stores, manages, and indexes JSON documents. Application developers can access these JSON documents via document-store APIs.Oracle Database fully supports schemaless application development using the JSON data model. This allows for a hybrid development approach: all of the schema flexibility and speedy application development of NoSQL document stores, combined with all of the enterprise-ready features in Oracle Database. Oracle Database provides a family of SODA API’s designed to support schemaless application development. Using these API’s, developers can work with JSON documents managed by the Oracle Database without needing to use SQL. SODA is supported across a number of platforms and languages including REST, Java, Node.js, Python, PL/SQL, and OCI.

The Oracle Database API for MongoDB allows MongoDB-compatible programs and drivers to connect directly to Oracle Database. Programs require little or no code changes or rewrites other than changing the connect string, and will then function exactly as though were connected to a MongoDB database. But at the same time, full SQL access is available to read or write documents and collections created through the MongoDB programs or drivers.
 
There is an Oracle Autonomous JSON Database cloud service that addresses the JSON developer community. It uses native JSON storage and is a fast and scalable service, using Oracle Cloud Infrastructure (OCI). Most importantly, it allows the usage of both non-SQL, document-centric code assets such as SODA and SQL-centric code assets, and it provides simple document APIs for programming languages via REST or the command line interface.

JSON held in the Oracle Database can be directly accessed via SQL, without the need to convert it into an intermediary form. The following example shows a simple example how you may access JSON in the Oracle Database. 

Example
Create a table to store JSON alongside other relational columns. The IS JSON check constraint will parse and reject any column value that uses not valid JSON syntax.

Note: The complete code is provided in the Live SQL tutorial Converged Database: JSON.

create table t (rid number, insdate date, jcol varchar2(32767), check (jcol is json));

Insert one JSON record (from a public source). The data contains information about recent earthquakes. Please note that this single entry contains multiple earthquakes by using a JSON array ‘features’.

insert into t values 
(1, SYSDATE, ' { "type_": "FeatureCollection", "metadata": { "generated": 1588331906000, "url": "https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/significant_month.geojson", "title": "USGS Significant Earthquakes, Past Month", "status": 200, "api": "1.8.1", "count": 11 }, "features": [{ "type": "Feature", "properties": { "mag": 6.3, ...

The next query extracts 3 values from the JSON.

select rid, 
t.jcol.metadata.count, 
t.jcol.features.size(), 
t.jcol.features.properties.mag 
from t t;

JSON_TABLE allows to ‘un-nest’ the values nested in the JSON array. JSON_TABLE is used in the FROM clause of a query as it produces rows from the inputted JSON.

select rid "key", jt.* 
from t, json_table(jcol, '$.features[*]' 
        columns (id, geometry format json, nested properties columns (mag, place, tsunami)))jt;

More information

Geospatial applications

Oracle Spatial allows developers and analysts to get started easily with location intelligence analytics and mapping services. It enables Geographic Information System (GIS) professionals to successfully deploy advanced geospatial applications. Organizations can manage different types of geospatial data, perform hundreds of spatial analytic operations, and use interactive map visualization tools with the spatial features. 
You can store and query 2D to 4D spatial data in the Oracle Database, such as points of interest, streets, administrative boundaries, or spatial-temporal data. You can perform queries based 
on proximity (how far is it?) and containment (is it inside a region?). You can access hundreds of functions to filter data, measure distance relationships and combine/transform 
spatial data. You can manage spatial sensor data from laser scanning or photogrammetry for enterprise 3D geographic information systems (GIS) and Smart City applications. The 3D support is optimized for point clouds and CityGML workflows. 
Oracle Spatial Studio, a self-service application, helps you to create interactive maps and and analyze business data quickly and easily. Users can visualize, explore, and analyze geospatial data stored in and managed by Oracle in the cloud or on-premises.

Use SQL, PL/SQL, Java, Python, JavaScript, Oracle Jet, Node.js, JSON, or REST to add spatial analysis and maps to applications. Develop applications using environment of choice – without learning new languages. Model road transportation, telco, utility, energy, and other networks and analyze this complex data for shortest path, nearest neighbor, within cost, and reachability.
Manage topology data used by mapping and land management organizations with fine-grained feature editing and data integrity across maps and map layers for very large land parcel datasets with high consistency and accuracy.

Example
Use the table t provided in the JSON section above and create a view. The ‘geometry’ column is returning a spatial type. It demonstrates how to access data stored as GEOJSON in the database. It can be queried like a regular relational table, and you can generate some JSON from a relational  source. Here we create a new JSON object which separates the earthquakes based on the ‘tsunami’ value.

Note: You can find the complete code in the Live SQL tutorial Converged Database: JSON 

create view quakeView 
as select rid "key", jt.* 
from t, json_table(jcol, '$.features[*]' 
         columns (id, geometry sdo_geometry, nested properties columns (mag, place, tsunami)))jt;
 

It can be queried like a normal relational table and you can generate some JSON from a relational source. Here we create a new JSON object which separates the earthquakes based on the ‘tsunami’ value.

select id, place, mag, tsunami, 
sdo_geom.sdo_sdictance(sdo_cs.make_2d(geometry), 
sdo_geometry(2001, 4326, sdo_point_type(-122.4194, 37.7749, NULL),NULL,NULL), 0.0001, 'unit=KM') "distance" 
from quakeView order by "distance"

More information

Relational data as a graph

Property graphs give you a different way of looking at your data. You can model your existing relational or new data as graphs by declaring information entities as vertices and relationships between them as edges in the graph. Imagine a typical banking use case with transactions between banking accounts; then, accounts are modeled as vertices, and the transactions are modeled as edges. You can now analyze direct and indirect transactions easily based on this graph-based view. 
The Property Graph feature of the Oracle Database offers powerful graph support for the Oracle Database to explore and discover complex relationships in data sets such as customer 
data, social networks, or financial transactions for applications in product recommendation, fraud detection, risk analysis, targeted marketing, and more. The Property Graph support includes features for graph querying using PGQL as Property Graph Query Language and more than 60 pre-built graph algorithms. You can also combine graph features with machine-learning capabilities.

Note: PGQL (Property Graph Query Language) is a graph query language built on top of SQL, bringing graph pattern matching capabilities to existing SQL users and new users who 
are interested in graph technology but do not have an SQL background (see also https://pgql-lang.org/).

There are various options for using Oracle Property Graph. The easiest is an Oracle Autonomous Database (see also Graph Reference Architecture) running in the Oracle Cloud that includes a web-based user interface, Graph Studio, to interactively model, load, analyze, visualize, and share graphs. The integrated notebook support provides Oracle Property Graph-specific interpreters for Java, Python, and PGQL to quickly execute graph algorithms, query graphs and visualize results.
If you want to use the Oracle Database as a Graph Database in an environment of your choice, you can download the Graph Server and various clients and tools from here. With this you can build e.g. Oracle Graph Server on docker (see postings Build Oracle Graph on Docker).

Example
The following example creates a property graph using the PGQL CREATE PROPERTY GRAPH statement using SQLcl as client tool.

As a prerequisite, to perform the steps in the following example, you first need to load the bank data set into an Oracle Database schema. See Using Sample Data for Graph Analysis for more information.

SQL> pgql auto on;
PGQL Auto enabled for graph=[null], execute=[true], translate=[false].
PGQL> create property graph bank_graph
            vertex tables (
              bank_accounts
                label accounts
                properties (id, name)
            )
            edge tables (
              bank_txns
               source key (from_acct_id) references bank_accounts (id)
               destination key (to_acct_id) references bank_accounts (id)
               label TRANSFERS
               properties (from_acct_id, to_acct_id, amount, description)
          );

In the next query, check if there are any circular payment chains of length 5starting from and ending in the bank account with ID 934.

select * from match (a)-/:TRANSFERS{5}/->(a) on bank_graph 
where a.acct_id=934;

More information

SQL for Accessing, Defining, and Maintaining Data

The declarative approach of SQL allows the developer to simply build the structure and elements of the required program in a way that expresses just the logic of the computation without having to describe the actual control flow. In simple terms it describes “what” the program, or query, should accomplish rather than describing “how” to actually accomplish the desired outcome. The “how” part of the processing is managed transparently by the underlying platform. This contrasts with the procedural approach to programming.

Oracle SQL provides an easy and performant architecture for accessing, defining, and maintaining data. Use SQL with Oracle with different drivers, interpreters and tools e.g. with PHP, Java, Python, .NET, Hadoop, Node.js, or Application Express (APEX).The flexibility and power make it a vital tool for all kind of projects. A powerful framework, transparent processing optimizations, continued evolutions to meet today’s demanding requirements and adherence to international ANSI standards are only a few examples to show the benefits of using Oracle SQL.
There are many capabilities to try out Oracle SQL. For example in your browser with Oracle Live SQL or try Oracle LiveLabs workshops. You can also use resources such as Docker Container Images, Pre-Built Developer VMs, the free Oracle Database Express Edition (XE) software for Linux and Windows or the Oracle Cloud free Tier.    

More information


Oracle contacts

In addition to Oracle colleagues who already support you, here is a list of Oracle Product Manager you may contact if you need more information on certain subjects: 

  • JSON, Machine Learning, Text, XML: Beda Hammerschmidt, George Lumpkin, Mark Hornick, Roger Ford 
  • Graph & Spatial: Melli Annamalai, Jayant Sharma, Hans Viehmann, Karin Patenge
  • In-Memory: Tirthankar Lahiri, Shasank Chavan, Ranjan Priyadarshi, Shailendra Mishra, Markus Kissling
  • Blockchain: Wei Hu, Mark Rakhmilevich