幻影长数据类型正在破坏我的SQL代码- ORA-00997。

Question

我有以下代码，用于查找数据库中的每一列，并将列名、表名、数据类型、空值数和行数输出。

我遇到的问题是，当我运行它时，它运行了大约两分钟，然后抱怨“非法使用长数据类型”，但我在这里没有使用任何LONG。

如果我编辑我的搜索，只选择rownum < 100 (在下面的代码中注释掉)，它将完美地工作。此外，如果我只执行SELECT语句，它将运行良好，并输出所有正确的SQL语句。(大约有18000 )所以我猜这个错误在循环的某个地方。

有关于如何解决这个问题的指导吗？

    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

正在拾取的其中一个表有一个LONG列。您的静态代码并不直接引用它，但是您正在生成的动态SQL是。

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$'

它在抱怨COUNT。您不能将聚合应用于LONG列，甚至那些看起来像计数一样简单的内容。或任何内置函数；来自数据类型文档：

此外，SQL语句的以下部分不能显示长列：

• 按子句分组、按子句排序、或按子句连接或与SELECT语句中的不同运算符连接
• SELECT语句的唯一运算符
• 创建群集语句的列列表。
• 创建物化视图语句的群集子句。
• SQL内置函数、表达式或条件

..。

ROWNUM过滤器恰好在遇到数据字典中的任何LONG列之前就停止了。

要运行其他所有内容，您需要从查询中排除LONG列。不过，您可能希望将其限制在所选的架构中；报告系统表/列的数据类型似乎有点奇怪。

我不知道为什么要在生成的查询中返回到all_tab_columns。这将得到相同的结果(对于同一表中具有不同数据类型的列)：

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

COUNT只对非空值进行计数，因此它将给出与SUM相同的结果(如果表为空，则之和表示为空)。如果要计数所有行，则计算常量，而不是列名。所以你可以这样做：

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

可以根据数据类型更改动态查询，从而为LOB值和LOB值提供空结果，而不是跳过这些列。您还可能需要引用所有标识符，以防出现混合情况或其他问题：

   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

或者使用您的SUM版本，如果您也想获得这些表的非空计数，我想是这样的，但是对于NVL，它报告为空表为零。