SQL

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.

JSON Conditional Logic: JSON_EXISTS, JSON_TEXTCONTAINS

JSON Conditionals: JSON_EXISTS, JSON_TEXTCONTAINS

Implement true/false tests on JSON documents in Oracle database 12c using JSON conditional logic: JSON_EXISTS and JSON_TEXTCONTAINS.

Read the Full Article →
Querying JSON data in Oracle

Querying JSON Data in Oracle: SQL/JSON Query Functions, Dot Notation

Querying JSON data in Oracle 12c using various query approaches: simple SQL, SQL/JSON functions, dot-notation syntax.

Read the Full Article →
SQL XML in Oracle: Generating XML from Relational Data

Generating XML from Relational Data using Oracle SQL/XML

How to generate XML from relational data using a combination of Oracle SQL/XML functions such as XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLAGG, XMLSERIALIZE.

Read the Full Article →
SQL: Compare Two Tables for Differences

SQL: Compare Two Tables for Differences

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: Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: If one or […]

Read the Full Article →
Thumbnail image for DATE Format SQL for Dates Stored as Strings

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 […]

Read the Full Article →
INNER JOIN and OUTER JOIN

INNER JOIN and OUTER JOIN Explained

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge. Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Read the Full Article →
Thumbnail image for Grant SELECT on V$ Views

Grant SELECT on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error: The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as logging in as sys and granting select on the v$ view […]

Read the Full Article →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Read the Full Article →
Thumbnail image for UNPIVOT in Oracle 11G to Select Columns As Rows

UNPIVOT in Oracle 11G to Select Columns As Rows

When the need is to transpose columns as rows, Oracle 11G+ enables you to easily implement this using the UNPIVOT clause is SQL. A typical scenario:

Read the Full Article →
SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority. A typical example is selecting one contact number for a customer, based on contact types.

Read the Full Article →