Re: Urgent help with UTL

From: AMARENDRA B NETTEM <nettama_at_charlie.cns.iit.edu>
Date: 1997/11/24
Message-ID: <347A0460.F2E@charlie.cns.iit.edu>#1/1

pawanputra_at_hotmail.com wrote:
>
> Friends,
> May be I am asking a trivial question but I need your help.
> I have to create FLAT files from ORACLE tables very frequently. At
> present I use SPOOL command to write to a flat file. Understand there
> is UTL_FILE package in PL/SQL 2.3 & later (?) which is used to write
> to operating System files. I do not have manuals with me so can't
> refer. I will appreciate if one of you could pl help me.
>
> Requirement :
> Table TEST has 1 million records.
> description of table TEST
>
> emp_id varchar2(10),
> fname varchar2(20),
> lname varchar2(20),
> address varchar2(30),
> zip number(10),
> Phone number(10)
> .....
> .....
> status Char(2)
>
> I want to create a comma delimited flat file and write the file in
> a particular directory on the UNIX box ( say /flat1/month). All the
> VARCHAR and CHAR fields are required to be RPADed and the NUMBER
> fields are required to be LPADed. Thanks for all your help.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

SET HEADING OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET LINESIZE 90
SET PAGESIZE 40
SPOOL /flat1/month.dat

SELECT RPAD(EMP_ID,10)||','||RPAD(FNAME,20)||','||
       RPAD(LNAME,20)||','||RPAD(ADDRESS,30)||','||
       LPAD(PHONE,10)||','||STATUS
      FROM TEST;

spool off

Save the above file with some name (test.sql) and this file from SQL*PLUS.

Hope this helps.

AMARENDRA

-- 
******************************************************************************
AMARENDRA B NETTEM			5039 N E River Road, Apt. 1A
Oracle Certified DBA (OCP)		NORRIDGE, IL 60656
Whittman-Hart Inc., 
311 South Wacker Drive, Suite 3500
Chicago, IL 60606.  

Ph.No. (708) 583 9870 (H)  
       (312) 913 6758 (W)  

 E-mail:nettama_at_charlie.cns.iit.edu,
        anettem_at_whittman-hart.com

Homepage: http://www.iit.edu/~nettama

****************************************************************************
Opinions are mine and do not necessarily reflect those of Whittman-hart
Inc.

Received on Mon Nov 24 1997 - 00:00:00 CST

Re: Urgent help with UTL_FILE