Some solution requirements are best addressed with Java code in the Oracle database. This article shows you use cases where Java methods in Oracle are useful, how Oracle lets you store Java methods and how they can be called from SQL or PL/SQL.
Datatypes
Each value in the database has a datatype, which associates a set of properties with it – for example, a DATE column can accept dates only, not strings or numbers. Datatypes may be built-in or user-defined.
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.
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.
Oracle XML Object-Relational Storage [EXAMPLE]
After the post on models for storing XML data in Oracle and examples of binary XML storage, here’s a detailed look at the rigorous and performant structured XML storage or object-relational storage.
Oracle Binary XML Storage, With or Without XML Schema [EXAMPLE]
The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.
This post shows working examples of binary XML storage
1.1. without XML Schema
1.2 with XML Schema
Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB
Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table, or an XMLType column in a relational table, to persist XML data.
Different storage models are available in Oracle to best fit the nature of the XML data and its expected use. These are:
- Binary XML storage
- Structured storage (object-relational)
- Unstructured XML storage (CLOB)
Here’s an overview of each of these XML storage models with the use cases the model is appropriate for.
Fuzzy Matching with UTL_MATCH
Oracle’s UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms:
- Levenshtein Distance
- Jaro-Winkler Distance
Let’s understand the algorithms and see UTL_MATCH subprograms in action.
DATE Format SQL for Dates Stored as Strings
It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I’m about to describe. Besides, using string-type columns to represent dates rules out your ability to build upon features like range interval partitioning. Yes – ideally you should never have to convert VARCHAR2 to date.
It is equally well-acknowledged that we do not live in an ideal world. In some situations, we do not control the database design; we have to live with what exists and provide a solution to the problem.
This article addresses one such problem — how to compare two dates when they are stored as strings with different DATE formats.
Oracle Total Recall for Tracking History
Oracle 11g Flashback Data Archive (Oracle Total Recall), meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities — say, the trail of changes to customer’s data needs to be tracked. Such requirements becomes trivial with Total Recall, eliminating a huge chunk of coding and testing effort.
How to Perform MULTISET Operations on Nested Table of Objects
We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes).
PL/SQL code when run with MULTISET operations on complex collections throws up the error:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_<operation type>'
There is a workaround though, which lets us use MULTISET operations successfully with complex types. Here’s how.