8 Copyright © Oracle Corporation, 2001. Tous droits réservés. Manipuler des objets de grande taille

8Copyright © Oracle Corporation, 2001. Tous droits réservés.

Manipuler des objets de grande taille

8-2 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Objectifs

A la fin de ce chapitre, vous pourrez :

• comparer et opposer les types de données LONG et LOB

• créer et gérer des types de données LOB

• distinguer les LOB internes et externes

• utiliser le package PL/SQL DBMS_LOB

• décrire l'utilisation des LOB temporaires


Définition d'un LOB

Film(BFILE)

Photo(BLOB)

“Four score and seven years ago

our fathers brought forth upon

this continent, a new nation,

conceived in LIBERTY, and dedicated

to the proposition that all men

are created equal.”

Texte(CLOB)

Les LOB permettent de stocker des données volumineuses non structurées, telles que du texte, des images, des films et du son


Comparaison des types de données LONG et LOBComparaison des types de données LONG et LOB

LONG et LONG RAW

Une colonne LONG par table

2 Go maximum

SELECT renvoie des données

Données stockées dans le segment

Accès séquentiel aux données

LOB

Plusieurs colonnes LOB par table

4 Go maximum

SELECT renvoie un pointeur

Données stockées dans le segment

ou à l'extérieur de la table

Accès aléatoire aux données


Composition d'un type LOB

Pointeur de LOB

La colonne LOB conserve un pointeur vers la valeur de LOB

Colonne LOB d'une table

Valeur de LOB


LOB internes

La valeur de LOB est stockée dans la base de données

“Four score and seven years ago

our fathers brought forth upon

this continent, a new nation,

conceived in LIBERTY, and dedicated

to the proposition that all men

are created equal.”

CLOB BLOB


Gérer les LOB internesGérer les LOB internes

• Pour interagir totalement avec les LOB, des interfaces de type fichier sont fournies :

– package PL/SQL DBMS_LOB

– Oracle Call Interface (OCI)

– Oracle Objects pour la liaison et l'incorporation d'objets (OLE)

– précompilateurs Pro*C/C++ et Pro*COBOL

– JDBC

• Le serveur Oracle prend en charge la gestion des LOB via SQL

• Pour interagir totalement avec les LOB, des interfaces de type fichier sont fournies :

– package PL/SQL DBMS_LOB

– Oracle Call Interface (OCI)

– Oracle Objects pour la liaison et l'incorporation d'objets (OLE)

– précompilateurs Pro*C/C++ et Pro*COBOL

– JDBC

• Le serveur Oracle prend en charge la gestion des LOB via SQL


Film(BFILE)

Définition d'un BFILE

Le type de données BFILE prenden charge un objet de grandetaille externe ou basé sur un fichier ; il peut s'agir :

• d'un attribut de type d'objet

• d'une valeur de colonne dans une table


Assurer la sécurité des BFILE

Utilisateur

Film(BFILE)

Droits d'accès


Nouvel objet de base de données : DIRECTORYNouvel objet de base de données : DIRECTORY

DIRECTORY

LOB_PATH = '/oracle/lob/'

Utilisateur

Film(BFILE)


Règles de création des objets DIRECTORYRègles de création des objets DIRECTORY

• Ne pas créer d'objets DIRECTORY dans les chemins d'accès aux fichiers de base de données

• Le nombre d'utilisateurs disposant des privilèges système suivants doit être limité :– CREATE ANY DIRECTORY

– DROP ANY DIRECTORY

• Tous les objets DIRECTORY appartiennent à SYS

• Créer des chemins d'accès aux répertoires et définir correctement les droits avant d'utiliser l'objet DIRECTORY, afin que le serveur Oracle puisse lire le fichier

• Ne pas créer d'objets DIRECTORY dans les chemins d'accès aux fichiers de base de données

• Le nombre d'utilisateurs disposant des privilèges système suivants doit être limité :– CREATE ANY DIRECTORY

– DROP ANY DIRECTORY

• Tous les objets DIRECTORY appartiennent à SYS

• Créer des chemins d'accès aux répertoires et définir correctement les droits avant d'utiliser l'objet DIRECTORY, afin que le serveur Oracle puisse lire le fichier


Gérer les BFILEGérer les BFILE

• Créer un répertoire du système d'exploitation et y ajouter des fichiers

• Créer une table Oracle dont l'une des colonnes contient le type de données BFILE

• Créer un objet DIRECTORY

• Accorder aux utilisateurs les privilèges permettant de lire l'objet DIRECTORY

• Insérer des lignes dans la table à l'aide de la fonction BFILENAME

• Déclarer et initialiser un pointeur de LOB dans un programme

• Lire le BFILE

• Créer un répertoire du système d'exploitation et y ajouter des fichiers

• Créer une table Oracle dont l'une des colonnes contient le type de données BFILE

• Créer un objet DIRECTORY

• Accorder aux utilisateurs les privilèges permettant de lire l'objet DIRECTORY

• Insérer des lignes dans la table à l'aide de la fonction BFILENAME

• Déclarer et initialiser un pointeur de LOB dans un programme

• Lire le BFILE


• Créer ou modifier une table Oracle dont l'une des colonnes contient le type de données BFILE.

• Créer un objet DIRECTORY en utilisant la commande CREATE DIRECTORY.

• Accorder aux utilisateurs les privilèges permettant de lire l'objet DIRECTORY.

• Créer ou modifier une table Oracle dont l'une des colonnes contient le type de données BFILE.

• Créer un objet DIRECTORY en utilisant la commande CREATE DIRECTORY.

• Accorder aux utilisateurs les privilèges permettant de lire l'objet DIRECTORY.

ALTER TABLE employees ADD emp_video BFILE;

Préparer l'utilisation des BFILEPréparer l'utilisation des BFILE

CREATE DIRECTORY dir_name AS os_path;

GRANT READ ON DIRECTORY dir_name TO user|role|PUBLIC;


FUNCTION BFILENAME (directory_alias IN VARCHAR2, filename IN VARCHAR2)RETURN BFILE;

Fonction BFILENAMEFonction BFILENAME

Utiliser la fonction BFILENAME pour initialiser unecolonne BFILEUtiliser la fonction BFILENAME pour initialiser unecolonne BFILE


CREATE OR REPLACE PROCEDURE load_emp_bfile (p_file_loc IN VARCHAR2) IS

v_file BFILE; v_filename VARCHAR2(16); CURSOR emp_cursor IS SELECT first_name FROM employees WHERE department_id = 60 FOR UPDATE;BEGIN FOR emp_record IN emp_cursor LOOP v_filename := emp_record.first_name || '.bmp'; v_file := BFILENAME(p_file_loc, v_filename); DBMS_LOB.FILEOPEN(v_file); UPDATE employees SET emp_video = v_file WHERE CURRENT OF emp_cursor; DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||v_filename || ' SIZE: ' || DBMS_LOB.GETLENGTH(v_file));

DBMS_LOB.FILECLOSE(v_file); END LOOP;END load_emp_bfile;/

Charger des BFILECharger des BFILE


Charger des BFILECharger des BFILE

Utiliser la fonction DBMS_LOB.FILEEXISTS pour vérifierque le fichier est présent dans le système d'exploitation.La fonction renvoie 0 si le fichier n'existe pas et 1 dans le cas contraire.

Utiliser la fonction DBMS_LOB.FILEEXISTS pour vérifierque le fichier est présent dans le système d'exploitation.La fonction renvoie 0 si le fichier n'existe pas et 1 dans le cas contraire.

CREATE OR REPLACE PROCEDURE load_emp_bfile (p_file_loc IN VARCHAR2)IS v_file BFILE; v_filename VARCHAR2(16); v_file_exists BOOLEAN; CURSOR emp_cursor IS ...BEGIN FOR emp_record IN emp_cursor LOOP v_filename := emp_record.first_name || '.bmp'; v_file := BFILENAME (p_file_loc, v_filename); v_file_exists := (DBMS_LOB.FILEEXISTS(v_file) = 1); IF v_file_exists THEN DBMS_LOB.FILEOPEN (v_file); ...


Migrer de LONG vers LOBMigrer de LONG vers LOB

Le serveur Oracle9i permet la migration des colonnes LONG vers les colonnes LOB

• La migration des données consiste à déplacer les tables existantes contenant des colonnes LONG pour permettre l'utilisation des LOB

• La migration des applications consiste à modifier les applications LONG existantes pour permettre d'utilisation des LOB

Le serveur Oracle9i permet la migration des colonnes LONG vers les colonnes LOB

• La migration des données consiste à déplacer les tables existantes contenant des colonnes LONG pour permettre l'utilisation des LOB

• La migration des applications consiste à modifier les applications LONG existantes pour permettre d'utilisation des LOB

ALTER TABLE [<schema>.] <table_name>MODIFY (<long_col_name> {CLOB | BLOB | NCLOB}


Migrer de LONG vers LOBMigrer de LONG vers LOB

• Conversion implicite : LONG (LONG RAW) ou variable VARCHAR2(RAW) convertie en variable CLOB (BLOB), et vice-versa

• Conversion explicite :– O_CLOB() convertit LONG, VARCHAR2 et CHAR en CLOB

– TO_BLOB() convertit LONG RAW et RAW en BLOB

• Transmission de paramètres de procédure et de fonction– CLOB et BLOB en tant que paramètres réels

– VARCHAR2, LONG, RAW et LONG RAW sous forme de paramètres formels, et vice-versa

• Les données LOB sont compatibles avec la plupart des opérateurs et fonctions intégrées SQL et PL/SQL

• Conversion implicite : LONG (LONG RAW) ou variable VARCHAR2(RAW) convertie en variable CLOB (BLOB), et vice-versa

• Conversion explicite :– O_CLOB() convertit LONG, VARCHAR2 et CHAR en CLOB

– TO_BLOB() convertit LONG RAW et RAW en BLOB

• Transmission de paramètres de procédure et de fonction– CLOB et BLOB en tant que paramètres réels

– VARCHAR2, LONG, RAW et LONG RAW sous forme de paramètres formels, et vice-versa

• Les données LOB sont compatibles avec la plupart des opérateurs et fonctions intégrées SQL et PL/SQL


Package Package DBMS_LOBPackage Package DBMS_LOB

• L'utilisation de LOB nécessite souvent le recours au package DBMS_LOB fourni par Oracle

• Ce package fournit des sous-programmes permettant d'accéder à des LOB internes et externes et de les manipuler

• Oracle9i permet d'extraire directement des données de LOB à l'aide du langage SQL, sans utiliser d'API de LOB particulière

• En PL/SQL, vous pouvez définir VARCHAR2 pour un CLOB et RAW pour un BLOB

• L'utilisation de LOB nécessite souvent le recours au package DBMS_LOB fourni par Oracle

• Ce package fournit des sous-programmes permettant d'accéder à des LOB internes et externes et de les manipuler

• Oracle9i permet d'extraire directement des données de LOB à l'aide du langage SQL, sans utiliser d'API de LOB particulière

• En PL/SQL, vous pouvez définir VARCHAR2 pour un CLOB et RAW pour un BLOB


• Pour modifier les valeurs de LOB :

APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE

• Pour lire ou consulter les valeurs de LOB :

GETLENGTH, INSTR, READ, SUBSTR

• Fonctions et procédures propres aux BFILE :

FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN, FILEOPEN

• Pour modifier les valeurs de LOB :

APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE

• Pour lire ou consulter les valeurs de LOB :

GETLENGTH, INSTR, READ, SUBSTR

• Fonctions et procédures propres aux BFILE :

FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN, FILEOPEN

Package DBMS_LOBPackage DBMS_LOB


Package DBMS_LOBPackage DBMS_LOB

• Les paramètres NULL renvoient des valeurs NULL

• Positions :– BLOB, BFILE : calculées en octets

– CLOB, NCLOB : calculées en caractères

• Il n'existe aucune valeur négative pour les paramètres

• Les paramètres NULL renvoient des valeurs NULL

• Positions :– BLOB, BFILE : calculées en octets

– CLOB, NCLOB : calculées en caractères

• Il n'existe aucune valeur négative pour les paramètres


DBMS_LOB.READ et DBMS_LOB.WRITEDBMS_LOB.READ et DBMS_LOB.WRITE

PROCEDURE READ (

lobsrc IN BFILE|BLOB|CLOB ,

amount IN OUT BINARY_INTEGER,

offset IN INTEGER,

buffer OUT RAW|VARCHAR2 )

PROCEDURE WRITE (

lobdst IN OUT BLOB|CLOB,

amount IN OUT BINARY_INTEGER,

offset IN INTEGER := 1,

buffer IN RAW|VARCHAR2 ) -- RAW for BLOB


Ajouter des colonnes LOB à une tableAjouter des colonnes LOB à une table

ALTER TABLE employees ADD (resume CLOB, picture BLOB);


Insérer une ligne dans une table possédant des colonnes LOB :Insérer une ligne dans une table possédant des colonnes LOB :

Initialiser une colonne LOB à l'aide de la fonction EMPTY_BLOB() :

Renseigner les colonnes LOBRenseigner les colonnes LOB

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id,

salary, resume, picture)VALUES (405, 'Marvin', 'Ellis', 'MELLIS', SYSDATE,

'AD_ASST', 4000, EMPTY_CLOB(),NULL);

UPDATE employeesSET resume = 'Date of Birth: 8 February 1951', picture = EMPTY_BLOB()WHERE employee_id = 405;


Colonne UPDATE CLOB

Mettre à jour un LOB à l'aide de SQLMettre à jour un LOB à l'aide de SQL

UPDATE employeesSET resume = 'Date of Birth: 1 June 1956'WHERE employee_id = 170;


Mettre à jour un LOB à l'aide de DBMS_LOB en PL/SQL

Mettre à jour un LOB à l'aide de DBMS_LOB en PL/SQL

DECLARE lobloc CLOB; -- serves as the LOB locator text VARCHAR2(32767):='Resigned: 5 August 2000'; amount NUMBER ; -- amount to be written offset INTEGER; -- where to start writingBEGIN SELECT resume INTO lobloc FROM employees WHERE employee_id = 405 FOR UPDATE; offset := DBMS_LOB.GETLENGTH(lobloc) + 2; amount := length(text); DBMS_LOB.WRITE (lobloc, amount, offset, text ); text := ' Resigned: 30 September 2000'; SELECT resume INTO lobloc FROM employees WHERE employee_id = 170 FOR UPDATE; amount := length(text); DBMS_LOB.WRITEAPPEND(lobloc, amount, text); COMMIT;END;


Sélectionner des valeurs de CLOB à l'aide de SQL

Sélectionner des valeurs de CLOB à l'aide de SQL

SELECT employee_id, last_name , resume -- CLOBFROM employeesWHERE employee_id IN (405, 170);


Sélectionner des valeurs de CLOB à l'aide de DBMS_LOB

Sélectionner des valeurs de CLOB à l'aide de DBMS_LOB

• DBMS_LOB.SUBSTR(lob_column, no_of_chars, starting)

• DBMS_LOB.INSTR (lob_column, pattern)

• DBMS_LOB.SUBSTR(lob_column, no_of_chars, starting)

• DBMS_LOB.INSTR (lob_column, pattern)

SELECT DBMS_LOB.SUBSTR (resume, 5, 18), DBMS_LOB.INSTR (resume,' = ')FROM employeesWHERE employee_id IN (170, 405);


Sélectionner les valeurs de CLOB en PL/SQL

DECLARE text VARCHAR2(4001);BEGIN SELECT resume INTO text FROM employees WHERE employee_id = 170; DBMS_OUTPUT.PUT_LINE('text is: '|| text);END;/


Supprimer une ligne contenant des LOB :

Dissocier une valeur de LOB d'une ligne :

Supprimer une ligne contenant des LOB :

Dissocier une valeur de LOB d'une ligne :

Supprimer les LOBSupprimer les LOB

DELETE FROM employeesWHERE employee_id = 405;

UPDATE employeesSET resume = EMPTY_CLOB()WHERE employee_id = 170;


LOB temporairesLOB temporaires

• Les LOB temporaires :

– fournissent une interface pour gérer la création de LOB fonctionnant comme des variables locales

– peuvent être de type BLOB, CLOB ou NCLOB

– ne sont pas associés à une table précise

– sont créés à l'aide de la procédure DBMS_LOB.CREATETEMPORARY

– utilisent des sous-programmes DBMS_LOB

• La durée de vie d'un LOB temporaire correspond à une session

• Les LOB temporaires permettent de transformer les données des LOB internes permanents

• Les LOB temporaires :

– fournissent une interface pour gérer la création de LOB fonctionnant comme des variables locales

– peuvent être de type BLOB, CLOB ou NCLOB

– ne sont pas associés à une table précise

– sont créés à l'aide de la procédure DBMS_LOB.CREATETEMPORARY

– utilisent des sous-programmes DBMS_LOB

• La durée de vie d'un LOB temporaire correspond à une session

• Les LOB temporaires permettent de transformer les données des LOB internes permanents


Procédure PL/SQL de création et de test d'un LOB temporaire :

Créer un LOB temporaireCréer un LOB temporaire

CREATE OR REPLACE PROCEDURE IsTempLOBOpen (p_lob_loc IN OUT BLOB, p_retval OUT INTEGER)

ISBEGIN -- create a temporary LOB DBMS_LOB.CREATETEMPORARY (p_lob_loc, TRUE); -- see if the LOB is open: returns 1 if open p_retval := DBMS_LOB.ISOPEN (p_lob_loc); DBMS_OUTPUT.PUT_LINE ('The file returned a value

....' || p_retval); -- free the temporary LOB DBMS_LOB.FREETEMPORARY (p_lob_loc);END;/


SynthèseSynthèse

Ce chapitre vous a permis d'apprendre à :

• identifier quatre types intégrés pour les objets de grande taille : BLOB, CLOB, NCLOB et BFILE

• décrire la manière dont LOB remplace LONG et LONG RAW

• décrire les deux options de stockage des LOB :

– serveur Oracle (LOB internes)

– fichiers hôte externes (LOB externes)

• utiliser le package PL/SQL DBMS_LOB pour fournir des sous-programmes de gestion des LOB

• utiliser les LOB temporaires dans une session

Ce chapitre vous a permis d'apprendre à :

• identifier quatre types intégrés pour les objets de grande taille : BLOB, CLOB, NCLOB et BFILE

• décrire la manière dont LOB remplace LONG et LONG RAW

• décrire les deux options de stockage des LOB :

– serveur Oracle (LOB internes)

– fichiers hôte externes (LOB externes)

• utiliser le package PL/SQL DBMS_LOB pour fournir des sous-programmes de gestion des LOB

• utiliser les LOB temporaires dans une session


Présentation de l'exercice 8Présentation de l'exercice 8

Dans cet exercice, vous allez :

• créer des types d'objet à l'aide des nouveaux types de données CLOB et BLOB

• créer une table dont les types de données LOB sont des colonnes

• utiliser le package DBMS_LOB pour renseigner les données de LOB et interagir avec elles

Dans cet exercice, vous allez :

• créer des types d'objet à l'aide des nouveaux types de données CLOB et BLOB

• créer une table dont les types de données LOB sont des colonnes

• utiliser le package DBMS_LOB pour renseigner les données de LOB et interagir avec elles

Documents

8 Copyright © Oracle Corporation, 2001. Tous droits réservés. Manipuler des objets de grande taille