this link may be helpful
http://asktom.oracle.com/pls/ask/f?p=4950:8:1116029::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:235814350980,
"sien" <linda_68_at_hotmail.com> wrote in message
news:31d3e92a.0207302229.1f836a11_at_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 - 08:22:04 CDT
