Re: Urgent help with UTL

From: Doug Swanson <104155.2074_at_compuserve.com>
Date: 1997/11/25
Message-ID: <01bcf9d9$e0a10640$af5e6580@j553fac.UMN.EDU>#1/1

We use the utl_file package to create flat files and it works pretty slick....I'm not sure how the method we use will be with 1 million records but...
1.)I believe in the init.ora file there is a secion for defining which directories are valid.
2.)make sure the directories exist
3.)Create a procedure

create or replace myproc as

cursor c1 is select rpad(lname)||','||rpad(fname) lc_write from test;

lv_file UTL_FILE.FILE_TYPE;
lv_path varchar2(100);
lv_name varchar2(100);
lv_mode char(1);

begin
/*set the path and the file name*/ - path as defined on the unix box....

lv_path:='/flat1/month;
lv_name:='emp.txt';
lv_mode:='w';

/*Open the file for writing*/
lv_file:=UTL_FILE.FOPEN(lv_path,lv_name,lv_mode); /*loop through records*/
for c1rec in c1 loop

	/*write the line*/
	UTL_FILE.PUT_LINE(lv_file,c1rec.lc_write);

end loop;

/*Close the file*/
UTL_FILE.FCLOSE(lv_file);

end myproc;
Watch the null values when building your string... You may want to look at the dbms_job package for scheduling? your proc to run at a more opportune time...good luck.

doug

pawanputra_at_hotmail.com wrote in article <880398034.15742_at_dejanews.com>...
> 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
>
Received on Tue Nov 25 1997 - 00:00:00 CST

Re: Urgent help with UTL_FILE