Home » SQL & PL/SQL » SQL & PL/SQL » utl_file not as expected
utl_file not as expected [message #297943] Mon, 04 February 2008 05:26 Go to next message
Meert
Messages: 3
Registered: February 2008
Junior Member
I got this PL/SQL procedure which outputs to a file.

I can't get the output in the right format as I want it.

When I try and put the Volume and Weight in this script into '099.990' and '0999.990', there's spaces added which I cannot explain. Confused

Any help would be appreciated.

SQL Procedure:
set echo off
set feedback on
set verify off
set heading on
set serveroutput on
set termout on
set pagesize 1500
set linesize 999 
set space 0

declare

-- File data
v_file               varchar2(56);
v_dir                varchar2(56);
v_outputfile         UTL_FILE.file_type;
v_date		     varchar2(6);
v_docnum             varchar2(16);
VolumeNR             varchar2(8);
WeightNR             varchar2(9);

long_string varchar2(400);

begin                                    

-- Create file                    
select directory 
  into v_dir
  from envardir where env_name='TRANSOUT';

select to_char(sysdate,'YYYYMMDD')
  into v_docnum
  from dual;

v_file:='test_file_'||v_docnum||'.txt';

v_outputfile := UTL_FILE.fopen (v_dir, v_file, 'W');

select nvl(trunc((7891/1000),3),0), trunc((965423/1000),3)
into VolumeNR, WeightNR
from dual;

select 
'SOMETEXT'||' '||rpad('MORETEXT',11)||to_char(VolumeNR,'099.990')||to_char(WeightNR,'0999.990')||rpad('TEXT',9)
into long_string
from dual;

UTL_FILE.put_line
(v_outputfile
,
'SOMETEXT'
||' '
||rpad('MORETEXT',11)
||VolumeNR
||WeightNR
||rpad('TEXT',9)
);

UTL_FILE.put_line
(v_outputfile
,long_string
);

UTL_FILE.fclose (v_outputfile);

end;
/
Output:
SOMETEXT MORETEXT   7.891965.423TEXT     
SOMETEXT MORETEXT    007.891 0965.423TEXT     

[Updated on: Mon, 04 February 2008 05:34] by Moderator

Report message to a moderator

Re: utl_file not as expected [message #297947 is a reply to message #297943] Mon, 04 February 2008 05:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oracle adds a leading space for a possible "-" sign.

You can use "fm" in the format mask to switch that off.

Note the difference between :
select to_char(1234,'9999') x from dual;

select to_char(1234,'fm9999') x from dual;
Re: utl_file not as expected [message #297948 is a reply to message #297947] Mon, 04 February 2008 05:36 Go to previous messageGo to next message
Meert
Messages: 3
Registered: February 2008
Junior Member
brilliant!
thanks a lot!
Re: utl_file not as expected [message #297949 is a reply to message #297943] Mon, 04 February 2008 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(10,'09'), to_char(-10,'09'), to_char(10,'S09') from dual;
TO_ TO_ TO_
--- --- ---
 10 -10 +10

1 row selected.

All 3 characters.

Regards
Michel

(Edit: ooops! too late!]

[Updated on: Mon, 04 February 2008 05:37]

Report message to a moderator

Re: utl_file not as expected [message #297950 is a reply to message #297943] Mon, 04 February 2008 05:36 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
text document always disappoints due to its output.so import it in excel fromat,if text is not neccessary.

regards,
Re: utl_file not as expected [message #297959 is a reply to message #297950] Mon, 04 February 2008 05:48 Go to previous message
Meert
Messages: 3
Registered: February 2008
Junior Member
mshrkshl wrote on Mon, 04 February 2008 12:36
text document always disappoints due to its output.so import it in excel fromat,if text is not neccessary.

regards,

mshrkshl- in this case, it needs to be a flat text file Confused but thanks for the info

Michael- thanks for looking and replying
Previous Topic: Oracle Job not running properly:
Next Topic: using external tables
Goto Forum:
  


Current Time: Sat Dec 03 18:06:45 CST 2016

Total time taken to generate the page: 0.04043 seconds