specify layout in txt file using utl_file

From: sien <linda_68_at_hotmail.com>
Date: 30 Jul 2002 23:29:55 -0700
Message-ID: <31d3e92a.0207302229.1f836a11@posting.google.com>

Hi!

I wrote a procedure to create a text file and put the result of a select statement to that file. The problem is that I don't know how to determine the layout in the txt file.

This is my procedure:

CREATE OR REPLACE PROCEDURE ROB001_D_ROOM (PathName varchar2) IS

 cursor RRoom_cur is
 SELECT FM_OBJ_1.OBJ_NUM LEVELNUMBER, FM_OBJ.OBJ_NUM, Count(FM_OBJ.OBJ_ID) CountOfOBJ_ID
 FROM FM_OBJ,VALIDOBJECTS, FM_OBJ FM_OBJ_1  WHERE FM_OBJ.OBJ_ID = VALIDOBJECTS.OBJ_ID AND FM_OBJ.OBJ_VORID = FM_OBJ_1.OBJ_ID
 GROUP BY FM_OBJ_1.OBJ_NUM, FM_OBJ.OBJ_NUM, FM_OBJ_1.OBJTYP_ID, FM_OBJ.OBJ_VORID, FM_OBJ.OBJTYP_ID
 HAVING (((Count(FM_OBJ.OBJ_ID))>1) AND ((FM_OBJ_1.OBJTYP_ID)=5) AND ((FM_OBJ.OBJTYP_ID)=8)); --I/O Variables

 in_file_handle utl_file.file_type;
 ObjNum fm_obj.obj_num%type;
 LevelNum fm_obj.obj_num%type;
 ObjCount number(6);   

 BEGIN
  open RRoom_cur;
  dbms_output.enable(30000);
  dbms_output.new_line;

  in_file_handle := utl_file.fopen (PathName, 'ROB001_D_ROOM.txt', 'w');

  UTL_FILE.PUT_LINE(in_file_handle,'Level' || '      ' || 'Object
Number' || '      ' ||  'Object Count');

  UTL_FILE.PUT_LINE(in_file_handle,'');     

  loop
   fetch RRoom_cur into LevelNum,ObjNum,ObjCount;    exit when RRoom_cur%NOTFOUND;
   dbms_output.put_line(ObjNum);

   UTL_FILE.PUT_line(in_file_handle,Levelnum || '         ' || objnum
|| '               ' || ObjCount);

  end loop;   

  close RRoom_cur;
  UTL_FILE.FCLOSE(in_file_Handle);

exception

    when utl_file.invalid_path then
        raise_application_error(-20001,'invalid path');
    when utl_file.invalid_mode then
        raise_application_error(-20002,'invalid_mode');
    when utl_file.invalid_filehandle then
        raise_application_error(-20002,'invalid_filehandle');
    when utl_file.invalid_operation then
        raise_application_error(-20003,'invalid_operation');
    when utl_file.read_error then
        raise_application_error(-20004,'read_error');
    when utl_file.write_error then
        raise_application_error(-20005,'write_error');
    when utl_file.internal_error then
        raise_application_error(-20006,'internal_error');

END; And this is the txt file:

Level Object Number Object Count

00TESA02         A020               2
00TESA02                        2
03GILA00         AA01               2
03GILA00         AA12               2
91GENA00         A07               2
91GENA00         A22B               2
91GENA00         A29A               2
91GENA00         A32               2

Anyone can help me please??

Thanks in advance. Received on Wed Jul 31 2002 - 01:29:55 CDT