file exist

[steeve123]

I want to know if a file exist.
i want a function that tells me if a file exist

ex:
c:\toto.txt


declare
exis boolean;
begin
exis := file_exist('c:\toto.txt');
if exis = TRUE then
--yé!
end if;
end;


plaz help!

[Zaggy]

There are a few way you can handle this and they are:

1. use UTL_File
2. or use Java

UTL_File way:
- Requires the UTL_FILE_DIR to be set with all directries that you may access. If you need to add another directory the database must be bounced.

Java way:
- Requires you creating a OS command and this piece of code:

This tip comes from Dave Sienknecht, Programmer for Genesys Software Consulting in Sunnyvale, CA.

Calling Unix commands from PL/SQL isn't a new idea, but this technique allows the user or program to execute any given OS command, view the printed output, and capture a success/fail argument.

Directions:

Compile the java program.
Load it to the DB using LOADJAVA.
Create a function call spec to call the program.
Turn serveroutput and java output ON.

Function Call Spec:
----------------------------------------
CREATE OR REPLACE FUNCTION shellcmd (v_cmd IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'oscmd.mainrun(java.lang.String[]) return int';
/


Java Source:
----------------------------------------
import java.io.*;
import java.util.*;

public class oscmd{
static public int runCommand(String cmd)
throws IOException {

int vSubResult;
vSubResult = 0;

// set up list to capture command output lines
ArrayList list = new ArrayList();

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

// get command's output stream and
// put a buffered reader input stream on it
InputStream istr = proc.getInputStream();
BufferedReader br =
new BufferedReader(new InputStreamReader(istr));

// read output lines from command
String str;
while ((str = br.readLine()) != null)
list.add(str);

// wait for command to terminate
try {
proc.waitFor();
}
catch (InterruptedException e) {
System.err.println("process was interrupted");
vSubResult = 1;
}

// check its exit value
if (proc.exitValue() != 0) {
System.err.println("exit value was non-zero");
vSubResult = 1;
}

// close stream
br.close();

// read all result strings into variable outlist
String outlist1[] = (String[])list.toArray(new String[0]);

// display the output
for (int i = 0; i < outlist1.length; i++)
System.out.println(outlist1[i]);

return vSubResult;
}

public static int mainrun(String args[]) throws IOException {
try {
int vResult;

// run the given command
vResult = runCommand(args[0]);

// return 0 on success, 1 on failure
return vResult;
}
catch (IOException e) {
System.err.println(e);
return 1;
}
}
}