Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL UTL_FILE output

PL/SQL UTL_FILE output

From: Randy Harris <randy_at_SpamFree.com>
Date: Sat, 19 Mar 2005 09:54:15 GMT
Message-ID: <bFS_d.11884$DW.2542@newssvr17.news.prodigy.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US