| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL UTL_FILE output
I'm just starting out with PL/SQL and would appreciate some advice. I've written a simple procedure to write some data out to a file. I would like the file to have fixed width fields, seperated by pipes. The data gets out to the file OK, but not fixed width. What can I do to correct that? Also, please point out my rookie mistakes.
--
Randy Harris
(tech at promail dot com)
CREATE OR REPLACE PROCEDURE E-test IS
CURSOR c1 IS
SELECT E.server_id, E.event_grad, E.pmt_refere,
E.USER023, E.APPROV_COD, E.PAYMENT_AM,
C.xcode, c.xstartdate,
P.xfirstname, P.xlastname, P.user017,P.country
FROM LMS_DATA.EVENT E, LMS_DATA.CLASS C, LMS_DATA.PERSON P
WHERE C.xlocator=E.xlocator
AND E.yuniquekey = P.yuniquekey
AND e.xstatus='E'
AND e.payment_am > 0
AND e.event_grad NOT IN ('TC','SS')
AND e.user024 IS NULL
AND E.server_id NOT IN ('1009543','1089787','62425')
AND TRUNC(e.xupdate) = '28-FEB-2005'
;
fhandle utl_file.file_type ;
BEGIN
fhandle := utl_file.fopen ( 'E:\Registrar', 'E-test.txt','W') ;
FOR o IN c1
LOOP
utl_file.put_line (fhandle,
o.server_id
|| '|' || o.event_grad
|| '|' || o.pmt_refere
|| '|' || o.user023
|| '|' || o.approv_cod
|| '|' || o.payment_am
|| '|' || o.xcode
|| '|' || o.xstartdate
|| '|' || o.xfirstname
|| '|' || o.xlastname
|| '|' || o.user017
|| '|' || UPPER(o.country)
);
-- Insert the records into the Log file
INSERT INTO EXTRACTLOG VALUES (
o.server_id,
o.event_grad,
o.pmt_refere,
o.user023,
o.approv_cod,
o.payment_am,
o.xcode,
o.xstartdate,
o.xfirstname,
o.xlastname,
o.user017,
UPPER(o.country),
SYSDATE) ;
COMMIT ;
END LOOP;
utl_file.fclose (fhandle);
EXCEPTION
[deleted for brevity]
END;
Received on Sat Mar 19 2005 - 03:54:15 CST
![]() |
![]() |