Java Beanshell and SQL Embedded Example in ODI

Scenario:
We need to get all the filenames inside a folder.
For Example:
Sample_Files (Folder)
      Sample_File.txt(File)
      Sample_File.csv(File)
      Sample_File.xls(File)

Output should be 'Sample_File.txt,Sample_File.csv,Sample_file.xls'

Steps:

Step1:
Create a procedure and select target technology as Oracle

Step2:
Copy below code and paste in target side and select technology as  Oracle and Logical Schema accordingly

<?
import java.io.File;
public class FileLists{
   public static String FileNames() {
     
      File f = null;
      String[] FileList;
   String fileNames="";
           
      try{     
        
         f = new File("<%=odiRef.getOption( "Dir" )%>");
         FileList = f.list();
         for(String FileName:FileList)
         {
           if(FileName.contains("."))
     {
   if (!fileNames.isEmpty())
    fileNames  = fileNames+','+ FileName ;
   else
    fileNames = FileName;
     }
         }
  
      }catch(Exception e){
         e.printStackTrace();
      }
    return fileNames;
   }
}
?>
select '<?=FileLists.FileNames()?>' from dual

Step 3:
Go to Option and create new option whose name is "Dir" , Data type as "Text" and Direct Value "<DIRECTORY_LOCATION>" (eg : C:/Sample_files)

Step 4:
Execute the procedure.
Completed Successfully!!!!!