Phantom LONG datatype is crashing my SQL code - ORA-00997

Question

I have the following code which is supposed to find each column in a database and ouptput the column name, table name, data type, number of null values, and number of rows.

The problem that I run into is that when I run it, it runs for about two minutes, and then complains about an 'illegal use of LONG datatype', but I am not using any LONG here.

If I edit my search to only select WHERE rownum < 100 (commented out in the following code), it works perfectly. Additionally, if I only do the SELECT statement, it runs just fine and outputs all of the correct SQL statements. (about 18000 of them) So I am guessing that the error is in the loop somewhere.

Any guidance on how to fix this?

    SET SERVEROUTPUT ON;
    declare
       myCol1 varchar2(1000);
       myCol2 varchar2(1000);
       myCol3 varchar2(1000);
       myCol4 number;
       myCol5 number;
     begin
       for line in
       (
         SELECT
             'SELECT ''' || atc.column_name || ''', ''' || atc.table_name || ''', ''' || atc.data_type || ''', 
         SUM(CASE WHEN temp.'|| atc.column_name || ' IS NULL THEN 0 ELSE 1 END)           "Filled Values", 
             COUNT(temp.' || atc.column_name || ') "Total Records" 
             FROM all_tab_columns atc 
             JOIN '|| atc.table_name || ' temp ON atc.column_name = ''' || 
             atc.column_name ||''' AND atc.table_name = ''' || atc.table_name || '''' AS SQLRow 
         FROM all_tab_columns atc --WHERE rownum < 100
       )
       loop
          execute immediate line.Sqlrow into myCol1, myCol2, myCol3, myCol4, myCol5;
          INSERT INTO results VALUES (myCol1, myCol2, myCol3, myCol4, myCol5);
       end loop;


     end;
     SELECT * FROM results;

     /

Answer 1

One of the tables being picked up has a LONG column. Your static code isn't referring to it directly, but the dynamic SQL you're generating is, e.g.

SELECT 'SQL_TEXT', 'OL$', 'LONG', 
     SUM(CASE WHEN temp.SQL_TEXT IS NULL THEN 0 ELSE 1 END) "Filled Values", 
         COUNT(temp.SQL_TEXT) "Total Records" 
         FROM all_tab_columns atc 
         JOIN OL$ temp ON atc.column_name = 'SQL_TEXT' AND atc.table_name = 'OL$'

It's complaining about the COUNT. You can't apply aggregates, even something that seems as simple as a count, to a LONG column. Or any built-in function; from the data types documentation:

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

• The UNIQUE operator of a SELECT statement

• The column list of a CREATE CLUSTER statement

• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

• SQL built-in functions, expressions, or conditions

...

The ROWNUM filter just happens to be stopping before it encounters any LONG columns in the data dictionary.

To run this for everything else you'd need to exclude LONG columns from your query. You might want to restrict it you selected schemas though; reporting the data types of system tables/columns seems a little odd.

I'm not sure why you're joining back to all_tab_columns in your generated query. This would get the same result (for a column with a different data type in the same table):

SELECT 'SPARE2', 'OL$', 'VARCHAR2', 
     SUM(CASE WHEN temp."SPARE2" IS NULL THEN 0 ELSE 1 END), 
     COUNT(temp."SPARE2")
     FROM SYSTEM."OL$" temp 

COUNT only counts non-null values, so it'll give you the same result as the SUM (except the sum gives null if the table is empty). If you want to count all rows then count a constant, not the column name. So instead you could do:

SELECT 'SPARE2', 'OL$', 'VARCHAR2', 
     COUNT(temp."SPARE2"),
     COUNT(1)
     FROM SYSTEM."OL$" temp 

You can give a null result for LONG and LOB values, rather than skipping those columns altogether, by changing the dynamic query based on the data type. You might also want to quote all the identifiers just in case you have mixed case or other problems:

   for line in (
     SELECT
         'SELECT ''' || atc.column_name || ''', '
           || '''' || atc.table_name || ''', '
           || '''' || atc.data_type || ''', '
           || CASE WHEN DATA_TYPE IN ('LONG', 'CLOB', 'BLOB') THEN 'NULL'
             ELSE 'COUNT(temp."' || atc.column_name || '")' END || ', '
           || 'COUNT(1) '
         || 'FROM '|| atc.owner || '."' || atc.table_name || '" temp ' AS SQLRow 
    FROM all_tab_columns atc 
    WHERE owner NOT IN ('SYS', 'SYSTEM') -- and others
   ) loop

Or use your SUM version if you want to get the not-null count for those too, I suppose, but with NVL so it reports zero for empty tables.