SQL*Loader (Parte 1)

David SanzDavid Sanz
10 min read

¿Qué es SQL*Loader? Es el software de Oracle por excelencia para realizar cargas en nuestras tablas a partir de ficheros planos, donde podemos:

  • Cargar datos en situaciones donde los archivos se encuentran en ubicaciones distintas a la base de datos permitiendo una mayor flexibilidad.

  • Capacidad de cargar múltiples archivos o tablas en una misma sesión.

  • Posibilidad de carga paralela (PARALLEL=TRUE) para aquellos escenarios que involucran grandes volúmenes de información.

  • Posibilidad de emplear diferentes conjuntos de caracteres a nivel de sesión garantiza que se pueda trabajar con datos en diversos formatos.

  • Manipulación de los datos antes de su carga es otra característica valiosa, ya que permite decidir qué información se debe importar.

  • El uso de funciones SQL para la manipulación de datos antes de su almacenamiento en las tablas proporciona un control adicional sobre el proceso de carga.

  • Posibilidad de usar cargas convencionales, direct path o tablas externas.

En el siguiente diagrama podemos ver el flujo que genera el uso del SQL*Loader:

Vamos a describir cada uno de los puntos que vemos en el flujo:

  • Input: Fichero con los datos que queremos insertar en nuestras tablas.

  • CTL: Archivo de control. Es un fichero de texto cuya sintaxis es case-insensitive. Cuando usamos el SQL*Loader lo podemos hacer de dos maneras, con o sin archivo de control. El uso de un archivo de control en las operaciones, nos permitirá tener un mejor control en las operaciones de carga. Algo idóneo cuando nuestras cargas cada vez son más complejas. Cuando no usamos archivos de control, esto se conoce como SQL*Loader Express.

    El archivo de control tiene tres secciones principales:

    • Información de la sesión

    • Información de la tabla así como sus campos.

    • Datos de entrada (opcional)

Ejemplo de un fichero de control:

    OPTIONS (SKIP=1)
    LOAD DATA
    APPEND
    INTO TABLE <SCHEMA>.<TABLE>
    FIELDS TERMINATED BY ";"
    TRAILING NULLCOLS
    (
    COL_01 CONSTANT '%?VALUE?%',
    COL_02 EXPRESSION 
       "case 
          when SUBSTR(SUBSTR('%?NOMBRE_FICHERO?%',0,INSTR('%?NOMBRE_FICHERO?%', '-',1)-1),5)*7 > 366 then 
          trunc(to_date(concat(SUBSTR(SUBSTR('%?NOMBRE_FICHERO?%',1,6),1,4),365) , 'YYYYddd'), 'iw') 
        else 
          trunc(to_date(concat(SUBSTR(SUBSTR('%?NOMBRE_FICHERO?%',1,6),1,4),SUBSTR(SUBSTR('%?NOMBRE_FICHERO?%',0,INSTR('%?NOMBRE_FICHERO?%', '-',1)-1),5)*7), 'YYYYdd d'), 'iw') 
        end",
    COL_03 CHAR TERMINATED BY ";",

  • DSC: Fichero de descarte, contiene los registros que no se han podido insertar porque no cumplen con los criterios de selección establecidos en el archivo de control, es decir, aquellos registros que han sido filtrados y no se procesarán.

  •     [oracle@]$ cat CONTROL_33.dsc
    ;;;;;;;;;;;;;;;;;;;;;;;

  • LOG: Archivo de log de la operación. Contiene información detallada del flujo generado, incluyendo descripciones o errores que se hayan producido. El log se genera cuando empieza el proceso del SQL*Loader y es importante recalcar que si el SQL*Loader no puede crear el archivo log, el proceso termina.

      [oracle]$ head -30 74334127.log
  SQL*Loader: Release 19.0.0.0.0 - Production on Sat Mar 15 19:35:26 2025
  Version 19.21.0.0.0
  Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  Control File:  CONTROL_33.ctl
  Character Set UTF8 specified for all input.
  Data File:    CONTROL_33.dat
  Bad File:     CONTROL_33.bad
  Discard File: CONTROL_33.dsc
   (Allow all discards)
  Number to load: ALL
  Number to skip: 0
  Errors allowed: 100000
  Bind array:     100 rows, maximum of 1048576 bytes
  Continuation:    none specified
  Path used:      Conventional
  Silent options: FEEDBACK
  Table <TABLE>, loaded from every logical record.
  Insert option in effect for this table: APPEND
  TRAILING NULLCOLS option in effect
     Column Name                  Position   Len  Term Encl Datatype
  ------------------------------ ---------- ----- ---- ---- ---------------------
  .....

  • BAD: Contiene los registros que fueron rechazados por SQL*Loader o por Oracle Database. Este fichero se crea automáticamente aunque no se especifique. El nombre que toma al crearse automáticamente, será el del fichero de datos pero cambiando la extensión a ".bad”.

      [oracle@]$ head -10 CONTROL_33.bad
  1010228;ES0031000001010228GB1F;ERZ;22;RA;220;;;;1010228-01;S;;;954269244;;;;;;N;;GS;fakedns-testformaxlength255-202310191143-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000:9999;;0;;;;;;;R;;;;;;

Para ejecutar esta herramienta, lo haremos con el comando sqlldr. Al ejecutarlo, internamente lo que hace es leer los archivos de datos si están especificados en el archivo de control (INFILE='CONTROL.dat' ) o bien a la hora de ejecutarlo (sqlldr user/pass@local_sid DATA='CONTROL.dat').

Los archivos de datos no son más que registros para nuestras tablas, donde cada registro puede ser:

  • Tamaño fijo: Cuando los registros tienen la misma longitud en bytes. Vamos a modificar el ejemplo anterior, especificando un fijo de 8 bytes (incluyendo espacios) “INFILE ___ "fix 8"“

      OPTIONS (SKIP=1)
  LOAD DATA
  INFILE 'CONTROL_33.dat'  "fix 8"
  INTO TABLE <SCHEMA>.<TABLE>
  FIELDS TERMINATED BY ";"
  TRAILING NULLCOLS
  (
  ....

  • Variable: Cuando la longitud de cada entrada en un campo de caracteres se incorpora al inicio de cada registro en el archivo de datos, se proporciona una mayor flexibilidad en comparación con un tamaño fijo. Este método permite gestionar de manera más eficiente las variaciones en la longitud de los datos, lo que facilita su procesamiento y almacenamiento.

    La especificación del tamaño variable de 8 bytes “INFILE ___ "var 8"“. El valor por defecto es 5 en caso de no especificar y un valor máximo de 40.

      LOAD DATA
  INFILE 'CONTROL_33.dat'  "var 8"
  INTO TABLE <SCHEMA>.<TABLE>
  FIELDS TERMINATED BY ";"
  TRAILING NULLCOLS
  (
  ....

  • De flujo: Cuando no especificamos un tamaño, será el propio SQL*Loader el que formará los registros buscando el record terminator.

  •     LOAD DATA
    INFILE 'CONTROL_33.dat'
    INTO TABLE <SCHEMA>.<TABLE>
    FIELDS TERMINATED BY ";"
    TRAILING NULLCOLS
    (
    ....

Vamos hacer un ejemplo de un flujo de SQL*Loader, definiendo una tabla y un fichero con cierto contenido para inyectar con SQL*Loader. En este caso no voy a especificar el fichero de datos en el archivo de control, sino que lo voy a especificar en la ejecución del comando. ¡Vamos a ello!

Contenido que vamos a insertar en nuestra tabla:

@> ! cat datos.dat
A;B
JOIN;consumidor directo mercado
VIEPR;Productor
ATAL;Productor
BURGE;Productor

Definición de una tabla para guardar el contenido del fichero Será una tabla sencilla de tres columnas, donde una de ellas será incremental.

@> 
 Create Table t_Sqlloader (
   id number generated by default as identity, 
   Info_1 Varchar2(100), 
   Info_2 Varchar2(100) 
 );

Archivo de Control, especificamos que nuestro fichero tiene una fila que no queremos que se vuelque en nuestro tabla porque es de encabezado y queremos que SQL*Loader la ignore (OPTIONS (SKIP=1))

@> ! cat datos.ctl
OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE T_SQLLOADER
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
(
 INFO_1,
 INFO_2
)

Ejecutamos y consultamos la tabla para ver el resultado. En nuestro caso especifico los nombres para el archivo LOG, BAD y DSC. Ninguno de ellos es obligatorio, en caso de no especificarlo, tomaría por defecto el nombre del archivo de datos pero cambiando la extensión.

SQL>  ! 
 sqlldr "*****"/"*****"@***** 
  DATA=datos.dat CONTROL=datos.ctl LOG=FLUJO.log BAD=REGISTROS_KO.bad DISCARD=REGISTROS_ERR.dsc

SQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 18 17:13:14 2025
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Path used:      Conventional
Commit point reached - logical record count 4
Table T_SQLLOADER:
  4 Rows successfully loaded.
Check the log file:
  FLUJO.log
for more information about the load.

SQL> r
  1* Select Info_1, Info_2 From t_SqlLoader

        ID INFO_1     INFO_2
---------- ---------- ----------------------------------------
         1 JOIN       consumidor directo mercado
         2 VIEPR      Productor
         3 ATAL       Productor
         4 BURGE      Productor

Si volvemos a ejecutar la misma instrucción, recibimos el siguiente error:

SQL> !
 sqlldr "*****"/"*****"@***** 
  DATA=datos.dat CONTROL=datos.ctl LOG=FLUJO.log BAD=REGISTROS_KO.bad DISCARD=REGISTROS_ERR.dsc
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 18 17:15:47 2025
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Path used:      Conventional
SQL*Loader-601: For INSERT option, table must be empty.  Error on table T_SQLLOADER

Esto es debido a que la tabla debe estar vacía a no ser que indiquemos que lo que queremos hacer es añadir más filas (APPEND). Vamos a modificar el archivo de control incluyendo esta clausula:

SQL> ! cat datos.ctl
OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE T_SQLLOADER
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
(
 INFO_1,
 INFO_2
)

Ejecutamos de nuevo y vemos el resultado:

SQL>  ! 
 sqlldr "*****"/"*****"@***** 
  DATA=datos.dat CONTROL=datos.ctl LOG=FLUJO.log BAD=REGISTROS_KO.bad DISCARD=REGISTROS_ERR.dsc
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 18 17:22:08 2025
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Path used:      Conventional
Commit point reached - logical record count 4
Table LIQUIDACIONES.T_SQLLOADER:
  4 Rows successfully loaded.
Check the log file:
  FLUJO.log
for more information about the load.

SQL> r
  1* Select Info_1, Info_2 From t_SqlLoader

INFO_1               INFO_2
-------------------- ----------------------------------------
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor

Vamos a complicar un poco más la carga de este fichero para que veamos la multitud de opciones que nos brinda esta herramienta.

Lo primero de todo vamos a limpiar la tabla y vamos a definir una nueva tabla con la misma estructura que la anterior, porque la idea es que el mismo archivo de control grabe en varias tablas.

Ejecutamos la creación de una nueva tabla, con la misma estructura pero con diferente nombre.

@> 
 Create Table t_sqlloader_Productor (
   id number generated by default as identity, 
   Info_1 Varchar2(100), 
   Info_2 Varchar2(100) 
 );

Modificamos el archivo de control para que informe los registros con valor Productor en la tabla T_SQLLOADER_PRODUCTOR y el resto en la tabla T_SQLLOADER.

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE T_SQLLOADER WHEN INFO_2 <> 'Productor'
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 INFO_1 CHAR ,
 INFO_2 CHAR
)
INTO TABLE T_SQLLOADER_PRODUCTOR WHEN INFO_2 = 'Productor'
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 INFO_1 POSITION(1) CHAR ,
 INFO_2 CHAR
)

En este contexto, la clave será el uso de POSITION(1). Al emplear la cláusula WHEN para diferentes bloques, es necesario utilizar POSITION(1) para el primer campo. Si no se especifica, se anticipa que los campos siguientes correspondan a las posiciones de los campos de la cláusula WHEN anterior, lo que resultaría en que los registros que cumplan con la condición INFO_2 = 'Productor' no se insertarían en nuestra tabla. Otra particularidad de la cláusula WHEN es que no admite el operador OR; únicamente se permite el uso de AND.

SQL>  ! 
 sqlldr "*****"/"*****"@*****  
 DATA=datos.dat CONTROL=datos.ctl LOG=datos.log BAD=datos.bad DISCARD=datos.dsc

SQL*Loader: Release 19.0.0.0.0 - Production on Tue Mar 18 20:35:08 2025
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Path used:      Conventional
Commit point reached - logical record count 4
Table T_SQLLOADER:
  1 Row successfully loaded.
Table T_SQLLOADER_PRODUCTOR:
  3 Rows successfully loaded.
Check the log file:
  datos.log
for more information about the load.

SQL>  Select Info_1, Info_2 From t_Sqlloader;

INFO_1               INFO_2
-------------------- ----------------------------------------
JOIN                 consumidor directo mercado

SQL> Select Info_1, Info_2 From t_Sqlloader_Productor;

INFO_1               INFO_2
-------------------- ----------------------------------------
VIEPR                Productor
ATAL                 Productor
BURGE                Productor

Otro manera de utilizar el SQL*Loader es External Table. Una External Tables es una tabla que no reside dentro de la base de datos. Oracle proporciona dos drivers para acceder a ellas: ORACLE_LOADER y ORACLE_DATAPUMP.

Vamos ha definir una tabla externa para el ejemplo anterior.

Lo primero que te tenemos que definir es un directorio.

SQL> Create Directory TMP_SQLLOADER As '/u01/app/oracle/sqlloader';

El fichero de datos lo tenemos, con lo que lo único que tendríamos que definir es la tabla. La sintaxis para crear una tabla es muy similar al o que definimos en un fichero de Control.

SQL> r
CREATE TABLE t_Ext_Sqlloader
(
 Info_1 Varchar2(100), 
 Info_2 Varchar2(100)
 )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY TMP_SQLLOADER
    ACCESS PARAMETERS
    ( 
      RECORDS DELIMITED BY NEWLINE 
      SKIP 1
      CHARACTERSET UTF8
      NOBADFILE
      NODISCARDFILE
      NOLOGFILE
      FIELDS TERMINATED BY ';'
      OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL 
      (
    INFO_1,
        INFO_2
      )
    )
      LOCATION ('datos.dat')
    )
REJECT LIMIT UNLIMITED
Table created.

Y si consultamos la tabla:

SQL> Select * From t_Ext_SqlLoader;
INFO_1               INFO_2
-------------------- ----------------------------------------
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor

También podemos cambiar el fichero fácilmente sin tener que recrear la tabla:

  • external modify ( default directory <DIRECTORY> location('<FILE>'))
SQL> r
  Select * 
    From t_Ext_SqlLoader 
     external modify ( default directory TMP_SQLLOADER location('datos2.dat'))

INFO_1               INFO_2
-------------------- ----------------------------------------
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor
  • external modify ( location(<DIRECTORY>'<FILE>'))
SQL> r
  Select * From t_Ext_SqlLoader external modify ( location(TMP_SQLLOADER:'datos2.dat') )

INFO_1               INFO_2
-------------------- ----------------------------------------
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor
JOIN                 consumidor directo mercado
VIEPR                Productor
ATAL                 Productor
BURGE                Productor

Ambas sentencias no modifican la estructura interna de la tabla como podemos observar:

SQL> 
COLUMN DIRECTORY_NAME FORMAT A20
COLUMN LOCATION FORMAT A20
 Select Directory_Name, Location From Dba_External_Locations Where Table_Name = 'T_EXT_SQLLOADER'

DIRECTORY_NAME       LOCATION
-------------------- --------------------
TMP_SQLLOADER        datos.dat

En los próximos capítulos, continuaremos con el resto de opciones que nos ofrece SQL*Loader.

Espero que os sirva.

2
Subscribe to my newsletter

Read articles from David Sanz directly inside your inbox. Subscribe to the newsletter, and don't miss out.

sqlloaderOCIOraclefiles

Written by

David Sanz
David Sanz

Soy desarrollador, Analista, DBA Oracle y Arquitecto OCI, certificado en OCI Migration and Integration Certified Professional y Certified Architect Associate con más de 15 años de experiencia en plataformas Oracle además de especialista en temas de rendimiento.