Oracle的创建文件

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;
学习至上
posted @ 2021-05-15 22:05  DarkerGuo  阅读(58)  评论(0编辑  收藏  举报