Remark: I'm not going to argue whether any of this is a clever thing to do or not.
Suppose we have a simple domain, as follows:
create domain d_str10 as varchar2(10) strict not null;
Remark: the keyword STRICT plays no role here: the outcome will be the same with, or without it.
With that, we create a simple table as follows:
create table ttst (
c1 d_str10,
c2 d_str10 null
);
There's a twist here: it used to be that the NULL clause in a column definition could be thought of as mere syntactic sugar: NULL was the default, so mentioning it was unnecessary (consequently I would only use it as a mean of insisting that the column would purposely allow NULL values). But now, with the D_STR10 domain including a NOT NULL constraint, the default behaviour is reversed: NOT NULL becomes the default for columns associated with the domain, and the NULL clause is not syntactic sugar anymore: it prevents the column from inheriting the domain's NOT NULL constraint, and it explicitly enables it to receive NULL values.
Here's how SQL*Plus 23.3 describes this table:
SQL> desc scott.ttst
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NOT NULL VARCHAR2(10) SCOTT.D_STR10
C2 VARCHAR2(10) SCOTT.D_STR10
And in case you'd have doubts, the following insert statement works:
SQL> insert into ttst(c1, c2) values ('aaa', 'aaa'), ('bbb', null);
2 rows created.
But then….
Right now DBMS_METADATA does not seem to handle this table 100% gracefully: here's what we've got out of the DDL transform:
CREATE TABLE "SCOTT"."TTST"
( "C1" VARCHAR2(10) DOMAIN "SCOTT"."D_STR10" COLLATE "USING_NLS_COMP",
"C2" VARCHAR2(10) DOMAIN "SCOTT"."D_STR10" COLLATE "USING_NLS_COMP"
) DEFAULT COLLATION "USING_NLS_COMP" ;
The explicit NULL clause on C2 is gone!
Same omission in the results of the SXML transform, apparently.
(See in this comment for an example of testing code. For brevity I'm not repeating that here, and I'll spare the generated SXML document too; suffice it to say that the NULL clause is not present in it.)
On the other hand, the NULL vs NOT NULL information appears to be present in the “raw XML” metadata. (It' verbose, so I'll spare that too.)
As a consequence, using Data Pump to export the table, and then import it, does not end well:
$ expdp pdb_admin@freepdb1 job_name=exp_ttst tables=scott.ttst directory=data_pump_dir dumpfile=scott_ttst.dmp exclude=statistics
Export: Release 23.0.0.0.0 - Production on Fri Sep 29 15:31:50 2023
Version 23.3.0.23.09
[...]
Starting "PDB_ADMIN"."EXP_TTST": pdb_admin/********@freepdb1 job_name=exp_ttst tables=scott.ttst directory=data_pump_dir dumpfile=scott_ttst.dmp exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/DOMAIN_ASSOCIATION
. . exported "SCOTT"."TTST" 5.539 KB 2 rows
Master table "PDB_ADMIN"."EXP_TTST" successfully loaded/unloaded
******************************************************************************
Dump file set for PDB_ADMIN.EXP_TTST is:
/opt/oracle/admin/FREE/dpdump/0543B3ED61857640E0630100007F0BCA/scott_ttst.dmp
Job "PDB_ADMIN"."EXP_TTST" successfully completed at Fri Sep 29 15:32:06 2023 elapsed 0 00:00:12
Import attempt #1: content=all implicitly
$ impdp pdb_admin@freepdb1 job_name=imp_ttst directory=data_pump_dir dumpfile=scott_ttst.dmp remap_table=scott.ttst:ttst2
Import: Release 23.0.0.0.0 - Production on Fri Sep 29 15:33:25 2023
Version 23.3.0.23.09
[...]
Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Master table "PDB_ADMIN"."IMP_TTST" successfully loaded/unloaded
Starting "PDB_ADMIN"."IMP_TTST": pdb_admin/********@freepdb1 job_name=imp_ttst directory=data_pump_dir dumpfile=scott_ttst.dmp remap_table=scott.ttst:ttst2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TTST2" 5.539 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/DOMAIN_ASSOCIATION
ORA-39083: Object type DOMAIN_ASSOCIATION failed to create with error:
ORA-02296: cannot enable (SCOTT.) - null values found
Failing sql is:
ALTER TABLE "SCOTT"."TTST2" MODIFY ("C1" DOMAIN "SCOTT"."D_STR10" , "C2" DOMAIN "SCOTT"."D_STR10" )
Job "PDB_ADMIN"."IMP_TTST" completed with 1 error(s) at Fri Sep 29 15:33:33 2023 elapsed 0 00:00:04
Import attempt #2: step 2.a: content=metadata_only; step 2.b: content=data
$ impdp pdb_admin@freepdb1 job_name=imp_ttst_a directory=data_pump_dir dumpfile=scott_ttst.dmp remap_table=scott.ttst:ttst3 content=metadata_only
Import: Release 23.0.0.0.0 - Production on Fri Sep 29 15:34:10 2023
Version 23.3.0.23.09
[...]
Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Master table "PDB_ADMIN"."IMP_TTST_A" successfully loaded/unloaded
Starting "PDB_ADMIN"."IMP_TTST_A": pdb_admin/********@freepdb1 job_name=imp_ttst_a directory=data_pump_dir dumpfile=scott_ttst.dmp remap_table=scott.ttst:ttst3 content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/DOMAIN_ASSOCIATION
Job "PDB_ADMIN"."IMP_TTST_A" successfully completed at Fri Sep 29 15:34:17 2023 elapsed 0 00:00:03
$ impdp pdb_admin@freepdb1 job_name=imp_ttst_b directory=data_pump_dir dumpfile=scott_ttst.dmp remap_table=scott.ttst:ttst3 content=data_only
Import: Release 23.0.0.0.0 - Production on Fri Sep 29 15:34:36 2023
Version 23.3.0.23.09
[...]
Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Master table "PDB_ADMIN"."IMP_TTST_B" successfully loaded/unloaded
Starting "PDB_ADMIN"."IMP_TTST_B": pdb_admin/********@freepdb1 job_name=imp_ttst_b directory=data_pump_dir dumpfile=scott_ttst.dmp remap_table=scott.ttst:ttst3 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-01400: cannot insert NULL into (C2)
. . imported "SCOTT"."TTST3" 5.539 KB 1 out of 2 rows
Job "PDB_ADMIN"."IMP_TTST_B" completed with 1 error(s) at Fri Sep 29 15:34:44 2023 elapsed 0 00:00:04
As Data Pump uses DBMS_METADATA internally, which seems to lose the explicit NULL clause on C2 at the time being, the above import errors are just 2 variations of the same issue.
(Remark: it's interesting to note that, when importing with content=all (the default), domain associations are processed after loading the data. This is similar to how indexes and constraints are handled.)
Best regards,