CREATE OR REPLACE PROCEDURE rw_demo (File_In VARCHAR2, File_out VARCHAR2) IS
 InFile   utl_file.file_type;
 OutFile  utl_file.file_type;
 vNewLine VARCHAR2(4000);
 i        PLS_INTEGER;
 j        PLS_INTEGER := 0;
 SeekFlag BOOLEAN := TRUE;
 vOutfile VARCHAR2(100) := 'output.txt';
 vInfile VARCHAR2(100) := 'Input.txt';
BEGIN
  -- open a file to read
  InFile := utl_file.fopen('ORALOAD', vInfile,'r');
  -- open a file to write
  OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');

  -- if the file to read was successfully opened
  IF utl_file.is_open(InFile) THEN
    -- loop through each line in the file
    LOOP
      BEGIN
        utl_file.get_line(InFile, vNewLine);
        dbms_output.put_line(vNewLine);
        i := utl_file.fgetpos(InFile);
        dbms_output.put_line(TO_CHAR(i));
        --dbms_output.put_line('loki');
/*
        --- I need to check the value (line) of input with the Flag table (Id, FlagType, Flag), 
        -- if there are entries in Flag table then new file has to be created. 
        -- Below given is only for GP2 type similarly there might be PA, PA2.....
        
        IF vNewLine LIKE 'GP2%' and FlagTable.Flag = 'Y'
        THEN
          vOutfile := 'output_GP2.txt';
          NewOutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');
          ELSE
          vOutfile := 'output.txt';
          OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');                         
        END IF;

*/        
         --OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');   
        utl_file.put_line(OutFile, vNewLine, FALSE);
        utl_file.fflush(OutFile);        
/*
        IF SeekFlag = TRUE THEN
          utl_file.fseek(InFile, NULL, -30);
          SeekFlag := FALSE;
        END IF;
  */      
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    COMMIT;
  END IF;
  utl_file.fclose(InFile);
  utl_file.fclose(OutFile);
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END rw_demo;

         IF vNewLine LIKE 'GP2%' AND FLAG = 'Y'
        THEN
          utl_file.fclose(OutFile);  
          vOutfile := 'outputnew.txt';
          OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');
          utl_file.put_line(OutFile, vNewLine, FALSE);
          utl_file.fflush(OutFile);
          ELSE
          --utl_file.fclose(NewOutFile); 
          vOutfile := 'output.txt';
          OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');                         
          utl_file.put_line(OutFile, vNewLine, FALSE);
          utl_file.fflush(OutFile);
        END IF;

ND1T00XNDCNON0080                                
ND2105C@B007817058051995010120391231a0000000000000000000000000000 
XR1    0000000179CONVERSION               
PT1006    ON00014     
XR195010120391231P0012 19950101    0000000179CONVERSION  
ND2995010120391231a000000000000000000
XR2    0000000416PP C941995010120391231PP0012 19950101                              
GP206   0120391231a000           
GP2061231a00000000000000 1231a0000000000000              

Id	Flagtype Flag
1	ND1	 Y
2	ND2	 Y
3	GP2	
4	XR1	 Y
5	XR2	
6	PT1	

BP_ND1.txt:
ND1T00XNDCNON0080                                
ND151995010120391231a0000000000000000000000000000XNDCNON0080                          

BP_ND2.txt:
ND2105C@B007817058051995010120391231a0000000000000000000000000000 
ND2995010120391231a000000000000000000

BP_XR1.txt:
XR1    0000000179CONVERSION               
XR195010120391231P0012 19950101    0000000179CONVERSION  

BP.txt:
PT1006    ON00014     
XR2    0000000416PP C941995010120391231PP0012 19950101                              
GP206   0120391231a000           
GP2061231a00000000000000 1231a0000000000000

create or replace
PROCEDURE multiplefilewriter_new AS
--DECLARE
  TYPE number_array IS VARRAY(10000) OF NUMBER;
  TYPE string_array IS VARRAY(10000) OF VARCHAR2(100);
  TYPE date_array IS VARRAY(10000) OF DATE;
  TYPE v_file_array IS VARRAY(10000) OF utl_file.FILE_TYPE;

TYPE D_EMP IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
   T_EMP D_EMP;

  a_first_name   string_array;
  a_hire_date    date_array;
  a_employees_id number_array;

  v_file	   v_file_array;
  v_file_name string_array;
  v_sql VARCHAR2(2000);
  v_fname VARCHAR2(2000);
  v_cntfilename NUMBER := 0;
  j NUMBER := 0;

  CURSOR c2 IS
	 SELECT Empno,
		Hiredate,
		Ename
	   FROM emp;

  CURSOR c_filehandler IS
  SELECT filehandler FROM FILENAME_TEMP;
  
  vs_message VARCHAR2(500);
   SeekFlag BOOLEAN := TRUE;

BEGIN

  OPEN c2;
  FETCH c2 BULK COLLECT
	 INTO a_employees_id,
	      a_hire_date,
	      a_first_name;

  CLOSE c2;

  -- intialize arrays:
  v_file_name := string_array();
  v_file	   := v_file_array();
  
v_sql := '(SELECT e.* FROM EMP e, dept d where e.deptno = d.deptno and d.deptno = 10)';  
  
  EXECUTE IMMEDIATE v_sql BULK COLLECT INTO T_EMP;


		FOR i IN t_emp.first .. t_emp.last LOOP
  
       v_fname := '';   
  
    SELECT DNAME||'_'||DEPTNO
    INTO   v_fname
    FROM DEPT WHERE deptno = t_emp(i).deptno;

	 -- extend arrays:
	 v_file_name.EXTEND;
	 v_file.EXTEND;
   --v_file_name(i) := a_first_name(i) ||'_'||v_fname||'.txt';
   v_file_name(i) := v_fname||'.txt';
    
   SELECT count(1)
   INTO   v_cntfilename
   FROM   filename_temp
   WHERE  filename = v_file_name(i) ;

  IF v_cntfilename < 1
  THEN
    INSERT INTO filename_temp (filename,filehandler) VALUES(v_file_name(i), NULL );    
    --dbms_output.put_line(v_file_name(i));
    	 v_file(i) := utl_file.fopen('ORALOAD', v_file_name(i), 'W');
        utl_file.put_line(v_file(i), 
t_emp(i).deptno ||','||t_emp(i).ename ||','||t_emp(i).empno ||','||t_emp(i).hiredate ||
','||t_emp(i).job);
     utl_file.fflush(v_file(i));
      
  ELSE
  
    utl_file.put_line(v_file(j), 
t_emp(i).deptno ||','||t_emp(i).ename ||','||t_emp(i).empno ||','||t_emp(i).hiredate ||
','||t_emp(i).job);
utl_file.fflush(v_file(j));

END IF;
j := 1;  /* HARD CODING HERE SO THAT IT IS ALWALYS first opened file handler. Need to change */
   
  END LOOP;
  
  DELETE FROM FILENAME_TEMP;
  
END ;

CREATE OR REPLACE PROCEDURE rw_demo_NEW (File_In VARCHAR2, File_out VARCHAR2) IS
   --InFile   utl_file.file_type;
   OutFile  utl_file.file_type;
   SeekFlag BOOLEAN := TRUE;
   vOutfile VARCHAR2(100);
   deptname VARCHAR(50) := 'NEW';
   pos    NUMBER;
   
  CURSOR c2 IS SELECT   Empno,
                        Hiredate,
                        Ename,
                        dept.deptno AS deptno,
                        dept.dname AS dname
               FROM     emp emp, dept dept
               WHERE    EMP.deptno = dept.deptno
               --AND      EMP.deptno = 40
               ORDER BY dept.deptno;
 
BEGIN
  FOR i IN C2
  LOOP
  IF i.dname <> deptname
  THEN
    vOutfile := i.dname||'_'||i.deptno||'.txt';
    --dbms_output.put_line('filename - '||vOutfile);
  IF utl_file.is_open(OutFile)
  THEN    
    utl_file.fclose(OutFile);
    OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');
    utl_file.put_line(OutFile,i.empno||', '||i.hiredate||', '||i.ename||', '||i.deptno||', '||i.dname);
    utl_file.fflush(OutFile);
    deptname := i.dname;
    dbms_output.put_line(i.empno||', '||i.hiredate||', '||i.ename||', '||i.deptno||', '||i.dname);
    dbms_output.put_line('cur - '||i.dname||' deptname - '||deptname);  

  ELSE
    -- open a file to write
    OutFile := utl_file.fopen('ORALOAD', vOutfile, 'w');
    utl_file.put_line(OutFile,i.empno||', '||i.hiredate||', '||i.ename||', '||i.deptno||', '||i.dname);
    utl_file.fflush(OutFile);
    deptname := i.dname;
    dbms_output.put_line(i.empno||', '||i.hiredate||', '||i.ename||', '||i.deptno||', '||i.dname);
    dbms_output.put_line('cur - '||i.dname||' deptname - '||deptname);    
  END IF;
  
  ELSE     
     IF utl_file.is_open(OutFile)
     THEN          
      utl_file.put_line(OutFile,i.empno||', '||i.hiredate||', '||i.ename||', '||i.deptno||', '||i.dname);
      utl_file.fflush(OutFile);
      deptname := i.dname;
    END IF;
    
  END IF;
  END LOOP;
  utl_file.fclose(OutFile);
END rw_demo_NEW;

Name	 DataType	Comments
Empno	 NUMBER	        Foreign key to Emp table
Filter	 VARCHAR2	Filtering condition like hiredate <=SYSDATE, deptno in (10,30,50)....
Action	 VARCHAR2	Valid values - COPY, MOVE, EXCLUDE
Priority NUMBER         Valid values - 1,2,3,4.....
FileName VARCHAR2       Name of the file

SELECT  Empno,
        Hiredate,
        Ename,
        dept.deptno AS deptno,
        dept.dname AS dname,
        fa.action
        fa.priority
FROM     emp emp 
         INNER JOIN dept dept ON EMP.deptno = dept.deptno
         LEFT OUTER JOIN file_action fa ON EMP.empno = fa.empno