使用PLSQL在FTP上写文件:
1、建立目录
以DBA账号登陆
#sqlplus / as sysdba
sql>create or replace directory file_path as '/u5/UT30/ut30ora/db/tech_st/11.1.0';
以apps账号编译程式
DECLARE
v_file utl_file.file_type;
begin
v_file:=utl_file.fopen('FILE_PATH','Test-Johnson.txt','w');--'FILE_PATH'要大写
utl_file.put_line(v_file, 'Writed By Johnson;');
utl_file.fclose(v_file);
end test_output;
2、出现错误:ORACLE目录路径无效错误解决方法(ORA-29280)
解决方法:
需要使用第1点的方式Create Directory,不能在fopen中直接写目录,即
create or replace directory asdf as '/u5/UT30/ut30ora/db/tech_st/11.1.0/';
需要这样的方式先create 目录
把目录附权限赋给apps:[SQL:]grant read, write on directory OUT_PATH to apps;
3、出现错误:ORA-29283: 文件操作无效
解决方法:
9i以前:写日志,把内容记到服务器指定目录下必须配置Utl_file_dir初始化参数,并保证日志路径与Utl_file_dir路径一致或者是其中一个
9i以后:Create Directory方式即可。只是这个目录需要是在BD account的目录下面.不然会提示这个错误
create or replace directory file_path as '/u5/UT30/ut30mgr/apps/apps_st/appl/au/12.0.0';->提示文件操作无效
create or replace directory file_path as '/u5/UT30/ut30ora/db/tech_st/11.1.0';->成功
可通过SQL查询系统上已经建立的Directory:
select * from dba_directories;
4、写文件example:
declare
i integer;
v_file utl_file.file_type;
v_filename varchar2(20);
cursor c_item is
SELECT * FROM mtl_system_items_b msi where msi.organization_id = 82;
begin
select to_char(sysdate, 'MONddHH24MI') into v_filename from dual;
if utl_file.is_open(v_file) then
utl_file.fclose(v_file);
end if;
v_file := utl_file.fopen('FILE_PATH',
v_filename || 'Test.txt',
'w');
for r_item in c_item loop
utl_file.put_line(v_file,r_item.segment1);
end loop;
if utl_file.is_open(v_file) then
utl_file.fclose(v_file);
end if;
commit;
end ;
1、建立目录
以DBA账号登陆
#sqlplus / as sysdba
sql>create or replace directory file_path as '/u5/UT30/ut30ora/db/tech_st/11.1.0';
以apps账号编译程式
DECLARE
v_file utl_file.file_type;
begin
v_file:=utl_file.fopen('FILE_PATH','Test-Johnson.txt','w');--'FILE_PATH'要大写
utl_file.put_line(v_file, 'Writed By Johnson;');
utl_file.fclose(v_file);
end test_output;
2、出现错误:ORACLE目录路径无效错误解决方法(ORA-29280)
解决方法:
需要使用第1点的方式Create Directory,不能在fopen中直接写目录,即
create or replace directory asdf as '/u5/UT30/ut30ora/db/tech_st/11.1.0/';
需要这样的方式先create 目录
把目录附权限赋给apps:[SQL:]grant read, write on directory OUT_PATH to apps;
3、出现错误:ORA-29283: 文件操作无效
解决方法:
9i以前:写日志,把内容记到服务器指定目录下必须配置Utl_file_dir初始化参数,并保证日志路径与Utl_file_dir路径一致或者是其中一个
9i以后:Create Directory方式即可。只是这个目录需要是在BD account的目录下面.不然会提示这个错误
create or replace directory file_path as '/u5/UT30/ut30mgr/apps/apps_st/appl/au/12.0.0';->提示文件操作无效
create or replace directory file_path as '/u5/UT30/ut30ora/db/tech_st/11.1.0';->成功
可通过SQL查询系统上已经建立的Directory:
select * from dba_directories;
4、写文件example:
declare
i integer;
v_file utl_file.file_type;
v_filename varchar2(20);
cursor c_item is
SELECT * FROM mtl_system_items_b msi where msi.organization_id = 82;
begin
select to_char(sysdate, 'MONddHH24MI') into v_filename from dual;
if utl_file.is_open(v_file) then
utl_file.fclose(v_file);
end if;
v_file := utl_file.fopen('FILE_PATH',
v_filename || 'Test.txt',
'w');
for r_item in c_item loop
utl_file.put_line(v_file,r_item.segment1);
end loop;
if utl_file.is_open(v_file) then
utl_file.fclose(v_file);
end if;
commit;
end ;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/385592/viewspace-687330/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/385592/viewspace-687330/