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_idReceived on Sat Mar 19 2005 - 03:54:15 CST
|| '|' || 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;
![]() |
![]() |