用dbms_lob包来处理!
既可以用来读取内部的lob对象,也可以用来处理bfile对象。但处理两者之间,还有一点差别。处理内部lob对象(blob,clob)时,可以进行读和写,但处理外部lob对象bfile时,只能进行读操作,写的操作可以用pl/sql处理。另外用sql也可以处理lob,但要注意sql仅可以处理整个lob,不能操作lob的数据片。
在dbms_lob包中内建了read(),append,write(),erase(),copy(),getlength(),substr()等函数
贴个例子实验工具:
win2000 advanced server
oracle8.1.7
ORACLE8 完全参考手册
SQL Navigator 4 (它的QUERY结果可以很方便的检查LOB的值)
SQL*Plus Worksheet (用力执行命令)准备工作:
1.create tablespaces
CREATE TABLESPACE LOBUSER
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORCL\LOBUSER1.ora' SIZE 100M REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 32767M DEFAULT
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 )
CREATE TABLESPACE LOBLOB
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORCL\LOBLOB.ora' SIZE 50M REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 32767M DEFAULT
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 )2. Create user LOBMAN
CREATE USER "LOBMAN" PROFILE "DEFAULT" IDENTIFIED BY "manager"
DEFAULT TABLESPACE "LOBUSER"
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "LOBMAN";
GRANT "DBA" TO "LOBMAN";3. Create Oracle Directory:
登陆LOBMAN/MANAGER
create or replace directory proposal_dir as 'e:\oracle\tmp'
这个object并不能在DBA studio中找到,你需要query
all_directories , 你会发现它的owner 是SYS.4. Create LOB Table
create table proposal
(proposal_id number(10) primary key,
recipient_name varchar2(25),
proposal_name varchar2(25),
short_description varchar2(1000),
proposal_text CLOB,
Budget BLOB,
cover_letter BFILE)
storage (initial 50K next 50K pctincrease 0)
tablespace lobuser
lob (proposal_text,Budget) store as
(tablespace loblob storage (initial 50K next 50K pctincrease 0)
chunk 16k pctversion 10 nocache logging); 这样一来,CLOB and BLOB Columns 就储存在另一个tablespace中.
注意,CLOB和BLOB是储存在数据库中,而BFILE是储存在数据库外(库中
仅仅放定位符)5. 建立e:\oracle\tmp 目录并存入文件操作:
1. 初始话(insert)
insert into PROPOSAL
values (1,'DOT PHILLIPS','CLEAR PHILLIPS FIELD',NULL,'This
is the text of proposal to clear Phillips field.',EMPTY_BLOB(),NULL);
commit;insert into PROPOSAL
values (2,'BRAD OHMONT','REBUILD FENCE',NULL,EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME('PROPOSAL_DIR','genmd.log'));
commit;
注意目录一定要大写!insert into PROPOSAL
select 3,'SKIP GATES','CLEAR GATES FIELD',NULL,Proposal_text,Budget,cover_letter
from proposal
where proposal = 1;
commit;2. Update
select proposal_text from proposal where proposal_id = 3 for update;
update proposal set proposal_text = 'This is the new proposal text .'
where proposal_id = 3;
commit;注意:for update 是必须的,当update clob 和blob 时,必须先把记录锁住
(对于BFILE则不用)3. DBMS_LOB Package->Read
set serveroutput on
declare
locator_var clob;
amount_var integer;
offset_var integer;
output_var varchar2(10);
begin
amount_var:=10;
offset_var:=1;
select proposal_text into locator_var
from Proposal
where proposal_id = 1;
DBMS_LOB.read(locator_var,amount_var,offset_var,output_var);
DBMS_OUTPUT.PUT_LINE('Start of proposal text: '||output_var);
end;
/4. DBMS_LOB Package->write
set serveroutput on
declare
locator_var clob;
amount_var integer;
offset_var integer;
buffer_var varchar2(12);
begin
amount_var:=12;
offset_var:=10;
buffer_var :='ADD NEW TEXT';
select proposal_text into locator_var
from Proposal
where proposal_id = 3 for update; /*for update*/
DBMS_LOB.WRITE(locator_var,amount_var,offset_var,buffer_var);
end;
/实验完成.DBMS_LOB package contains a lot of sub routines which are
very useful to operator the LOB column .
既可以用来读取内部的lob对象,也可以用来处理bfile对象。但处理两者之间,还有一点差别。处理内部lob对象(blob,clob)时,可以进行读和写,但处理外部lob对象bfile时,只能进行读操作,写的操作可以用pl/sql处理。另外用sql也可以处理lob,但要注意sql仅可以处理整个lob,不能操作lob的数据片。
在dbms_lob包中内建了read(),append,write(),erase(),copy(),getlength(),substr()等函数
贴个例子实验工具:
win2000 advanced server
oracle8.1.7
ORACLE8 完全参考手册
SQL Navigator 4 (它的QUERY结果可以很方便的检查LOB的值)
SQL*Plus Worksheet (用力执行命令)准备工作:
1.create tablespaces
CREATE TABLESPACE LOBUSER
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORCL\LOBUSER1.ora' SIZE 100M REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 32767M DEFAULT
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 )
CREATE TABLESPACE LOBLOB
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORCL\LOBLOB.ora' SIZE 50M REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 32767M DEFAULT
STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 )2. Create user LOBMAN
CREATE USER "LOBMAN" PROFILE "DEFAULT" IDENTIFIED BY "manager"
DEFAULT TABLESPACE "LOBUSER"
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "LOBMAN";
GRANT "DBA" TO "LOBMAN";3. Create Oracle Directory:
登陆LOBMAN/MANAGER
create or replace directory proposal_dir as 'e:\oracle\tmp'
这个object并不能在DBA studio中找到,你需要query
all_directories , 你会发现它的owner 是SYS.4. Create LOB Table
create table proposal
(proposal_id number(10) primary key,
recipient_name varchar2(25),
proposal_name varchar2(25),
short_description varchar2(1000),
proposal_text CLOB,
Budget BLOB,
cover_letter BFILE)
storage (initial 50K next 50K pctincrease 0)
tablespace lobuser
lob (proposal_text,Budget) store as
(tablespace loblob storage (initial 50K next 50K pctincrease 0)
chunk 16k pctversion 10 nocache logging); 这样一来,CLOB and BLOB Columns 就储存在另一个tablespace中.
注意,CLOB和BLOB是储存在数据库中,而BFILE是储存在数据库外(库中
仅仅放定位符)5. 建立e:\oracle\tmp 目录并存入文件操作:
1. 初始话(insert)
insert into PROPOSAL
values (1,'DOT PHILLIPS','CLEAR PHILLIPS FIELD',NULL,'This
is the text of proposal to clear Phillips field.',EMPTY_BLOB(),NULL);
commit;insert into PROPOSAL
values (2,'BRAD OHMONT','REBUILD FENCE',NULL,EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME('PROPOSAL_DIR','genmd.log'));
commit;
注意目录一定要大写!insert into PROPOSAL
select 3,'SKIP GATES','CLEAR GATES FIELD',NULL,Proposal_text,Budget,cover_letter
from proposal
where proposal = 1;
commit;2. Update
select proposal_text from proposal where proposal_id = 3 for update;
update proposal set proposal_text = 'This is the new proposal text .'
where proposal_id = 3;
commit;注意:for update 是必须的,当update clob 和blob 时,必须先把记录锁住
(对于BFILE则不用)3. DBMS_LOB Package->Read
set serveroutput on
declare
locator_var clob;
amount_var integer;
offset_var integer;
output_var varchar2(10);
begin
amount_var:=10;
offset_var:=1;
select proposal_text into locator_var
from Proposal
where proposal_id = 1;
DBMS_LOB.read(locator_var,amount_var,offset_var,output_var);
DBMS_OUTPUT.PUT_LINE('Start of proposal text: '||output_var);
end;
/4. DBMS_LOB Package->write
set serveroutput on
declare
locator_var clob;
amount_var integer;
offset_var integer;
buffer_var varchar2(12);
begin
amount_var:=12;
offset_var:=10;
buffer_var :='ADD NEW TEXT';
select proposal_text into locator_var
from Proposal
where proposal_id = 3 for update; /*for update*/
DBMS_LOB.WRITE(locator_var,amount_var,offset_var,buffer_var);
end;
/实验完成.DBMS_LOB package contains a lot of sub routines which are
very useful to operator the LOB column .
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货