Skip to Main Content

Oracle Database Free


For appeals, questions and feedback, please email

PTF enhancement for DOMAINS

Mike KutzNov 13 2023

90% of data science is cleaning up crappy data

(so I'm told)

I'm trying to reduce the number of Full Table Scans by asking (in the same SQL):

  • is the data valid? ( domain_check )
  • if not, why not?

For the 2nd one, this is very tedious amount of typing.

1st desire is for Oracle to provide a function that says:

This data value is invalid because it's [null|can't convert|too long|fails CHECK]

I attempt to use an SQL SCALAR MACRO, but that fails because you can NOT use a domain as a parameter for an SQL Macro (or I'm missing something).

Work around : A LOT of typing (SQL Developer snippet .. maybe)

create or replace
function domain_null_error( domain_name dbms_tf.sql_domain_t, data_value varchar2 ) return varchar2
  sql_macro( scalar )
  sql_txt varchar2(32767);
  sql_txt := q'['case
        when data_value is null then 'value is null' -- should exist based on definition
        when domain_check_type( domain_name, data_value ) is false then 'value is too long'
        when domain_check( domain_name, data_value ) is false then 'value fails CHECK'
        when domain_check( domain_name, data_value ) is true then null
        else 'unkown error'

  return sql_txt;
This post has been answered by Chris Saxon-Oracle on Nov 17 2023
Jump to Answer
Post Details
Added on Nov 13 2023