PLSQL Script to Fetch all Files Names from FTP Server

If you have requirement that your ftp server has 4 files and you want to schedule import program to upload data in oracle staging tables from these files but usually import programs import files one by one and you also have to put the name of file from ftp.

To make it automate you have to find some way by which you can get all file names available in the server and run the import program from back end one by one for each file

For this you have to use below source code this source code will provide you the list of files names which is available in the directory path shared by you

Register these below objects one by one.

CREATE GLOBAL TEMPORARY TABLE XXDIRECTORY_FILELIST

       ( filename VARCHAR2(255) )

    ON COMMIT DELETE ROWS;

CREATE OR REPLACE PROCEDURE get_file_list(p_directory IN VARCHAR2) AS

LANGUAGE JAVA NAME 'ListDirectoryfiles.getList( java.lang.String )';

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListDirectoryfiles" AS

import java.io.*;

import java.sql.*;

public class ListDirectory

{

public static void getList(String directory)

                   throws SQLException

{

   File path = new File( directory );

   String[] list = path.list();

   String element;

     for(int i = 0; i < list.length; i++)

     {

         element = list[i];

         #sql { INSERT INTO XXDIRECTORY_FILELIST (filename)

                VALUES (:element) };

     }

 }

 };

/

After this you have to execute this procedure with file server path

begin

get_directory_list(‘/d01/apps/appl_top/file/infile/’);

end;

This procedure will insert all the files names in Table XXDIRECTORY_FILELIST.

Now query the this table and get the files names of all the files available in the directory.

Select filename from XXDIRECTORY_FILELIST;

Now you can create a procedure from backend and submit your import program from backend one by one based on the rows/files names fetching by this directory table.

Please share your comments if you like this post and pls email me if you are getting any issue in that.