4

I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql

create table table_name (
    column integer NOT NULL DEFAULT (random())
)

how can I do something similar in oracle (preferably without triggers)?

I've tried this

create table table_name (
    column integer generated always as (dbms_random.random) virtual
)

but oracle doesn't like it since the function is non deterministic.

KianTern
  • 41
  • 2

1 Answers1

4

Trigger is the only way to do it.

Virtual column is different from what you want, and that will not accept DBMS_RANDOM.RANDOM, because that is not deterministic:

Column Expressions

...it can contain only the following forms of expression:

  • Columns of the subject table — the table being created, altered, or indexed
  • Constants (strings or numbers)
  • Deterministic functions — either SQL built-in functions or user-defined functions

The syntax of default values for columns is similar to your original statement, but that does not accept DBMS_RANDOM.RANDOM because PL/SQL is not allowed there:

Restrictions on Default Column Values

Default column values are subject to the following restrictions:

  • A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.
  • The expression can be of any form except a scalar subquery expression.
Balazs Papp
  • 41,488
  • 2
  • 28
  • 47