Lob dump clob

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 CLOB vers des fichiers
Voir aussi Lob dump blob · Lob dump nclob · Lob fragmentation user

Lob dump clob extrait, ligne par ligne, le contenu d'une colonne de type CLOB vers des fichiers texte 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 CLOB 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 texte (jeu de caractères base) de la famille de scripts d'extraction de LOB : voir Lob dump blob pour le binaire et Lob dump nclob pour le texte Unicode national. La structure est strictement identique, seuls le type de la variable de lecture et la procédure d'écriture changent.

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 CLOB                              |
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 CLOB                    : '
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 CLOB
  v_clob_loc     CLOB;
  v_buffer       VARCHAR2(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_clob_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_clob_loc,
            amount  => v_amount,
            offset  => v_offset,
            buffer  => v_buffer);

        v_offset := v_offset + v_amount;

        UTL_FILE.PUT(file => v_file_handle, buffer => v_buffer);
        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;

Comme pour Lob dump blob, la sortie de boucle s'appuie ici sur l'exception NO_DATA_FOUND levée par DBMS_LOB.READ lorsque l'offset dépasse la fin du LOB — plus fiable que le ORA-06502 capturé dans le script d'origine.

Voir aussi

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