Example lob demonstration

De wiki.nexiat.fr
Aller à la navigation Aller à la recherche
Fiche express
Type Exemple DDL/PL-SQL Oracle
Contexte Manipulation des types LOB (CLOB, BLOB, BFILE) via SQL et DBMS_LOB
Voir aussi Example create table · Example create tablespace

Tour d'horizon pratique des LOB (Large OBjects) Oracle — CLOB (texte), BLOB (binaire) et BFILE (référence vers un fichier externe au système de fichiers, en lecture seule côté Oracle) — et du package DBMS_LOB qui permet de les manipuler par programmation, au-delà de ce que le SQL standard permet.

Ce qu'il faut retenir sur les LOB

  • Une colonne LOB stocke en réalité un localisateur (locator), pas directement la
 valeur : le SELECT ramène ce pointeur, et Oracle va chercher la donnée
 correspondante en arrière-plan.
  • SQL*Plus ne peut pas afficher une colonne BLOB ou BFILE
 directement (SELECT * FROM … échoue avec « Column or attribute type can not be
 displayed by SQL*Plus ») — il faut sélectionner explicitement les colonnes affichables et
 formater les colonnes CLOB (COLUMN … FORMAT a60 WRAP).
  • Une petite chaîne de caractères peut être insérée directement dans une colonne LOB
 (jusqu'à 4 Ko environ) : Oracle gère la conversion en localisateur en arrière-plan. Pour du
 binaire, la chaîne doit être hexadécimale, ou passer par
 UTL_RAW.CAST_TO_RAW('...').
  • Les localisateurs ne survivent pas à un COMMIT avant Oracle8i : il faut les
 reséletionner après validation. À partir de 8i, un localisateur en lecture seule peut
 franchir les limites de transaction.
  • Pour écrire via DBMS_LOB, la ligne doit être verrouillée
 (SELECT ... FOR UPDATE) — une simple sélection ne suffit pas.
  • LOB temporaires (8i+, DBMS_LOB.CREATETEMPORARY) : localisateurs pointant
 vers le tablespace temporaire de la session, sans génération de redo/undo, existant pour la
 durée de l'appel ou de la session ; à libérer explicitement
 (DBMS_LOB.FREETEMPORARY) une fois l'usage terminé.
  • Ouverture/fermeture explicite (8i+, DBMS_LOB.OPEN/CLOSE) :
 regroupe les écritures pour éviter de déclencher les triggers sur index étendus à chaque
 écriture individuelle ; un LOB ouvert en lecture seule refuse toute écriture tant qu'il n'est
 pas refermé (ORA-22294).

Script

/* --------------------------------------------------------
 * Préparation : table de test avec un CLOB, un BFILE et un
 * BLOB. Les LOB de moins de ~4 Ko peuvent rester "en ligne"
 * dans la ligne (ENABLE STORAGE IN ROW) ; les plus gros sont
 * stockés hors ligne (DISABLE STORAGE IN ROW).
 * -------------------------------------------------------- */

DROP TABLE test_lobs;
DROP DIRECTORY tmp_dir;
DROP TABLE long_data;

CREATE TABLE test_lobs (
    c1 NUMBER
  , c2 CLOB
  , c3 BFILE
  , c4 BLOB
)
LOB (c2) STORE AS (ENABLE STORAGE IN ROW)
LOB (c4) STORE AS (DISABLE STORAGE IN ROW)
/

-- Ligne sans localisateur initialisé
INSERT INTO test_lobs VALUES (1, NULL, NULL, NULL);

-- Localisateurs créés mais pointant "dans le vide" (EMPTY_CLOB/EMPTY_BLOB)
INSERT INTO test_lobs VALUES (2, EMPTY_CLOB(), BFILENAME(NULL, NULL), EMPTY_BLOB());

-- Insertion directe de données courtes. '48656C6C6F' est du hexadécimal
-- ('Hello' en ASCII) ; UTL_RAW.CAST_TO_RAW convertit une chaîne de
-- caractères en RAW pour compléter le BLOB.
INSERT INTO test_lobs
  VALUES (3, 'Some data for record 3.', BFILENAME(NULL, NULL),
             '48656C6C6F' || UTL_RAW.CAST_TO_RAW(' there!'));

-- SQL*Plus ne peut afficher c3 (BFILE) ni c4 (BLOB) : ne sélectionner que c1/c2
COLUMN c2 FORMAT a60 WRAP
SELECT c1, c2 FROM test_lobs;

/* --------------------------------------------------------
 * Depuis un bloc PL/SQL, on peut insérer une variable
 * caractère dans un CLOB, mais pas la sélectionner directement
 * dans une variable caractère si elle dépasse la taille du
 * type cible (ici VARCHAR2(10), volontairement trop petit
 * pour illustrer la limite).
 * -------------------------------------------------------- */

DECLARE
  c_lob VARCHAR2(10);
BEGIN
  c_lob := 'Record 4.';
  INSERT INTO test_lobs VALUES (4, c_lob, BFILENAME(NULL, NULL), EMPTY_BLOB());
END;
/

/* --------------------------------------------------------
 * Depuis 8.1, TO_LOB migre des données LONG/LONG RAW vers
 * CLOB/BLOB, dans un INSERT...SELECT ou un CREATE TABLE AS SELECT.
 * -------------------------------------------------------- */

CREATE TABLE long_data (
    c1 NUMBER
  , c2 LONG
)
/

INSERT INTO long_data VALUES (1, 'This is some long data to be migrated to a CLOB');

INSERT INTO test_lobs
  SELECT 5, TO_LOB(c2), NULL, NULL
  FROM   long_data
/

SELECT c1, c2 FROM test_lobs WHERE c1 = 5;

ROLLBACK;

/* --------------------------------------------------------
 * BFILE : référence en lecture seule vers un fichier du système
 * de fichiers du serveur, via un alias DIRECTORY. Les fichiers
 * doivent être créés côté OS au préalable (hors SQL*Plus, via
 * un shell ou un outil externe) puis associés par BFILENAME.
 * -------------------------------------------------------- */

CREATE DIRECTORY tmp_dir AS '/tmp'
/

UPDATE test_lobs SET c3 = BFILENAME('TMP_DIR', 'rec2.txt') WHERE c1 = 2;
UPDATE test_lobs SET c3 = BFILENAME('TMP_DIR', 'rec3.txt') WHERE c1 = 3;

COMMIT;

-- Longueur des LOB (0 pour les localisateurs "vides")
COLUMN len_c2 FORMAT 9999
COLUMN len_c3 FORMAT 9999
COLUMN len_c4 FORMAT 9999

SELECT c1
     , DBMS_LOB.GETLENGTH(c2) len_c2
     , DBMS_LOB.GETLENGTH(c3) len_c3
     , DBMS_LOB.GETLENGTH(c4) len_c4
FROM   test_lobs
/

/* --------------------------------------------------------
 * SUBSTR/INSTR sur LOB : paramètres inversés par rapport aux
 * fonctions SQL standard (LOB, longueur, offset pour SUBSTR ;
 * LOB, motif, offset, occurrence pour INSTR). Pour un BFILE le
 * fichier doit d'abord être ouvert -> utilisable en PL/SQL
 * uniquement dans ce cas.
 * -------------------------------------------------------- */

COLUMN sub_c2 FORMAT a10
COLUMN ins_c4 FORMAT 99

SELECT c1
     , DBMS_LOB.SUBSTR(c2, 9, 3) sub_c2
     , DBMS_LOB.INSTR(c4, UTL_RAW.CAST_TO_RAW('ello'), 1, 1) ins_c4
FROM   test_lobs
/

/* --------------------------------------------------------
 * Écriture/lecture pas à pas avec DBMS_LOB : WRITE, READ,
 * WRITEAPPEND, APPEND, TRIM, ERASE, COPY, plus lecture d'un
 * BFILE vers un CLOB via LOADFROMFILE.
 * -------------------------------------------------------- */

SET SERVEROUTPUT ON
SET LONG 1000

DECLARE
  b_lob  BLOB;
  c_lob  CLOB;
  c_lob2 CLOB;
  bf     BFILE;
  buf    VARCHAR2(100) :=
      'This is some text to put into a CLOB column in the' ||
      CHR(10) || 'database. The data spans 2 lines.';
  n      NUMBER;
  fn     VARCHAR2(50);
  fd     VARCHAR2(50);

  -- Affiche le contenu d'un CLOB ligne par ligne (découpe sur CHR(10))
  PROCEDURE print_clob IS
    offset  NUMBER;
    len     NUMBER;
    o_buf   VARCHAR2(200);
    amount  NUMBER;
    f_amt   NUMBER := 0;
    f_amt2  NUMBER;
    amt2    NUMBER := -1;
  BEGIN
    len := DBMS_LOB.GETLENGTH(c_lob);
    offset := 1;
    WHILE len > 0 LOOP
      amount := DBMS_LOB.INSTR(c_lob, CHR(10), offset, 1);
      IF amount = 0 THEN
        amount := len;
        amt2 := amount;
      ELSE
        f_amt2 := amount;
        amount := amount - f_amt;
        f_amt  := f_amt2;
        amt2   := amount - 1;
      END IF;

      IF amt2 != 0 THEN
        DBMS_LOB.READ(c_lob, amt2, offset, o_buf);
        DBMS_OUTPUT.PUT_LINE(o_buf);
      END IF;

      len    := len - amount;
      offset := offset + amount;
    END LOOP;
  END;

BEGIN
  -- Initialise les localisateurs de la ligne 1 (créés vides à la volée) et
  -- verrouille la ligne via la clause RETURNING de l'UPDATE
  UPDATE test_lobs SET c2 = EMPTY_CLOB(), c4 = EMPTY_BLOB()
    WHERE c1 = 1 RETURNING c2, c4 INTO c_lob, b_lob;

  SELECT c2 INTO c_lob2 FROM test_lobs WHERE c1 = 3;

  DBMS_LOB.WRITE(c_lob, LENGTH(buf), 1, buf);
  print_clob;

  COMMIT;

  -- Après COMMIT, le localisateur en écriture doit être resélectionné
  -- avec verrou (FOR UPDATE)
  SELECT c2 INTO c_lob FROM test_lobs WHERE c1 = 1 FOR UPDATE;

  DBMS_LOB.WRITEAPPEND(c_lob, 1, CHR(10));
  DBMS_LOB.APPEND(c_lob, c_lob2);
  DBMS_OUTPUT.PUT_LINE(CHR(10));
  print_clob;

  -- Compare la fin de c_lob avec c_lob2 : si identique, la retire (TRIM
  -- prend la taille FINALE souhaitée, pas la quantité à retirer)
  n := DBMS_LOB.GETLENGTH(c_lob) - DBMS_LOB.GETLENGTH(c_lob2);
  IF DBMS_LOB.COMPARE(c_lob, c_lob2, DBMS_LOB.GETLENGTH(c_lob2), n + 1, 1) = 0 THEN
    DBMS_LOB.TRIM(c_lob, n - 1);
  END IF;
  DBMS_OUTPUT.PUT_LINE(CHR(10));
  print_clob;

  -- ERASE remet les octets à zéro SANS raccourcir la longueur logique
  -- (contrairement à TRIM)
  n := DBMS_LOB.GETLENGTH(c_lob);
  DBMS_LOB.ERASE(c_lob, n, 1);

  DBMS_LOB.COPY(c_lob, c_lob2, DBMS_LOB.GETLENGTH(c_lob2), 1, 1);
  n := DBMS_LOB.GETLENGTH(c_lob2) + 1;
  DBMS_LOB.WRITE(c_lob, 1, n, CHR(10));

  -- Ajoute des données lues depuis le BFILE de la ligne 3
  SELECT c3 INTO bf FROM test_lobs WHERE c1 = 3;

  DBMS_LOB.FILEGETNAME(bf, fd, fn);
  DBMS_OUTPUT.PUT_LINE(CHR(10));
  DBMS_OUTPUT.PUT_LINE('Appending data from file ' || fn ||
                        ' in directory aliased by ' || fd || ':');
  DBMS_OUTPUT.PUT_LINE(CHR(10));

  IF DBMS_LOB.FILEEXISTS(bf) = 1 AND DBMS_LOB.FILEISOPEN(bf) = 0 THEN
    DBMS_LOB.FILEOPEN(bf);
  END IF;

  DBMS_LOB.LOADFROMFILE(c_lob, bf, DBMS_LOB.GETLENGTH(bf), n + 1, 1);
  DBMS_LOB.FILECLOSE(bf);
  print_clob;

  COMMIT;
END;
/

COMMIT;
SELECT c1, c2 FROM test_lobs;

/* --------------------------------------------------------
 * Isolation en lecture : un localisateur donne une image
 * cohérente au moment où il a été sélectionné. Les changements
 * faits via SQL direct (pas via ce même localisateur) ne sont
 * pas visibles tant qu'on ne resélectionne pas.
 * -------------------------------------------------------- */

DECLARE
  c_lob CLOB;
BEGIN
  SELECT c2 INTO c_lob FROM test_lobs WHERE c1 = 1;

  DBMS_OUTPUT.PUT_LINE('Before update length of c2 is ' ||
                        DBMS_LOB.GETLENGTH(c_lob));

  UPDATE test_lobs SET c2 = 'This is a string.' WHERE c1 = 1;

  DBMS_OUTPUT.PUT_LINE('After update length of c2 is ' ||
                        DBMS_LOB.GETLENGTH(c_lob));

  SELECT c2 INTO c_lob FROM test_lobs WHERE c1 = 1;

  DBMS_OUTPUT.PUT_LINE('After reselecting locator length of c2 is ' ||
                        DBMS_LOB.GETLENGTH(c_lob));

  ROLLBACK;
END;
/

COMMIT;

/* --------------------------------------------------------
 * LOB temporaires (8i+) : localisateurs dans le tablespace
 * temporaire, sans undo/redo, à libérer explicitement. Exemple :
 * inverser une chaîne CLOB via un LOB temporaire puis insérer
 * le résultat comme nouvelle ligne.
 * -------------------------------------------------------- */

DECLARE
  c_lob  CLOB;
  t_lob  CLOB;
  buf    VARCHAR2(32000);
  buf2   VARCHAR2(32000);
  chunk  NUMBER;
  len    NUMBER;
  offset NUMBER;
  amount NUMBER;
BEGIN
  SELECT c2 INTO c_lob FROM test_lobs WHERE c1 = 1;

  -- LOB temporaire, sans cache, de durée "call" (limitée à l'appel courant)
  DBMS_LOB.CREATETEMPORARY(t_lob, FALSE, DBMS_LOB.CALL);

  -- GETCHUNKSIZE renvoie la taille de bloc de stockage du LOB : aligner
  -- lectures/écritures sur cette taille améliore les performances
  chunk := DBMS_LOB.GETCHUNKSIZE(c_lob);
  DBMS_OUTPUT.PUT_LINE('Chunksize of column c2 is ' || chunk);
  DBMS_OUTPUT.PUT_LINE('Chunksize of temporary LOB is ' ||
                        DBMS_LOB.GETCHUNKSIZE(t_lob));

  len := DBMS_LOB.GETLENGTH(c_lob);
  offset := 1;
  buf := NULL;

  WHILE offset < len LOOP
    IF len - (offset - 1) > chunk THEN
      amount := chunk;
    ELSE
      amount := len - (offset - 1);
    END IF;
    buf2 := NULL;
    DBMS_LOB.READ(c_lob, amount, offset, buf2);
    buf := buf || buf2;
    offset := offset + amount;
  END LOOP;

  buf2 := NULL;
  FOR i IN REVERSE 1..len LOOP
    buf2 := buf2 || SUBSTR(buf, i, 1);
  END LOOP;

  DBMS_LOB.WRITEAPPEND(t_lob, len, buf2);

  -- Insertion en passant directement le localisateur temporaire en bind
  INSERT INTO test_lobs VALUES (5, t_lob, NULL, NULL) RETURNING c2 INTO c_lob;

  IF DBMS_LOB.ISTEMPORARY(t_lob) = 1 THEN
    DBMS_LOB.FREETEMPORARY(t_lob);
  END IF;

  DBMS_OUTPUT.PUT_LINE('Length of CLOB inserted into record 5 is ' ||
                        DBMS_LOB.GETLENGTH(c_lob));
  COMMIT;
END;
/

SELECT c1, c2 FROM test_lobs WHERE c1 = 5;

/* --------------------------------------------------------
 * Ouverture/fermeture explicite (8i+) : un LOB ouvert en lecture
 * seule (LOB_READONLY) refuse toute écriture jusqu'à sa fermeture
 * (ORA-22294) ; pour écrire, il faut le sélectionner FOR UPDATE
 * puis l'ouvrir en LOB_READWRITE.
 * -------------------------------------------------------- */

DECLARE
  c_lob1 CLOB;
  c_lob2 CLOB;
BEGIN
  SELECT c2 INTO c_lob1 FROM test_lobs WHERE c1 = 2;
  c_lob2 := c_lob1;

  DBMS_LOB.OPEN(c_lob1, DBMS_LOB.LOB_READONLY);

  BEGIN
    DBMS_LOB.WRITEAPPEND(c_lob1, 5, 'Hello');  -- lève ORA-22294
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;

  -- COMMIT/ROLLBACK sont autorisés (aucune transaction ouverte) ; le LOB
  -- reste ouvert après
  ROLLBACK;

  IF DBMS_LOB.ISOPEN(c_lob2) = 1 THEN
    DBMS_OUTPUT.PUT_LINE('Closing LOB via locator 2');
    DBMS_LOB.CLOSE(c_lob2);
  END IF;

  IF DBMS_LOB.ISOPEN(c_lob1) = 1 THEN
    DBMS_OUTPUT.PUT_LINE('Closing LOB via locator 1');
    DBMS_LOB.CLOSE(c_lob1);
  END IF;

  -- Pour écrire, il faut verrouiller la ligne puis ouvrir en lecture/écriture
  SELECT c2 INTO c_lob1 FROM test_lobs WHERE c1 = 2 FOR UPDATE;

  DBMS_LOB.OPEN(c_lob1, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.WRITEAPPEND(c_lob1, 5, 'Hello');
  DBMS_LOB.WRITEAPPEND(c_lob1, 7, ' there.');

  -- Le LOB doit être refermé avant COMMIT/ROLLBACK
  DBMS_LOB.CLOSE(c_lob1);

  COMMIT;
END;
/

SELECT c2 FROM test_lobs WHERE c1 = 2;

Les créations de fichiers texte via l'échappement shell SQL*Plus (!echo "..." > /tmp/rec2.txt) ont été retirées du script : elles dépendent de l'OS hôte et sortent du périmètre SQL — à recréer manuellement côté serveur si l'exemple est rejoué (deux fichiers texte, référencés ensuite par BFILENAME('TMP_DIR', 'rec2.txt') et 'rec3.txt').

Voir aussi

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