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

Đừng để bản thân luôn sống trong sự sợ hãi
Chia sẻ Trong cuộc sống thường nhật, chúng ta có hàng trăm ngàn thứ để sợ, nhưng thực ra không nên gộp tất cả thành một vì không phải nỗi sợ nào cũng không tốt. Nhìn chung, ta có thể phần nào phân loại nỗi sợ theo hai dạng: nỗi sợ bản năng và nỗi sợ tâm lý.
5 bí quyết để tập trung cao độ trong công việc
Chia sẻ Sự tập trung cao độ sẽ là chìa khóa giúp bạn vượt qua những cám dỗ và đạt được các mục tiêu trong công việc. Hiệu suất làm việc tăng đồng nghĩa với việc bạn sẽ có thêm thời gian đầu tư cho việc phát triển sự nghiệp. Tuy nhiên, không phải lúc nào bạn cũng có được sự tập trung cần thiết. Vậy làm cách nào để có thể tập trung tối đa?
Nằm bên người đẹp mà vẫn lạnh
Chia sẻ Một đêm tối trời ở một làng quê hẻo lánh, một khách bộ hành nhỡ độ đường ghé vào một căn nhà xin thuê phòng nghỉ qua đêm. Chủ nhà từ chối vì nhà chỉ có hai phòng, một của hai vợ chồng và một của cô con gái...
Hạnh phúc đến từ đâu
Chia sẻ Vị vua nọ đang đi công du trên một chiếc tàu thì gặp cơn bão lớn. Gió to, sóng dữ gầm thét như muốn quật đổ những cột buồm và nuốt chửng con tàu