1.创建目录(需要在服务器上创建对应的目录)
create or replace directory testdir as ‘/u01/software/test’;
2.给用户授权(这里可能会有问题，找不到xxx用户，需要创建用户)
grant read,write on directory testdir to xxx;
3.写入文件脚本
declare
filehandle utl_file.file_type; --句柄
begin
filehandle := utl_file.fopen('testdir', 'hello.txt', 'w'); --打开文件
utl_file.put_line(filehandle, 'hello world!');
utl_file.fclose(filehandle); --关闭句柄
end;
暂存另一份脚本
create or replace procedure get_test_blob(i_id varchar2) is
l_file utl_file.file_type;
l_filename varchar2(300);
v_modules varchar2(3000);
v_classes varchar2(3000);
v_proc varchar2(3000);
v_sysdate varchar2(3000);
v_count number;
begin
l_filename := to_char(sysdate, 'yyyymmdd') || i_id ||'.sh';
l_file := utl_file.fopen('testdir', l_filename, 'w');
dbms_output.put_line('===open ok===' || l_filename);
dbms_output.put_line('#!/bin/sh');
utl_file.put_line(l_file, '#!/bin/sh'); --写入文件
v_sysdate := to_char(sysdate, 'yyyymmdd') || i_id;
v_modules := '/u01/srm/prod/' || v_sysdate || '/webapp/webroot/';
v_classes := '/u01/srm/prod/' || v_sysdate ||
'/webapp/webroot/web-inf/classes/';
v_proc := '/u01/srm/prod/' || v_sysdate || '/jobrunner/';
dbms_output.put_line('mkdir -p /u01/srm/prod/' || v_sysdate ||
'/webapp/webroot/modules');
dbms_output.put_line('mkdir -p /u01/srm/prod/' || v_sysdate ||
'/webapp/webroot/web-inf/classes');
utl_file.put_line(l_file,
'mkdir -p /u01/srm/prod/' || v_sysdate ||
'/webapp/webroot/modules');
utl_file.put_line(l_file,
'mkdir -p /u01/srm/prod/' || v_sysdate ||
'/webapp/webroot/web-inf/classes');
select count(*) into v_count from h3c_test_pro_proc;
if v_count > 0 then
dbms_output.put_line('mkdir -p /u01/srm/prod/' || v_sysdate ||
'/jobrunner');
utl_file.put_line(l_file,
'mkdir -p /u01/srm/prod/' || v_sysdate ||
'/jobrunner');
end if;
dbms_output.put_line('#screen');
utl_file.put_line(l_file, '#screen');
for rec in (select distinct trim(h.screen_path) screen_path
from h3c_test_pro_screen h) loop
--复制某个目录下的文件
if rec.screen_path like '%.screen' or rec.screen_path like '%.svc' then
dbms_output.put_line('mkdir -p ' || v_modules ||
substr(rec.screen_path,
1,
instr(rec.screen_path,
'/',
1,
length(rec.screen_path) -
length(replace(rec.screen_path,
'/',
'')))));
dbms_output.put_line('cp -rn /u01/srm/webapp/webroot/' ||
rec.screen_path || ' ' || v_modules ||
rec.screen_path);
utl_file.put_line(l_file,
'mkdir -p ' || v_modules ||
substr(rec.screen_path,
1,
instr(rec.screen_path,
'/',
1,
length(rec.screen_path) -
length(replace(rec.screen_path, '/', '')))));
utl_file.put_line(l_file,
'cp -rn /u01/srm/webapp/webroot/' ||
rec.screen_path || ' ' || v_modules ||
rec.screen_path);
--复制文件夹
else
dbms_output.put_line('mkdir -p ' || v_modules || rec.screen_path);
dbms_output.put_line('cp -rn /u01/srm/webapp/webroot/' ||
rec.screen_path || ' ' || v_modules ||
substr(rec.screen_path,
1,
instr(rec.screen_path,
'/',
1,
length(rec.screen_path) -
length(replace(rec.screen_path,
'/',
'')) - 1)));
utl_file.put_line(l_file,
'mkdir -p ' || v_modules || rec.screen_path);
utl_file.put_line(l_file,
'cp -rn /u01/srm/webapp/webroot/' ||
rec.screen_path || ' ' || v_modules ||
substr(rec.screen_path,
1,
instr(rec.screen_path,
'/',
1,
length(rec.screen_path) -
length(replace(rec.screen_path, '/', '')) - 1)));
end if;
end loop;
dbms_output.put_line('#bm');
utl_file.put_line(l_file, '#bm');
for rec in (select distinct trim(h.bm_path) bm_path from h3c_test_pro_bm h) loop
if rec.bm_path like '%.bm' then
dbms_output.put_line('mkdir -p ' || v_classes ||
replace(substr(rec.bm_path,
1,
instr(replace(rec.bm_path,
'.bm',
''),
'.',
-1) - 1),
'.',
'/'));
dbms_output.put_line('cp -rn /u01/srm/webapp/webroot/web-inf/classes/' ||
replace(replace(rec.bm_path, '.bm', ''),
'.',
'/') || '.bm' || ' ' || v_classes ||
replace(replace(rec.bm_path, '.bm', ''),
'.',
'/') || '.bm');
utl_file.put_line(l_file,
'mkdir -p ' || v_classes ||
replace(substr(rec.bm_path,
1,
instr(replace(rec.bm_path, '.bm', ''),
'.',
-1) - 1),
'.',
'/'));
utl_file.put_line(l_file,
'cp -rn /u01/srm/webapp/webroot/web-inf/classes/' ||
replace(replace(rec.bm_path, '.bm', ''), '.', '/') ||
'.bm' || ' ' || v_classes ||
replace(replace(rec.bm_path, '.bm', ''), '.', '/') ||
'.bm');
else
dbms_output.put_line('mkdir -p ' || v_classes ||
replace(rec.bm_path, '.', '/'));
dbms_output.put_line('cp -rn /u01/srm/webapp/webroot/web-inf/classes/' ||
replace(rec.bm_path, '.', '/') || ' ' ||
v_classes ||
substr(rec.bm_path,
1,
instr(rec.bm_path, '.', 1) - 1));
utl_file.put_line(l_file,
'mkdir -p ' || v_classes ||
replace(rec.bm_path, '.', '/'));
utl_file.put_line(l_file,
'cp -rn /u01/srm/webapp/webroot/web-inf/classes/' ||
replace(rec.bm_path, '.', '/') || ' ' || v_classes ||
substr(rec.bm_path,
1,
instr(rec.bm_path, '.', 1) - 1));
end if;
end loop;
dbms_output.put_line('#proc');
utl_file.put_line(l_file, '#proc');
for rec in (select distinct trim(h.bm_path) proc_path
from h3c_test_pro_proc h) loop
if rec.proc_path like '%.bm' or rec.proc_path like '%.proc' then
dbms_output.put_line('mkdir -p ' || v_proc ||
substr(rec.proc_path,
1,
instr(rec.proc_path,
'/',
1,
length(rec.proc_path) -
length(replace(rec.proc_path,
'/',
'')))));
dbms_output.put_line('cp -rn /u01/srm/jobrunner/' || rec.proc_path || ' ' ||
v_proc || rec.proc_path);
utl_file.put_line(l_file,
'mkdir -p ' || v_proc ||
substr(rec.proc_path,
1,
instr(rec.proc_path,
'/',
1,
length(rec.proc_path) -
length(replace(rec.proc_path, '/', '')))));
utl_file.put_line(l_file,
'cp -rn /u01/srm/jobrunner/' || rec.proc_path || ' ' ||
v_proc || rec.proc_path);
end if;
end loop;
dbms_output.put_line('===export ok===');
utl_file.fclose(l_file);
exception
when utl_file.invalid_path then
--无效的路径
dbms_output.put_line('===invalid_path===' || i_id);
raise;
when utl_file.invalid_mode then
--无效的打开模式
dbms_output.put_line('===invalid_mode===' || i_id);
raise;
when utl_file.invalid_operation then
--无效的操作，文件打开错误会报这个异常，一般来说都是超长或打开方式byte型和非byte型
dbms_output.put_line('===invalid_operation===' || i_id);
raise;
when utl_file.invalid_maxlinesize then
--无效的最大长度，varchar2最大4000，raw最大32676，超过回报这个异常，所以一般要进行循环操作
dbms_output.put_line('===invalid_maxlinesize===' || i_id);
raise;
when utl_file.access_denied then
--拒绝进入指定路径，可能是授权问题
dbms_output.put_line('===access_denied===' || i_id);
raise;
when utl_file.invalid_filehandle then
--文件处理错误，不常见
dbms_output.put_line('===invalid_filehandle===' || i_id);
raise;
when utl_file.write_error then
--写入错误，处理该异常最好的方式是将要写入的文件简单化，然后找准错误原因
dbms_output.put_line('===write_error===' || i_id);
raise;
when no_data_found then
--select时候未找到数据，不是utl_file的异常
dbms_output.put_line('===no_data_found===' || i_id);
utl_file.fclose(l_file);
raise;
when others then
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
raise;
end if;
end get_test_blob;