Friday, January 26, 2018

ORACLE - How generate text file in PL/SQL

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