Como determinar el tipo de dato de una columna en una tabla, sin hacer uso del DESC

Este ejemplo, puede ser utilizado para adaptar una función que devuelva el tipo de dato almacenado en una columna de una tabla específica.

La consulta fue publicada en el foro de la Comunidad Oracle Hispana, así que les remito la respuesta a todos ustedes:

Foro: http://comunidadoraclehispana.ning.com/forum/topics/como-saber-si-un-dato-es

Cómo saber si un dato es número, letra, fecha ?
Publicado por Ivan Dario Guerrero Martinez el julio 27, 2009 a las 5:00pm en Tecnologias Oracle

Fuente:http://stackoverflow.com/questions/1087161/oracle-identify-data-type

DECLARE
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
BEGIN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('col_type = '
rec.col_type);
DBMS_OUTPUT.PUT_LINE('col_maxlen = '
rec.col_max_len);
DBMS_OUTPUT.PUT_LINE('col_name = '
rec.col_name);
DBMS_OUTPUT.PUT_LINE('col_name_len = '
rec.col_name_len);
DBMS_OUTPUT.PUT_LINE('col_schema_name = '
rec.col_schema_name);
DBMS_OUTPUT.PUT_LINE('col_schema_name_len = '
rec.col_schema_name_len);
DBMS_OUTPUT.PUT_LINE('col_precision = '
rec.col_precision);
DBMS_OUTPUT.PUT_LINE('col_scale = '
rec.col_scale);
DBMS_OUTPUT.PUT('col_null_ok = ');
IF (rec.col_null_ok) THEN
DBMS_OUTPUT.PUT_LINE('true');
ELSE
DBMS_OUTPUT.PUT_LINE('false');
END IF;
END;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c, 'SELECT * FROM RD_ENLACES', DBMS_SQL.NATIVE);

d := DBMS_SQL.EXECUTE(c);

DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := rec_tab.first;
IF (col_num IS NOT NULL) THEN
LOOP
print_rec(rec_tab(col_num));
col_num := rec_tab.next(col_num);
EXIT WHEN (col_num IS NULL);
END LOOP;
END IF;

DBMS_SQL.CLOSE_CURSOR(c);
END;
/

create view vw_test as
select 1 an_integer, 'abc' a_string, sysdate a_date from dual;

SQL> set linesize 80
SQL> desc rd_enlaces
Name Null? Type
----------------------------------------- -------- ----------------
CODIGO NOT NULL VARCHAR2(5)
ORDEN NOT NULL NUMBER(2)
DESCRIPCION NOT NULL VARCHAR2(30)
DB_LINK NOT NULL VARCHAR2(80)
IND_OFICINA NOT NULL VARCHAR2(1)
IND_ACTIVO NOT NULL VARCHAR2(1)

SQL> @0
col_type = 1
col_maxlen = 5
col_name = CODIGO
col_name_len = 6
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = false
col_type = 2
col_maxlen = 22
col_name = ORDEN
col_name_len = 5
col_schema_name =
col_schema_name_len = 0
col_precision = 2
col_scale = 0
col_null_ok = false
col_type = 1
col_maxlen = 30
col_name = DESCRIPCION
col_name_len = 11
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = false
col_type = 1
col_maxlen = 80
col_name = DB_LINK
col_name_len = 7
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = false
col_type = 1
col_maxlen = 1
col_name = IND_OFICINA
col_name_len = 11
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = false
col_type = 1
col_maxlen = 1
col_name = IND_ACTIVO
col_name_len = 10
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = false

PL/SQL procedure successfully completed.

SQL> select * from vw_test;

NUMBER,STRING,FECHA)
-----------------------
1 abc 29-JUL-09