The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, sangita.

Asked: February 20, 2007 - 5:46 pm UTC

Last updated: September 28, 2007 - 4:59 pm UTC

Version: 9.2.0.8.0

Viewed 10K+ times! This question is

You Asked

Here is a simpler example
declare
f utl_file.file_type;
CRLF1 CONSTANT CHAR(2) := CHR(13)||CHR(10); -- Carriage Return+Line Feed
CRLF CONSTANT CHAR(1) := CHR(10); -- Line Feed

begin
f := utl_file.fopen('BTAM_DIR', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text'||CRLF);
utl_file.put_line(f, 'line two: more text'||CRLF);
utl_file.put_line(f, 'EOF'||CRLF1);
utl_file.fclose(f);
end;

The carriage return comes out as ^M even though i am creating the file on unix and there is no FTP involved. The only reason I sent my original code was for you to see if I was making any mistake while creating or appending the clob or while creating a file from it.
Thanks
Sangita

Hi Tom,

I am creating a flat file from the database using utl_file.

The first step is to format the data in way it is need into a clob and then create a file on unix using utl_file. However the unix folks processing the file cannot see the linefeed or carriage return.

SQL> desc extract
Name Null? Type
----------------------------------------- -------- -------------------
CLIENT_CD NOT NULL VARCHAR2(12)
FILE_ID NOT NULL NUMBER(20)
EXTRACT NOT NULL CLOB

PROCEDURE SSP_GENERATE_CLOB (p_client_cd in extract.client_cd%type,
p_recd_file_id in extract.received_file_id%type,
p_upd_user_id in extract.update_user_id%type)
AS
/******************************************************************************
NAME: AIP_CREATE_EXTRACT
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 01/15/2006 A268343 Created this package.
******************************************************************************/
-------------------------
-- Constants
-------------------------
c_method_nm CONSTANT TRANSACTION_LOG.METHOD_NM%TYPE := 'SSP_GENERATE_CLOB';
l_clob EXTRACT.EXTRACT%TYPE;
l_last_record varchar2(200) := 'EOF';
l_alloc_header varchar2(4000);
l_alloc_detail varchar2(4000);
l_ref_no varchar2(11);
l_alloc_cnt_in_trd number(4);
l_tot_commission_a trades.tot_commission_a%type;
l_file_header varchar2(500);
allc_cnt number := 0;
CRLF1 CONSTANT CHAR(2) := CHR(13)||CHR(10); -- Carriage Return+Line Feed
CRLF CONSTANT CHAR(1) := CHR(10); -- Line Feed
l_message_txt transaction_log.message_txt%type;
l_commission varchar2(30);--trades.tot_commission_a%type;
l_comm_code varchar2(1);
l_cusip_symbol varchar2(20);
-- l_master_acct_type trade_allocation.acct_type_c%type;
BEGIN
BEGIN
--Step 1. Create FTO header
l_file_header := ssp_get_file_header(p_client_cd,p_recd_file_id);
-- Step 2 Create the Allocation header / detail record for trades and trade allocations
-- Loop thru the trades for the given file
INSERT INTO btam_extract
(CLIENT_CD,
RECEIVED_FILE_ID,
EXTRACT,
CREATE_USER_ID,
CREATE_TMSTP,
UPDATE_USER_ID,
UPDATE_TMSTP )
values
( p_client_cd,p_recd_file_id, empty_clob(),p_upd_user_id,sysdate,p_upd_user_id,sysdate )
returning extract into l_clob;
-- Append the file header to the empty clob
dbms_lob.writeappend( l_clob,length(l_file_header|| CRLF),l_file_header|| CRLF );
-- Create The allocation group record. For format please refer to the documentation or
-- SDS / TSD for AutoAllocations.
For trd in (Select trd.CLIENT_CD ,trd.RECEIVED_FILE_ID, trd.TRADE_ID , trd.MASTER_FBSI_BRCH_C,
trd.MASTER_FBSI_BASE_C, trd.PRICE , trd.TRADE_DT, trd.SETTLEMENT_DT, trd.CUSP_N ,
trd.TICKER_NM, trd.TRADE_AWAY_IND,trd.EXECUTING_BROKER,trd.FBSI_ORDER_NUM ,trd.REF_ORDER_NUM,
trd.TRADE_QTY,trd.TRADE_STATUS_CD ,trd.TOT_COMMISSION_A,trd.ACTION_CD
from trades trd
where received_file_id = p_recd_file_id
and trade_status_cd = 'P')
LOOP

-- Create the trade header record. Format of this record can be found in the SDS or TSD
l_alloc_header := 'H'||'A'|| rpad(nvl(trd.master_fbsi_brch_c,' '),3,' ')||rpad(nvl(trd.master_fbsi_base_c,' '),6,' ')||' '||
to_char(trd.trade_dt,'yyyy-mm-dd')||l_cusip_symbol||
rpad(trd.action_cd,2,' ')||
' '||'P'||to_char(trd.settlement_dt,'yyyy-mm-dd')||
'A'||substr(trd.ref_order_num,2,11)||
lpad(l_alloc_cnt_in_trd,4,'0')||btam.SSP_GET_BTAM_FMT_NUMBER(trd.price,6,9)||rpad(' ',9,' ')|| btam.SSP_GET_BTAM_FMT_NUMBER(trd.trade_qty,12,5)||rpad(' ',3,' ')||
lpad(nvl(btam.SSP_GET_BTAM_FMT_NUMBER(l_commission,8,5),' '),13,' ')||rpad(nvl(l_comm_code,' '),1,' ')||
-- since all fields are optional after this, I am just adding space for the cumulative length
rpad(' ',36,' ')||rpad(nvl(substr(trd.executing_broker,1,4),' '),4,' ')||rpad(' ',4,' ')||
rpad(' ',27,' ');

dbms_lob.writeappend( l_clob, length(l_alloc_header || CRLF),l_alloc_header || CRLF );
-- create the allocation detail block
FOR allc in (SELECT allc.CLIENT_CD ,allc.RECEIVED_FILE_ID, allc.TRADE_ID, allc.ALLOC_FBSI_BRCH_C,
allc.ALLOC_FBSI_BASE_C,allc.ACCT_TYPE_C,allc.ALLOC_QTY, allc.ACTION_CD,
allc.COMMISSION_CD, allc.COMMISSION_A,allc.ALLOCATION_STATUS_CD
from trade_allocation allc
WHERE trade_id = trd.trade_id
and allocation_status_cd = 'P')
LOOP
allc_cnt := allc_cnt + 1;
-- Format of this detail record can be found in the BTAM doc or SDS / TSD
l_alloc_detail:= 'D'||'A'||rpad(trd.master_fbsi_brch_c,3,' ')||rpad(trd.master_fbsi_base_c,6,' ')||
allc.acct_type_c||to_char(trd.trade_dt,'yyyy-mm-dd')||
l_cusip_symbol||rpad(allc.action_cd,2,' ')||
' '||lpad(allc_cnt,4,'0')||rpad(allc.alloc_fbsi_brch_c,3,' ')||rpad(allc.alloc_fbsi_base_c,6,' ')||
rpad(' ',1,' ')||btam.SSP_GET_BTAM_FMT_NUMBER(allc.alloc_qty,12,5)||'FIX'||lpad(' ',13,' ')||
' '||lpad(' ',11,' ')||rpad(' ',2,' ')||rpad(' ',2,' ')||' '||'Y'||rpad(' ',4,' ')||
rpad(' ',4,' ')||rpad(' ',20,' ')||rpad(' ',6,' ') ;
dbms_lob.writeappend( l_clob,length(l_alloc_detail|| CRLF),l_alloc_detail || CRLF );
END LOOP;
END LOOP;
-- Step 3 End of file
-- Append the last record
dbms_lob.writeappend( l_clob,length(l_last_record|| CRLF1),l_last_record || CRLF1 );
COMMIT;

The next step is to create a file from the db
PROCEDURE SSP_CLOB_TO_FILE ( p_client_cd in btam_extract.client_cd%type,
p_recd_file_id in btam_extract.received_file_id%type,
p_output_file_nm OUT received_files.output_file_nm%type)
AS
-------------------------
-- Constants
-------------------------
c_method_nm CONSTANT TRANSACTION_LOG.METHOD_NM%TYPE := 'SSP_CLOB_TO_FILE';
l_output utl_file.file_type;
l_amt number default 32000;
l_offset number default 1;
l_clob clob;
l_length number ;
l_file_nm varchar2(30):= 'OUTPUTFILE';
l_message_txt transaction_log.message_txt%type;
BEGIN
-- get the extract for this client for this recd_file_id
BEGIN
select extract
into l_clob
from btam_extract
where client_cd = p_client_cd
and received_file_id = p_recd_file_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_message_txt := 'EXTRACT NOT FOUND';
alloc_adm.aip_ins_trans_log (p_client_cd,p_recd_file_id, null, null, null, null, 'ERROR', 'E', g_package_nm, c_method_nm,l_message_txt);
RAISE;
WHEN OTHERS THEN
l_message_txt := substr(sqlerrm,1,500);
alloc_adm.aip_ins_trans_log (p_client_cd,p_recd_file_id, null, null, null, null, 'ERROR', 'E', g_package_nm, c_method_nm,l_message_txt);
RAISE;
END;
-- Get the output file name for this client.
BEGIN
select fto_file_nm
into l_file_nm
from clients
where client_cd = p_client_cd;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_message_txt := 'FTO FILE NAME NOT FOUND';
alloc_adm.aip_ins_trans_log (p_client_cd,p_recd_file_id, null, null, null, null, 'ERROR', 'E', g_package_nm, c_method_nm,l_message_txt);
RAISE;
WHEN OTHERS THEN
l_message_txt := substr(sqlerrm,1,500);
alloc_adm.aip_ins_trans_log (p_client_cd,p_recd_file_id, null, null, null, null, 'ERROR', 'E', g_package_nm, c_method_nm,l_message_txt);
RAISE;
END;
-- append date/time to the file name
l_file_nm := l_file_nm||to_char(sysdate,'mmddyyyyhh24miss');
l_length := nvl(dbms_lob.getlength(l_clob),0);
l_output := utl_file.fopen('BTAM_DIR', l_file_nm, 'w', 32760);
while ( l_offset < l_length )
loop
utl_file.put(l_output,
dbms_lob.substr(l_clob,l_amt,l_offset) );
utl_file.fflush(l_output);
l_offset := l_offset + l_amt;
end loop;
-- utl_file.new_line(l_output);
utl_file.fclose(l_output);
p_output_file_nm := l_file_nm;
EXCEPTION
WHEN OTHERS THEN
l_message_txt := substr(sqlerrm,1,500);
alloc_adm.aip_ins_trans_log (p_client_cd,p_recd_file_id, null, null, null, null, 'ERROR', 'E', g_package_nm, c_method_nm,l_message_txt);
RAISE;
END SSP_CLOB_TO_FILE ;

Please advice - what am I not doing right regarding the linefeed and carriage return. Thanks Sangita

and Tom said...

On unix, only the linefeed is the end of line.

If you have a file that has \r\n (carriage return, linefeed) the carriage return will appear at ^M in many editors.

this is normal, this is expected.

You put it there. If you don't want it, don't use it.

If you just used utl-file.put_line - it would do the right thing on the right OS's as you move the code.

Rating

(7 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Justin, February 21, 2007 - 1:45 pm UTC

On our own systems, we have to deal with other entities requirements for our file types.

If it's for ourselves only, we prefer to use UTL_FILE.PUT_LINE. If it's for the other entities and they have specific requirements about expecting a CR and/or LF, then we just use UTL_FILE.PUT and tack on the characters using CHR.

We just have to make sure that we put a comment in stating why.

February 21, 2007 - 3:10 pm UTC

then you will have to live with seeing ^M in editors when looking at the file and make sure to NOT write the files out using that editor as they might lose the ^M

Page Break on UTL File

Arindam Mukherjee, May 07, 2007 - 10:45 am UTC

Sir,

We are generating voucher of 100 thousands agents through utl file but could not put page break option. Could you kindly tell me how to implement "page-break" in utl file?

Spool to a file with no Linefeed nor carriage return in file created with utl_file

Ryan, June 13, 2007 - 12:23 pm UTC

Hi Tom,

I'using the following function to send the result of a query to a file (containing EBCDIC and packed decimal format).

My question is : what should I do so that I won't have a linefeed or carriage return in my output file?

the following function works well if I put : utl_file.new_line( l_output, 1);
but not when I put utl_file.new_line( l_output, 0); in this case I have the following error : ORA-29285: file write error
CREATE OR REPLACE function Spool_to_file( p_query in varchar2,
p_separator in varchar2
default '',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
AUTHID CURRENT_USER
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output, l_columnValue );
utl_file.fflush(l_output);
end loop;

utl_file.new_line( l_output, 0);
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;
end Spool_to_file;
/

June 13, 2007 - 2:40 pm UTC

you will not be using utl_file to do this. utl_file is wanting a newline every 32k.

Please respond to me

Arindam Mukherjee, June 14, 2007 - 12:26 am UTC

Sir,

We are generating voucher of 100 thousands agents through utl file but could not put page break option. Could you kindly tell me how to implement "page-break" in utl file so every agent's voucher will be printed in new page ? The application is web-based developed in JAVA, JSP and database is Oracle 10g, OS is Linux.

Please help me.

June 14, 2007 - 7:10 am UTC

you tell us, what is a "page break"

utl_file creates text files, what character would you like to have output in order to achieve a line break. A popular one is typically "ctl-l" ^L

On page feed...

Frank van Bortel, June 14, 2007 - 7:33 am UTC

also known as form feed:
standard printers will accept form feed (Hex '0C', Octal '014', decimal '12') as meaning move paper to the top of a new page.
Code it, just like the Carriage Return and/or Line Feed in above examples:
FormFeed constant varchar2(1) := chr(12);

Use utl_file.put_line(f, FormFeed); when you need to.

clob to a text file

sara, September 27, 2007 - 4:18 pm UTC

Hi Tom,

I am trying to move the clob value got from a table into a text file. By the following, only a portion of the clob value is getting to the file. i am not getting the full clob value to the txt file. When i check the file size it is only 32848, whereas it has to be about 50000

DECLARE
p_dir VARCHAR2(30) := 'TEST_DIR';
p_file VARCHAR2(30) := 'test_wrap_1.txt';
p_clob CLOB;
l_output utl_file.file_type;
l_amt number default 32000;
l_offset number default 1;
l_length number ;

BEGIN
SELECT clob_val
INTO p_clob
FROM X;
l_length := nvl(dbms_lob.getlength(p_clob),0);
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
--this loop passes 32000 bytes from the clob till the end.
while ( l_offset < l_length )
loop
utl_file.put_line(l_output, dbms_lob.substr(p_clob,l_amt,l_offset) );
--dbms_lob.read(p_clob ,l_amt, pos, buf);
utl_file.fflush(l_output);
l_offset := l_offset + l_amt;
end loop;
--utl_file.new_line(l_output);
utl_file.fclose(l_output);
end;

Thanks

September 28, 2007 - 4:59 pm UTC

ops$tkyte%ORA10GR2> create table x ( clob_val clob );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_clob clob;
  3          l_data long := rpad( '*',10000,'*' );
  4  begin
  5          insert into x values ( empty_clob() ) returning clob_val into l_clob;
  6          for i in 1 .. 5
  7          loop
  8                  dbms_lob.writeAppend( l_clob, length(l_data), l_data );
  9          end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select dbms_lob.getlength(clob_val) from x;

DBMS_LOB.GETLENGTH(CLOB_VAL)
----------------------------
                       50000

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace directory TEST_DIR as '/tmp';

Directory created.

ops$tkyte%ORA10GR2> exec utl_file.fremove( 'TEST_DIR', 'test_wrap_1.txt' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> !ls -l /tmp/test_wrap_1.txt
ls: /tmp/test_wrap_1.txt: No such file or directory

ops$tkyte%ORA10GR2> DECLARE
  2     p_dir  VARCHAR2(30) := 'TEST_DIR';
  3     p_file VARCHAR2(30) := 'test_wrap_1.txt';
  4     p_clob CLOB;
  5     l_output utl_file.file_type;
  6     l_amt number default 32000;
  7     l_offset number default 1;
  8     l_length number ;
  9  BEGIN
 10     SELECT clob_val
 11     INTO p_clob
 12     FROM X;
 13     l_length := nvl(dbms_lob.getlength(p_clob),0);
 14     l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
 15     --this loop passes 32000 bytes from the clob till the end.
 16     while ( l_offset < l_length )
 17     loop
 18     utl_file.put_line(l_output, dbms_lob.substr(p_clob,l_amt,l_offset) );
 19     --dbms_lob.read(p_clob ,l_amt, pos, buf);
 20     utl_file.fflush(l_output);
 21     l_offset := l_offset + l_amt;
 22     end loop;
 23     --utl_file.new_line(l_output);
 24     utl_file.fclose(l_output);
 25  end;
 26  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> !ls -l /tmp/test_wrap_1.txt
-rw-rw-r--  1 ora10gr2 ora10gr2 50002 Sep 28 16:43 /tmp/test_wrap_1.txt

seems to function correctly.... it'll add extra newlines of course (so you do not get out what you put in) but you are doing that yourself...

write clob to file

sara, October 02, 2007 - 9:59 am UTC

Thanks, that helped

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Page Break on UTL File

Spool to a file with no Linefeed nor carriage return in file created with utl_file

Please respond to me

On page feed...

clob to a text file

write clob to file

More to Explore

CLOB/BLOB/etc

Linefeed and carriage return in file created with utl_file

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Page Break on UTL File

Spool to a file with no Linefeed nor carriage return in file created with utl_file

Please respond to me

On page feed...

clob to a text file

write clob to file

More to Explore

CLOB/BLOB/etc