--Loading the files into the directory and processing all using external table

--1.table for storing all file info present in the directory
create table file_list(file_name varchar2(100),file_status varchar2(1));

--Actual table which hold the final data along with the file name
 CREATE TABLE emp_temp_file
  (
    EMPNO     VARCHAR2(50) ,
    ENAME     VARCHAR2(50) ,
    JOB       VARCHAR2(50) ,
    MGR       VARCHAR2(50) ,
    HIREDATE  VARCHAR2(50) ,
    SAL       VARCHAR2(50) ,
    COMM      VARCHAR2(50) ,
    DEPTNO    VARCHAR2(50) ,
    FILE_NAME VARCHAR2(50)
  );


--2.create a directory for the actual location where files will be stored
CREATE OR REPLACE DIRECTORY  temp_dir AS '/home/oracle/files/';

--3.external table for loading the emp csv file(make sure atleast one file like emp structure must be there while creation  )
CREATE TABLE ext_emp_table
  (
    c1  VARCHAR2(100),
    c2  VARCHAR2(100),
    c3  VARCHAR2(100),
    c4  VARCHAR2(100),
    c5  VARCHAR2(100),
    c6  VARCHAR2(100),
    c7  VARCHAR2(100),
    c8  VARCHAR2(100)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE oracle_loader
    DEFAULT DIRECTORY TEMP_DIR
    ACCESS PARAMETERS ( fields terminated BY ',' missing field VALUES are NULL )
    LOCATION ('emp_deptno_10.txt'));
	
--checking whether it got created successfully or not 
	select * from ext_emp_table; 

--4.Searching the specified directory and getting all the files and storing the filenames into a table
--java class to load the file name into the table

SET define OFF;
create or replace and compile java source named FileDirList as
       import java.io.*;
       import java.sql.*;
       import oracle.sql.*;
       import oracle.jdbc.*;
       public class FileDirList
		{
		   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];
				File temp=new File(path,element);
				if(temp.isFile()){
				   #sql{ Insert into file_LIST(file_name,file_status) values (:element,'N') };
				}
			  }
		 }
	   };
	   
--Procedure for loading the table with the file name present in the directory
create or replace procedure get_dir_list( p_directory in varchar2 )
  as language java
  NAME 'FileDirList.getList( java.lang.String )';
    
--4.Place the sample file into the directory(actual location) 
--For testing generation of sample csv file and put in the location /home/oracle/files/

select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno  file_name 
from emp where deptno=10;--emp_deptno_10.txt
select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno  file_name 
from emp where deptno=20;--emp_deptno_20.txt
select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno  file_name
 from emp where deptno=30;--emp_deptno_30.txt

 --5.Getting the actual content of the file to the actual table via external table
create or replace procedure load_file_info(p_file_path in varchar2) is 
BEGIN
DELETE FROM file_list;
COMMIT;
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', ''||p_file_path||''||'*', 'read' );
get_dir_list(''||p_file_path||'');
  FOR i IN (SELECT * FROM file_list where file_name like '%.txt%' )
  LOOP
    EXECUTE IMMEDIATE 'alter table ext_emp_table location ('''||i.file_name||''')';
    
    INSERT INTO emp_temp_file(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,file_name) 
    SELECT t.*,i.file_name FROM ext_emp_table t;
    COMMIT;
    END loop;
end load_file_info;


--6.Final execution of the procedure for getting the data
--execute the procedure to load the data
exec load_file_info('/home/oracle/files/');

--Checking of actual table data
select * from emp_temp_file;