This article provides an overview of the JSON functionality available when using an Oracle database, along with links to relevant articles. Related articles. In today's development world relational databases are considered a legacy technology by many. PL/SQL is also considered a legacy language. With that in mind, how do we as Oracle DBAs and PL/SQL developers stay relevant? One way is to make sure everything we do is easily accessible. I'm a fan of the smart-database model . For years I've been telling people to publish their PL/SQL APIs as web services. In the past I was mostly preaching XML (REST and SOAP). With the popularity of JavaScript in the development world, RESTful web services and JSON are everywhere. Fortunately Oracle gives us lots of tools to present SQL and PL/SQL as RESTful web services. Here is a quote. > "JSON is a syntax for serializing objects, arrays, numbers, strings, booleans, and null. It is based upon JavaScript syntax but is distinct from it: some JavaScript is not JSON, and some JSON is not JavaScript." https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON "JSON is a syntax for serializing objects, arrays, numbers, strings, booleans, and null. It is based upon JavaScript syntax but is distinct from it: some JavaScript is not JSON, and some JSON is not JavaScript." https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON "JSON is a syntax for serializing objects, arrays, numbers, strings, booleans, and null. It is based upon JavaScript syntax but is distinct from it: some JavaScript is not JSON, and some JSON is not JavaScript." https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON JSON data is made up of name/value pairs, where the value can be a JSON object, JSON array, number, string, boolean or null. A JSON object is made up of one-to-many name/value pairs separated by commas and placed inside curly braces. The name/value pairs don't have to be of the same types. A JSON array is a comma-separated list of objects inside square brackets. The objects don't have to be of the same type. It's really easy to convert JSON to a JavaScript object and vice-versa. Even if you don’t use JavaScript, JSON is still useful. You can think of JSON as "skinny XML". Typically the JSON representation of data requires less characters than the XML equivalent, whether you use tag-based or attribute-based XML. The following example of tag-based XML requires about 768 characters when minified. Minification is the removal of all unnecessary characters, including unnecessary white spaces and new lines. The same data represented as attribute-based XML requires about 537 characters when minified. The JSON representation of the data requires just 470 characters when minified. Anything that reduces the number of characters being sent, whilst retaining readability, is interesting where data transfers are concerned, which is why JSON has become a strong favourite when coding web services. REST stands for Representational State Transfer. There are many long and boring discussions about what it is or is not. If you are interested, you can start reading about it here . Instead of that, let's cut to the chase by focussing on JSON-based RESTful web services using HTTP. To put it simply, we should have a URI which represents a specific resource. Assuming we want to interact with data about employees, we might have something like the following. All interactions with employees will be via this same URI. We identify what we want to do using the HTTP method. - GET : This is for returning information about a resource. Depending on the parameters used this could return all records, a specific subset of records, or a single record. The important point is a GET should not be used to alter data. In simple examples a GET is often mapped to a SELECT statement. - POST : This is used to create a new resource. So we might use a POST to create a new employee. In simple examples a POST is often mapped to an INSERT statement. - PUT : This is used to amend an existing resource. So we might use a PUT to amend an existing employee. In simple examples a PUT is often mapped to an UPDATE statement. In many cases, a put will amend a resource if it is present, or create it if it isn't. I personally don't like this functionality, but that is just my opinion. - DELETE : This is used to remove an existing resource. In simple examples a DELETE is often mapped to an DELETE statement. There are other methods, but we will keep things simple by ignoring them. Each method can itself perform several related actions, depending on the parameters in the URI, HTTP header in the request or an associated JSON payload. To be true to REST, we should never include specific actions in the URI. For example, the following would be considered wrong. Using this type of URI would be considered Remote Procedure Call (RPC) over HTTP, not REST. HTTP response codes are used to determine success or failure. Typically a successful GET, PUT or DELETE call will return a 200 success response, while a successful POST call will return a 201 created response. It makes sense to output additional information about errors, rather than relying solely on the HTTP return codes. REST does not dictate the output returned by a web service call. Some companies will attempt to standardise their approach, but this is not SOAP and no rigid format is forced. Useful starting points to see what you should be aiming for are listed below. - JSON Schema: A Media Type for Describing JSON Documents - JSON Hyper-Schema: A Vocabulary for Hypermedia Annotation of JSON - Google JSON Style Guide Oracle REST Data Services (ORDS) is a WAR file (ords.war) that can run in standalone mode, under Tomcat or WebLogic. These articles explain how to install, configure and run ORDS. - Oracle REST Data Services (ORDS) : Installation on Tomcat - Oracle REST Data Services (ORDS) : Standalone Mode - Oracle REST Data Services (ORDS) : Configure Multiple Databases ORDS was originally known as the "APEX Listener", which was a replacement for , an Apache module that was part of the Oracle HTTP Server. You will often see ORDS in APEX architecture diagrams such as this. As more functionality was added, it got renamed to ORDS, which better reflects its functionality. In addition to fronting APEX and other PL/SQL Web Toolkit applications, ORDS enables RESTful web services for Oracle databases (10g upward) and the Oracle NoSQL Database. It also allows access to the 12c JSON Document Store using SODA. How does ORDS enable RESTful web services?

URIs passed to ORDS are mapped to SQL or PL/SQL calls. If the URI contains parameters, or if parameters are passed as a JSON payload, they are mapped to the parameters in the SQL or PL/SQL. If a result set is returned by the call, it is transformed to JSON or CSV. If the call is to a PL/SQL Web Toolkit application, like APEX, the output of the call is just pushed out to the client. ORDS provides several ways to enable JSON web services. - Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL - Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : Auto PL/SQL - Oracle REST Data Services (ORDS) : REST Enabled SQL - Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST In addition to the automatic generation of JSON, you can use the package or the PL/SQL object types for JSON to generate JSON to push through the gateway or store in the database. Here are some of my opinions on the various options available to you. - In my opinion, the best option is to use ORDS to present your PL/SQL APIs as JSON web services. This allows you to continue to use your current skill set, but present your code to the outside world in a modern fashion. It also means tools can interact with your APIs without needing an Oracle client installation or Oracle-specific driver. - If you are not using ORDS, you can still generate JSON web services through APEX, manually using the package, manually using the PL/SQL object types for JSON, or manually using basic string handling. In all four cases, you will still need a gateway to present the web services. This could be and the Oracle HTTP Server or the Embedded PL/SQL gateway. Both methods use a Database Access Descriptor (DAD) to map URIs to PL/SQL calls. - AutoREST may look quite appealing as it allows you to present tables and views as RESTful web services with very little effort, but I think using this option is likely to be a mistake in many cases. You should be trying to present and receive data through APIs that give a real-world representation of the data, not display your underlying implementation of the relational schema. For example, creating a new employee is a single logical operation, but this might result in new records being added to many tables in the database. You can present this as a single operation through your JSON web service and hide the relational complexity beneath a PL/SQL API. That seems like a more natural approach to me, rather than expecting a JavaScript client developer to interact will all the tables independently. Of course, opinions may vary. In a future release it will be possible to AutoREST-enable a stored procedure. That will make AutoREST much more appealing. - Auto PL/SQL can give you a quick win to produce web services from existing PL/SQL code, but this may not result in true RESTful web services. This functionality typically gives you Remote Procedure Call (RPC) over HTTP(S), rather than RESTful web services. It is up to you to decide if this matters. - REST Enabled SQL is and approach that is probably the opposite of what most DBAs and PL/SQL developers would advise. It allows you to make SQL and PL/SQL calls directly from your application, as well as allowing you to run scripts. If you need this functionality that's great, but having SQL littered throughout your client application is not an approach I would recommend. Always think of logical units of work and encapsulate those in an API, which you present as a web service. - The Simple Oracle Document Access (SODA) functionality is useful in a situation where you are predominantly an Oracle shop focusing on relational development, but have a requirement that suits a JSON Document Store. SODA allows you to meet that need, while still staying in the Oracle world, and has the advantage of allowing you to query the JSON data like it were relational data using the SQL/JSON functionality built into the database. I don't see this as a replacement for dedicated JSON stores like MongoDB or CouchDB, but it might find a specific niche. Each release of the Oracle database since 12c Release 1 (12.1) has introduced a number of new JSON related features. You can read about them in these articles. Some related articles are listed below. - APEX_JSON Package: Generate and Parse JSON Documents in Oracle - APEX_WEB_SERVICE : Consuming SOAP and REST Web Services - Oracle REST Data Services (ORDS) : Authentication - Oracle REST Data Services (ORDS) : Using SQL Developer For more information see: Hope this helps. Regards Tim...