Les Larges Objects (LOB) sous Oracle 9i

Cette article fait le point sur les LOB, les Larges OBjects sous Oracle, vous trouverez des exemples d'utilisation en environnement de production, ainsi que des exemples appliqués au portail WEB.

Article lu fois.

L'auteur

Helyos

L'article

Publié le 23 avril 2004

Version PDF Version hors-ligne

ePub, Azw et Mobi

Liens sociaux

I. Introduction▲

Qu'est-ce qu'un LOB ?

En 1999, SQL définit trois nouveaux types d'objets destinés aux stockages volumineux il s'agit des LOBs (Large OBject). C'est un nouveau type de donnée qui permet de stocker dans un champ un gros volume d'informations. Le type LOB est similaire au type de données LONG et LONG RAW.

Il existe différents types de LOBs. Ceux qui sont stockés dans la base de données (LOB interne) et ceux qui sont stockés en dehors de la base de données (LOB externe). Les LOBs externe sont référencés dans la base par un pointeur.

On distinguera en LOB interne les types de données suivants :

- CLOB (Character Large Object) : Dédié pour les chaînes de caractères.
- BLOB (Binary Large Object): Dédié aux données binaires
- NCLOB (National Character Large Object) : Dédié pour les chaînes de caractères Unicode.

Et en LOB externe

- BFILE (Binary File): Dédié aux fichiers de données stockés dans le système de fichier du système d'exploitation.

Pourquoi LOB et pas LONG ou LONG RAW ?

La taille d'un LONG ne peut excéder 2Go alors que la taille d'un LOB peut monter jusqu'à 4Go.

De plus lorsque vous souhaiterez récupérer une valeur de type LONG dans une variable PL/SQL, vous ne pourrez pas récupérer une valeur ayant une taille supérieure à 32760 Bytes (alors qu'une colonne de type LONG supporte jusqu'à 2Go).

Il n'est pas possible d'avoir une table avec plusieurs colonnes de type LONG ou LONG RAW, pour un LOB il n'existe pas cette limitation.

Il n'existe aucune API dédiée aux LONG permettant de travailler sur des types LONG ou LONG RAW (il existe le package DBMS_SQL qui fournit quelques fonctions pour les LONG) alors qu'il existe le package DBMS_LOB (voir l'article sur le DBMS_LOB de SheikYerbouti) pour gérer tout les types de données LOB.

Il faut savoir que les accès à un LONG se font de manière séquentielle (vous êtes dans l'obligation de lire le LONG du début à la fin) alors que pour les LOBs les accès se font de manière directe (d'où un gain de performances).

Il n'est pas possible de passer une valeur LONG à une fonction SQL, et en PL/SQL une variable LONG sera automatiquement convertie en VARCHAR2 (à moins que la taille du VARCHAR2 ne le permette pas, auquel cas la variable sera convertie en LONG).

Lors d'une requête SELECT l'intégralité du LONG vous est retournée alors que pour un LOB seul le pointeur vous sera retourné.

Maintenant que vous avez pu voir les différences entre ces deux types de données, la question qui normalement vous vient à l'esprit est: « Comment faire pour passer d'un LONG à un LOB ? ». Et bien c'est le point suivant.

Comment convertir un LONG ou un LONG RAW en CLOB ou en BLOB ?

Et bien c'est assez simple pour une fois, car Oracle depuis la version 8.1 (8i) a ajouté une fonction TO_LOB qui vous permettra de convertir très facilement un LONG (ou LONG RAW) vers un CLOB (ou un BLOB).

Par exemple :

Sélectionnez

CREATE TABLE t1(x INT, y LONG);
CREATE TABLE t2(x INT, y CLOB);
INSERT INTO t1 VALUES (1, rpad('*', 4000, '*'));
INSERT INTO t2
SELECT x, to_lob(y) FROM t1;

Il est important de savoir que la fonction TO_LOB ne pourra pas être utilisée en PL/SQL et qu'il faudra l'utiliser à l'intérieur d'un EXECUTE IMMEDIATE par exemple.
Tous les codes que vous trouverez dans cet article ont été testés sur une base de données Oracle 9.2.0.1.0 et avec l'utilisateur SCOTT

II. Les CLOBs▲

II-A. Introduction aux CLOB▲

Le type de données CLOB est un type de données qui va permettre de stocker d'importants volumes de données de type « chaîne de caractères ». La taille maximale d'un CLOB est de 4Go.

Il faut savoir que les CLOB conservent les mêmes règles transactionnelles que les types de données tels que VARCHAR2, NUMBER, etc. et qu'ils peuvent être restaurés sans aucunes actions complémentaires.

II-B. Comment faire pour utiliser un CLOB en SQL ?▲

L'utilisation des CLOB en SQL est très simple.
Voici le code de la table d'exemple :

Sélectionnez

create table t_test (id_clob number, texte clob);

INSERT INTO t_test VALUES (1,'Hello World');
INSERT INTO t_test VALUES (2,rpad('*',32000,'*'));
COMMIT;

SELECT *
FROM t_test;
   ID_CLOB TEXTE
---------- ----------------------------------------------------------------
         1 Hello World
         2 ****************************************************************

L'insertion et la récupération d'un CLOB en SQL ne sont en rien différentes d'une colonne de type standard. Par contre on rencontrera des difficultés à l'affichage qui sont dues à une limitation de SQL. (On ne pourra afficher que les 4000 premiers caractères)

II-C. Comment faire pour utiliser un CLOB en PL/SQL▲

Voici un exemple d'insertion en utilisant la fonction empty_clob() qui permet de remplir la colonne de type CLOB avec un pointeur vers un CLOB vide que l'on remplira après avec la procédure DBMS_LOB.WRITE.

Sélectionnez

CREATE OR REPLACE PROCEDURE insert_test (p_id NUMBER, p_text VARCHAR2)
IS
v_clob CLOB;
BEGIN
-- On insere la ligne avec un CLOB vide
INSERT INTO t_test VALUES (p_id, empty_clob()) returning texte into v_clob;
-- On le rempli avec un contenu
DBMS_LOB.WRITE(v_clob, 1, length(p_text),p_text);
COMMIT;
END;
/

Ou bien de manière plus simple

Sélectionnez

CREATE OR REPLACE PROCEDURE insert_test (p_id NUMBER, p_text CLOB)
IS
BEGIN
INSERT INTO t_test VALUES (p_id, p_text);
COMMIT;
END;
/

II-D. Comment faire pour utiliser un CLOB en HTML ?▲

Avant toute choses nous allons devoir configurer notre passerelle mod_plsql pour pouvoir utiliser nos fonctions et procédure (la méthode est identique pour une base de données 8i ou 9i).

Par défaut quand vous installez Oracle, celui-ci installe un serveur Apache.

La première étape va être de le démarrer pour pouvoir accéder à l'url suivante (page de configuration de la passerelle PL/SQL).

http://127.0.0.1/pls/simpledad/admin_/gateway.htm

Attention : Nous n'aborderons pas ici les problèmes de sécurité, ni la configuration avancée de la passerelle.

Vous arrivez sur la page d'administration suivante

Ensuite cliquez sur Paramètres des DAD de la passerelle.

Puis cliquez sur Ajout d'un descripteur par défaut
Voici les informations à saisir (nous allons utiliser le compte de notre bon vieux Scott).

Nom du descripteur d'accès à la base de données = test

Schema Name = scott

Nom d'utilisateur Oracle = scott

Mot de passe Oracle = tiger

Chaîne de connexion Oracle = <host string>

Puis cliquez sur ok
Nous serons alors maintenant capable d'appeler des procédures PL/SQL (de notre utilisateur scott) au travers de notre browser web favori en utilisant des urls du type

http://127.0.0.1/pls/test/<nom package>.<procedure>

Ou bien

http://127.0.0.1/pls/test/<procedure>

Pour les insertions nous allons créer un formulaire qui appellera une procédure d'insertion de notre CLOB.
Une fois notre ligne créee, cette procédure nous redirigera automatiquement vers une page qui affichera le contenu de notre table.

Sélectionnez

CREATE OR REPLACE PACKAGE html_clob IS

  -- Affichage du contenu de la table
  PROCEDURE display_table_content;

  -- Insertion dans la table
  PROCEDURE add_clob(p_id NUMBER, p_text CLOB);

  -- Affichage du formulaire d'ajout
  PROCEDURE display_add_clob;

END html_clob;
/
CREATE OR REPLACE PACKAGE BODY html_clob IS

  PROCEDURE display_table_content IS
  BEGIN
    htp.print('<html><head></head><body>');
    htp.print('<TABLE BORDER="1" width="100%">');
    htp.print('<TR><TD width="10%">ID</TD><TD width="90%">TEXTE</TD></TR>');
    FOR rec IN (SELECT * FROM t_test ORDER BY id_clob) LOOP
      htp.print('<TR><TD width="10%">' || rec.id_clob ||
              '</TD><TD width="90%">' || rec.texte || '</TD></TR>');
    END LOOP;
    htp.print('</TABLE>');
    htp.print('</body></html>');
  END;

  PROCEDURE add_clob(p_id NUMBER, p_text CLOB) IS
  BEGIN
    INSERT INTO t_test VALUES (p_id, p_text);
    COMMIT;
    owa_util.redirect_url(curl => 'scott.html_clob.display_table_content');
  END;

  PROCEDURE display_add_clob IS
  BEGIN
    htp.print('<html><head></head><body>');
    htp.formopen(curl => 'scott.html_clob.add_clob', cmethod => 'POST');
    htp.print('<TABLE BORDER="1" WIDTH="100%"><TR><TD width="20%">Id</TD><TD>');
    htp.formtext(cname => 'p_id', csize => 10, cattributes => 'style="width: 100%"');
    htp.print('</TD></TR><TR><TD>Texte</TD><TD>');
    htp.formtextareaopen(cname => 'p_text', nrows => 10, ncolumns => 50, cattributes => 'style="width: 100%"');
    htp.formtextareaclose;
    htp.print('</TD></TR><TR><TD colspan="2" align="center">');
    htp.formsubmit;
    htp.print('</TD></TR></TABLE>');
    htp.formclose;
    htp.print('</body></html>');
  END;

END html_clob;
/

Sélectionnez

grant execute on html_clob to public;

Ensuite il vous suffit d'appeler l'url suivante pour ajouter du contenu à la table

http://127.0.0.1/pls/test/scott.html_clob.display_add_clob

Et vous pourrez aussi appeler l'url suivante pour voir le contenu de la table

http://127.0.0.1/pls/test/scott.html_clob.display_table_content

Vous pourrez ainsi voir que contrairement à SQL*Plus la page HTML affiche l'intégralité du contenu de la colonne TEXTE.

III. Les BLOBs▲

III-A. Introduction▲

Le type de données BLOB va permettre de stocker d'important volume de données de type binaire. La taille maximale d'un BLOB ne peut excéder 4Go. Ce type de données nous permettra de stocker n'importe quel type de données dans la base (images, pdf, mp3, doc, etc.).

Il faut savoir que les BLOB conservent les mêmes règles transactionnelles que les types de données tels que VARCHAR2, NUMBER, etc. et qu'ils peuvent être restaurés sans aucunes actions complémentaires.

III-B. Comment faire pour utiliser un BLOB en PL/SQL▲

Il est à noter qu'il n'est pas possible d'ajouter un BLOB à une table avec un ordre INSERT basique, nous serons donc dans l'obligation d'utiliser un bloc PL/SQL.

Dans cet exemple nous allons voir comment stocker dans la base des images (la méthode serait identique pour tout autre type de fichier).

La première étape est de créer un répertoire qui nous servira à placer les images qui devront être uploader. Ce répertoire doit obligatoirement être accessible par le serveur. Celui-ci se servira uniquement de ce répertoire pour accéder à nos images.

On se connecte en tant que DBA pour créer le DIRECTORY et on donnera les droits à notre utilisateur.

Sélectionnez

CONNECT system / < pass > 
CREATE directory blobdir AS 'c:\temp\';
GRANT READ ON directory blobdir TO scott;

On crée une table qui contiendra nos images

Sélectionnez

CREATE TABLE t_blob (id number, image blob);

Ensuite on crée notre package

Sélectionnez

CREATE OR REPLACE PACKAGE sql_blob IS

  -- Procédure servant à ajouter un BLOB à notre table.
  -- p_id correspond à l'id
  -- p_name correspond au nom du fichier à insérer.
  PROCEDURE add_blob(p_id NUMBER, p_name VARCHAR2);

END sql_blob;
/
CREATE OR REPLACE PACKAGE BODY sql_blob IS

  PROCEDURE add_blob(p_id NUMBER, p_name VARCHAR2) IS
    v_blob  BLOB;
    v_bfile BFILE;
  BEGIN
    -- On insère la ligne avec un blob vide dont on récupère le pointeur
    INSERT INTO t_blob
    VALUES
      (p_id, empty_blob())
    RETURNING image INTO v_blob;
    -- On déclare un pointeur vers notre fichier
    v_bfile := bfilename(directory => 'BLOBDIR', filename => p_name);
    -- On ouvre ce fichier
    dbms_lob.fileopen(v_bfile);
    -- On rempli l'emplacement du BLOB vide dans la table avec le contenu de notre fichier
    dbms_lob.loadfromfile(v_blob, v_bfile, dbms_lob.getlength(v_bfile));
    -- On ferme le fichier
    dbms_lob.fileclose(v_bfile);
  END;
END sql_blob;
/

Pour rajouter une image dans notre table il nous suffira de lancer la procédure add_blob de la manière suivante :

Sélectionnez

exec sql_blob.add_blob(1,'helyos.bmp');

Maintenant que nous avons stocké notre image dans la base, comment faire pour régénérer un fichier à partir de notre BLOB ?

Et bien nous allons rajouter une nouvelle procédure à notre package.

Attention: Cette procédure bien que fonctionnant ne donnera pas le résultat escompté car le package UTL_FILE est buggé (il remplace les chaînes hexadécimale 0A par 0D 0A, cette erreur correspond au bug <Bug:2546782> UTL_FILE.PUT_RAW WRITES WRONG NUMBER OF BYTES qui ne sera fixé que en 10G et pas en 9i)

Voici la procédure en elle-même :

Sélectionnez

PROCEDURE write_blob(p_id NUMBER, p_name VARCHAR2) IS
  v_file       utl_file.file_type;
  v_repertoire VARCHAR2(512) := 'c:\temp\';
  v_fichier    VARCHAR2(256) := p_name;
  v_buffer     RAW(32000);
  v_offset     PLS_INTEGER DEFAULT 1;
  v_taille     PLS_INTEGER;
  v_longueur   PLS_INTEGER;
  v_chunk      PLS_INTEGER;
  v_blob       BLOB;
BEGIN
 -- On récupére le BLOB
  SELECT image INTO v_blob FROM t_blob WHERE id = 1;
  -- On l'ouvre en lecture afin de pouvoir le parser plus facilement
  dbms_lob.OPEN(v_blob, dbms_lob.lob_readonly);
  -- On regarde la taille de Chunk idéale
  v_chunk := dbms_lob.getchunksize(v_blob);
  -- On regarde sa longueur
  v_longueur := dbms_lob.getlength(v_blob);
  -- On crée le fichier sur le disque dur
  v_file     := utl_file.fopen(v_repertoire, v_fichier, 'w', 32767);
  -- On ecrit dans le fichier tant que l'on a pas fait tout le BLOB
  WHILE v_offset < v_longueur LOOP
    IF v_longueur - (v_offset - 1) > v_chunk THEN
      v_taille := v_chunk;
    ELSE
      v_taille := v_longueur - (v_offset - 1);
    END IF;
    v_buffer := NULL;
    -- On lit la partie du BLOB qui nous interesse
    dbms_lob.READ(v_blob, v_taille, v_offset, v_buffer);
    -- On ecrit cette partie dans le fichier
    utl_file.put(file => v_file, buffer => utl_raw.cast_to_varchar2(v_buffer));
    utl_file.fflush(file => v_file);
    v_offset := v_offset + v_taille;
  END LOOP;
  -- On ferme le BLOB
  dbms_lob.CLOSE(v_blob);
  -- On ferme le fichier
  utl_file.fclose(v_file);EXCEPTION
  WHEN OTHERS THEN
    IF dbms_lob.ISOPEN(v_blob) = 1 THEN
      dbms_lob.CLOSE(v_blob);
    END IF;
    IF utl_file.is_open(file => v_file) THEN
      utl_file.fclose(file => v_file);
    END IF;
END;

Vous pouvez tester cette procédure mais le fichier résultant aura des caractères en trop. Pour cela Metalink conseille d'utiliser une procédure utilisant du java pour générer des fichiers à partir de BLOB contenu dans la base (How To Unload LOBs to a File Using PL/SQL Java Stored <Note:247546.1>.)

III-C. Comment faire pour utiliser un BLOB en HTML▲

Il est toutefois possible d'afficher un BLOB au travers d'une page web et ce quel que soit la méthode qui ait été utilisée pour l'ajouter à la base de données.

L'Upload et le Download de fichier au travers de MOD_PLSQL vont nécessiter quelques petites modifications de notre DAD. La table de référence devra être différente afin de contenir un certain nombre d'informations supplémentaire.

Sélectionnez

CREATE TABLE t_html_blob (
NAME               VARCHAR(128)   UNIQUE NOT NULL,
  MIME_TYPE          VARCHAR(128),
  DOC_SIZE           NUMBER,
  DAD_CHARSET        VARCHAR(128),
  LAST_UPDATED       DATE,
  CONTENT_TYPE       VARCHAR(128),
  CONTENT            LONG RAW,
  BLOB_CONTENT       BLOB) ;

Voici les différentes étapes à suivre sur une configuration de base.
Ouvrir l'url http://127.0.0.1/pls/simpledad/admin_/gateway.htm

Ensuite cliquez sur Paramètres des DAD de la passerelle

Puis cliquez sur Ajout d'un descripteur par défaut

Voici les informations à saisir

Nom du descripteur d'accès à la base de données = test

Schema Name = scott

Nom d'utilisateur Oracle = scott

Mot de passe Oracle = tiger

Chaîne de connexion Oracle = <host string>

Table de documents = scott.t_html_blob

Chemin d'accès au document = docs

Procédure d'accès au document = scott.html_blob.process_download

Puis cliquez sur ok

Maintenant que nous avons configuré le DAD pour pouvoir Uploader nos documents. Nous allons créer une table de correspondances entre le BLOB et l'id que nous avons saisi.

Sélectionnez

CREATE TABLE t_images (id number, filename varchar2(128));

Il ne vous reste plus qu'a créer le petit package suivant

Sélectionnez

CREATE OR REPLACE PACKAGE html_blob IS

  -- Insertion dans la table des correspondances
  PROCEDURE add_blob(p_id NUMBER, p_name VARCHAR2);

  -- Formulaire d'upload
  PROCEDURE display_add_blob;

END html_blob;
/
CREATE OR REPLACE PACKAGE BODY html_blob IS

  PROCEDURE add_blob(p_id NUMBER, p_name VARCHAR2) IS
  BEGIN
    -- L'image ayant été ajouté par mod_plsql dans notre table t_html_blob nous avons juste à 
    -- enregistrer la correspondance entre notre id et le nouveau nom du fichier dans la base
    INSERT INTO t_images VALUES (p_id, p_name);
    COMMIT;
    htp.print('Votre image à bien été uploadée ');
  END;

  PROCEDURE display_add_blob IS
  BEGIN
    htp.print('<HTML><BODY>');
    -- Il est important de ne pas oublier le cenctype => 'multipart/form-data'
    -- car c'est lui qui demande au DAD d'uploader notre image dans notre table par défaut.
    htp.formopen(curl => 'scott.html_blob.add_blob', cmethod => 'POST', cenctype => 'multipart/form-data');
    htp.print('<TABLE BORDER="1" WIDTH="100%">');
    htp.print('<TR><TD width="20%">Id</TD><TD>');
    htp.formtext(cname => 'p_id', csize => '50', cattributes => 'style="width: 100%"');
    htp.print('</TD></TR>');
    htp.print('<TR><TD width="20%">File</TD><TD>');
    htp.print('<INPUT type="file" name="p_name" style="width: 100%">');
    htp.print('</TD></TR>');
    htp.print('<TR><TD colspan="2" align="center">');
    htp.formsubmit;
    htp.print('</TD></TR></TABLE>');
    htp.formclose;
    htp.print('</BODY></HTML>');
  END;

END html_blob;
/

Une fois ce package crée il ne vous reste plus qu'à appeler l'url
http://127.0.0.1/pls/test/scott.html_blob.display_add_blob

Saisissez alors un id puis le fichier que vous souhaitez Uploader. Cliquez sur submit et le tour est joué.

Il faut savoir qu'il n'y a pas de procédure ou de méthode particulière pour insérer ce BLOB dans notre table t_blob, c'est la passerelle PL/SQL qui va se charger automatiquement de cela, en analysant les caractéristiques du fichier envoyé.

Il est intéressant de voir que la passerelle détecte automatiquement le mime/type du fichier, ce qui rend cette méthode beaucoup plus dynamique vis-à-vis des différents formats de fichiers.

Nous allons voir comment faire pour afficher le contenu de notre BLOB sur une page HTML

Tout d'abord ajoutons la procédure suivante à notre package html_blob.

Sélectionnez

PROCEDURE display_blob(p_id NUMBER) IS
    rec   t_html_blob%ROWTYPE;
    v_amt NUMBER DEFAULT 4096;
    v_off NUMBER DEFAULT 1;
    v_raw RAW(4096);
  BEGIN
  -- On récupere les infos relatives à notre BLOB
    SELECT *
    INTO   rec
    FROM   t_html_blob
    WHERE  NAME = (SELECT filename FROM t_images WHERE id = p_id);
    
    -- On défini le header en fonction de l'objet à afficher
    owa_util.mime_header(rec.mime_type);
    BEGIN
      LOOP
      -- On lit la partie du  BLOB qui nous interesse
        dbms_lob.READ(rec.blob_content, v_amt, v_off, v_raw);
        -- On l'affiche ATTENTION le HTP.PRN est OBLIGATOIRE pour éviter les retours à la ligne intempestif
        htp.prn(utl_raw.cast_to_varchar2(v_raw));
        v_off := v_off + v_amt;
        v_amt := 4096;
      END LOOP;
      dbms_lob.CLOSE(rec.blob_content);
    EXCEPTION
      WHEN no_data_found THEN
        NULL;
    END;
  END;

Pour afficher notre BLOB voici l'url qui faudra lancer.
http://127.0.0.1/pls/test/scott.html_blob.display_blob?p_id=1

Vous ne pourrez pas afficher autre chose sur cette page, car cette page n'est en fait constituée que du contenu de notre BLOB. Si vous voulez utiliser ce BLOB comme une image vous devrez créer une autre page web avec une balise image. <IMG SRC=« http://127.0.0.1/pls/test/scott.html_blob.display_blob?p_id=1 »>

IV. LES BFILES▲

IV-A. Introduction▲

Le type de données BFILE vous permet de stocker des objets de types binaires en dehors de la base de données.

Le type BFILE est en fait un pointeur vers le fichier binaire (ce pointeur contient le path complet vers un fichier système).

Les BFILE sont de types Read-only et ne peuvent donc être modifiés par le serveur. Leur taille, dépendante du système, ne pourra pas dépasser la taille de 2^32-1 Bytes.

L'intégrité des données n'est plus assurée par Oracle mais par le système d'exploitation.

Ne faisant pas partie de la base de données, les BFILEs ne participent pas aux transactions, ne sont pas récupérables sans actions de sauvegarde complémentaire.

Le nombre maximum de BFILES ouverts est déterminé par le paramètre SESSION_MAX_OPEN_FILES (qui lui aussi dépend du système d'exploitation).

IV-B. Comment utiliser des BFILES▲

Comme le serveur a besoin d'accéder physiquement aux fichiers nous allons devoir utiliser un DIRECTORY.

Dans cet exemple nous allons utiliser notre DIRECTORY blobdir.

Nous allons ensuite créer notre table qui nous servira à contenir nos pointeurs de type BFILE.

Sélectionnez

CREATE TABLE t_bfile (id number, filename bfile);

Ensuite voici la commande qui va permettre de rajouter une ligne avec un pointeur vers un fichier toto.txt (contenant hello world).

Sélectionnez

INSERT INTO t_bfile VALUES (1, bfilename('BLOBDIR', 'toto.txt'));
COMMIT;

Nous venons donc d'insérer une nouvelle ligne contenant un pointeur vers notre fichier. Attention : Il n'est pas possible d'afficher le contenu de notre colonne BFILE sous SQL*plus à l'aide d'une commande SELECT. Le seul moyen est d'utiliser une fonction PL/SQL qui nous permettra d'aller lire le contenu du ficher et de nous ramener les 4000 premiers caractères (à cause de la limitation de SQL).

Voici cette fonction

Sélectionnez

CREATE OR REPLACE FUNCTION blob_to_char(p_file IN BFILE) RETURN VARCHAR2 AS
  v_raw   RAW(4000);
  v_bfile BFILE DEFAULT p_file;
BEGIN
  -- On ouvre notre fichier désigné par notre pointeur
  dbms_lob.fileopen(v_bfile);
  -- On récupère les 4000 premiers caractères
  v_raw := dbms_lob.substr(v_bfile, 4000, 1);
  -- On fermer notre fichier
  dbms_lob.fileclose(v_bfile);
  -- On converti notre buffer en VARCHAR2
  RETURN utl_raw.cast_to_varchar2(v_raw);
END;
/

Ensuite nous pourrons donc afficher le contenu de notre fichier grâce à une requête SQL.

Sélectionnez

SQL> SELECT blob_to_char(filename)
  2  FROM t_bfile
  3  WHERE id=1;

BLOB_TO_CHAR(FILENAME)
--------------------------------------------------------------------------

hello world

Cet exemple était basé sur un fichier texte, nous allons maintenant refaire un essai avec une image et nous tenterons de l'afficher sur une page web.

Placez une image dans le répertoire de notre DIRECTORY (dans mon exemple helyos.bmp)

On ajoute le pointeur à notre table

Sélectionnez

INSERT INTO t_bfile VALUES (2, bfilename('BLOBDIR', 'helyos.bmp'));
COMMIT;

Si nous retentons d'afficher le contenu de notre BFILE à l'aide d'une requête nous arrivons à un affichage binaire du contenu de notre image. C'est donc illisible.

IV-C. Comment faire pour afficher le contenu d'un BFILE sur une page web▲

Pour cela nous allons utiliser une petite procédure qui va se charger d'aller lire le contenu du fichier et de l'afficher. Il n'y a que très peut de différence entre cette procédure et la procédure qui affichait le contenu d'un BLOB.

Sélectionnez

CREATE OR REPLACE PROCEDURE display_bfile(p_id NUMBER) IS
  v_amt   NUMBER DEFAULT 4096;
  v_off   NUMBER DEFAULT 1;
  v_raw   RAW(4096);
  v_bfile BFILE;
BEGIN
  -- On récupère le pointeur vers le fichier
  SELECT filename INTO v_bfile FROM t_bfile WHERE id = p_id;
  -- On ouvre le fichier
  dbms_lob.fileopen(v_bfile);
  -- On défini de manière arbitraire un mime/type
  owa_util.mime_header('image/gif');
  -- On affiche le contenu de notre fichier
  BEGIN
    LOOP
      dbms_lob.READ(v_bfile, v_amt, v_off, v_raw);
      htp.print(utl_raw.cast_to_varchar2(v_raw));
      v_off := v_off + v_amt;
      v_amt := 4096;
    END LOOP;
  EXCEPTION
    WHEN no_data_found THEN
      NULL;
  END;
  -- On ferme notre fichier
  dbms_lob.fileclose(v_bfile);
END;
/

Ensuite il suffira d'appeler l'url :
http://127.0.0.1/pls/test/scott.display_bfile?p_id=2 pour afficher le contenu de notre BFILE

V. Annexes▲

V-A. Description des procédures et fonctions utilisées▲

Pour pouvoir consulter ces documents vous devez avoir un compte sur OTN (Oracle Technology Network) valide.
Vous pouvez en créer un gratuitement en suivant ce lien

DBMS_LOB
Un article sur le PL/SQL avec un chapitre sur le DBMS_LOB par SheikYerbouti
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_lob.htm

WRITE
Cette procédure sert à écrire le contenu d'un buffer dans un LOB à un emplacement donné
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_25b.htm#1009075

LOADFROMFILE
Cette procédure sert à lire le contenu d'un BFILE dans un LOB
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_20b.htm#1009007

FILEOPEN
Cette procédure sert à ouvrir un fichier donné.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_13b.htm#1009025

FILECLOSE
Cette procédure sert à fermer un fichier donné.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_lo8.htm#1009038

GETCHUNKSIZE
Cette fonction renvoie l'espace utilisé dans le LOB Chunk pour stocker le LOB
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_15b.htm#1008992

GETLENGTH
Cette fonction renvoie la taille d'un LOB
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_16b.htm#1008995

READ
Cette procédure sert à lire le contenu d'un LOB en partant d'un offset spécifié
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_22b.htm#1009010

ISOPEN
Cette fonction renvoie TRUE si le LOB est bien ouvert actuellement
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_18b.htm#1009004

CLOSE
Cette procédure sert à fermer l'accès à un LOB précédemment ouvert.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_lo3.htm#1009050

OPEN
Cette procédure sert à ouvrir un LOB dans un mode donnée.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_21b.htm#1009016

SUBSTR
Cette fonction permet de renvoyer une partie définie d'un LOB
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_23b.htm#1009072

UTL_FILE
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_file.htm

FOPEN
Cette fonction sert à ouvrir un fichier dans un emplacement donné.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_fil3.htm#1002215

PUT
Cette procédure sert à écrire le contenu d'un buffer dans le fichier ouvert.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_fi11.htm#1001590

FFLUSH
Cette procédure écris physiquement toutes les sorties en attente dans un fichier.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_fi18.htm#1000726

FCLOSE
Cette procédure ferme un fichier donné.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_fil7.htm#1000741

UTL_RAW
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_raw.htm

CAST_TO_VARCHAR2
Cette fonction convertie une variable de type RAW en type VARCHAR2
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/utl_raw7.htm#1000586

BFILENAME
Cette fonction sert à construire un pointeur associé à un fichier physique
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/functions12.htm#SQLRF00610

EMPTY_BLOB
Cette fonction renvoie un pointeur vers un LOB vide qui peut être utilisé pour initialiser une variable ou autre.
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/functions38.htm#SQLRF00636

V-B. Références▲

Tahiti.oracle.com (non pas le lieu de vacances )
Asktom.oracle.com
http://download-west.oracle.com/docs/cd/A97329_03/web.902/a90855/feature.htm#1005870(configuration mod_plsql)
Et retrouvez d'autres informations sur le PL/SQL dans l'article suivant
https://sheikyerbouti.developpez.com/pl_sql/

VI. Remerciements▲

Merci à Pomalaix,SheikYerbouti et Demco pour leurs avis et suggestions de correction ainsi qu'à Gaël pour la mise en forme de mon article.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants :

Copyright © 2004 Helyos. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.