已经建表结构如下:
CREATE TABLE MAP
(MAP_NO NEMBUR(2) PRIMARYKEY,
MAP_DATE DATE,
MAP_BLOB BLOB DEFAULT EMPTY_BLOB)create or replace directory utllobdir as 'e:\';
create table bfile_tab (bfile_column BFILE);
create table utl_lob_test (blob_column BLOB);declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','test.jpg');
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/
select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;
CREATE TABLE MAP
(MAP_NO NEMBUR(2) PRIMARYKEY,
MAP_DATE DATE,
MAP_BLOB BLOB DEFAULT EMPTY_BLOB)create or replace directory utllobdir as 'e:\';
create table bfile_tab (bfile_column BFILE);
create table utl_lob_test (blob_column BLOB);declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','test.jpg');
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/
select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;
ORA-22288: 文件或 LOB 操作FILEOPEN失败
系统找不到指定的路径。
ORA-06512: 在"SYS.DBMS_LOB", line 504
ORA-06512: 在line 9
SQL> CREATE TABLE MAP
2 (MAP_NO NEMBUR(2) PRIMARYKEY,
3 MAP_DATE DATE,
4 MAP_BLOB BLOB DEFAULT EMPTY_BLOB);
(MAP_NO NEMBUR(2) PRIMARYKEY,
*
ERROR 位于第 2 行:
ORA-00907: 缺少右括号
(p_rno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
Begin-- Update the employee photo
insert into blob_test values (p_rno,empty_blob())
RETURN picture into b_photo;-- find where the photo's pointer is located.
f_photo := bfilename('IMAGE', p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END;
2 (MAP_NO NEMBUR(2) PRIMARYKEY,
3 MAP_DATE DATE,
4 MAP_BLOB BLOB DEFAULT EMPTY_BLOB);
(MAP_NO NEMBUR(2) PRIMARYKEY,
应该是NUMBER吧
MAP_NO Number(2) PRIMARY KEY,
MAP_DATE DATE,
MAP_BLOB BLOB DEFAULT EMPTY_BLOB());