将外部文件加载到oracle数据库中 [英] Loading external files into oracle database

问题描述

我已经创建了下表.

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数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！