Oracle

Tablas externas en Oracle

Un requerimiento en mi pasado proyecto, consistía en que el usuario pudiera cargar en el momento que desee un archivo proveniente de Excel (que serían los usuarios sin Excel), se procesara la información con datos complementarios y le fuera devuelta en otro archivo que pudiera manejar también en Excel.

Para la carga inicial del archivo contaba con varias opciones, al sistema estar desarrollado en Oracle Developer 6i, igual se puede generar un nuevo modulo y mediante TEXT_IO manejar la información del archivo, al ser una carga de información se podría usar SQL*Loader, también podría haberse usado UTL_FILE para leer y cargar el archivo a la base o la que finalmente el cliente acabo aceptando por su facilidad de uso y mantenimiento, las tablas externas.

En la practica una tabla externa no es mas que un archivo plano con cierto layout predefinido en el cual los campos están separados por algún carácter como una coma, comillas, o tabuladores, muy parecido al que se usaría para hacer una carga desde SQL*Loader, Que es declarado en Oracle y que puede ser manejado directamente como cualquier otro objeto de la base de datos, pero a fin de cuentas es un archivo en el sistema operativo, con algunas limitantes, como no poder realizar inserts, deletes, updates sobre el objeto, no se pueden generar indices y su tamaño máximo es de 2GB, veamos ahora como crearlo:

  • Previamente se crea un directorio donde vivirá el mencionado archivo plano, (la ruta depende de nuestro servidor):
    1
    
    CREATE OR REPLACE DIRECTORY EXTERNAL_TABLES AS '/usr/files/external/';
  • Le damos privilegios a determinado usuario para leer y escribir en este directorio:
    1
    
    GRANT READ, WRITE ON DIRECTORY EXTERNAL_TABLES TO USUARIO;
  • En el directorio físico del servidor copiamos el archivo que contendrá los datos llamado “tabla_externa.txt”, para este ejemplo usaremos datos como los que siguen (¿creo que es evidente que son de prueba verdad?):
    1
    2
    3
    4
    5
    
    1,00001,551122334455,11111111111
    2,00002,551122334466,22222222222
    3,00003,551122334477,33333333333
    4,00004,551122334488,44444444444
    5,00005,551122334499,55555555555
  • Ahora creamos la tabla externa:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    
    CREATE TABLE tabla_externa (
      CLI_ID         VARCHAR2(50),
      CONTRATO       VARCHAR2(50),
      TELEFONO       VARCHAR2(50),
      NO_SERIE       VARCHAR2(50)
    )
    ORGANIZATION EXTERNAL (
      TYPE oracle_loader 
      DEFAULT directory EXTERNAL_TABLES
      access parameters
    ( records delimited BY newline
      skip 1
      badfile 'tabla_externa.bad'
      logfile 'tabla_externa.log'
      fields terminated BY ','
      missing field VALUES are NULL
    )
    location ('tabla_externa.txt')
    )
    reject limit unlimited;

Listo, a partir de este momento tenemos acceso a los datos dentro de cualquier consulta normal, solo unas observaciones adicionales:

Si el archivo que deseas mostrar contiene muchos, muchos datos, igual y te conviene mejor usar SQL*Loader para previamente cargar tus datos a Oracle ya que los tiempos para consultas a estas tablas con bastante mas grandes que los tiempos de una tabla normal, esto debido a que no se pueden crear indices sobre la tabla externa o, en su defecto, usar una segunda tabla, que sea llenada con la información de la externa con un simple “insert as select” la cual tenga sus debidos indices.

Para no perder la facilidad en el acceso a la información ganado con la tabla externa por algún error “de dedo”, esta fue creada especificando varchar2(50) para sus campos, aunque en realidad se espera en esas columnas valores numéricos y de mucha menos extensión, por ejemplo para el cli_id se espera un numérico de 10 caracteres, pero declararlo así no me regresaría por ejemplo un valor que tuviera un carácter, preferí dejarlo como varchar2 y manejar esto mediante la consulta que extrae los datos, en este caso, limpiando la cadena de caracteres no numéricos, esto claro depende de la calidad esperada de la información, si se espera una información de calidad igual y conviene declarar los tipos de acuerdo a su valor esperado, en mi caso esto no fue así.

23 thoughts on “Tablas externas en Oracle

  1. Hola a todos, implementamos oracle_loader en una aplicacion y la carga de datos funciona a la perfeccion, pero para las lineas que tienen error el archivo de log se genera pero cuando dichos errores estan en lineas superiores a 30.000 o 40.000 aprox, los números de lineas informadas en el archivo .log son incorrectas. Por ej: si el error esta en la linea 30.000 informa la linea 30.010 o 30.020, etc.

    ¿Alguien tiene idea por que sucede esto?

    Muchisimas gracias.

  2. @probando

    Table created.

    SQL> describe tabla_externa;
    Name Null? Type
    —————————————– ——– —————————-
    CLI_ID VARCHAR2(50)
    CONTRATO VARCHAR2(50)
    TELEFONO VARCHAR2(50)
    NO_SERIE VARCHAR2(50)

    SQL> select * from tabla_externa;
    select * from tabla_externa
    *
    ERROR at line 1:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04063: unable to open log file tabla_externa.log
    OS error Permission denied
    ORA-06512: at “SYS.ORACLE_LOADER”, line 19

    cuando voy a consultar los datos de la tabla me sale lo siguiente.

  3. Necesito cargar archivos planos a oracle… leí sobre las tablas externas y me parece interesante y práctico, pero los nombres de mis archivos no son fijos, además en un paquete quise crear la tabla externa pero no me permite… necesito permisos?… por favor me pueden ayudar?

  4. select * from empleados_ext;

    Estimados amigos tengo este error podrian ayudarme…?
    Gracias por la atencion…

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04063: unable to open log file empleados.log
    OS error El sistema no puede encontrar el archivo especificado.
    ORA-06512: at “SYS.ORACLE_LOADER”, line 19

  5. me parece rara tu pregunta, como mostrar los registros con error que no muestra por que tienen errores, mmmm, lee de nuevo el post, creo que una segunda leidita te solucionara el problema, saludos

  6. una onsulta tengo un archivo plano con errores, pero cuando yo le hago select a la tabla externa esta solo me muestra los registros que estan bien y no los que estan con errores como podria hacer para que me muestre estos registros tambien en el sl loader

    atte

  7. Cual puede ser la instruccion para poder ver la ruta que se ha puesto para almacenar los ficheros externos?.
    No lo quiero cambiar, pero no se cuál es la ruta que se creó en su día.
    Cómo sería la sentencia?

  8. si no te los inserta lo unico que se me ocurre es que los datos sean alfanumericos y tu pienses que son numericos, si no, de plano mejor usa la tabla externa solo de paso a la final, y de la externa a la final usa un to_number() para insertar los datos

  9. La duda anterior ya la he solucionado!!! Mi duda ahora viene porque al crear la tabla con tipos distintos a VARCHAR2() no me los inserta, para que me los inserte deben ser de ese tipo…pero yo quiero que mantenga mi tipo de datos (number, date..etc..).

    GRACIAS!!

  10. Hola, qué tal? Mi problema es que al dar privilegios a un usuario no me deja porque yo mismo me los estoy dando, y me muestra un error..Pensareís que cambie de usuario…pero como? Como se crea un nuevo usuario?? Estoy utilizando Oracle 10g Release 2, y mi usuario es SYSTEM xq tampoco me dió opción a elegir otro..que puedo hacer????

    Muchas gracias de antemano!!

  11. creo que tu duda viene de: una cosa es una herramienta de oracle llamada sqlloader para carga masiva de informacion a la base de datos y otra las tablas externas, las tablas externas usan una sintaxis casi identica a la de sqlloader, su ventaja es precisamente como comento en el post es poder accesar a ellas con sentencias sql como a cualquier otra tabla, pero la informacion como tal de esa tabla esta contenida en el archivo, no es cargada a oracle por “default” por asi decirlo en cambio con sqlloader si cargas la informacion a la base de datos

  12. Para quien pidio informacion, a cerca de como usar BULK INSERT, para mi esta a sido una muy buena herramienta para cargarme los datos de ficheros creados en excel, a mis tablas de SQL_SERVER, aun que este no es un foro de SQL_SERVER, te voy a poner la sintaxis para hacerlo, es sumamente sencillo.

    BULK INSERT database_name . Owner . table_name
    FROM ‘path\data_file.txt’
    WITH
    FIELDTERMINATOR = ‘,’
    ROWTERMINATOR = ‘\n’

    Listo!! con 5 sencillas lineas te cargas el contenido del data_file.txt en tu table_name, ojo…si usas excel, guardate el achivo como tipo csv, para que te lo delimite por comas, y depues lo grabas como txt. y lo cargas.

    Ahora para los masters de Oracle….de este foro… si yo ya tengo creada mi tabla y con datos, onde rayos le indico con esa serie de instrucciones que dieron…en que tabla quiero que se vacie el fichero??????? es decir, no requiero crear la tabla, asi que no puedo empezar el codigo desde ahi, y si lo empiezo desde la parte:

    organization EXTERNAL (
    type oracle_loader
    default directory EXTERNAL_TABLES
    access parameters
    ( records delimited by newline
    skip 1
    badfile ‘tabla_externa.bad’
    logfile ‘tabla_externa.log’
    fields terminated by ‘,’
    missing field values are null
    )
    location (‘tabla_externa.txt’)
    )
    reject limit unlimited;

    en que parte le diria, a que tabla quiero que inserte los datos del Archivo externo.

  13. hola por favor necesito ayuda
    he intentado pero no funciona quisiera que me envie la sintaxis correcta sobre bulk insert para que extenciones trabaja y si esta desde el sql server 2000 muchas gracias de antemana
    atte
    sandra

  14. Buenas, el asunto es lo siguiente: yo me he decidido a usar sqlldr y lo comprendo y funciona bien si los datos que quiero llevar a BBDD estan separados por algún caracter tipo “,” “·” o lo que sea. Ahora bien el problema lo tengo porque no se que demonios poner si los datos estan separados por un tabulador. Porfavor si sabes como hacerlo comentamelo plz. Gracias

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *