Oracle PL/SQL gives you the ability to perform DML operations in bulk instead of via the regular row-by-row FOR loop. This article shows you how to use bulk DML and handle exceptions along the way.
JSON_TABLE Options: Error Handling, Nested Path
In the article on SQL/JSON query functions we saw how JSON_TABLE converts JSON data to relational form. This article further explores various JSON_TABLE options for parsing and handling errors in JSON data.
Unique Constraint vs Unique Index in Oracle
A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.
How to Lock a Row: SELECT FOR UPDATE
Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.
This can be achieved by locking the row in Oracle before update. Here’s how.
Crop a CLOB with DBMS_LOB FRAGMENT_DELETE
One way to get a smaller CLOB from another CLOB is to follow the extract CLOB from JSON example: use DBMS_LOB INSTR/SUBSTR functions to obtain the CLOB fragment of interest. We could look at the same problem from another angle: delete the CLOB fragments *not* of interest, retaining only what we want, with DBMS_LOB.FRAGMENT_DELETE.
Extract Very Long String or CLOB from JSON [pre 12.2]
"Wheels within wheels", as Monty Bodkin would say. Extracting a very long string or CLOB from a JSON CLOB (very long => larger than max_string_size of 32767), in a pre-12.2 Oracle database, turned out to be more complex than it appeared at first.
This case study shows how to extract CLOB data from JSON CLOB, in an Oracle database with no/limited JSON parsing features.
Convert Relational Data to JSON in Oracle: PL/JSON Method
In the JSON series so far, we’ve talked about how to store JSON data in Oracle and apply JSON conditional checks, and how to query JSON data and convert it to relational form. What if you want the opposite i.e. to convert relational data to JSON form? That’s doable too – let’s see how.
What you have: a relational table in Oracle.
What you want: data extracted from said relational table in JSON format.
How do you do it?
JSON Conditionals: JSON_EXISTS, JSON_TEXTCONTAINS
In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.
JSON conditionals check for the existence of specified paths/values within JSON documents. They are typically applied as row filters in the SQL WHERE clause.
Querying JSON Data in Oracle: SQL/JSON Query Functions, Dot Notation
The last article talked about how to store JSON in the database. This article shows you how to retrieve it meaningfully using various query approaches in Oracle 12c.
Storing JSON Data in Oracle: Why and How
JSON is a simple data interchange format, an alternative to XML that’s gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.
Here’s a look at how JSON can be persisted in Oracle, and uses cases where doing so would be apt.