DDL

Data definition language (DDL) commands define and maintain data structures in the database. For example, commands for creating tables, modifying packages, dropping constraints.

Thumbnail image for Unique Constraint vs Unique Index in Oracle

Unique Constraint vs Unique Index in Oracle

A unique constraint and a unique index, on the face of it, look very alike. Dig deeper, and you will see that they differ. A rundown of unique constraint vs unique index in Oracle.

Read the Full Article →
Thumbnail image for JSON Conditionals: 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 →
When should we use dynamic SQL

When Should We Use Dynamic SQL?

We know that, if there exists a choice between static SQL vs dynamic SQL, static SQL is the way to go. In that case, why and when should we use dynamic SQL?

Read the Full Article →
Thumbnail image for Range Interval Partitioning

Range Interval Partitioning

Create Oracle table partitions on-the-fly using range interval partitioning, a beautiful new feature in 11G that obviates the need to pre-create partitions.

Read the Full Article →
Thumbnail image for ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

“ORA-01450 maximum key length (6398) exceeded” might occur when a table index is being created in the Oracle database. Possible reasons why this error shows up and how to fix it.

Read the Full Article →
Thumbnail image for Replace Duplicates in ID Column with Unique Values

Replace Duplicates in ID Column with Unique Values

A solution to the requirement of replacing only the duplicate IDs with unique values in a database table, and letting everything else stay untouched.

Read the Full Article →
Cast PLSQL Function Output as View

How to Cast PLSQL Function Output as View

An Oracle view is typically like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient to get you the result you want. You may need to do some procedural data manipulation before you get the output you want. Does this mean, you cannot use an Oracle view if […]

Read the Full Article →
Overlapping Ranges in a Table

Stop Overlapping Ranges in a Table

Range-based definition of values is a common business scenario, as in the case of mortgage pre-payment penalty charges depending on "pre-payment amount" slabs, or incentives for a dealer depending on "business generated" slabs. A necessary validation is such cases is that the slabs should have no overlaps: each discrete value should match no more than […]

Read the Full Article →
ORA-55610: Invalid DDL statement on history-tracked table

ORA-55610: Invalid DDL statement on history-tracked table

If you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem. Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled. How do you get around this problem?

Read the Full Article →
Difference between User and Schema in Oracle

The Difference between User and Schema in Oracle

As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it? This post helps to clarify the sameness – or otherwise – of Oracle user and schema.

Read the Full Article →