SQL (Structured Query Language) is a standard language used to operate relational databases. It includes operations such as database creation, retrieving and modifying data, granting privileges to data.
It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken. This article takes a closer look at:
Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows (say N): if N is 20, then the first page shows 1-20 rows, a “Next” navigation moves control to the second page with 21-40 rows, and so on.
This article demonstrates how to write queries to achieve this style of pagination in Oracle.
When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.
A typical scenario that involves splitting a comma-separated string into rows:
The count of IDs in the list can vary — the user may enter a single value or multiple, and the underlying SQL should fetch the details for all of them.
This sounds simple enough on the face of it: bind a parameter to a comma-separated list of values such as ‘A1,A2,A4’ and then look for corresponding rows using SQL for those IDs.
There’s a catch though.
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.
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.
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.
Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.
This article shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.
An approach for comparing two tables for differences in data, using a pure SQL solution:
In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables.
The nature of differences fell into one of these buckets:
Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.
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.