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;
经验首页