Phantom LONG数据类型正在崩溃我的SQL代码 - ORA-00997

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.

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

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.

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

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;

     /

1 个解决方案

#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.

其中一个表格有一个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$'

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:

它在抱怨COUNT。您不能将聚合(即使是像计数一样简单的聚合)应用于LONG列。或任何内置功能;从数据类型文档:

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

另外,LONG列不能出现在SQL语句的这些部分中:

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

GROUP BY子句,ORDER BY子句或CONNECT BY子句或SELECT语句中的DISTINCT运算符

The UNIQUE operator of a SELECT statement

SELECT语句的UNIQUE运算符

The column list of a CREATE CLUSTER statement

CREATE CLUSTER语句的列列表

The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

CREATE MATERIALIZED VIEW语句的CLUSTER子句

SQL built-in functions, expressions, or conditions

SQL内置函数,表达式或条件

...

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

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

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.

要为其他所有内容运行此操作,您需要从查询中排除LONG列。您可能希望限制它选择的模式;报告系统表/列的数据类型似乎有点奇怪。

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):

我不确定你为什么要加入生成的查询中的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 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:

COUNT只计算非空值,因此它将为您提供与SUM相同的结果(如果表为空,则sum为null)。如果要计算所有行,则计算常量,而不是列名。所以你可以这样做:

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:

您可以通过根据数据类型更改动态查询,为LONG和LOB值提供null结果,而不是完全跳过这些列。您可能还想引用所有标识符,以防您遇到大小写或其他问题:

   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.

或者使用你的SUM版本,如果你想获得那些非空计数,我想,但是使用NVL,所以它报告空表的零。

#1