当我试图最大化所有列时，ORA-00904和ORA-06512

Question

create or replace PROCEDURE COMPARAISON_TEST as

begin
 declare

cursor c_tab is
select table_name, column_name , data_type 
from all_tab_columns;

  v_sql VARCHAR2 (32000);

        begin  

            FOR r_tab in c_tab LOOP     
            v_sql := 'SELECT ' || 
            r_tab.table_name || ' TABLE_NAME, ' || 
            r_tab.column_name || ' COLUMN_NAME, ' || 
            '(SELECT MAX(' || r_tab.column_name || ') FROM ' || r_tab.table_name || ') VALUE ' || 
            'FROM DUAL ';
    execute immediate v_sql;
end LOOP;
end;  
end COMPARAISON_TEST;

误差

   ORA-00904: "DUAL" : invalid identifier   
   ORA-06512: à "ODS.COMPARAISON_TEST", line 20    
   ORA-06512: à "ODS.COMPARAISON_TEST", line 20    
   ORA-06512: à line 2

Answer 1

问题是你是在黑暗中做的。

通常，在实际执行动态SQL之前，在屏幕上显示它为，以便您看到要执行的字符串。

这就是您所做的(我向游标中添加了WHERE子句，以缩短输出)：

SQL>  declare
  2
  3  cursor c_tab is
  4  select table_name, column_name , data_type
  5  from all_tab_columns
  6  where owner = 'SCOTT' and table_name = 'DEPT';
  7
  8    v_sql VARCHAR2 (32000);
  9
 10          begin
 11
 12              FOR r_tab in c_tab LOOP
 13              v_sql := 'SELECT ' ||
 14              r_tab.table_name || ' TABLE_NAME, ' ||
 15              r_tab.column_name || ' COLUMN_NAME, ' ||
 16              '(SELECT MAX(' || r_tab.column_name || ') FROM ' || r_tab.table_name || ') VALUE ' ||
 17              'FROM DUAL ';
 18      dbms_output.put_line(v_sql);
 19      --execute immediate v_sql;
 20  end LOOP;
 21  end;
 22  /
SELECT DEPT TABLE_NAME, DEPTNO COLUMN_NAME, (SELECT MAX(DEPTNO) FROM DEPT) VALUE
FROM DUAL
SELECT DEPT TABLE_NAME, DNAME COLUMN_NAME, (SELECT MAX(DNAME) FROM DEPT) VALUE
FROM DUAL
SELECT DEPT TABLE_NAME, LOC COLUMN_NAME, (SELECT MAX(LOC) FROM DEPT) VALUE FROM
DUAL

PL/SQL procedure successfully completed.

SQL>

看到那些无效的字符串了吗？他们不能被处决。为了使它们有效，将表名和列名包含在单引号中。

SELECT 'DEPT' TABLE_NAME, 'DEPTNO' COLUMN_NAME, (SELECT MAX(DEPTNO) FROM DEPT) VALUE
FROM DUAL;

这就是你可能想要的：

SQL> set serveroutput on
SQL>
SQL> declare
  2    cursor c_tab is
  3      select table_name, column_name , data_type
  4      from user_tab_columns
  5      where table_name = 'DEPT';
  6    v_sql VARCHAR2 (32000);
  7    v_val varchar2(200);
  8  begin
  9    FOR r_tab in c_tab LOOP
 10      v_sql := 'SELECT MAX(' || r_tab.column_name || ') FROM ' ||
 11                r_tab.table_name;
 12      execute immediate v_sql into v_val;
 13      dbms_output.put_line(r_tab.table_name||'.'||r_tab.column_name||': '|| v_val);
 14    end LOOP;
 15  end;
 16  /
DEPT.DEPTNO: 40
DEPT.DNAME: SALES
DEPT.LOC: NEW YORK

PL/SQL procedure successfully completed.

SQL>

如果要将结果存储到表中，则需要执行如下操作：

SQL> create table maxes
  2    (table_name   varchar2(30),
  3     column_name  varchar2(30),
  4     max_value    varchar2(30)
  5    );

Table created.

SQL> declare
  2    cursor c_tab is
  3      select table_name, column_name , data_type
  4      from user_tab_columns
  5      where table_name = 'DEPT';
  6    v_sql VARCHAR2 (32000);
  7    v_val varchar2(200);
  8  begin
  9    FOR r_tab in c_tab LOOP
 10      v_sql := 'SELECT MAX(' || r_tab.column_name || ') FROM ' ||
 11                r_tab.table_name;
 12      execute immediate v_sql into v_val;
 13      insert into maxes (table_name, column_name, max_value)
 14        values
 15        (r_tab.table_name, r_tab.column_name, v_val);
 16    end LOOP;
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL> select * From maxes;

TABLE_NAME      COLUMN_NAME     MAX_VALUE
--------------- --------------- ---------------
DEPT            DEPTNO          40
DEPT            DNAME           SALES
DEPT            LOC             NEW YORK

SQL>

Answer 2

您应该出现在字符串中的双引号。

v_sql := 'SELECT ''' || 
            r_tab.table_name || ''' TABLE_NAME, ''' || 
            r_tab.column_name || ''' COLUMN_NAME, ' || 
            '(SELECT MAX(' || r_tab.column_name || ') FROM ' || r_tab.table_name || ') VALUE ' || 
            'FROM DUAL ';