将外部文件加载到oracle数据库中

问题描述：

我已经创建了下表.

create table emps( id varchar2(20), clobi clob);

Am尝试使用以下过程将bfile插入COLBI列:

Am trying to insert bfile into the COLBI column using the below procedure:

create or replace procedure bfii is 
   bfil bfile; 
   clo clob; 
begin 
   insert into emps values('3',empty_clob() ) returning clobi into clo;
   bfil := bfilename('clobr', 'Man.ext'); 
   dbms_lob.fileopen(bfil); 
   dbms_lob.loadfromfile(clo,bfil, dbms_lob.getlength(bfil)); 
   dbms_lob.fileclose(bfil); 
end;

但是我在运行时而不是编译时收到错误消息.

But I'm getting the error message at run-time rather than compilation.

Error at line 2
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "SYS.HIMA", line 12
ORA-06512: at line 2

有人可以帮我吗?

答

目录名和其他对象一样，默认情况下会被创建为大写，除非使用引号将其引起来.当您将目录称为字符串时，它需要与数据字典中的大小写匹配(例如all_directories).因此，您需要更改为:

Directory names, like other objects, are created as uppercase by default unless the name was quoted. When you refer to the directory as a string it needs to match the case in the data dictionary (e.g. all_directories). So you need to change to:

   bfil := bfilename('CLOBR', 'Man.ext');

...即使使用create directory clobr ...

将外部文件加载到oracle数据库中

相关推荐