Home » SQL & PL/SQL » SQL & PL/SQL » Writing multiple Files using UTL_FILE (Oracle 11g, Windows xp)
Writing multiple Files using UTL_FILE [message #563671] Thu, 16 August 2012 07:57 Go to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Hi,
I'm trying to read the data from flat file and write data into multiple files based on the condition. The value of each line is checked with the FlagTable (Id NUMBER, FlagType VARCHAR(25), Flag CHAR(1)), If the Flag is True then new file has to be created and corresponding line has to be moved into new file otherwise it has to continue with the same file.

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;


Appreciate your valuable inputs on it.

Regards,
Lokesh
Re: Writing multiple Files using UTL_FILE [message #563676 is a reply to message #563671] Thu, 16 August 2012 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');


This is just stupid, remove it and read WHEN OTHERS.

Quote:
If the Flag is True then new file has to be created and corresponding line has to be moved into new file otherwise it has to continue with the same file.


Then just insert in the loop that read (and write) (pseudo-code):
if flag then 
  utl_file.fclose(OutFile);  
  OutFile := utl_file.fopen('ORALOAD', 'new file', 'w');
end if;

Regards
Michel
Re: Writing multiple Files using UTL_FILE [message #563688 is a reply to message #563676] Thu, 16 August 2012 08:49 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Michel for your quick response.

Based on your input, code was modified, but values are being overwritten.

         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;


Please let me know, where I'm going wrong.

Regards,
Lokesh
Re: Writing multiple Files using UTL_FILE [message #563691 is a reply to message #563688] Thu, 16 August 2012 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>Please let me know, where I'm going wrong.
problem is with the "fopen"
Re: Writing multiple Files using UTL_FILE [message #563693 is a reply to message #563688] Thu, 16 August 2012 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Based on your input, code was modified, but values are being overwritten.


I don't understand what you mean.
As I understood your issue, but I could be wrong, when you encounter a flag Y then you stop writing in the current file and write in another one.
In this correct?
If not than explain clearer what you want.

Regards
Michel
Re: Writing multiple Files using UTL_FILE [message #563700 is a reply to message #563693] Thu, 16 August 2012 09:37 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Procedure has to read line by line in flat file. If the first 3 values in the line is like GP2, ND1, ND2, PT1, XR2.... and if there are entry of this in FlagTable then it has to create new file (No duplicate files)for each of it and move the records / line to respective files.
If the first 3 character doesn't match with any of the records in FlagTable then all the records/lines should be moved into one file.
Example : Content of Flat file
ND1T00XNDCNON0080                                
ND2105C@B007817058051995010120391231a0000000000000000000000000000 
XR1    0000000179CONVERSION               
PT1006    ON00014     
XR195010120391231P0012 19950101    0000000179CONVERSION  
ND2995010120391231a000000000000000000
XR2    0000000416PP C941995010120391231PP0012 19950101                              
GP206   0120391231a000           
GP2061231a00000000000000 1231a0000000000000              


Please let me know, if it is clear.

Regards,
Lokesh



Re: Writing multiple Files using UTL_FILE [message #563702 is a reply to message #563700] Thu, 16 August 2012 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not for me maybe for another one.
In your example, in which file(s) will go the lines?

Regards
Michel
Re: Writing multiple Files using UTL_FILE [message #563707 is a reply to message #563702] Thu, 16 August 2012 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>The value of each line is checked with the FlagTable
We don't have any data for this table


based upon posted sample data, what should be expected & desired results?
Re: Writing multiple Files using UTL_FILE [message #563712 is a reply to message #563707] Thu, 16 August 2012 10:09 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Blackswan for your response.

Below given are the values for FlagTable
Id	Flagtype Flag
1	ND1	 Y
2	ND2	 Y
3	GP2	
4	XR1	 Y
5	XR2	
6	PT1	



Below given are the possible output files and their contents

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


Please let me know, if more details are required

Regards,
Lokesh
Re: Writing multiple Files using UTL_FILE [message #563713 is a reply to message #563712] Thu, 16 August 2012 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then open a file for each type, test the type of the line after reading it and write it to the appropriate file.
Something like (pseudo-code):
open  in
open out1
open out2
open out3
...
open outelse
loop while there is line in "in"
  case when type is t1 then write into out1
       when type is t2 then write into out2
       else write into outelse
end loop
close outelse
...
close out3
close out2
close out1
close in

Regards
Michel

[Updated on: Thu, 16 August 2012 10:20]

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #563835 is a reply to message #563713] Fri, 17 August 2012 06:57 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Michel for the suggestion.
But is it possible to create the FileHandler dynamically i.e out1, out2, out3.....outn ?

Also please let me know, whether there is any limit to open number of filehandler in a procedure.

Regards,
Lokesh

[Updated on: Fri, 17 August 2012 07:13]

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #563846 is a reply to message #563835] Fri, 17 August 2012 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>But is it possible to create the FileHandler dynamically i.e out1, out2, out3.....outn ?
yes
>Also please let me know, whether there is any limit to open number of filehandler in a procedure.
no practical limit
Re: Writing multiple Files using UTL_FILE [message #563855 is a reply to message #563846] Fri, 17 August 2012 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For the second question (BlackSwan already answered the first one), you may encounter an OS limit given to oracle user (I don't know if this is possible on Windows, it is on Unix OS).

Regards
Michel
Re: Writing multiple Files using UTL_FILE [message #563997 is a reply to message #563855] Sun, 19 August 2012 13:36 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Blackswan and Michel for the response.
Can you please help me (logic) to create filehandler dynamically.
Also if filehandler opened for writting and not closed (by mistake) at the end will lock the file until the connection to db is closed. Is there any better way to unlock the file.

Regards,
Lokesh
Re: Writing multiple Files using UTL_FILE [message #563998 is a reply to message #563997] Sun, 19 August 2012 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if filehandler opened for writting and not closed (by mistake) at the end will lock the file until the connection to db is closed.


Yes.

Quote:
Is there any better way to unlock the file.


Call utl_file.fclose on all the opened files or utl_file.fclose_all if you are sure the session didn't open another file for another task.

Regards
Michel

[Updated on: Sun, 19 August 2012 14:47]

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #564002 is a reply to message #563998] Sun, 19 August 2012 15:22 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Michel.

I found code to dynamically open and write multiple files from the orafaq.
I have modified the same code to write the employee details based on department code.
I have hard coded for department = 10 (J = 1).
Can you please help in creating for all the department i.e for each department new file has to be created having respective employee details.
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 ;

Regards,
Lokesh

[Updated on: Sun, 19 August 2012 23:59] by Moderator

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #564006 is a reply to message #564002] Sun, 19 August 2012 15:43 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
WOW!
arrays, temporary tables, dynamic SQL & convoluted logic for such a simple task.
Only 1 file at a time ever needs to be open, if the records are processed in an intelligent order.

Re: Writing multiple Files using UTL_FILE [message #564015 is a reply to message #564006] Mon, 20 August 2012 07:11 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Blackswan,
Can you please suggest better logic / way of doing it.

Regards,
Lokesh
Re: Writing multiple Files using UTL_FILE [message #564016 is a reply to message #564015] Mon, 20 August 2012 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read the data in the right order.
You want one file per dept.
So order the data by dept.
Then you only need one file open at a time.
It's not complicated.
Re: Writing multiple Files using UTL_FILE [message #564019 is a reply to message #564016] Mon, 20 August 2012 07:43 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks cookiemonster for your response.
Do you mean to say that order the data by dept and open one file handler at a time and is it possible to do it without using VARRAY?
I tried to use one file handler, but data was overwritten everytime.
So could you please share pseudo code or logic how to do that.

Regards,
Lokesh
Re: Writing multiple Files using UTL_FILE [message #564023 is a reply to message #564019] Mon, 20 August 2012 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is it possible to do it without using VARRAY?


Yes.

Quote:
I tried to use one file handler, but data was overwritten everytime


Close the file before opening the next one.
You have to realize that we have ONLY what you post and so saying "I tried to use one file handler, but data was overwritten everytime" does not help us in any way to know what you did and got.
Post the COMPLETE AND REAL thing you executed.

Regards
Michel

[Updated on: Mon, 20 August 2012 08:21]

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #564076 is a reply to message #564023] Tue, 21 August 2012 05:21 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Blackswan, cookiemonster and Michel for your valuable suggestions.
Based on the input and my understanding. I created below given procedure.
Please check and let me know, if you find any issue in it.

Also now it is ordered by deptno, What if the record that are written to be in file as to be in different order say for example descending order by Hiredate.

Please let me know if you can figure out.

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;


Regards,
Lokesh

[Updated on: Tue, 21 August 2012 05:32]

Report message to a moderator

Re: Writing multiple Files using UTL_FILE [message #564080 is a reply to message #564076] Tue, 21 August 2012 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do as I previously said but why do you want such a silly thing? You like to uselessly complexify the code?

Regards
Michel
Re: Writing multiple Files using UTL_FILE [message #564099 is a reply to message #564076] Tue, 21 August 2012 07:05 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
lokimisc wrote on Tue, 21 August 2012 11:21
.
Also now it is ordered by deptno, What if the record that are written to be in file as to be in different order say for example descending order by Hiredate.

Then order the data by deptno, hiredate desc.
That should be obvious.
Re: Writing multiple Files using UTL_FILE [message #567530 is a reply to message #564076] Wed, 03 October 2012 02:38 Go to previous message
lokimisc
Messages: 72
Registered: February 2008
Member
Here is the Change request to the above requirement.
There is an table File_Action
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


Based on the action and filter condition, corresponding records has to be moved to their respective filename. (Assume that there is all the records in the default file based on the action in file_action table either the records has to be copied or moved out from default file to non default file)

Eg: If File_action has values (Empno = 250; filter = Deptno IN (10,30,50); Action = COPY; Priority = 1; FileName = AAA )
Above condition should create 2 files - one file deptemp_AAA.txt having only records of empno 250 who are in deptno 10,30 and 50 and all other employees including the above should be created in another file (default) - deptemp.txt

Similarly

If File_action has values (Empno = 250; filter = Deptno IN (10,30,50); Action = MOVE; Priority = 1; FileName = AAA )
Above condition should create 2 files - one file deptemp_AAA.txt having only records of empno 250 who are in deptno 10,30 and 50 and all other employees EXCLUDING the above should be created in another file (default) - deptemp.txt

IF action = EXCLUDE then corresponding records should not appear both in default file as well as deptemp_AAA.txt file

So I modified the existing query as below and in the loop each record is checked with the action and based on it either it will move to default file or non default 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


Challenge:
For the same empno, if there are 2 MOVE action then
Empno = 250; filter = Deptno IN (10,30,50,60,70,80); Action = MOVE; Priority = 1;FileName = AAA
Empno = 250; filter = Deptno IN (70,80,90,100); Action = MOVE; Priority = 2; FileName = BBB

In the above case, it should create 3 files
deptemp_AAA.txt file should have all the records matching Deptno IN (10,30,50,60,70,80). since its priority is 1
deptemp_BBB.txt file whose priority is 2, should have ONLY records DEPTNO IN (90, 100) as deptno (70,80) are already moved out.

I hope requirement is clear.

Please let me know the logic, how to handle it.

Regards,
Lokesh
Previous Topic: Materialized view recreate
Next Topic: Dropping unused columns in a partition
Goto Forum:
  


Current Time: Tue Sep 16 03:12:55 CDT 2014

Total time taken to generate the page: 0.05499 seconds