Sử dụng iểu dữ liệu LOB (CLOB, BLOB, NCLOB, BFILE) trong Oracle Database
Trong Oracle có các kiểu dữ liệu như CLOB, BLOB, BFILE, ... hỗ trợ người dùng có thể lưu trữ các file văn bản, hình ảnh, âm thanh, v.v... vào trong Oracle Database. Dưới đây tôi có các ví dụ hướng dẫn các bạn sử dụng package DBMS_LOB thực hiện việc lưu file vào table và ngược lại đọc dữ liệu từ table ra file.

 

Trước tiên tôi xin giới thiệu các kiểu dữ liệu LOB (large object) trong Oracle. LOB là kiểu dữ liệu dùng để lưu những dữ liệu lớn không có cấu trúc như file text, hình, phim ảnh, v.v... Có 4 kiểu dữ liệu LOB là:

    -  BLOB: dùng cho dữ liệu lớn ở dạng nhị phân (binary large object)
    -  CLOB: dùng cho dữ liệu lớn ở dạng text (character large object)
    -  NCLOB: tương tự như CLOB nhưng với ký tự >1byte (multibyte character)
    -  BFILE: dùng cho các file dạng nhị phân (binary file) trong hệ thống được nhóm thành Internal LOB (CLOB, BLOB, NCLOB) tức được lưu vào trong database và External files (BFILE) lưu bên ngoài database.

Thành phần của một LOB gồm:
    -  LOB value: dữ liệu cấu thành nên đối tượng được lưu trữ (nếu tạo một Internal LOB thì dữ liệu được lưu trong một LOB segment trong database)
    -  LOB locator: con trỏ chỉ đến vị trí của LOB value

DIRECTORY object:
Để attach file vào trong Oracle database, trước tiên bạn phải copy file lên Oracle server và tạo đối tượng DIRECTORY để Oracle nhận biết thư mục file của bạn. DIRECTORY object thường do DBA tạo hoặc những user có quyền CREATE ANY DIRECTORY, rồi cấp quyền cho các user khác được đọc trên thư mục này.
   1. connect as sysdba
      vd: connect sys/oracle as sysdba
   2. tạo DIRECTORY object
      syntax: CREATE DIRECTORY dir_name AS os_path
       vd: create directory files_dir as 'E:/oravn/files/';
   3. cấp quyền cho user
      vd: grant read on directory files_dir to HR, SCOTT;

External LOB: (BFILE)
   - tạo bảng với cột mang kiểu BFILE
      vd: create table bfile_table(id number(1), bfile_col bfile);
         alter table employees add(emp_image bfile);
   - sử dụng hàm BFILENAME để insert dữ liệu
      vd: insert into bfile_table values(1,bfilename('files_dir','writes.txt'));
         insert into bfile_table values(2, null);
         update employees set emp_image=bfilename('attfiles','king.bmp') where employee_id=100;

Hàm dưới đây thực hiện việc update lại cột emp_image trong bảng employees với file hình được đặt tên theo ID của từng người.

CREATE OR REPLACE PROCEDURE load_bfile IS
   v_file BFILE;
   v_filename VARCHAR2(20);
   v_file_exist BOOLEAN;
   CURSOR emp_cursor IS
          SELECT employee_id FROM employees FOR UPDATE;
BEGIN
   FOR emp_record IN emp_cursor LOOP
      v_filename := emp_record.employee_id || ‘.bmp’;
      v_file := BFILENAME (‘FILE_DIR’, v_filename); -- FILES_DIR là tên một DIRECTORY object, các bạn có thể khai báo truyền thông số cho thủ tục (bắt buộc là chữ hoa)
      v_file_exist := (DBMS_LOB.FILEEXISTS(v_file) = 1);
      IF v_file_exist THEN -- kiểm tra file trước khi load
         DBMS_LOB.FILEOPEN (v_file);
         UPDATE employees SET emp_image = 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 IF;
   END LOOP;
END load_bfile;
/

Internal LOBs:
   - tạo bảng:
      vd: create table lobs_table(id number(1), blob_col BLOB, clob_col CLOB);
          alter table employees add(blob_col BLOB, clob_col CLOB);
   - insert dữ liệu:
      vd: insert into lobs_table values(1,empty_blob(), empty_clob());

Dữ liệu cho column BLOB và CLOB có thể được update từ nội dung của một file hoặc từ column khác kiểu BFILE và ghi ngược ra file.

/** thủ tục này sẽ load dữ liệu từ external lob (BFILE) vào internal lob (BLOB) */

CREATE OR REPLACE PROCEDURE bfile_to_blob IS
   v_blob    blob;
   v_bfile   bfile;
   CURSOR emp_cursor IS
SELECT employee_id, emp_image, blob_col FROM employees FOR UPDATE;
BEGIN
   FOR emp_record IN emp_cursor LOOP
      v_bfile := emp_record.emp_image;
      v_blob := emp_record.blob_col;
      DBMS_LOB.FILEOPEN(v_bfile);
      DBMS_LOB.LOADFROMFILE (v_blob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
      DBMS_LOB.FILECLOSE(v_bfile);


      UPDATE employees SET blob_col = v_blob
      WHERE CURRENT OF emp_cursor;
   END LOOP;
END;
/

/** thủ tục này đọc dữ liệu từ file text rồi gán vào cho cột kiểu CLOB */

CREATE OR REPLACE PROCEDURE file_to_clob
     (p_emp_id IN number, p_dir IN varchar2, p_file IN varchar2) AS
   v_buffer raw(32760);
   v_handler UTL_FILE.FILE_TYPE;
   v_length number;
   v_exists boolean;
   v_block binary_integer; -- block size
BEGIN
   v_handler := UTL_FILE.FOPEN(p_dir, p_file, 'r', 32760);
   UTL_FILE.FGETATTR(p_dir, p_file, v_exists, v_length, v_block);
   IF (v_exists) THEN
      UTL_FILE.GET_RAW(v_handler, v_buffer, 32760);
      UPDATE employees SET clob_col = UTL_RAW.CAST_TO_VARCHAR2(v_buffer)
      WHERE employee_id = p_emp_id;
   END IF;
   UTL_FILE.FCLOSE(v_handler);
END;
/

/** thủ tục này đọc dữ liệu từ internal lob ghi ra file */

CREATE OR REPLACE PROCEDURE clob_to_file
     (p_emp_id IN number, p_dir IN varchar2, p_file IN varchar2) AS
   v_output UTL_FILE.FILE_TYPE;
   v_amt number := 32000;
   v_offset number := 1;
   v_length number;
   v_clob CLOB;
BEGIN
   select clob_col into v_clob from employees where employee_id = p_emp_id;
   v_length := nvl(DBMS_LOB.GETLENGTH(v_clob),0);
   v_output := UTL_FILE.FOPEN(p_dir, p_file, 'w', 32760);
   WHILE (v_offset < v_length )
   LOOP
      UTL_FILE.PUT(v_output, DBMS_LOB.SUBSTR(v_clob, v_amt, v_offset));
      UTL_FILE.FFLUSH(v_output);
      v_offset := v_offset + v_amt;
   END LOOP;
   UTL_FILE.NEW_LINE(v_output);
   UTL_FILE.FCLOSE(v_output);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     null;
END;
/

*** Lưu ý ***
Khi sử dụng package UTL_FILE thì thông số UTL_FILE_DIR phải được set trước trong init hoặc spfile, và đường dẫn thư mục truyền cho các thủ tục READ và WRITE phải bằng giá trị của thông số này.

Bài viết cùng danh mục

Hà Nội: Phòng dịch Covid, phong tỏa tòa chung cư trên phố Ngụy Như Kon Tum
Chia sẻ ANTD.VN - Ngày 9-5, Công an phường Nhân Chính, quận Thanh Xuân, cho biết đơn vị đã phối hợp với các lực lượng chức năng tổ chức lập rào chắn cách ly phong tỏa tòa nhà The Legacy, ngay sau khi xác định 1 trường hợp dương tính Covid sống tại tòa nhà này.
Hà Nội thông báo tìm người liên quan đến các điểm nguy cơ cao COVID-19
Chia sẻ Theo Sở Y tế Hà Nội, tính đến 17 giờ ngày 9/5, trên địa bàn thành phố Hà Nội đã có 38 ca dương tính với SARS-CoV-2 lây nhiễm ngoài cộng đồng.
Đà Nẵng: Ngày đầu người dân đi chợ bằng thẻ để phòng chống dịch Covid-19
Chia sẻ Ngày 9-5, ghi nhận tại các chợ dân sinh tại TP Đà Nẵng như chợ Cồn, chợ đầu mối Hòa Cường, chợ Đống Đa, Chợ Nại Hiên Đông,… đã thực hiện kiểm soát việc ra vào chợ bằng thẻ đi chợ. Hầu hết người dân chấp hành nghiêm túc chủ trương đi chợ theo hình thức phát phiếu theo ngày chẵn, lẻ được Sở Công thương và các địa phương phối hợp thực hiện.
Ngải cứu có hoạt chất chống virus COVID-19 rất mạnh
Chia sẻ Thảo dược trị sốt cao có tiềm năng chống COVID-19? Công trình nghiên cứu của các nhà khoa học bang Massgachusetts, Hoa Kỳ về tác động của thảo dược tới virus.
Ăn ngay 5 loại quả này để tăng đề kháng phòng chống dịch bệnh
Chia sẻ Ăn một chế độ dinh dưỡng phù hợp sẽ góp phần quan trọng nâng cao sức đề kháng, giảm nguy cơ mắc các bệnh truyền nhiễm và các biến chứng của bệnh lý nền có sẵn.