My Oracle Support Banner

"Generated Always" Clause Allows Non Numeric Data to Be Saved in Number Column (Doc ID 2997692.1)

Last updated on JULY 20, 2024

Applies to:

SQL*Plus - Version 10.1.0.5 and later
Oracle Database - Enterprise Edition - Version 10.1.0.5 and later
Information in this document applies to any platform.

Symptoms

Using "Generated Always" in table creation allows non numeric value to be saved in a NUMBER column though it is no longer possible to query the records afterwards.
The fix is to to include a validation in the PL/SQL package that is inserting data into the table, but I would like to know if this a bug or any explanation is available why this is be allowed.

The row can be saved correctly, but the concatenation expression in the "hhmm" column will yield a "1.2.2" that is an invalid number, however the error only shows up when the SELECT is executed.

This has been reproduced in SQLPlus 10g/12c and 21c, SQLcl 23.4.0 and SQLDeveloper 23.1.1, connecting to either an Oracle DB 12c or 19c.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.