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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.