function CreateExportFile( pNumber varchar2 ) return number
is
cursor c_lower is
select * from table
where
lief_sch_nr = pNumber and
c_pom = 2 /* spec.type */
order by
bsg_nr, pos_lfd_nr, tl_nr, tl_fam, tl_atn
;
pc_lower c_lower%rowtype;
iCount number;
sFileName varchar2( 50 );
sDir varchar2( 256 );
fileHandler utl_file.file_type;
begin
/* -- check if some data exists */
select count( * ) into iCount
from
factory.cieb_karosa_rozpad
where
lief_sch_nr = pNumber;
if iCount = 0 then
return( -1 );
end if;
/* -- export */
sDir := 'c:\ifc42\utlfile';
sFileName := pNumber || '_name_' || to_char( sysdate, 'dd_mm_yyyy_hh24_mi' ) || '.csv';
fileHandler := utl_file.fopen( sDir, sFileName, 'w' );
open c_lower;
loop
fetch c_lower into pc_lower;
exit when c_lower%notfound;
utl_file.putf( fileHandler,
pc_lower.lief_sch_nr || ';' ||
pc_lower.bsg_nr_kd || ';' ||
pc_lower.pos_lfd_nr || ';' ||
to_char( pc_lower.abschl_dat, 'dd.mm.yyyy' ) || ';' ||
pc_lower.tl_nr_lief || ';' ||
pc_lower.lsch_mng_me || ';' ||
to_char( to_number( pc_lower.lsch_mng_mz ) ) || ';' ||
'\n'
);
end loop;
close c_lower;
utl_file.fclose( fileHandler );
return( 0 );
end;
Output (could be):
DL160386;30003313;10;11.03.2016;5006006343;ks;5;
DL160386;30003313;240;11.03.2016;E5801470258;ks;4;
DL160386;30003313;240;11.03.2016;E5801470258;ks;12;
...
No comments:
Post a Comment