创建目录(需要在服务器上创建对应的目录)
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;
