0

Data in table(Oracle database)

Date        Id      Flag
16-DEC-13   163750  1
16-DEC-13   163755  1
16-DEC-13   063801  1

whenever I'm read above data from table and writing into flat file using UTL file function,it misses zero from field in the flat file.

Flat file abc.txt

16-DEC-13|163750|1
16-DEC-13|163755|1
16-DEC-13|63801|1
  • 1
    What is the data type of the column? How are you querying the data and writing to the file? Showing your code would help, but if it's supposed to be a string then you're presumably treating it as a number somewhere you didn't intend to, And if it's a number then the leading zero doesn't really exist, and you're formatting the column to show it... – Alex Poole Nov 02 '15 at 14:32

1 Answers1

1

I think you lose the 0 somewhere in your script below a sample where UTL_FILE save same value into 2 files, in file tst-i.csv we lose the leading zero, but in file tst-v.csv we have it correctly

create table tst (col1 varchar2(50));
insert into tst values('1');
insert into tst values('02');
insert into tst values('3');

DECLARE
  fi UTL_FILE.FILE_TYPE;
  fv UTL_FILE.FILE_TYPE;
  i  integer;
  v  varchar2(50);
begin

  fi := utl_file.fopen('MYDIR','tst-i.csv','w');
  fv := utl_file.fopen('MYDIR','tst-v.csv','w');
  for rc in (select * from tst) loop
    i := rc.col1;
    v := rc.col1;
    utl_file.PUTF(fi, i ||' \n');
    utl_file.PUTF(fv, v ||' \n');
  end loop;

  utl_file.FCLOSE(fi);
  utl_file.FCLOSE(fv);
end;

and output is tst-i.csv

1 
2 
3

tst-v.csv

1 
02 
3
are
  • 2,535
  • 2
  • 22
  • 27