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.
DDL
Data definition language (DDL) commands define and maintain data structures in the database. For example, commands for creating tables, modifying packages, dropping constraints.
Unique Constraint vs Unique Index in Oracle
A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.
JSON Conditionals: JSON_EXISTS, JSON_TEXTCONTAINS
In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.
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.
When Should We Use Dynamic SQL?
A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.
So, when should we use dynamic SQL?
Oracle Partitioning: Range Interval Partitioning
Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.
Hello range interval partitioning.
ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix
ORA-01450 might crop up when a table index is being created in the database.
ORA-01450: maximum key length (6398) exceeded
Oracle documentation has this to say about the error:
Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system.
The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns.
Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.
Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system.
The action suggests choosing index columns differently so as to remain within the index length limit.
Things are not always so simple though. When faced with this error while installing Oracle’s standard products such as FMW components or OBIEE, it is not in one’s hands to follow this advice and fiddle with the index columns.
How does one fix this error, then? This post suggests possible root causes and solution for ORA-01450 when changing the index itself is not a viable option.
Replace Duplicates in ID Column with Unique Values
A common scenario: in the development environment, a table’s ID column is missing its unique constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data."
Can the DBA escape this catch-22? For sure, says this post — with an approach of replacing *only* the duplicate IDs with unique values, and letting everything else stay untouched.
How to Cast PLSQL Function Output as View
An Oracle view is like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient – you may need to do some procedural data manipulation before you get the output you want. Does this mean an Oracle view cannot be used if the underlying code cannot be framed as a single SQL? Not at all! This article shows you how to cast PLSQL function output as a view.
Stop Overlapping Ranges in a Table
Income tax brackets. Sales commission rates. Loan pre-payment penalty charges. Those are types of master data that get defined as range-based matrices. An important validation for such data is that the slabs should have no overlapping ranges: any input value should match only a single row in the master range definition.
When range-based matrices are stored in a database table, how can overlapping ranges be prevented? Read on to find out the solution for an Oracle database.
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.
SQL> truncate table customer;
truncate table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> drop table customer;
drop table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled.
SQL> -- Check history tracking SQL> select flashback_archive_name 2 from dba_flashback_archive_tables 3 where table_name = 'CUSTOMER'; FLASHBACK_ARCHIVE_NA -------------------- TOTALRECALL_FA
How do you get around this problem?