这样吧
CREATE OR REPLACE DIRECTORY MyFilePath AS 'e:/test';
CREATE OR REPLACE DIRECTORY MyFilePath AS 'e:/test';
解决方案 »
- oracle连接远程数据库ORA-12541: TNS: 无监听程序,本地没问题
- 求一条sql语句
- Visual FoxPro问题
- 用PL/SQL Developer 怎么创建用户阿
- 能不能实现这个功能?(立即结贴)
- PB前端程序,ORACLE后端,如何在安装过程中加载ORACLE客户端?谢谢指教!
- oracle在客户端的运行的问题
- 登陆不到sql plus
- 十万火急!!!!!!!高额悬赏!!!SQL语句再ORACL中的错误!
- Navicat Premium 12 连接oracle问题【求助】
- 请教关于@和@@的区别??
- 大家都进来看看,在oracle8i中创建表时候,出现了一个很奇怪的问题。
分隔符:\ \\ / // 都试验过的,一样的报错,e:\test\a.txt肯定存在的
按弱水兄的试一下吧
utl_file_dir=e:\test修改之后如何使之生效?比如说重启oracle服务
dos下
svrmgrl
connect internal/oracle
shutdown immediate
startup
我用的是win2000,那个目录是fat32,权限是普通的everyone
init.ora所在目录:\oracle\admin\orcl\pfile
oracle目录下只含有一个init.ora操作用户是自定义的,在system账户下赋予该自定义账户以create any directory和drop any directory的权限我在init.ora最后一行加入utl_file_dir=e:\test
然后用:
svrmgrl
connect internal/oracle
shutdown immediate
startup
方法重启oracle最后重新建立目录和存储过程
然后执行,仍然是那个错误 :(
现在仍然是那个问题
我的test目录现在设成了d:\test,[oracle安装目录为d:\oracle]init.ora设置成:utl_file_dir=/test
代码为:
CREATE OR REPLACE DIRECTORY MyFilePath AS '\test';[/test、d:\test、d:/test都尝试过]
COMMIT;
CREATE OR REPLACE PROCEDURE Save_Blob AS
f_lob BFILE;
b_lob BLOB;
BEGIN
INSERT INTO oratext_blob VALUES('10', empty_blob()) RETURN content INTO b_lob;
f_lob := BFILENAME('MyFilePath', 'a.txt');
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;但是仍然是原来那个错误
在oracle论坛找到了解决方法:问题和我一模一样:
Hello,An error occurs when I launch the load.sql script below:!ls -l /usr/local/cmsbuffer;create or replace directory bfile_dir as '/usr/local/cmsbuffer';create or replace procedure frombfile is
src bfile := bfilename('bfile_dir', 'toto.sql');
begin
dbms_lob.fileopen(src, dbms_lob.file_readonly);
dbms_lob.fileclose(src);
end;
/
exec frombfile;
~
~
~
"load.sql" 15 lignes, 327 caractèresWith sqlplus, an error occurs.SQL> start load.sql;
total 32
-rw-r--r-- 1 weblogic other 1922 jun 6 10:50 SVG1.svg
-rw-rw-rw- 1 oracle dba 8753 jun 8 17:54 toto.sql
-rw-r--r-- 1 weblogic other 3591 jun 5 17:00 yafus.class
-rw-r--r-- 1 weblogic other 643 jun 5 17:01 yafus.htmlDirectory created.Procedure created.BEGIN frombfile; END;
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "RAYNE.FROMBFILE", line 4
ORA-06512: at line 1Has anybody succeed to open a bfile with BDMS_LOB package ?
How can I declare my directory and my file ?
My OS is Solaris with Oracle8i.Thanks for help. 解决方法:
the problem is a case matter one.if your statement is:
create directory bfile_dir as...means that you have to refer to the directory in uppercase
eg: src bfile := bfilename('BFILE_DIR', 'toto.sql');if you want to create an alias in lower case or mixed you have to delimit it in creation:eg:create directory "bfile_dir" as ...then your load.sql can run unmodified
successfully .
Hope it helps.
I verified it in 8.1.6 with AIX 4.3.2
Bye,
Gianluca 我按照方法把
CREATE OR REPLACE DIRECTORY MyFilePath AS 'e:\test';
变成
CREATE OR REPLACE DIRECTORY "MyFilePath" AS 'e:\test';就行了,不用在init.ora里写什么的
竟然是大小写问题 >:(