Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Veersh.

Asked: October 19, 2016 - 5:18 am UTC

Last updated: October 20, 2016 - 4:21 am UTC

Version: NLSRTL Version 11.2.0.4.0 -Production

Viewed 1000+ times

You Asked

Hi Tom,

actually i created a job (it will run for every 5 mins)which will execute the procedure,In that procedure we are reading file list from directory and constructing a string which contains all the file names using java class.

Here my problem is some times string is returning null instead of file name list when ever job executes automatically.if u force the job it is working properly .

DROP JAVA SOURCE "DirectoryLister";

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirectoryLister" as import java.io.File;
import java.util.Arrays;
public class DirectoryLister
{
public static String getFileList(String idir, String sep)
{
File aDirectory = new File(idir);
File[] filesInDir = aDirectory.listFiles();
String result = "";
for ( int i=0; i<filesInDir.length; i++ )
{
if ( filesInDir[i].isFile()
&& !filesInDir[i].isHidden() )
{
result = result + sep + filesInDir[i].getName();
}
}
return result;
}
};

/


CREATE OR REPLACE PACKAGE BODY inter_file
AS
FUNCTION list_oracle_directory (
p_dir IN VARCHAR2,
p_sep IN VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'DirectoryLister.getFileList(java.lang.String, java.lang.String) return String';


CREATE OR REPLACE PACKAGE BODY interface_file
AS
FUNCTION list_oracle_directory (
p_dir IN VARCHAR2,
p_sep IN VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'DirectoryLister.getFileList(java.lang.String, java.lang.String) return String';

PROCEDURE load_nscc_file_from_directory
IS
cs_directory_in CONSTANT VARCHAR2 (20)
:= 'D:\Oracle\Connectors\DEV\N_FILES\IN' ;

cs_file_separator CONSTANT VARCHAR2 (10) := ';';
v_list_of_files VARCHAR2 (32767);


Err EXCEPTION;
BEGIN
v_list_of_files :=
file_util.list_oracle_directory (p_dir => cs_directory_in,
p_sep => cs_file_separator);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;

Please suggest me what i suppose to do,we need to change any db parameters or any special rights schema should have?

The schema which will run this job have all the rights on directory.

and Connor said...

Firstly - remove this:

EXCEPTION
WHEN OTHERS
THEN
NULL;
END;


because if you catch all errors, then we cant see whats going wrong. Once this is removed, if you are getting an error from the scheduler, it will be written to a trace file.

So try that first.

Can you show us the plsql you've used to schedule the job as well.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library