Syntax

Purpose

DOMAIN_NAME returns the fully qualified name of the ___domain associated with expr. This returns NULL if expr is associated with a ___domain.

When calling DOMAIN_NAME for multicolumn domains, all values of expr should be from the same ___domain. It returns NULL if the number of expr arguments are different from the number of ___domain columns or they are in a different order in the ___domain.

Examples

The following example creates the ___domain DAY_OF_WEEK in the schema HR and associates it with the column HR.CALENDAR_DATES.DAY_OF_WEEK_ABBR. Passing this column to DOMAIN_NAME returns the fully qualified name of the ___domain.

The query casts the string "MON" to DAY_OF_WEEK to get the ___domain name.

All other calls to DOMAIN_NAME return NULL.

CREATE DOMAIN hr.day_of_week AS CHAR(3 CHAR);

CREATE TABLE hr.calendar_dates (
  calendar_date    DATE,
  day_of_week_abbr hr.day_of_week
);

INSERT INTO hr.calendar_dates 
VALUES(DATE'2023-05-01', 'MON');

SELECT day_of_week_abbr, 
       DOMAIN_NAME(day_of_week_abbr) domain_column, 
       DOMAIN_NAME(calendar_date) nondomain_column, 
       DOMAIN_NAME(CAST('MON' AS hr.day_of_week)) domain_value,
       DOMAIN_NAME('MON') nondomain_value
  FROM hr.calendar_dates;
  
DAY_OF_WEEK_ABBR DOMAIN_COLUMN  NONDOMAIN_COLUMN DOMAIN_VALUE   NONDOMAIN_VALUE    
---------------- -------------- ---------------- -------------- ---------------
MON              HR.DAY_OF_WEEK <null>           HR.DAY_OF_WEEK <null> 

The following example creates the multicolumn ___domain CURRENCY in the schema CO. The columns AMOUNT and CURRENCY_CODE in CO.ORDER_ITEMS are associated with this ___domain.

In the query, the arguments for DOMAIN_NAME only match the ___domain definition for the domain_cols expression. This returns the fully qualified name of the ___domain. All other calls to DOMAIN_NAME have a mismatch between its arguments and the ___domain columns so return NULL:

CREATE DOMAIN co.currency AS (
  amount        AS NUMBER(10, 2)
  currency_code AS CHAR(3 CHAR)
);

CREATE TABLE co.order_items (
  order_id      INTEGER,
  product_id    INTEGER,
  amount        NUMBER(10, 2),
  currency_code CHAR(3 CHAR),
  DOMAIN co.currency(amount, currency_code)
);

INSERT INTO co.order_items
VALUES (1, 1, 9.99, 'USD');

SELECT order_id,
       product_id,
       DOMAIN_NAME(amount, currency_code) domain_cols,
       DOMAIN_NAME(currency_code, amount) domain_cols_wrong_order,
       DOMAIN_NAME(order_id, product_id) nondomain_cols,
       DOMAIN_NAME(amount) domain_cols_subset
  FROM co.order_items
  ORDER BY domain_cols;
  
  ORDER_ID PRODUCT_ID DOMAIN_COLS     DOMAIN_COLS_WRONG_ORDER   NONDOMAIN_COLS  DOMAIN_COLS_SUBSET  
---------- ---------- --------------- ------------------------- --------------- --------------------
         1          1 CO.CURRENCY     <null>                    <null>          <null>