Java stored procedure returns nothing in Oracle Database

Question

I have a fairly simple stored java procedure in an oracle database. The intended purpose is to read the contents of a folder which resides on the Oracle server. If it encounters a folder it will step into the folder and write the name of the contents into a global temp table, and move on to the next folder. The Java procedure compiles fine and submits into the database with no issues. When it's called by a stored Oracle procedure it runs successfully as well. But produces no results into the global temp table. I am using TOAD and i'm not sure how to put a break or view the variables during run time so i'm kind of flying blind. And i'm admittedly not great a java.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED BALT_CHECK."WebDirList" AS
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;

public class WebDirList
{

public static void getList(String rootdirectory) throws SQLException
{

    File path = new File( rootdirectory );

    String[] rootDirList = path.list();
    String element;

    for( int x = 0; x < rootDirList.length; x++)
    {
        element = rootDirList[x];
        String newPath = rootdirectory + "/" + rootDirList[x] ; 
        File f = new File(newPath);

        if (f.isFile()){
        /* Do Nothing */
        } else {
        /*if it is a folder than load the subDirPath variable with the newPath variable  */
            File subDirPath = new File( newPath+"/");
            String[] subDirList = subDirPath.list();
            String efileName;

            for(int i = 0; i < subDirList.length; i++)
            {
                efileName = subDirList[i];
                String fpath = subDirPath + "/" + subDirList[i];
                File nf = new File(fpath);

                long len;
                Date date;

                String ftype;
                String sqlDate;

                SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss");

                if (f.isFile()) {

                    len = f.length();
                    date = new Date(f.lastModified());
                    sqlDate = df.format(date);

                    #sql { INSERT INTO WEB_DIRLIST (FILENAME, LENGTH,  CREATEDATE)
                     VALUES (:efileName, :len, to_date(:sqlDate, 'YYYY-MM-DD HH24:MI:SS')) };

                }else{
                /* Do nothing */
                }
            }

        }
    }   
}
}
/

Procedure is created as

CREATE OR REPLACE procedure BALT_CHECK.get_webdir_list( p_directory in varchar2)
as language java
name 'WebDirList.getList( java.lang.String )';
/

Procedure is called as

    exec get_webdir_list( '/transfer_edi/hs122/');

in the folder /transfer/edi/hs122/ are 10 sub directories each have between 1 and 100 items in them at any given time.

Answer 1

There were a few errors in this code that prevented it from writing to the database. Based on Yavor's suggestion of writing String variables to a temp table I was able to find that I had duplicated "/" on the file path e.g. (/transfer_edi/hs122//Acctg). I also found I had an incorrect data type on one of my columns in my data table that I was writing too. I also switched to a regular table instead of a global temp table which was deleting after commit. Again thanks Yavor. Regardless of all that I ended up re-writing the entire thing. I realized that I needed to traverse down the directory structure to get all the files so here is the final code that worked for me. Again i'm not a java guy so i'm sure this could be done better.

This link helped me quite a bit http://rosettacode.org/wiki/Walk_a_directory/Recursively#Java

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED BALT_CHECK."WebDocs" AS
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.lang.String;

public class WebDocs
{

public static long fileID;

public static void GetDocs(String rootdirectory) throws SQLException
{
    stepinto(rootdirectory);
}

public static void stepinto(String rootdirectory) throws SQLException
{
    File path = new File( rootdirectory );
    String[] DirList = path.list();       

     for( int x = 0; x < DirList.length; x++)
    {
        String newPath = rootdirectory  + DirList[x]; 
         if (newPath != null) {
            File f = new File(newPath);
            if (f.isDirectory()) {
             GetDocs(newPath +"/");
            }
            if (f.isFile()){
             WriteFile(f);    
            }else{
            }
        }
    }    
 }

public static void WriteFile(File file) throws SQLException
 {
    String fileName;
    String filePath; 
    String elementID;
    long len;
    Date date;
    String sqlDate;
    SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss");

    fileID = fileID + 1;

    elementID = String.valueOf(fileID);
    fileName = file.getName();
    filePath = file.getPath();
    len = file.length();
    date = new Date(file.lastModified());
    sqlDate = df.format(date);

#sql { INSERT INTO WEB_STATICDOCS (ID, FILE_NAME, FILE_SIZE,  CREATE_DATE, FILE_PATH)
         VALUES (:elementID, :fileName, :len, to_date(:sqlDate, 'YYYY-MM-DD HH24:MI:SS'), :filePath) };

 }        

 }

/

Oracle Stored Procedure

CREATE OR REPLACE procedure BALT_CHECK.getWebDocs( p_directory in varchar2)
as language java
name 'WebDocs.GetDocs( java.lang.String )';
/

Calling the stored Procedure

exec getWebDocs( '/transfer_edi/hs122/');

Answer 2

• I'm not sure how you check the results (same session or not). Do you perform commit somewhere? There are some specifics with global temp tables (there is option whether data is purged after commit or not). You may wish to initially try with permanent one until you sort out the problem.
• It may be useful if you add some logging (e.g. to another table). E.g. rootDirList.length may be a good indicator to check.

Some other remarks:

• The /* Do nothing */ branches in your if statements are adding additional noise. Good to remove them.
• Perhaps would be better to use .isDirectory() if you want to check if the paths is a directory (instead of isFile).