Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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 )
as
  sql_txt varchar2(32767);
begin
  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'
      end]';

  return sql_txt;
end;
/
This post has been answered by Chris Saxon-Oracle on Nov 17 2023
Jump to Answer
Comments
Post Details
Added on Nov 13 2023
4 comments
242 views