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) 
                          ); 

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; 

CREATE OR REPLACE PACKAGE BODY PKG_ALL_TAB_COLUMNS 
AS  
  
 /* Imprimir los datos consltados 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 
    DBMS_OUTPUT.PUT_LINE ('MENSAJE: NO EXISTEN DATOS A CONSULTAR' ); 
   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; 

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