Lob dump blob

De wiki.nexiat.fr
Aller à la navigation Aller à la recherche
Fiche express
Type Script SQL*Plus / PL*SQL de diagnostic
Rôle Extraction du contenu d'une colonne BLOB vers des fichiers
Voir aussi Lob dump clob · Lob dump nclob · Lob fragmentation user

Lob dump blob extrait, ligne par ligne, le contenu d'une colonne de type BLOB vers des fichiers sur disque. Le script interroge le propriétaire, la table, la colonne et une clause WHERE optionnelle (pour cibler une ligne précise), puis écrit chaque valeur BLOB rencontrée dans un fichier nommé OWNER_TABLE_COLONNE_<n>.out (un fichier par ligne retournée), dans le répertoire indiqué.

C'est la variante binaire de la famille de scripts d'extraction de LOB : voir Lob dump clob et Lob dump nclob pour les types texte, qui partagent exactement la même structure à l'exception du type de LOB manipulé et du mode d'écriture (binaire vs caractère).

Script

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
COLUMN current_user     NEW_VALUE current_user     NOPRINT
SELECT RPAD(instance_name, 17) current_instance, RPAD(USER, 13) current_user FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Rapport   : Extraction d'une colonne BLOB                              |
PROMPT | Instance  : &current_instance                                         |
PROMPT | User      : &current_user                                             |
PROMPT +------------------------------------------------------------------------+

SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 180 PAGESIZE 50000 SERVEROUTPUT ON TIMING OFF VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES

ACCEPT oname   PROMPT 'Nom du propriétaire (owner)               : '
ACCEPT tname   PROMPT 'Nom de la table                           : '
ACCEPT cname   PROMPT 'Nom de la colonne BLOB                    : '
ACCEPT wclause PROMPT 'Clause WHERE (avec le mot-clé WHERE)      : '
ACCEPT odir    PROMPT 'Répertoire de sortie (chemin OS)          : '

CREATE OR REPLACE DIRECTORY temp_dump_lob_dir AS '&odir';

DECLARE

  -- Paramètres saisis
  v_oname    VARCHAR2(100)  := UPPER('&oname');
  v_tname    VARCHAR2(100)  := UPPER('&tname');
  v_cname    VARCHAR2(100)  := UPPER('&cname');
  v_outdir   VARCHAR2(30)   := 'TEMP_DUMP_LOB_DIR';
  v_wclause  VARCHAR2(4000) := '&wclause';

  -- Fichiers de sortie
  v_out_filename       VARCHAR2(500) := v_oname || '_' || v_tname || '_' || v_cname;
  v_out_fileext        VARCHAR2(4)   := '.out';
  v_out_filename_full  VARCHAR2(500);
  v_out_dirname        VARCHAR2(2000);
  v_file_count         NUMBER        := 0;
  v_file_handle        UTL_FILE.FILE_TYPE;

  -- Curseur dynamique
  TYPE v_lob_cur_typ IS REF CURSOR;
  v_lob_cur     v_lob_cur_typ;
  v_sql_string  VARCHAR2(4000);

  -- Lecture du BLOB
  v_blob_loc     BLOB;
  v_buffer       RAW(32767);
  v_buffer_size  CONSTANT BINARY_INTEGER := 32767;
  v_amount       BINARY_INTEGER;
  v_offset       NUMBER(38);

  invalid_directory_path EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_directory_path, -29280);
  table_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
  invalid_identifier EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_identifier, -904);
  sql_cmd_not_prop_ended EXCEPTION;
  PRAGMA EXCEPTION_INIT(sql_cmd_not_prop_ended, -933);

BEGIN

  DBMS_OUTPUT.ENABLE(1000000);

  SELECT directory_path INTO v_out_dirname
  FROM   all_directories
  WHERE  directory_name = 'TEMP_DUMP_LOB_DIR';

  v_sql_string := 'SELECT ' || v_cname || ' FROM ' || v_oname || '.' || v_tname || ' ' || v_wclause;

  OPEN v_lob_cur FOR v_sql_string;

  LOOP
    FETCH v_lob_cur INTO v_blob_loc;
    EXIT WHEN v_lob_cur%NOTFOUND;

    v_file_count := v_file_count + 1;
    v_out_filename_full := v_out_filename || '_' || v_file_count || v_out_fileext;
    v_file_handle := UTL_FILE.FOPEN(v_outdir, v_out_filename_full, 'w', 32767);

    v_amount := v_buffer_size;
    v_offset := 1;

    BEGIN
      WHILE v_amount >= v_buffer_size LOOP
        DBMS_LOB.READ(
            lob_loc => v_blob_loc,
            amount  => v_amount,
            offset  => v_offset,
            buffer  => v_buffer);

        v_offset := v_offset + v_amount;

        UTL_FILE.PUT_RAW(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
        UTL_FILE.FFLUSH(file => v_file_handle);
      END LOOP;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- Fin normale de lecture (dernier bloc partiel)
        NULL;
      WHEN OTHERS THEN
        UTL_FILE.PUT_LINE(v_file_handle, '*** ERREUR lors de la lecture ***');
        UTL_FILE.PUT_LINE(v_file_handle, 'Objet : ' || v_oname || '.' || v_tname || '.' || v_cname);
        UTL_FILE.PUT_LINE(v_file_handle, 'SQL   : ' || v_sql_string);
        UTL_FILE.PUT_LINE(v_file_handle, 'Code  : ' || SQLCODE || ' - ' || SQLERRM);
        UTL_FILE.FFLUSH(v_file_handle);
    END;

    UTL_FILE.FCLOSE(v_file_handle);
  END LOOP;

  CLOSE v_lob_cur;

  DBMS_OUTPUT.PUT_LINE(v_file_count || ' fichier(s) écrit(s) dans ' || v_out_dirname || '.');

EXCEPTION
  WHEN invalid_directory_path THEN
    DBMS_OUTPUT.PUT_LINE('** ERREUR ** Répertoire OS invalide : ' || v_outdir);
  WHEN table_does_not_exist THEN
    DBMS_OUTPUT.PUT_LINE('** ERREUR ** Table introuvable. SQL: ' || v_sql_string);
  WHEN invalid_identifier THEN
    DBMS_OUTPUT.PUT_LINE('** ERREUR ** Identifiant invalide. SQL: ' || v_sql_string);
  WHEN sql_cmd_not_prop_ended THEN
    DBMS_OUTPUT.PUT_LINE('** ERREUR ** Commande SQL mal terminée. SQL: ' || v_sql_string);
END;
/

DROP DIRECTORY temp_dump_lob_dir;

Le script original gérait la fin de lecture par une exception ORA-06502 (« invalid LOB locator », erreur de troncature de buffer) déclenchée par DBMS_LOB.READ à la fin du LOB. C'est un usage détourné d'une exception qui n'est pas garanti par la documentation Oracle selon les versions ; ici, la sortie de boucle normale via NO_DATA_FOUND — remontée par DBMS_LOB.READ quand il ne reste plus rien à lire au-delà de l'offset courant — est plus fiable.

L'objet DIRECTORY Oracle doit pointer vers un chemin OS accessible en écriture par le compte système exécutant l'instance ; il est créé puis supprimé dans le script pour éviter de laisser un répertoire orphelin en cas d'exécutions répétées avec des chemins différents.

Voir aussi

Récupérée de "https://wiki.nexiat.fr/index.php?title=Lob_dump_blob&oldid=2238"