我想用BLOB来加插图画 ”C:/pic.jpg”到Oracle10g里面。
我用SqlPlus成功建立了一个table 如下:CREATE TABLE IMAGES (
ID NUMBER(4) PRIMARY KEY,
IMAGE BLOB
);然后就不知道怎莫继续了。
请各位好心的大哥帮帮忙 @_@
如何用BLOB在Oracle10g里面加插图画呢 ?_?谢谢:>皎皎
我用SqlPlus成功建立了一个table 如下:CREATE TABLE IMAGES (
ID NUMBER(4) PRIMARY KEY,
IMAGE BLOB
);然后就不知道怎莫继续了。
请各位好心的大哥帮帮忙 @_@
如何用BLOB在Oracle10g里面加插图画呢 ?_?谢谢:>皎皎
2 ID NUMBER(4),
3 FILENAME VARCHAR2) AS
4 F_LOB BFILE;
5 B_LOB BLOB;
6 BEGIN
7 INSERT INTO IMAGE_LOB (ID, IMAGE) VALUES (ID,
8 EMPTY_BLOB ()) RETURN IMAGE INTO B_LOB;
9 F_LOB:= BFILENAME ('IMAGES', FILENAME);
10 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
11 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
12 DBMS_LOB.GETLENGTH (F_LOB));
13 DBMS_LOB.FILECLOSE (F_LOB);
14 COMMIT;
15 END;
16 /调用代码:
SQL> BEGIN
2 IMG_INSERT(1,'test.gif');
3 END;
4 /
"warning procedure creating with errors"CREATE TABLE IMAGE_LOB (
ID NUMBER(4) PRIMARY KEY,
IMAGE BLOB
);CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\';CREATE OR REPLACE PROCEDURE IMG_INSERT (
ID NUMBER(4),
FILENAME VARCHAR2) AS
F_LOB BFILE;
B_LOB BLOB;
BEGIN
INSERT INTO IMAGE_LOB (ID, IMAGE) VALUES (ID,
EMPTY_BLOB ()) RETURN IMAGE INTO B_LOB;
F_LOB:= BFILENAME ('IMAGES', FILENAME);
DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
DBMS_LOB.GETLENGTH (F_LOB));
DBMS_LOB.FILECLOSE (F_LOB);
COMMIT;
END;
/
is
temp_bfile bfile;
inserted_blob_file blob;
begin
temp_bfile := bfilename('blod_dir',pfname)
insert into giga_table(fid,fname,fpic)
values (pfid,pfname,empty_blob())
returning fpic into inserted_blob_file;
select fpic into inserted_blob_file
from giga_table
where fname=pfname for update;
dbms_lob.fileopen(temp_bfile,dbms_lob.readonly);
dbms_lob.loadfromfile(inserted_blob_file,temp_bfile,dbms_lob.getlength(temp_bfile));
set giga_blob set fpic=inserted_blob
where fname=pfname;
dbms_lob.filecolse(temp_bfile);
commit;
end;
2 ID NUMBER(4) PRIMARY KEY,
3 IMAGE BLOB
4 );表被创建SQL> CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\';目录被创建SQL>
SQL> CREATE OR REPLACE PROCEDURE IMG_INSERT (
2 ID NUMBER,
3 FILENAME VARCHAR2) AS
4 F_LOB BFILE;
5 B_LOB BLOB;
6 BEGIN
7 INSERT INTO IMAGES (ID, IMAGE) VALUES (ID,
8 EMPTY_BLOB ()) RETURN IMAGE INTO B_LOB;
9 F_LOB:= BFILENAME ('IMAGES', FILENAME);
10 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
11 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
12 DBMS_LOB.GETLENGTH (F_LOB));
13 DBMS_LOB.FILECLOSE (F_LOB);
14 COMMIT;
15 END;
16 /过程被创建SQL> host dir c: Volume in drive C has no label
Volume Serial Number is DC86-CFD4
Directory of C:\0 JPG 1087043 05-07-31 23:05
AUTOEXEC BAT 0 03-01-03 23:10
CONFIG SYS 0 03-01-03 23:10
DOCUME~1 <DIR> 03-01-03 22:59
DOWNLO~1 <DIR> 06-02-26 0:25
INETPUB <DIR> 05-11-20 16:59
JCB_GJ <DIR> 05-12-13 13:39
PROGRA~1 <DIR> 03-01-03 23:01
RAVBIN <DIR> 05-10-02 13:44
TEMP <DIR> 06-01-15 16:22
TOOLS <DIR> 04-10-18 10:37
USAGET~1 TXT 16 05-11-24 10:28
WINDOWS <DIR> 03-01-03 22:53
~DTLOG TXT 315 06-02-07 10:03
14 file(s) 1087374 bytes
794268160 bytes freeSQL> ed
SQL> BEGIN
2 IMG_INSERT(1,'0.jpg');
3 END;
4 /PL/SQL 过程成功完成SQL> select dbms_lob.getlength(image) from images;DBMS_LOB.GETLENGTH(IMAGE)
-------------------------
1087043