代码之家 › 专栏 › 技术社区 › Fazal

Oracle中的to_数函数出现了奇怪的问题

ora-01722 oracle10g oracle sql

Fazal · 技术社区 · 14 年前

在varchar2列的where子句中执行to_number函数时,我遇到了一个间歇性的问题,如果记录数超过了某个数字n,我就使用n,因为没有确切的记录数。在一个数据库中,N之后发生,而在另一个数据库中,N是100万,当时是0.1。百万。

例如,我有一个有1000万条记录的表,比如说表国家,它有包含数字数据和ID的字段1 varchar2

如果我以一个查询为例

select * 
from country 
where to_number(field1) = 23
and id >1 and id < 100000

这作品

但是如果我问的话

select * 
from country 
where to_number(field1) = 23 
and id >1 and id < 100001

它不能说无效的数字

下一步我试试这个问题

select * 
from country
where to_number(field1) = 23 
and id >2 and id < 100001

它又起作用了

因为我只得到了无效的数字,所以很混乱,但在日志文件中它说

Memory Notification: Library Cache Object loaded into SGA
Heap size 3823K exceeds notification threshold (2048K)
KGL object name :with sqlplan as (
    select c006 object_owner, c007 object_type,c008 object_name
      from htmldb_collections
     where COLLECTION_NAME='HTMLDB_QUERY_PLAN'
       and c007 in ('TABLE','INDEX','MATERIALIZED VIEW','INDEX (UNIQUE)')),
ws_schemas as(
    select schema 
      from wwv_flow_company_schemas
     where security_group_id = :flow_security_group_id),
t as(
        select s.object_owner table_owner,s.object_name table_name,
               d.OBJECT_ID
          from sqlplan s,sys.dba_objects d

这似乎与SGA的规模有关,但谷歌并没有给我多少帮助。

对于大数据的to_编号或Oracle函数,有人知道这个问题吗?

4 回复 | 直到 13 年前

Jeffrey Kemp 14 年前

假设您知道给定的ID范围将始终导致包含数字数据的字段1,您可以这样做:

select *
from (
  select /*+NO_MERGE*/ * 
  from country 
  where id >1 and id < 100000
)
where to_number(field1) = 23;

APC 14 年前

其中field1 varchar2包含数字数据

这是不好的做法。数字数据应保存在数字列中。原因很简单:如果不强制使用强数据类型,可能会发现varchar2列中有非数字数据。如果发生这种情况,那么像这样的过滤器

where to_number(field1) = 23

会失败 ORA-01722: invalid number .

我不能肯定地说这就是您的场景中正在发生的事情,因为我不明白为什么ID过滤器中明显不重要的更改会改变查询的成功。查看查询的不同版本的执行计划将具有指导意义。但我认为这更可能是你的数据问题,而不是SGA中的一个bug。

Dave Costa 14 年前

建议执行以下操作以确定是否有包含非数字数据的记录。正如其他人所说,执行计划和评估顺序的变化可以解释为什么错误不总是出现。

(假设sqlplus是客户机)

SET SERVEROUTPUT ON

DECLARE
   x  NUMBER;
BEGIN
  FOR rec IN (SELECT id, field1 FROM country) LOOP
    BEGIN
      x := TO_NUMBER( rec.field1 );
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line( rec.id || ' ' || rec.field1 );
    END;
  END LOOP;
END;
/

对于原始问题,另一种解决方法是重写查询以避免隐式类型转换,例如。

SELECT id, TO_NUMBER( field1 )
  FROM county
  WHERE field1 = '23'
    AND <whatever condition on id you want, if any>

Adam Musch 14 年前

考虑编写一个is_number pl/sql函数:

CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER 
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/

SQL> SELECT COUNT(*) FROM DUAL WHERE IS_NUMBER ('TEST') IS NOT NULL;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM DUAL WHERE IS_NUMBER ('123.45') IS NOT NULL;

  COUNT(*)
----------
         1