Tengo una tabla donde dos columnas son de tipo VARCHAR2(3BYTE) y VARCHAR2(32BYTE). Cuando hago una consulta de selección (where col1=10 y where col1='10') o (where col2=70001 o col2='70001') el número de registros obtenidos son los mismos en cada conjunto de cláusulas where. ¿Cómo sucede esto? ¿Cómo Oracle tratamiento de cadenas literales y constantes numéricas y comparar los datos a pesar de columna de tipo de datos?

Pero esto no funciona para una columna de tipo VARCHAR2(128BYTE). La consulta necesaria para ser where col3='55555555001' a trabajar y where col3=55555555001 lanza ORA-01722 error.

  • Nunca almacene los números en las columnas varchar!
  • El problema no es que 55555555001 no puede ser convertido a una cadena; es que en algún lugar en su mesa hay una fila que tiene un carácter no numérico en COL3. Usted puede utilizar la siguiente consulta para encontrarlo: SELECT COL3 FROM YOUR_TABLE WHERE TRANSLATE(COL3, 'x0123456789', 'x') IS NOT NULL. Compartir y disfrutar.
  • Relacionados: stackoverflow.com/questions/2330437/…
InformationsquelleAutor James Jithin | 2013-01-24

2 Comentarios

  1. 13

    Como se señaló en el El Lenguaje SQL de Referencia:

    • Durante SELECCIONE una DE las operaciones, Oracle convierte los datos de la columna con el tipo de la variable objetivo.
    • Cuando se compara un valor de carácter con un valor numérico, Oracle convierte los datos de caracteres en un valor numérico.

    La conversión implícita se realiza en la columna de la tabla cuando los tipos no coinciden. Esto puede ser visto por el seguimiento en SQL*Plus, con algunos datos ficticios.

    create table t42 (foo varchar2(3 byte));
    insert into t42 (foo) values ('10');
    insert into t42 (foo) values ('2A');
    set autotrace on explain

    Esto funciona:

    select * from t42 where foo = '10';
    
    FOO
    ---
    10
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3843907281
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T42  |     1 |     3 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("FOO"='10')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)

    Pero este error:

    select * from t42 where foo = 10;
    
    ERROR:
    ORA-01722: invalid number
    
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3843907281
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T42  |     1 |     3 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(TO_NUMBER("FOO")=10)

    Nota la diferencia en el filtro; filter("FOO"='10') frente a filter(TO_NUMBER("FOO")=10). En el último caso, la comparación de un número, una to_number() se realiza en contra de cada fila en la tabla el resultado de que la conversión se compara con el valor fijo. Así que si alguno de los valores de los caracteres no se puede convertir, usted obtendrá una ORA-01722. La función que se aplica también dejará un índice que se utiliza, si uno está presente en esa columna.

    Donde se pone interesante es que si usted tiene más de un filtro. Oracle puede evaluar en los diferentes órdenes en diferentes momentos, por lo que no siempre se puede ver la ORA-01722, y aparecerá a veces. Decir que había where foo = 10 and bar = 'X'. Si Oracle pensé que podría filtro de la no-X valores de la primera, sólo se aplicaría la to_number() para lo que queda, y que el más pequeño de la muestra podría no tener valores no numéricos en foo. Pero si usted tiene and bar = 'Y', la no-Y valores pueden incluir caracteres numéricos, o Oracle podría filtrar por foo primera, dependiendo de cómo selectivo se piensa que son los valores.

    La moraleja es nunca almacenar información numérica como un tipo de carácter.


    Yo estaba buscando un AskTom referencia a la copia de seguridad de la moral, y la primero me miró convenientemente se refiere al efecto de «un cambio en el orden de un predicado» así como diciendo: «no almacenar números en varchar2 s».

  2. 1

    Si una columna numérica o el valor, y una columna de caracteres están involucrados, Oracle convierte el carácter de los valores de la columna de los números y, a continuación, convierte los números con los números. Es como si hubiera escrito:

    where to_number(col3) = 55555555001

    Es por eso que usted consigue una ORA-01722: invalid number de error si una sola fila contiene una cadena (n col3) que no puede ser convertido a un valor numérico.

    Por esa razón tenemos la IS_NUMBER función en nuestra base de datos de Oracle que no causa un error, pero devuelve NULL para los valores que no pueden ser convertidos a números. A continuación, puede escribir:

    where is_number(col3) = 55555555001

    La función se define como:

    CREATE OR REPLACE FUNCTION is_number (p_str IN VARCHAR2)
      RETURN NUMBER
    IS
      l_num NUMBER;
    BEGIN
      l_num := to_number(p_str);
      RETURN l_num;
    
    EXCEPTION
      WHEN others THEN
        RETURN NULL;
    END is_number;

Dejar respuesta

Please enter your comment!
Please enter your name here