oracle创建文件夹_oracle数据导出expdp

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;