Paquete en Oracle para consultar la definición de las columnas de una tabla

Paquete en Oracle que permite conocer las propiedades (tipo de dato, longitud, precisión, entre otras) de una columna tras ser definida por el usuario al momento de crear una tabla en la base de datos.

Se crea un sencillo paquete en Oracle que ofrezca una opción para consultar desde cualquier sistema, las propiedades de las columnas definidas durante la creación de una tabla en la base de datos.

Dicho paquete lo he nombrado PKG_ALL_TAB_COLUMNS, en razón de que internamente hace llamadas de consultas específicas a la tabla ALL_TAB_COLUMNS, la cual maneja información útil sobre las características o propiedades con las que se definieron las columnas de las diversas tablas creadas en la base de datos.

Con este programa es posible trabajar en base a los tipos de datos que puede manejar una columna, así como su longitud máxima de aceptación de datos, la precisión cuando el dato es numérico y trabaja con cierta cantidad de decimales; de igual forma podemos consultar si el campo es NULLABLE.

Es posible hacer uso de este paquete para realizar validaciones específicas de los datos que se envían a un programa, como por ejemplo: limitar una cadena de acuerdo a la longitud ya conocida de una columna, o obligar la entrada de datos cuando el campo está definido como NOT NULL.

Creando el paquete PKG_ALL_TAB_COLUMNS

Lo primero que hice fue crear en nuestra base de datos un nuevo Tipo de Dato Objeto, que nos permitirá imprimir la información que requerimos a partir de un Constructor.

Este Tipo de Dato Objeto lo he llamado all_tab_columns_obj_typ y servirá además para devolver la información de las funciones internamente definidas en nuestro paquete.

CREATE OR REPLACE TYPE all_tab_columns_obj_typ
AS OBJECT (
owner VARCHAR2 (30),
table_name VARCHAR2 (30),
column_name VARCHAR2 (30),
data_type VARCHAR2 (30),
data_length NUMBER,
data_precision NUMBER,
nullable VARCHAR2 (1)
);
/

Type created.

Ahora vamos a crear la especificación de nuestro paquete.

CREATE OR REPLACE PACKAGE PKG_ALL_TAB_COLUMNS
AS
FUNCTION F_GET_DATA_TYPE_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN VARCHAR2;
FUNCTION F_GET_DATA_LENGTH_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN NUMBER;
FUNCTION F_GET_DATA_PRECISION_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN NUMBER;
FUNCTION F_GET_NULLABLE_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN VARCHAR2;
END PKG_ALL_TAB_COLUMNS;
/

Package created.

Como podrán darse cuenta, en la especificación del paquete he definido las siguientes funciones que estarán disponible para ser llamadas desde otros programas o subprogramas. Estas son:

F_GET_DATA_TYPE_COLUMN
F_GET_DATA_LENGTH_COLUMN
F_GET_DATA_PRECISION_COLUMN
F_GET_NULLABLE_COLUMN

Estas funciones requieren de tres parámetros obligatorios para lograr su cometido, los cuales son:

p_owner - hace referencia al dueño de la tabla
p_table_name - hace referencia al nombre de la tabla a consultar
p_column_name - hace referencia al nombre de la columna a consultar

Por último, creamos el cuerpo de nuestro paquete.

CREATE OR REPLACE PACKAGE BODY PKG_ALL_TAB_COLUMNS
AS
/* Imprimir los datos consultados de ALL_TAB_COLUMNS */
FUNCTION F_PRINT_ALL_TAB_COLUMNS (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN all_tab_columns_obj_typ
IS
CURSOR cur_atc (p_owner IN VARCHAR2,
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2
)
IS
SELECT atc.OWNER AS owner,
atc.TABLE_NAME AS table_name,
atc.COLUMN_NAME AS column_name,
atc.DATA_TYPE AS data_type,
atc.DATA_LENGTH AS data_length,
atc.DATA_PRECISION AS data_precision,
atc.NULLABLE AS NULLABLE
FROM ALL_TAB_COLUMNS atc
WHERE atc.OWNER = p_owner
AND atc.TABLE_NAME = p_table_name
AND atc.COLUMN_NAME = p_column_name;
v_exists BOOLEAN := FALSE;
BEGIN
FOR rec IN cur_atc (p_owner,
p_table_name,
p_column_name
)
LOOP
v_exists := TRUE;
RETURN all_tab_columns_obj_typ (rec.owner,
rec.table_name,
rec.column_name,
rec.data_type,
rec.data_length,
rec.data_precision,
rec.nullable
);
END LOOP;
IF NOT v_exists THEN
RETURN all_tab_columns_obj_typ (NULL,NULL,NULL,NULL,NULL,NULL,NULL);
END IF;
END F_PRINT_ALL_TAB_COLUMNS;
/* Get data_type column table */
FUNCTION F_GET_DATA_TYPE_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN VARCHAR2
IS
v_data all_tab_columns_obj_typ;
BEGIN
v_data := F_PRINT_ALL_TAB_COLUMNS (p_owner,
p_table_name,
p_column_name
);
RETURN NVL (v_data.data_type, 'NULL');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR ( '
|| sqlcode
|| ') '
|| '(PROCESO: F_GET_DATA_TYPE_COLUMN)'
|| sqlerrm);
END F_GET_DATA_TYPE_COLUMN;
/* Get data_length column table */
FUNCTION F_GET_DATA_LENGTH_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN NUMBER
IS
v_data all_tab_columns_obj_typ;
BEGIN
v_data := F_PRINT_ALL_TAB_COLUMNS (p_owner,
p_table_name,
p_column_name
);
RETURN NVL (v_data.data_length, 0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR ( '
|| sqlcode
|| ') '
|| '(PROCESO: F_GET_DATA_LENGTH_COLUMN)'
|| sqlerrm);
END F_GET_DATA_LENGTH_COLUMN;
/* Get data_precision column table */
FUNCTION F_GET_DATA_PRECISION_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN NUMBER
IS
v_data all_tab_columns_obj_typ;
BEGIN
v_data := F_PRINT_ALL_TAB_COLUMNS (p_owner,
p_table_name,
p_column_name
);
RETURN NVL (v_data.data_precision, 0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR ( '
|| sqlcode
|| ') '
|| '(PROCESO: F_GET_DATA_PRECISION_COLUMN)'
|| sqlerrm);
END F_GET_DATA_PRECISION_COLUMN;
/* Get nullable column table */
FUNCTION F_GET_NULLABLE_COLUMN (p_owner IN ALL_TAB_COLUMNS.OWNER%TYPE,
p_table_name IN ALL_TAB_COLUMNS.TABLE_NAME%TYPE,
p_column_name IN ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
RETURN VARCHAR2
IS
v_data all_tab_columns_obj_typ;
BEGIN
v_data := F_PRINT_ALL_TAB_COLUMNS (p_owner,
p_table_name,
p_column_name
);
RETURN NVL (v_data.nullable, 'NULL');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR ( '
|| sqlcode
|| ') '
|| '(PROCESO: F_GET_NULLABLE_COLUMN)'
|| sqlerrm);
END F_GET_NULLABLE_COLUMN;
END PKG_ALL_TAB_COLUMNS;
/

Package Body created.

Luego de que nuestro paquete ya ha sido creado y compilado correctamente en nuestra base de datos, entonces procedemos a probar su funcionamiento a través de una consulta en SQL*Plus o a través de nuestro programa de comandos SQL preferido.

SELECT
PKG_ALL_TAB_COLUMNS.F_GET_DATA_TYPE_COLUMN ('SYS','DUAL','DUMMY') data_type,
PKG_ALL_TAB_COLUMNS.F_GET_DATA_LENGTH_COLUMN ('SYS','DUAL','DUMMY') data_length,
PKG_ALL_TAB_COLUMNS.F_GET_DATA_PRECISION_COLUMN ('SYS','DUAL','DUMMY') data_precision,
PKG_ALL_TAB_COLUMNS.F_GET_NULLABLE_COLUMN ('SYS','DUAL','DUMMY') nullable
FROM DUAL;

DATA_TYPE DATA_LENGTH DATA_PRECISION NULLABLE
VARCHAR2 1 0 Y

Si te ha servido de ayuda este código, entonces recuerda apoyar ésta y otras publicaciones relacionadas realizando una contribución a través de mi perfil siguiendo el enlace que se encuentra al inicio del artículo o escribiéndome tus comentarios a través de nuestro sistema de contacto.

Si lo prefiere puede consultar los resultados de este trabajo y validar su funcionamiento a través de Live Sql de Oracle.

Consulta Lo+ Top de Base De Datos