如何从PLSQL oracle调用形成Unix目录中单行的文件名列表

Question

我有一个文件列表比如

File 1 : a_0001,
File 2 : b_1001,
File 3 : c_2001 

存在于

Directory : /home/swa/IBI directory. 

我想形成一个oracle字符串如下

" [a_001] [b_1001] [c_2001] "

，并使用该字符串进行进一步的oracle处理。

我不能在这里给出任何代码。因为，我不知道有什么函数能做到这一点

Answer 1

从Oracle 11开始，您可以使用外部表预处理列出目录中的文件，然后遍历从外部表读取的文件。

像这样:http://www.oracle-developer.net/display.php?id=513

Answer 2

如果我没记错的话，oracle没有在目录中列出文件的功能。为此，您需要使用java源代码。列出目录中的文件，读取输入并创建变量。下面是在unix环境下执行命令的java源代码:

create or replace and compile java source named "Host" as
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    //String[] commands = command.split(" ");
    try {
      String[] finalCommand;
      /*if (isWindows()) {
        finalCommand = new String[4];
        // Use the appropriate path for your windows version.
        //finalCommand[0] = "C:\winnt\system32\cmd.exe";    // Windows NT/2000
        finalCommand[0] = "C:\windows\system32\cmd.exe";    // Windows XP/2003
        //finalCommand[0] = "C:\windows\syswow64\cmd.exe";  // Windows 64-bit
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      }
      else {
        //finalCommand = new String[commands.length + 2];
        finalCommand = new String[commands.length];
        //finalCommand[0] = "/bin/bash";
        //finalCommand[1] = "-c";
        //for (int i = 2; i < commands.length; i++){
        //  finalCommand[i] = command[i];
        //}
        for (int i = 0; i < commands.length; i++){
          finalCommand[i] = commands[i];
        }
      }*/
      //final Process pr = Runtime.getRuntime().exec(finalCommand);
      final Process pr = Runtime.getRuntime().exec(command.split(" "));
      pr.waitFor();
      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_in = null;
          try {
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(10); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_in.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_err = null;
          try {
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(10); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process error.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_err.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }
  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }
};

PLSQL功能:

CREATE OR REPLACE PROCEDURE host_command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';

脚本:

DECLARE
  l_output DBMS_OUTPUT.chararr;
  l_lines  INTEGER := 1000;
BEGIN
  DBMS_OUTPUT.enable(1000000);
  DBMS_JAVA.set_output(1000000);
  host_command('printenv');
  --host_command('/bin/ls /home/oracle');
  DBMS_OUTPUT.get_lines(l_output, l_lines);
  FOR i IN 1 .. l_lines LOOP
    -- Do something with the line.
    -- Data in the collection - l_output(i)
    /*
      l_output(i) - unix / java output
      Parse files list here and make your variable:
    */
    DBMS_OUTPUT.put_line(l_output(i));
  END LOOP;
END;
/

Answer 3

有一个C库/侦听器。ora/库/外部函数方法。在没有java

的XE版本中可能会很有帮助。

注意:当目录列表超过32000个字符时，此解决方案会导致缓冲区溢出

1)准备你的共享C库(in list.c) -这绝对不是最安全的&关于c语言中如何编写代码目录列表的最佳解决方案，但是您已经了解了基本思想。

//
// FILE: list.c
//
// gcc -Wall -fPIC -c list.c
// gcc -shared -o list.so list.o
// mkdir -p /u01/lib
// cp list.so /u01/lib
//
#include <stdio.h>
#include <dirent.h>
#include <sys/types.h>
char *list_dir(const char *path) {
  char *filelist;
  filelist=(char *) calloc(32000,sizeof(char));
  struct dirent *entry;
  DIR *dir=opendir(path);
  if (dir==NULL) {
    return;
  }
  strcat(filelist,"");
  while ((entry = readdir(dir)) != NULL) {
    strcat(filelist,entry->d_name);
    strcat(filelist,"n");
  }
  strcat(filelist,"");
  closedir(dir);
  return (filelist);
}

2)编辑你的听众。奥拉

...
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = <PATH TO ORACLE >)
      (ENVS="EXTPROC_DLLS=/u01/lib/list.so")
      ^^^^^^^^^^^^^^^^^^^^ This line enables calling of external libs
      (PROGRAM = extproc)
    )
...

3)准备数据库对象

create or replace library c_list is '/u01/lib/list.so';
/
create or replace function host_list(v_directory in varchar2) return varchar2 as external
  name "list_dir"
  library c_list
  language c
  parameters (v_directory string, return string);
/

最后使用:

declare 
  v_ret varchar2(32000);
begin
  v_ret := host_list('/u01');
  dbms_output.put_line(v_ret);
end;
/

你当然可以修改C和字符串的后处理来满足你的需要。

问好

注:您可以将结果转换为表，如下所示

create or replace package sysop as
  type file_list_rec is record(filename varchar2(1024));
  type file_list is table of file_list_rec;
  function ls(v_directory varchar2) return file_list pipelined;
end;
/
create or replace package body sysop as
  function ls(v_directory varchar2) return file_list pipelined is
    rec file_list_rec;
    v_host_list varchar2(32000) := '';
  begin    
    v_host_list := host_list(v_directory);
    for file in (
      select regexp_substr(v_host_list, '[^'||chr(10)||']+', 1, level) 
        from dual
          connect by 
            regexp_substr(v_host_list, '[^'||chr(10)||']+', 1, level) is not null)
      loop         
        pipe row (file);
      end loop;         
     return;
  end ls;
end sysop;
/

并像这样使用:

select * from table(sysop.ls('/u01')) order by 1;