VALIDATE_CONVERSION

VALIDATE_CONVERSION

A função VALIDATE_CONVERSION foi introduzida no Oracle Database 12c Release 2, e possibilita detectar se o valor de entrada pode ser convertido para o tipo de dados especificado.

Algumas vezes nos deparamos com problemas de conversão de tipo de dados durante um processo de importação de dados por exemplo, nesse caso a função VALIDATE_CONVERSION pode ser extremamente útil como filtro, para ajudar a identificar dados que podem ou não serem convertidos para o tipo de dados especificado no destino.

A lógica da função é bastante simples, o primeiro parâmetro é o dado a ser convertido, o segundo parâmetro é o tipo de dado a ser convertido, também é possível especificar o formato.

A função retorna 1 se a conversão for bem sucedida, caso contrário, retornará 0.

Podemos usar a função VALIDATE_CONVERSION para testar as seguintes conversões de tipo de dados:

•          NUMBER.
•          DATE.
•          TIMESTAMP.
•          TIMESTAMP com Time Zone.
•          BINARY_FLOAT.
•          BINARY_DOUBLE.
•          INTERVAL DAY TO SECOND.
•          INTERVAL YEAR TO MONTH.

Vamos aos exemplos:

GPO@morpheus> select VALIDATE_CONVERSION('72497' as number), VALIDATE_CONVERSION('72497p' as number) from dual;

VALIDATE_CONVERSION('72497'ASNUMBER) VALIDATE_CONVERSION('72497P'ASNUMBER)
------------------------------------ -------------------------------------
                         1                                0

GPO@morpheus> select VALIDATE_CONVERSION('february 05, 2019' as date, 'month dd, yyyy', 'NLS_DATE_LANGUAGE = american') ENGLISH,

  2 VALIDATE_CONVERSION('febrero 05, 2019' as date, 'month dd, yyyy', 'NLS_DATE_LANGUAGE = spanish') SPANISH,

  3 VALIDATE_CONVERSION('febrero 05, 2019' as date, 'month dd, yyyy', 'NLS_DATE_LANGUAGE = portuguese') PORTUGUESE

  4 from dual;

   ENGLISH SPANISH PORTUGUESE
---------- ---------- ----------
      1      1       0

GPO@morpheus> create table TESTE (a varchar2(10));

Table created.

GPO@morpheus> insert into TESTE values (8273);

1 row created.

GPO@morpheus> insert into TESTE values ('acbd');

1 row created.

GPO@morpheus> insert into TESTE values (20190205);

1 row created.

GPO@morpheus> select * from TESTE;

A
----------
8273

acbd

20190205

GPO@morpheus> select * from TESTE where VALIDATE_CONVERSION (a as date, 'yyyymmdd') = 1;

A
----------
20190205

GPO@morpheus> select * from TESTE where VALIDATE_CONVERSION (a as number) = 0;

A
----------
acbd

Se a EXPR for avaliada como nulo, a função retornará 1.

GPO@morpheus> insert into TESTE values (null);

1 row created.

GPO@morpheus> insert into TESTE values (null);

1 row created.

GPO@morpheus> select nvl(a,'NULO') from TESTE where VALIDATE_CONVERSION (a as number) = 1;

NVL(A,'NUL
----------
8273

20190205

NULO

NULO

Referências

• Livro: Oracle Database 12c Release 2 New Features (Oracle Press).
• Oracle Database 12c Release 2 SQL Language Reference.  
• Oracle Live SQL.