Oracle中用EXECUTE IMMEDIATE获取主键统计遇ORA-01722错误排查

从Oracle数据库各表的主键元数据生成统计信息，编写了如下PL/SQL脚本：

SET SERVEROUTPUT ON;
DECLARE
    v_owner            VARCHAR2(40);
    v_table_name       VARCHAR2(40);
    v_column_name      VARCHAR2(40);
    v_count_rows       NUMBER;
    v_count_real_rows  NUMBER;
    v_count_rows_diff  NUMBER;
    v_rn_tables        NUMBER;
    v_count_tables     NUMBER;
    v_max_primary_key  NUMBER;
    sql_stmt           VARCHAR2(32767);
    CURSOR get_tables IS
    SELECT
        cons.owner,
        cols.table_name,
        cols.column_name,
        nvl(num_rows, - 1)      AS count_rows,
        ROW_NUMBER()
        OVER(PARTITION BY cons.owner
             ORDER BY cols.table_name
        )                       AS rn_tables,
        COUNT(DISTINCT cols.table_name)
        OVER(PARTITION BY cons.owner
            -- ORDER BY cols.table_name
        )                       AS count_tables
    FROM
        all_constraints   cons,
        all_cons_columns  cols,
        all_tables        tab
    WHERE
            cols.table_name = tab.table_name
        AND cons.constraint_type = 'P'
        AND cons.constraint_name = cols.constraint_name
        AND cons.owner = cols.owner
        AND tab.table_name NOT LIKE '%_MV'
        AND cols.position = 1
    ORDER BY
        cons.owner,
        cols.table_name;

BEGIN
    OPEN get_tables;
    LOOP
        FETCH get_tables INTO
            v_owner,
            v_table_name,
            v_column_name,
            v_count_real_rows,
            v_rn_tables,
            v_count_tables;
        EXIT WHEN get_tables%notfound;
         -- dbms_output.put_line('Tabelle ' || v_owner ||' ' || v_table_name||' ' || v_column_name||' ' || v_count_real_rows  );

        sql_stmt := 'SELECT COUNT(*) as count_rows,max('||v_column_name||') as max_primary_key FROM '
                    || v_owner
                    || '.'
                    || v_table_name;
     --   dbms_output.put_line(sql_stmt);
        EXECUTE IMMEDIATE sql_stmt
        INTO v_count_rows,v_max_primary_key;
            dbms_output.put_line(v_rn_tables ||' out of '||v_count_tables ||' '||v_owner
                                 || '.'
                                 || v_table_name
                                 || ': '
                                 || v_count_rows
                                 || ': '
                                 || v_count_real_rows
                                 || ': '
                                 || v_count_rows_diff);

    END LOOP;

    CLOSE get_tables;
END;

运行脚本时出现如下错误：

ORA-01722: invalid number
ORA-06512: at line 63
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

移除v_max_primary_key相关逻辑后脚本可正常运行，请问错误原因是什么？

• 主键列数据类型与变量不匹配：脚本中v_max_primary_key被定义为NUMBER类型，但数据库中存在非数值类型的主键列（比如VARCHAR2、CHAR或日期类型）。当执行MAX(主键列)时，返回的结果是对应类型的值，无法直接赋值给数值类型变量，触发类型转换错误。
• 隐式转换失败：Oracle尝试将非数值类型的主键最大值转为NUMBER时，若主键值包含非数字内容（例如字符串主键'EMP_001'），会直接抛出ORA-01722无效数字错误。

内容的提问来源于stack exchange，提问作者kirilb