CODEWITHSUNDEEP
plsqloracle11g
0x41ndrea
0x41ndrea

Reputation: 385

pl/sql utl_file endline is always \r\n

i've written a function in pl/sql on a oracle 11g db to export tables that follows particolar specifics. One of those specifics is the \n as break line.

PROCEDURE exportTableAScsv (p_tname varchar2) IS
    fileHandler     UTL_FILE.FILE_TYPE;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_query         varchar2(1000) default 'select * from ' || p_tname;

    l_colCnt        number := 0;
    l_separator     varchar2(1) default '|';
    l_descTbl       dbms_sql.desc_tab;
    test_n          number:=0;
    filename        varchar2(100) := 'custom_export_'|| p_tname ||'_' ||    to_char(sysdate,'yyyymmddhhmmss')|| '.csv';
    ex_custom EXCEPTION;
    PRAGMA EXCEPTION_INIT( ex_custom, -20001 );

 BEGIN

 fileHandler := UTL_FILE.FOPEN('TEMPDIR', filename , 'W');
 dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 . . .

 for i in 1 .. l_colCnt loop
     if (i>1) then
         UTL_FILE.put( fileHandler, l_separator || l_descTbl(i).col_name );
     else
         UTL_FILE.put( fileHandler, l_descTbl(i).col_name );
     end if;
     dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 end loop;
 -- UTL_FILE.put_line(fileHandler,'')
 UTL_FILE.fflush(fileHandler)
 UTL_FILE.put( fileHandler, CHR(10) );

 l_status := dbms_sql.execute(l_theCursor);
 . . . 
 while ( SYS.dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
     for i in 1 .. l_colCnt loop
         SYS.dbms_sql.column_value( l_theCursor, i, l_columnValue );
         if (i > 1) then
            utl_file.put( fileHandler, l_separator || l_columnValue );
         else
            utl_file.put( fileHandler, l_columnValue );
         end if;
     end loop;
    -- UTL_FILE.put_line(fileHandler,'')
    UTL_FILE.fflush(fileHandler)
    UTL_FILE.put( fileHandler, CHR(10) );
  end loop;

  dbms_sql.close_cursor(l_theCursor);
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
 . . .
 UTL_FILE.FCLOSE(fileHandler);
 raise;
. . . 
END exportTableAScsv;

as mentioned in the title, despite the code above, when i run the function i always get the data extracted as windows format with the \r\n as the end of line. The Oracle DB is on a windows machine. P.S. Unfortunately i can't put the db on a linux box and use put_line instead.

Upvotes: 0

Views: 1976

Answers (1)

Ben
Ben

Reputation: 52863

I suspect this is being caused by your use of UTL_FILE.FFLUSH. To quote from the documentation (my emphasis):

FFLUSH physically writes pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character.

In your function, however, you're calling FFLUSH before using PUT to append a line-feed. As you're on a Windows box you're therefore getting the Windows new line characters instead.

As a little aside; why is this a function and not a procedure? It makes more sense as a procedure as you're unlikely to want people to be able to call this from SQL.

Upvotes: 1

Related Questions