Oracle SQL to retrieve blank values, nulls, not nulls,distinct values of all columns in a table

Question

I had picked this code from a previous post and tried tweaking the sql to count the blank count of records for the column.. however I am encountering the error below.. i am unable to resolve the error.. please can you help

select owner, table_name, column_name,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(distinct "' || column_name || '") as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as distinct_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when (' || column_name || ' = ' ' ) then 0 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as null_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when "' || column_name || '" is not null then 1 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as notnull_count
from all_tab_columns
where owner = 'JAMES'
and table_name = 'TEST'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
'NCHAR', 'NVARCHAR2');

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 9 Column: 58

Answer 1

try this out:

select owner, table_name, column_name,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(distinct "' || column_name || '") as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as distinct_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when (''' || column_name || ''' = '' ) then 0 end) as c from ' || owner || '''.''' || table_name || ''''))
returning content)) as null_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when "' || column_name || '" is not null then 1 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as notnull_count
from all_tab_columns
where owner = 'JAMES'
and table_name = 'TEST'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
'NCHAR', 'NVARCHAR2');

Answer 2

It seems no need a null length string(''), and to provide one more quote per each inside a quoted string. So, you need to convert the string '' at Line: 9 Column: 58 to '''' in your code as

select owner,
       table_name,
       column_name,
       to_number(xmlquery('/ROWSET/ROW/C/text()' passing
                          xmltype(dbms_xmlgen.getxml('select count(distinct "' ||
                                                     column_name ||
                                                     '") as c ' || 'from "' ||
                                                     owner || '"."' ||
                                                     table_name || '"'))
                          returning content)) as distinct_count,
       to_number(xmlquery('/ROWSET/ROW/C/text()' passing
                          xmltype(dbms_xmlgen.getxml('select count(case when (' ||
                                                     column_name || ' = '''' ) then 0 end) 
                                                                             as c '||
                                                                      --^^^^ these quotes
                                                     'from "' || owner ||
                                                     '"."' || table_name || '"'))
                          returning content)) as null_count,
       to_number(xmlquery('/ROWSET/ROW/C/text()' passing
                          xmltype(dbms_xmlgen.getxml('select count(case when "' ||
                                                     column_name ||
                                                     '" is not null then 1 end) as c ' ||
                                                     'from "' || owner ||
                                                     '"."' || table_name || '"'))
                          returning content)) as notnull_count
  from all_tab_columns
 where owner = 'JAMES'
   and table_name = 'TEST'
   and data_type in ('NUMBER','DATE','TIMESTAMP','CHAR','VARCHAR2','NCHAR','NVARCHAR2');