reading files in a folder

[swakoo]

hmm...
i got a problem..
i am required to read a file in a folder.
that should be quite easy but the problem is i don't know the file name.
what i have is the folder name and extention.(might have more than 1 file with the same ext but i want to read every file)
is there any way to do it like a cursor? or smth like tat?

my qns is is it possible?
if it is, how?
thanks

 

[ScottMattes]

I think that you will have to use Java. Though I would love to be proved wrong and that there is a way in PL/SQL

 

[mike]

I don't know how to do it using just PL/SQL either.

If it's a concurrent request and if the file is on a UNIX box, (I know - 2 big if's that might not be true) I would use a shell script to:

1. Append all files with that extension to a temporary file with a known name
2. Open SQL Plus to call a PL/SQL package that needs to read the temporary file.
3. Remove the temporary file so it's ready for the next run.

 

[swakoo]

hmm...
i am new to pl/sql.. is it possible that someone write the script for me? and explain on it to me?
thanks in advance..

 

[Bo Pedersen]

To the best of my knowledge it cannot be done in pure PL/SQL. You can either write a java store procedure, that returns the files or you can write a java stored procedure for calling a host command. I have implemented the later. This enables me to retrieve a string containing all the files and then break it up:

declare
  status number;
  cmd_output varchar2(32000);
  start_of_line number :=1;
  end_of_line number;
begin
  status := os_cmd('cmd /c dir /b c:\dev\scripts\*.sql', 'sync', cmd_output);

  end_of_line := instr(cmd_output, chr(10));
  while end_of_line != 0 loop
    dbms_output.put_line(substr(cmd_output, start_of_line,  end_of_line-start_of_line));
    start_of_line := end_of_line + 1;
    end_of_line := instr(cmd_output, chr(10), start_of_line);
  end loop;
end;
/

This will print out all sql-file in the given directory. Note that only OS commands can be called. Dir is not an OS command but a built-in command in the command interpreter. That's why I have wrapped the dir inside a cmd /c command. On Unix one can use ls or similar.

My java class is declared like:

create or replace and compile java source named util as
import java.io.*;
import java.util.*;

public class util
{
  //
  // Executes an operating system command. The command shall be fully qualified.
  // The Java connects with silent login. No environment set-up files are run
  // and no path is set. The mode can be "sync" or "async" for respectively
  // synchronous and asynchronous execution
  //
  // Requires at least some of the following permissions:
  //   call dbms_java.grant_permission( '<user>', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );
  //   call dbms_java.grant_permission( '<user>', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '' );
  //   call dbms_java.grant_permission( '<user>', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );
  //
  static public int OSCmd(String cmd, String mode, String[] output)
  throws IOException, InterruptedException
  {
    System.out.println("OSCmd "+cmd+" ("+mode+")");

    output[0]="";

    // start command
    Process proc = Runtime.getRuntime().exec(cmd);

    if (mode.equals("sync"))
    {
      // get commands stdout and stderr
      InputStream stdout = proc.getInputStream();
      InputStream stderr = proc.getErrorStream();
      String str;

      // Stdout
      BufferedReader br = new BufferedReader(new InputStreamReader(stdout));
      while ((str = br.readLine()) != null)
        output[0]+=str+"\n";
      br.close();

      // Stderr
      br = new BufferedReader(new InputStreamReader(stderr));
      while ((str = br.readLine()) != null)
        output[0]+=str+"\n";
      br.close();

      // wait for command to terminate
      proc.waitFor();
      return proc.exitValue();
    }
    return 0;
  }
}
;

And the wrapper:

create or replace function os_cmd(p_cmd varchar2, p_mode varchar2, p_output in out varchar2) return number
as language java
name 'util.OSCmd(java.lang.String, java.lang.String, java.lang.String[]) return int';
/

Note the needed permissions stated in the comment.