关于存储过程中变量遇到属性为Height Balanced的Histogram时报错问题

Question

正在开发远程自动统计的过程，用动态语句，筛选用的变量为转变成VARCHAR2格式的系统日期,遇到统计某一表数字时报错，错误类型为ora-01722：invalid number，ora-02063等，将过程中的变量直接改成常量，运行通过，同一过程中其他表用变量统计正常，后来研究远程数据库表的类型，发现该表COLUMNS里相应字段的histogram属性为height balanced，而其他统计正常的表相应字段的该属性为NONE。
请教，该如何解决此问题。

Answer 1

跟你histogram属性为height balanced应该没关系，该属性表示是否收集此字段的 histogram 统计信息，即列值分布的直方图信息，系统作查询的执行计划时使用。 对表中数据没有影响，对查询性能有影响。
而你错误信息ora-01722：invalid number 是说无效的数字，应该是你格式的问题，你贴出你具体的SQL看下呢

Answer 2

如果使用to_date将字符转换成日期或者使用to_number将字符转换为数字时，如果字符串不符合格式，不能转换则会报，ora-01722：invalid number

Answer 3

CREATE OR REPLACE PROCEDURE TJ
IS
   JQ       NUMBER;
   XF       NUMBER;
   strSQL   VARCHAR2 (4000);
   strEnd   VARCHAR2 (14);
BEGIN
   strSQL := 'select to_char(sysdate,''yyyyMMdd'')||''170000'' from dual';   EXECUTE IMMEDIATE strSQL INTO   strEnd;   strSQL :=
      'CREATE TABLE TJ_' 
      || SUBSTR (strEnd, 1, 8)
      || '
        (
        lx     VARCHAR2 (255),
        jq     VARCHAR2 (255),
        xf     VARCHAR2 (255)
        )';   EXECUTE IMMEDIATE strSQL;   strSQL :=
      'SELECT COUNT(distinct bh) FROM xt_dba.xx@dblink_xt WHERE (rqsj BETWEEN ''20101018'' AND '
      || strEnd
      || ') AND lx =''704'' AND BS != ''4''';   EXECUTE IMMEDIATE strSQL INTO   jq;   strSQL :=
      'select count(distinct jbbh) FROM XT_DBA.jbxx@DBLINK_XT WHERE (sj between ''20101018'' AND '
      || strEnd
      || ') and fg=''01'')';   EXECUTE IMMEDIATE strSQL INTO   xf;   strSQL :=
         'insert into TJ_'
      || SUBSTR (strEnd, 1, 8)
      || ' values(''总计'','
      || jq
      || ','
      || xf
      || ')';   EXECUTE IMMEDIATE strSQL;   COMMIT;
END TJ;
xt_dba.xx的rqsj的Histogram为Height Balanced，XT_DBA.jbxx的SJ的Histogram为NONE

Answer 4

运行后就会报错，如果把rqsj用常量表示而下边的SJ继续用变量就能通过

Answer 5

看下你表xt_dba.xx@dblink_xt 中的rqsj类型是字串型的吧，格式是什么样的？是否存在非数字的？
还有，你说将过程中变量改成常量，你改的是哪个变量为常量？
我认为是你动态SQL拼接错了，应该这样吧：strSQL :=
      'SELECT COUNT(distinct bh) FROM xt_dba.xx@dblink_xt WHERE (rqsj BETWEEN ''20101018'' AND '''||strEnd||''') AND lx =''704'' AND BS != ''4''';

Answer 6

xt_dba.xx@dblink_xt 中的rqsj类型是VARCHAR2(14),变量就是STREND，改成'20101115215000'就能通过，动态语句应该没错，因为这个过程原本是统计我本地的数据用的，本地的数据是每天凌晨空闲时运行JOB抓取远程库的内容然后在本地统计，但是现在需要下午5点的数据，远程抓取耽误时间了，所以才直接在线查询的。
抓取回来的数据的Histogram为NONE，统计正常。

Answer 7

动态语句按照你说的多加了''，然后通过了，但我只在xt_dba.xx的这一段改成了'''，在另一个表没有加，还是1个'，正常通过是什么原因啊？

Answer 8

难道查询第一个表ORACLE会把STREND自动变成数字？多添两个'变成字符串？第二个表直接默认成字符串？但是表和过程中都定义成字符串了，怎么还会出这种问题？和那个直方图真没关系？

Answer 9

最直接的办法是你将每一个strSQL打印出来看下，具体是什么？
dbms_output.put_line(strSQL);