CREATE TABLE TEST(TP BLOB,WZ CLOB)
我D盘根目录有图片1.jpg,2.jpg............1000.jpg
和文本文件里面有大于4000字节的文字也有1000个
1.txt,2.txt.......1000.txt
我怎么将他们放到表的对应字段中去
我D盘根目录有图片1.jpg,2.jpg............1000.jpg
和文本文件里面有大于4000字节的文字也有1000个
1.txt,2.txt.......1000.txt
我怎么将他们放到表的对应字段中去
SQL> create table t (
2 name varchar2(30),
3 content clob
4 )
5 /
Table created.SQL> create or replace Directory dir1 as '/opt/oracle'; --创建读取目录
Directory created.
SQL>
SQL> declare
2 l_bfile bfile;
3 l_clob clob;
4 l_str varchar2(1000);
5 begin
6 insert into t (name,content)
7 values ('b.txt',empty_clob()) --此处的'b.txt'可改为变量的形式,你的文件名比较有规律,好办
8 returning content into l_clob;
9
10 l_bfile := bfilename('DIR1','b.txt');
11
12 dbms_lob.fileopen(l_bfile);
13 dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
14 dbms_lob.fileclose(l_bfile);
15 commit;
16 exception when others then
17 l_str:=sqlerrm(sqlcode);
18 dbms_output.put_line(l_str);
19 end;
20 /
PL/SQL procedure successfully completed.
CREATE TABLE image(NAME VARCHAR2(100),DATA BLOB);
DECLARE
LOB_LOC BLOB;
SRC_FILE BFILE;
DST_FILE BLOB;
LGH_FILE BINARY_INTEGER;
BEGIN
INSERT INTO IMAGE
VALUES
('my_1.png', EMPTY_BLOB())
RETURNING DATA INTO LOB_LOC; SRC_FILE := BFILENAME('DDD', 'my.png'); SELECT DATA INTO DST_FILE FROM IMAGE WHERE NAME = 'my_1.png' FOR UPDATE; DBMS_LOB.FILEOPEN(SRC_FILE, DBMS_LOB.FILE_READONLY);
LGH_FILE := DBMS_LOB.GETLENGTH(SRC_FILE);
DBMS_LOB.LOADFROMFILE(DST_FILE, SRC_FILE, LGH_FILE); UPDATE IMAGE SET DATA = DST_FILE WHERE NAME = 'my_1.png'; DBMS_LOB.FILECLOSE(SRC_FILE);
COMMIT;
END;
/
谢谢3楼 4楼不管怎么说先接帖给分