Because the world needs more PL/SQL blogs
Iudith Mentzel noticed a problem in my OS_LIST_UNIQUE function. It relied on the Ordering System (OS) number being incremental. For example [3,2,1] would not be considered a unique list. The problem lay in the following steps: Load the passed JSON Array into a table 1 Loop through table 1 sequentially to load unique values into … Read more
This post covers continues with the Virtual Column discussion including making them invisible, handling errors in the underlying function and creating indexes on them. Invisibility First up is invisibility. Virtual columns are treated as just another column in Data Manipulation Language (DML) statements. For example and INSERT statement like the one below assumes that all … Read more
Oracle offers like JSON_TABLE and JSON_VALUE to translate JSON keys and values into RDBMS rows and columns. But there is no fair way to compare the performance of those functions with home grown code because its not possible to see the inner workings and influences of the built in functions. Thus an apples-to-apples comparison is … Read more
The previous posts in this series here and here explain the details of adding a Virtual Column (VC) to a table containing JSON data. The VC parsed the JSON to assemble a unique list of values. This post changes that VC to determine if the list passed in is unique and then return 1 if … Read more
This post continues our quest to find Bad JSON data while reducing code complexity. In particular we hid many lines of complex PL/SQL behind a simple to understand and use Virtual Column (VC). The previous post explained the theory and syntax for VC’s – this post continues our implementation of them to find bad data. … Read more
Oracle’s JSON Path Expression (JPE ) uses specific characters such as dollar signs and dots to step through JSON structures. For example a JPE of $.NAME steps to the NAME key at the absolute top of a JSON structure. Whenever invalid syntax JPE syntax is encountered Oracle throws an error with a JZN prefix. Why … Read more
The previous post in this series showed SQL and PLSQL code to find bad JSON data and then fix that bad data. It required many more lines of code (and complexity) than the RDBMS Version for the same functionality. This post begins introducing ways to simplify the “finding bad JSON data” using an Oracle feature … Read more
After a few weeks using our JSON patient management system the users have noticed patients with more than one MRN in a single Ordering System. This should not have been allowed to happen. We’ll put something in place later to prevent future occurrences. For this post we will look at the cleanup. Here’s an example … Read more
This post compares the coding approach to fixing bad data in an RDBMS and JSON system. First up is the RDBMS version… After using our patient management system for a few weeks the users started noticing patients with multiple MRN’s in a single Ordering System. This is happening because we forgot to add a unique … Read more
The last few posts in this series covered adding entries to a JSON array named mrns using SQL and PLSQL. It even handled cases where the mrns array did not exist yet. But what if it did exist but it was not an array? JSON storage is quite flexible we have to gird our code … Read more