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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/286077.html
標籤：Oracle
上一篇：初識Oracle資料庫基礎筆記