DBMS_LOB

功能描述

DBMS_LOB包提供了用于处理BLOB（二进制数据类型）、CLOB（字符数据类型）和BFILE数据类型（存储数据库外的大型对象，指向操作系统物理文件）的子程序。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。
NCLOB 是 CLOB 数据类型在Oracle兼容模式中的别名。因此，本文中的 CLOB 数据类型均可使用 NCLOB 数据类型代替。

支持范围

常量

表1 DBMS_LOB支持的常量

常量	值
LOBMAXSIZE	1073741832
DEFAULT_CSID	0
DEFAULT_LANG_CTX	0

LOBMAXSIZE

示例

1、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、打印常量lobmaxsize的值。

declare 
begin
  dbms_output.put_line(dbms_lob.lobmaxsize);
end;
/

返回结果为：

1073741823
ANONYMOUS BLOCK EXECUTE

DEFAULT_CSID

示例

1、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、打印常量default_csid的值。

declare 
begin
  dbms_output.put_line(dbms_lob.default_csid);
end;
/

返回结果为：

0
ANONYMOUS BLOCK EXECUTE

DEFAULT_LANG_CTX

示例

1、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、打印常量default_lang_ctx的值。

declare 
begin
  dbms_output.put_line(dbms_lob.default_lang_ctx);
end;
/

返回结果为：

0
ANONYMOUS BLOCK EXECUTE

函数

表2 DBMS_LOB支持的函数

函数	描述
GETLENGTH	获取BLOB、CLOB和BFILE数据类型的长度。
FILEOPEN	打开一个BFILE文件。
READ	从一个BFILE文件中读取指定长度的内容。
FILECLOSE	关闭一个BFILE文件。
SUBSTR	该函数用于返回 LOB 中从指定位置开始的部分内容，从指定的偏移量开始。
INSTR	该函数用来返回LOB中指定模式第n次出现的匹配位置，从指定的偏移量开始。
WRITE	从BOLB（CLOB）开头的指定偏移量offset开始，将指定数量amount的数据写入BLOB（CLOB）中，数据从缓冲区写入。如果从BLOB（CLOB）指定偏移量开始有数据存在，WRITE函数会从偏移位置开始覆盖指定长度amount的数据。
APPEND	将源BLOB（CLOB）数据追加到目标BLOB（CLOB）数据后。
ERASE	用于擦除整个或部分的内部LOB。
FILEEXISTS	检查服务器是否存在该文件。
LOADFROMFILE	加载BFILE类型的数据到内部LOB中。
LOADBLOBFROMFILE	从BFILE对象加载数据到BLOB对象。
CLOSE	关闭一个之前打开的LOB或BFILE对象。

GETLENGTH

语法格式

BLOB对象类型：由于存储的内容即为二进制数据，直接返回二进制内容的字节数即可，函数原型为：

DBMS_LOB.GETLENGTH (
   lob_loc    IN  BLOB) 
  RETURN INTEGER;

CLOB对象类型：该对象存储的数据格式为字符数据，返回值为字符数。函数原型为：

DBMS_LOB.GETLENGTH (
lob_loc    IN  CLOB) 
RETURN INTEGER;

BFILE对象类型：函数返回bfile数据指向的操作系统物理文件的长度，以字节为单位，bfile文件的长度包含了文件结尾符（eof）。函数原型为：

DBMS_LOB.GETLENGTH (
 file_loc   IN  BFILE) 
 RETURN INT8;

参数说明

file_loc：将要返回其长度的文件位置。

示例1：

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建测试表并插入数据。

create table testclob (data clob);
insert into testclob values('中文测试');

3、查询数据长度。

select dbms_lob. getlength (data) from testclob;

返回结果为：

getlength
-----------
         4
(1 row)

示例2：

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建测试表并插入数据。

create table testblob (data blob);
insert into testblob values(utl_raw.cast_to_raw('中文测试'));

3、查询数据长度。

select dbms_lob. getlength (data) from testblob;

返回结果为：

getlength
-----------
        12
(1 row)

FILEOPEN

语法格式

DBMS_LOB.FILEOPEN (
file_loc   IN OUT BFILE, 
open_mode  IN   BINARY_INTEGER );

参数说明

file_loc

要打开的文件定位。
open_mode

文件访问模式。

示例

1、在操作系统环境下创建文件，输入数据并保存。

vi /home/vastbase/bfile.data

文件内容为：

张三
李四

2、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

3、在数据库中执行如下命令创建目录和表。

create directory d_bfile as '/home/vastbase';
create table testbfile1(id number,bfile_name bfile);

4、调用bfilename函数构造bfile数据并插入到表中。

insert into testbfile1 values(1,bfilename('d_bfile', 'bfile.data'));

5、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。

set serveroutput on;

6、创建自定义函数：

create or replace function f_read_bfile() returns void as $$
declare
buff raw;
amount int := 0 ;
offset int :=0;
lob_loc bfile;
filesize int;
begin
select bfile_name into lob_loc from testbfile1 where id=1;
--打开BFILE文件
dbms_lob.fileopen(lob_loc,0);
--获取文件大小
filesize := dbms_lob.getlength(lob_loc);
raise notice 'amount:%',amount;
--读取文件全部内容
amount = filesize;
dbms_lob.read(lob_loc,amount,0,buff);
dbms_output.put_line('file data:');
dbms_output.put_line(utl_raw.cast_to_varchar2(buff));
--关闭bfile文件
dbms_lob.fileclose(lob_loc);
return;
end;
$$ language plpgsql;

7、调用自定义函数。

select f_read_bfile();

返回结果为：

file data:
张三
李四

 f_read_bfile
--------------

(1 row)

READ

语法格式

DBMS_LOB.READ (
   file_loc   IN             BFILE,
   amount    IN OUT   INTEGER,
   offset    IN              INTEGER,
   buffer    OUT             RAW);

参数说明

file_loc

lob要检查的文件。
amount

读取文件的长度。为了防止打开的文件过大，单次读取的内容超过机器缓冲区导致系统崩溃，规定单次读取的字节数不能大于32767字节，即amount为大于0小于等于32767的正整数。
offset

用于多次读取文件时设置读取的起始位置，如果offset参数指向bfile文件结尾或超出文件结尾，则将amount参数设置为0，并抛出异常。
buffer

读取操作的输出缓冲区。

示例

参见dbms_lob.fileopen的示例。

FILECLOSE

语法格式

DBMS_LOB.FILECLOSE (
    file_loc IN OUT BFILE);

参数说明

file_loc：要关闭的文件。

示例

参见dbms_lob.fileopen的示例。

SUBSTR

语法格式

DBMS_LOB.SUBSTR (
   lob_loc    IN   BLOB,
   amount     IN   INTEGER := 32767,
   offset     IN   INTEGER := 1)
  RETURN RAW;
  
DBMS_LOB.SUBSTR (
   lob_loc    IN   CLOB   CHARACTER SET ANY_CS,
   amount     IN   INTEGER := 32767,
   offset     IN   INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
  
DBMS_LOB.SUBSTR (
   file_loc   IN   BFILE,
   amount     IN   INTEGER := 32767,
   offset     IN   INTEGER := 1)
  RETURN RAW;

参数说明

lob_loc

LOB的locator。
file_loc

FILE的locator。
amount

要读取的BLOB的字节数或CLOB的字符数。默认值为32767。
offset

LOB从1开始的字节数（BLOB）或字符数（CLOB）偏移量。默认值为1。

返回值

传入BLOB或BFILE类型时返回RAW类型。
传入CLOB类型时返回VARCHAR2类型。
当任意传入值为NULL，或amount<1、amount>32767、offset<1、offset>LOBMAXSIZE时，返回NULL。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、直接调用SUBSTR函数。

select dbms_lob.substr('ABCD'::clob);
select dbms_lob.substr('ABCD');
select dbms_lob.substr('this is lob function test',1,5) as a1;
select dbms_lob.substr('this is lob function test',length('this is lob function test'),1) as a2;

返回结果为：

substr
--------
 ABCD
(1 row)

substr
--------
 ABCD
(1 row)

 a1
----

(1 row)

            a2
---------------------------
 this is lob function test
(1 row)

INSTR

语法格式

DBMS_LOB.INSTR (
   lob_loc    IN   BLOB,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;
  
DBMS_LOB.INSTR (
   lob_loc    IN   CLOB      CHARACTER SET ANY_CS,
   pattern    IN   VARCHAR2  CHARACTER SET lob_loc%CHARSET,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;
  
DBMS_LOB.INSTR (
   file_loc   IN   BFILE,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

参数说明

lob_loc

待查找的LOB对象。
file_loc

待查找的BFILE类型LOB对象。
pattern

以字节为单位或以字符为单位的模式，用于匹配大对象中的内容。如果lob_loc代表BLOB类型大对象或file_loc代表BFILE类型大对象，那么模式必须是RAW类型。如果lob_loc代表CLOB类型大对象，那么模式必须是VARCHAR2类型。
offset

参数lob_loc或file_loc代表的大对象中搜索模式时的开始位置。第一个字节或字符是位置1。默认值为1。
nth

由给定的偏移量指定起始位置，开始搜索指定模式第n次出现时的位置。默认值为1。

返回值

匹配模式的起始偏移量，以字节或字符为单位。如果找不到模式，则返回0。

以下几种情况下返回NULL：

任何一个或多个IN参数为NULL或无效。
offset < 1 或者 offset > LOBMAXSIZE。
nth < 1 或者 nth > LOBMAXSIZE

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、直接调用instr函数。

select dbms_lob.instr('blob test'::clob,'test',1,1);

返回结果为：

instr
-------
     6
(1 row)

WRITE

语法格式

DBMS_LOB.WRITE(
    lob_loc IN OUT NOCOPY BLOB,
    amount IN INTEGER,
    offset IN INTEGER,
    buffer IN RAW);

DBMS_LOB.WRITE(
    lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
    amount IN INTEGER,
    offset IN INTEGER,
    buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);

参数说明

lob_loc

LOB 的locator。
amount

要写入的BLOB字节数或者CLOB的字符数。
offset

写操作中从BLOB开头的字节数偏移量，或者CLOB开头的字符数偏移量，起始处下标为1。
buffer

写入操作的输入缓冲区。

返回值

传入BLOB时返回RAW类型。
传入CLOB时返回TEXT类型。

使用DBMS_LOB.WRITE函数时，以下情况将出现报错： - 传入lob_loc、amount、offset参数的任何一个为NULL，超出范围或无效时； - 传入amount<1、amount>32767、offset<1、offset>LOBMAXSIZE时。

示例1： 入参lob_loc为BLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.WRITE函数，根据指定偏移量对输入的BLOB内容进行覆盖。

declare
b1 blob :=utl_raw.cast_to_raw('测试ABC123');
amount INTEGER :=3;
off_set INTEGER :=4;
b2 blob :=utl_raw.cast_to_raw('abc');
BEGIN
dbms_lob.write(b1,amount,off_set,b2);
dbms_output.put_line(utl_raw.cast_to_varchar2(b1));
end;
/

返回结果如下：

测abcABC123
ANONYMOUS BLOCK EXECUTE

示例2： 入参lob_loc为CLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.WRITE函数，根据指定偏移量对输入的CLOB内容进行覆盖。

declare
b1 clob :='测试ABC123';
amount INTEGER :=3;
off_set INTEGER :=4;
b2 clob :='abc';
BEGIN
dbms_lob.write(b1,amount,off_set,b2);
dbms_output.put_line(b1);
end;
/

返回结果如下：

测试Aabc23
ANONYMOUS BLOCK EXECUTE

APPEND

语法格式

DBMS_LOB.APPEND(
    dest_lob IN OUT NOCOPY BLOB,
    src_lob IN BLOB);

DBMS_LOB.APPEND(
    dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
    src_lob IN CLOB CHARACTER SET dest lob%CHARSET);

参数说明

dest_lob

要向其添加数据的目标BLOB（CLOB）的locator。
src_lob

要从中读取数据的源BLOB（CLOB）的locator。

返回值

传入BLOB时返回RAW类型。
传入CLOB时返回TEXT类型。

传入的dest_lob或者src_lob为空时，报错。

示例1： 入参lob_loc为BLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.APPEND函数，将源BLOB追加到目标BLOB后。

declare
b1 blob :=utl_raw.cast_to_raw('测试ABC123');
b2 blob :=utl_raw.cast_to_raw('abc');
BEGIN
dbms_lob.append(b1,b2);
dbms_output.put_line(utl_raw.cast_to_varchar2(b1));
end;
/

返回结果如下：

测试ABC123abc
ANONYMOUS BLOCK EXECUTE

示例2： 入参lob_loc为CLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.APPEND函数，将源CLOB追加到目标CLOB后。

declare
b1 clob :='测试ABC123';
amount INTEGER :=3;
off_set INTEGER :=4;
b2 clob :='abc';
BEGIN
dbms_lob.write(b1,amount,off_set,b2);
dbms_output.put_line(b1);
end;
/

返回结果如下：

测试Aabc23
ANONYMOUS BLOCK EXECUTE

ERASE

功能描述

在LOB对象指针中，从offset开始及后续的amount个字符置为空格字符，即擦除内容。

语法格式

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   BLOB,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   CLOB CHARACTER SET ANY_CS,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

参数说明

lob_loc

要删除的LOB对象指针。
amount

要擦除的字节数（BLOB）或字符数（CLOB）。
offset

擦除点距离LOB开头的绝对偏移量（初始值为1），以字节（BLOB）或字符（CLOB）为单位。

使用说明

擦除LOB的中间数据时，分别为BLOB或CLOB写入零字节填充符或空格。
如果在擦除指定的数字之前已到LOB值的末尾，则擦除的实际字节数或字符数可以与amount参数中指定的数字不同。实际删除的字符数或字节数将在amount参数中返回。

只擦除LOB的部分数据时，LOB的长度不会减小。

异常情况

异常	描述
VALUE_ERROR	任何输入参数均为NULL。
INVALID_ARGVAL	发生以下某种情况: offset 小于 1 或者 offset 大于 LOBMAXSIZE。 amount 小于 1 或者 amount 大于 LOBMAXSIZE
QUERY_WRITE	无法在查询中执行LOB写入。
BUFFERING_ENABLED	如果在LOB上启用了缓冲，则无法在启用LOB缓冲的情况下执行操作。

示例

1、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。

SET SERVEROUTPUT ON;

2、执行如下匿名块并调用，其中调用了DBMS_LOB.erase子程序，擦除内容。

declare
l_c clob := 'Hello World!';
l_a number := 9;
begin
dbms_lob.erase( l_c, l_a, 6 );
dbms_output.put_line( 'The clob now = *' || l_c || '*' );
dbms_output.put_line( 'The amount that was erased was: ' || l_a );
end;
/

返回结果为如下：

The clob now = *Hello       *
The amount that was erased was: 7
ANONYMOUS BLOCK EXECUTE

FILEEXISTS

语法格式

DBMS_LOB.FILEEXISTS (
   file_loc     IN    BFILE)
  RETURN INTEGER;

程序使用的宏扩展：

pragma restrict_references(FILEEXISTS, WNDS, RNDS, WNPS, RNPS);

参数说明

file_loc

BFILE类型对象。

返回值

0：物理文件不存在。
1：物理文件存在。

异常情况

异常	描述
NOEXIST_DIRECTORY	目录不存在。
NOPRIV_DIRECTORY	没有此目录的权限。
NOPRIV_DIRECTORY	打开文件后，目录已失效。

示例

1、创建BFILE类型测试文件1（如下语句以数据库安装用户Vastbase为例），编辑后保存退出。

vi /home/vastbase/bfile1.txt
abcd1234
efgh5678

2、创建BFILE类型测试文件2（如下语句以数据库安装用户vastbase为例），编辑后保存退出。

vi /home/vastbase/bfile2.txt
this is a bfile test file!

3、使用Vsql连接数据库后，创建BFILE文件目录。

create directory d_bfile as '/home/vastbase/bfiletest';

4、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。

SET SERVEROUTPUT ON;

5、执行如下匿名块，其中调用了DBMS_LOB.fileexists子程序，用于判断BFILE文件是否存在。

文件存在：

declare
l_blob blob;
l_bfile BFILE;
begin
DBMS_LOB.createtemporary (lob_loc => l_blob,
cache => TRUE);
l_bfile := bfilename('d_bfile', 'bfile1.txt');
if DBMS_LOB.FILEEXISTS( l_bfile ) = 1 then
dbms_output.put_line( 'Exists!');
else
dbms_output.put_line( 'Not Exists!');
end if;
dbms_output.put_line('length='||dbms_lob.getlength (l_bfile));
dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
dbms_lob.fileclose(l_bfile);
dbms_output.put_line( 'OK');
end;
/

匿名块返回结果为如下：

Exists!
length=18
OK

文件不存在：

declare
l_blob blob;
l_bfile BFILE;
begin
DBMS_LOB.createtemporary (lob_loc => l_blob,
cache => TRUE);
l_bfile := bfilename('d_bfile', 'bfile3.txt');
if DBMS_LOB.FILEEXISTS( l_bfile ) = 1 then
dbms_output.put_line( 'Exists!');
else
dbms_output.put_line( 'Not Exists!');
end if;
end;
/

匿名块返回结果为如下：

Not Exists!

LOADFROMFILE

语法格式

DBMS_LOB.LOADFROMFILE (
   dest_lob    IN OUT NOCOPY BLOB, 
   src_file    IN            BFILE, 
   amount      IN            INTEGER, 
   dest_offset IN            INTEGER  := 1, 
   src_offset  IN            INTEGER  := 1);

参数说明

dest_lob

目标BLOB对象。
src_file

源BFILE对象。
amount

要从BFILE对象加载到BLOB对象的字节数。
amount

开始写入BLOB对象的偏移。
src_offset

从BFILE对象开始读取的偏移。

示例

1、创建BFILE类型测试文件1（如下语句以数据库安装用户vastbase为例），编辑后保存退出。

vi /home/vastbase/bfiletest/f1.txt
asdfgh12345

2、创建测试表。

create table msta_alertfile(
line number,
prio number,
datum date,
text varchar2(4000));

3、使用Vsql连接数据库后，创建BFILE文件目录。

CREATE DIRECTORY ALERTLOG_DIR as '/home/$username/bfiletest1';

4、创建存储过程，其中调用DBMS_LOB.loadfromfile子程序，将其执行结果插入到测试表中。

create or replace procedure Get_alert_file is
dir varchar2(1000);
dummy number;
start_pos number :=1;
stop_pos number :=1;
v_text varchar2(2000);
line number:=0;
amt number :=2;
v_clob CLOB;
v_file bfile := null;
begin
v_file := BFILENAME ('alertlog_dir','f1.txt');
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
DBMS_LOB.FILEOPEN(v_file, 0);
DBMS_LOB.LOADFROMFILE(v_clob,v_file,DBMS_LOB.getlength(v_file));
commit;
dbms_lob.fileclose (v_file);

loop
start_pos := start_pos +1;
stop_pos := dbms_lob.instr(v_clob, chr(10), start_pos);
amt := stop_pos - start_pos -1;
dbms_lob.read(v_clob, amt, start_pos, v_text);
line := line +1;
insert into msta_alertfile values(line, 1, null, v_text);
end loop;
commit;
end;
/

5、调用存储过程并查看测试表中数据。

call Get_alert_file();
select count(*) from msta_alertfile;

存储过程调用成功，且测试表查询结果为如下，已成功插入五条数据：

Call completed.

COUNT(*)
---------
        5
(1 row)

LOADBLOBFROMFILE

语法格式

DBMS_LOB.LOADBLOBFROMFILE (
   dest_lob    IN OUT NOCOPY BLOB, 
   src_bfile   IN            BFILE, 
   amount      IN            INTEGER, 
   dest_offset IN OUT        INTEGER, 
   src_offset  IN OUT        INTEGER);

参数说明

dest_lob

目标 BLOB 对象。
src_bfile

源BFILE对象。
amount

要从BFILE对象加载到BLOB对象的字节数。
dest_offset

开始写入BLOB对象的偏移。
src_offset

从BFILE对象开始读取的偏移。

示例

1、创建BFILE类型测试文件1（如下语句以数据库安装用户vastbase为例），编辑后保存退出。

mkdir  /home/vastbase/bfiletest
touch test_clob_data.txt;
vi test_clob_data.txt
You can define CLOB or NCLOB in a table or a nested table, it allows you to store very large text files. CLOB columns are usually stored separately from the rest of the row in the table, only the descriptor or locator (pointer) is physically stored in the column. The locator points to where the physical contents of a CLOB are stored and provides a reference to a private work area in the SGA. This work area allows us to scroll through the contents and write new chunks of data. SQL allows you to convert streams upto 4,000 bytes whereas PL/SQL lets you convert 32,767 bytes of characters.

2、使用vsql连接数据库后，创建BFILE文件目录。

create or replace directory blob_dir as '/home/vastbase/bfiletest';

3、创建测试表并插入数据。

create table tab1 (
id number,
blob_data blob
);
insert into tab1 values(1,empty_blob());

4、创建如下存储过程，切中调用DBMS_LOB.loadblobfromfile子程序，用于将BFILE文件中的数据加载到测试表中。

create or replace procedure file_to_blob (p_blob in out nocopy blob,
p_dir in varchar2,
p_filename in varchar2)
as
l_bfile bfile;

l_dest_offset integer := 1;
l_src_offset integer := 1;
begin
l_bfile := bfilename(p_dir, p_filename);
dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
if dbms_lob.getlength(l_bfile) > 0 then
dbms_lob.loadblobfromfile (
dest_lob => p_blob,
src_bfile => l_bfile,
amount => dbms_lob.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset);
end if;
dbms_lob.fileclose(l_bfile);
end file_to_blob;
/

5、执行如下匿名块。

declare
l_blob blob;
begin
select blob_data
into l_blob
from tab1
where id = 1
for update;

file_to_blob (p_blob => l_blob,
p_dir => 'BLOB_DIR',
p_filename => 'MyImage.gif');
end;
/

6、查看测试表数据。

select * from tab1;

返回结果为如下，存储过程和匿名块执行成功后，测试表中的数据得到更新，表中id为1的数据的blob_data字段值得到更新：

ID | BLOB_DATA
---+---------------------------------------
1  | 596F752063616E20646566696E6520434C4F42206F72204E434C4F4220696E2061207461626C65206F722061206E6573746564207461626C652C20697420616C6C6F777320796F7520746F2073746F72

(1 row)

CLOSE

语法格式

DBMS_LOB.CLOSE (
   lob_loc    IN OUT NOCOPY BLOB); 

DBMS_LOB.CLOSE (
   lob_loc    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); 

DBMS_LOB.CLOSE (
   file_loc   IN OUT NOCOPY BFILE);

参数说明

lob_loc

BLOB或CLOB类型的大对象。
FILE_LOC

BFILE类型对象。

示例

示例可参见DBMS_LOB.open子程序的示例。

存储过程

表3 DBMS_LOB支持的存储过程

存储过程	描述
OPEN	根据传入的参数打开对应的LOB对象。
COPY	根据传入的参数从指定位置开始将源LOB复制到目标LOB。
WRITEAPPEND	将指定数据添加到一个大对象的末尾。
CREATETEMPORARY	用来在临时表空间中创建临时BLOB或CLOB。
FREETEMPORARY	释放临时表空间中的临时BLOB或CLOB。
CONVERTTOBBLOB	将CLOB从指定偏移开始的amount个字符转换到BLOB中，并返回转换后的偏移量。

OPEN

语法格式

DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY BLOB,
   open_mode IN            BINARY_INTEGER);
   
DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   open_mode IN            BINARY_INTEGER);
 
DBMS_LOB.OPEN (
   file_loc  IN OUT NOCOPY BFILE,
   open_mode IN            BINARY_INTEGER := file_readonly);

参数说明

lob_loc

LOB类型的对象名。
open_mode

打开模式。对于BLOB，CLOB对象，可以选择LOB_READONLY或者LOB_READWRITE。对于BFILE类型，只能是FILE_READONLY。
- LOB_READONLY：只读。
- LOB_READWRITE：可读写。
- FILE_READONLY：只读。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建含BLOB、CLOB类型字段的表。

create table testtable(id int,c_lob clob,b_lob blob);
insert into testtable values(1,'clob测试',utl_raw.cast_to_raw('blob'));

3、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

4、调用OPEN存储过程。

declare 
  v_clob clob;
  v_clob2 clob;
  len int;
begin
  select c_lob into v_clob from testtable where id=1;
  dbms_lob.open(v_clob,dbms_lob.lob_readwrite);
  dbms_output.put_line('clob1:'||v_clob);
  dbms_lob.close(v_clob);
end;
/

返回结果为：

clob1:clob测试
ANONYMOUS BLOCK EXECUTE

COPY

语法格式

DBMS_LOB.COPY (
  dest_lob    IN OUT NOCOPY BLOB,
  src_lob     IN            BLOB,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

DBMS_LOB.COPY ( 
  dest_lob    IN OUT NOCOPY CLOB  CHARACTER SET ANY_CS,
  src_lob     IN            CLOB  CHARACTER SET dest_lob%CHARSET,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

参数说明

dest_lob

目标LOB对象。
src_lob

源LOB对象。
amount

复制的长度。
dest_offset

目标LOB中的偏移量。
src_offset

源LOB中的偏移量。

示例： 存储过程调用copy函数，输入参数中的dest_lob为clob类型。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建含blob类型字段的表并插入数据。

create table testtable_01(id int,c_lob clob);
insert into testtable_01 values(1,'blobtest1测试测试');
insert into testtable_01 values(2,'blobtest2测试测试');

3、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

4、调用COPY函数。

DECLARE
vdest_lob CLOB;
vsrc_lob CLOB;
amount NUMBER;
dest_offset NUMBER;
src_offset NUMBER;
BEGIN
SELECT c_lob INTO vdest_lob FROM testtable_01 WHERE id = 1 ;
SELECT c_lob INTO vsrc_lob FROM testtable_01 WHERE id = 2 ;
amount := DBMS_LOB.GETLENGTH(vsrc_lob);
dest_offset := DBMS_LOB.GETLENGTH(vdest_lob) + 1;
src_offset := 1;
DBMS_LOB.COPY(vdest_lob, vsrc_lob, amount, dest_offset, src_offset);
DBMS_OUTPUT.PUT_LINE('拷贝结果为: ' || vdest_lob);
END;
/

返回结果为：

拷贝结果为: blobtest1测试测试blobtest2测试测试
ANONYMOUS BLOCK EXECUTE

WRITEAPPEND

语法格式

DBMS_LOB.WRITEAPPEND (
   lob_loc IN OUT NOCOPY BLOB, 
   amount  IN            INTEGER, 
   buffer  IN            RAW); 
   
DBMS_LOB.WRITEAPPEND (
   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, 
   amount  IN            INTEGER, 
   buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);

参数说明

lob_loc

LOB类型的对象名。
amount

追加的长度。
buffer

待追加的内容。

示例： 调用writeappend函数将数据添加对lob的末尾，lob_loc类型为clob。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建包含clob类型的测试表。

create table bak_dbms_lob(
bak_id number(4),
bak_comment clob
);

3、插入测试数据。

insert into bak_dbms_lob(bak_id,bak_comment) values(1,'a');
insert into bak_dbms_lob(bak_id,bak_comment) values(2,'ab');
insert into bak_dbms_lob(bak_id,bak_comment) values(3,'abcdefgccccccc');
insert into bak_dbms_lob(bak_id,bak_comment) values(4,'a bcdefg');

4、查看测试表内容。

select * from bak_dbms_lob;

返回结果为：

bak_id |  bak_comment
--------+----------------
      1 | a
      2 | ab
      3 | abcdefgccccccc
      4 | a bcdefg
(4 rows)

5、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

6、调用writeappend函数。

declare
lob_loc clob;
amount integer:= 18;
buffer_text varchar2(20) := 'added text to clob';
begin
select bak_comment into lob_loc from bak_dbms_lob where bak_id = 2;
dbms_lob.writeappend(lob_loc,amount,buffer_text);
dbms_output.put_line('添加后的字段为：' || lob_loc);
end;
/

返回结果为：

添加后的字段为：abadded text to clob
ANONYMOUS BLOCK EXECUTE

CREATETEMPORARY

语法格式

DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY BLOB,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := DBMS_LOB.SESSION);
  
DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := 10);

参数说明

lob_loc

LOB类型的对象名。
cache

是否将LOB读取到缓冲区。
dur

指定何时清除临时LOB。
- 10/ SESSION：会话结束时
- 12/ CALL：调用结束时。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用createtemporary存储过程，创建临时clob。

DECLARE
v_clob CLOB;
v_amount INT;
v_offset INT;
v_char VARCHAR2(100);
BEGIN
v_char := 'Chinese中国人';
v_offset := 1;
v_amount := 8;
dbms_lob.createtemporary(v_clob,TRUE,12);
FOR i IN 1..2 LOOP
dbms_lob.writeappend(v_clob,v_amount,v_char);
dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob));
END LOOP;
END;
/

返回结果为：

Chinese中 字符长度：8
Chinese中Chinese中 字符长度：16
ANONYMOUS BLOCK EXECUTE

FREETEMPORARY

语法格式

DBMS_LOB.FREETEMPORARY (
   lob_loc  IN OUT  NOCOPY BLOB); 

DBMS_LOB.FREETEMPORARY (
   lob_loc  IN OUT  NOCOPY CLOB CHARACTER SET ANY_CS);

参数说明

lob_loc：LOB类型的对象名。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用freetemporary存储过程，创建临时clob后释放。

DECLARE
v_clob CLOB;
v_amount INT;
v_offset INT;
v_char VARCHAR2(100);
BEGIN
v_char := 'Chinese中国人';
v_offset := 1;
v_amount := 8;
dbms_lob.createtemporary(v_clob,TRUE,12);
dbms_lob.writeappend(v_clob,v_amount,v_char);
dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob));
dbms_lob.freetemporary(v_clob);
dbms_output.put_line(' 释放后再输出：'||v_clob);
END;
/

返回结果为：

Chinese中 字符长度：8
释放后再输出：
ANONYMOUS BLOCK EXECUTE

CONVERTTOBBLOB

语法格式

DBMS_LOB.CONVERTTOBBOLB(
dest_blob  IN OUT  NOCOPY  BLOB,
src_clob   IN      CLOB CHARACTER SET ANY_CS,
amount     IN      INTEGER,
dest_offset IN OUT INTEGER,
src_offset  IN OUT INTEGER,
blob_csid   IN     NUMBER,
lang_context IN OUT INTEGER,
warning     OUT   INTEGER);

参数说明

dest_lob IN

表示一个BLOB类型的大对象。
dest_lob OUT

表示转换后的BLOB类型的大对象。
src_clob

表示一个CLOB类型的大对象。
amount

表示在参数src_clob所指定的大对象中要转换的字符数量。
dest_offset IN

BLOB类型的目标大对象中字节的位置。
dest_offset OUT

写操作完成后，在BLOB类型大对象中字节的位置。
src_offset IN

转换操作中，CLOB类型大对象开始的位置。
src_offset OUT

在转换操作完成后，CLOB类型大对象中字符的位置。
blob_csid

BLOB类型大对象中的字符集ID。

> 0并不是真实存在的字符集ID，为0时表示使用源CLOB的字符集。暂不支持其它取值。

lang_context IN

在转换操作中使用的语言环境。默认值为0。
lang_context OUT

转换后的语言环境。
warning

如果转换成功，则返回0；如果遇到不可转换的字符，则返回1。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用CONVERTTOBBLOB存储过程。

declare
b blob := utl_raw.cast_to_raw('ABCDEFGH');
c clob := '测试123';
amount INTEGER := 4;
dest_offset INTEGER := 1;
src_offset INTEGER := 2;
csid INTEGER := 0;
lang_ctx INTEGER := 0;
warning INTEGER;
begin
dbms_lob.converttoblob(b,c,amount,dest_offset,src_offset,csid,lang_ctx,warning);
dbms_output.put_line(utl_raw.cast_to_varchar2(b));
dbms_output.put_line(dest_offset||' ' ||src_offset||' '||lang_ctx||' '||warning);
end;
/

返回结果为：

试123GH
7 6 0 0
ANONYMOUS BLOCK EXECUTE

VastbaseG100

DBMS_LOB

功能描述

注意事项

支持范围

常量

LOBMAXSIZE

DEFAULT_CSID

DEFAULT_LANG_CTX

函数

GETLENGTH

FILEOPEN

READ

FILECLOSE

SUBSTR

INSTR

WRITE

APPEND

ERASE

FILEEXISTS

LOADFROMFILE

LOADBLOBFROMFILE

CLOSE

存储过程

OPEN

COPY

WRITEAPPEND

CREATETEMPORARY

FREETEMPORARY

CONVERTTOBBLOB

弹框头部