5 Replies Latest reply: Feb 23, 2013 8:36 AM by EdStevens RSS

    Write to text file (including spaces/next line) using oracle??

    992876
      How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.) thanks!
        • 1. Re: Write to text file (including spaces/next line) using oracle??
          Solomon Yakobson
          There is nothing special about spaces. And for new lines Use UTL_FILE.PUT_LINE. For example:
          declare 
               v_file utl_file.file_type;
          begin
              v_file := UTL_FILE.FOPEN('TEMP','TEST.TXT','w');
              UTL_FILE.PUT_LINE(v_file,'This is line 1');
              UTL_FILE.PUT_LINE(v_file,'This is line 2');
              UTL_FILE.FCLOSE(v_file);
          end;
          /
          
          PL/SQL procedure successfully completed.
          
          SQL>
          Now I will read above created file:
          declare
               v_file utl_file.file_type;
               v_line varchar2(100);
          begin
              v_file := UTL_FILE.FOPEN('TEMP','TEST.TXT','r');
              UTL_FILE.GET_LINE(v_file,v_line);
              DBMS_OUTPUT.PUT_LINE(v_line);
              UTL_FILE.GET_LINE(v_file,v_line);
              DBMS_OUTPUT.PUT_LINE(v_line);
              UTL_FILE.FCLOSE(v_file);
          end;
          /
          This is line 1
          This is line 2
          
          PL/SQL procedure successfully completed.
          
          SQL>
          SY.
          • 2. Re: Write to text file (including spaces/next line) using oracle??
            sb92075
            989873 wrote:
            How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.) thanks!
            when all else fails, Read The Fine Manual

            http://www.oracle.com/pls/db112/search?remark=quick_search&word=UTL_FILE&partno=
            • 3. Re: Write to text file (including spaces/next line) using oracle??
              992876
              Thanks for your prompt reply. How about tabs? I was trying to retrieve data from my db tables and I want them to look like this. Thanks

              ColumnName1 ColumnName2 ColumnName3 AmountPaid
              Name1          Address1          Phone1 3
              Name2          Address2          Phone2 5
              Name3          Address3          Phone3 2

              Total Amount: 10
              Total Records: 3
              • 4. Re: Write to text file (including spaces/next line) using oracle??
                Solomon Yakobson
                989873 wrote:
                How about tabs?
                It does not matter what character it is. UTL_FILE deals correctly with any text file. Tabb is CHR(9):
                SQL> declare
                  2       v_file utl_file.file_type;
                  3       v_total_rec number := 0;
                  4       v_total_sal number := 0;
                  5  begin
                  6      v_file := UTL_FILE.FOPEN('TEMP','TEST.TXT','w');
                  7      for v_rec in (select * from emp) loop
                  8        v_total_rec := v_total_rec + 1;
                  9        v_total_sal := v_total_sal + v_rec.sal;
                 10        UTL_FILE.PUT_LINE(
                 11                          v_file,
                 12                          lpad(v_rec.deptno,2) || chr(9) || rpad(v_rec.ename,14) || chr(9) || to_char(v_rec.sal,'99999D99')
                 13                         );
                 14      end loop;
                 15      UTL_FILE.PUT_LINE(v_file,'Total Salary:' || chr(9) || v_total_sal);
                 16      UTL_FILE.PUT_LINE(v_file,'Total Records:' || chr(9) || v_total_rec);
                 17      UTL_FILE.FCLOSE(v_file);
                 18  end;
                 19  /
                
                PL/SQL procedure successfully completed.
                
                SQL> declare
                  2       v_file utl_file.file_type;
                  3       v_line varchar2(100);
                  4  begin
                  5      v_file := UTL_FILE.FOPEN('TEMP','TEST.TXT','r');
                  6      loop
                  7        UTL_FILE.GET_LINE(v_file,v_line);
                  8        DBMS_OUTPUT.PUT_LINE(v_line);
                  9      end loop;
                 10    exception
                 11      when no_data_found
                 12        then
                 13          UTL_FILE.FCLOSE(v_file);
                 14  end;
                 15  /
                20      SMITH              800.00
                30      ALLEN             1600.00
                30      WARD              1250.00
                20      JONES             2975.00
                30      MARTIN            1250.00
                30      BLAKE             2850.00
                10      CLARK             2450.00
                20      SCOTT             3000.00
                10      KING              5000.00
                30      TURNER            1500.00
                20      ADAMS             1100.00
                30      JAMES              950.00
                20      FORD              3000.00
                10      MILLER            1300.00
                Total Salary:   29025
                Total Records:  14
                
                PL/SQL procedure successfully completed.
                
                SQL>
                SY.
                • 5. Re: Write to text file (including spaces/next line) using oracle??
                  EdStevens
                  989873 wrote:
                  How to write to text file using oracle? And how do I handle spaces/next line? (i was trying to use spaces(ch(32)), however it is just converted into squares in the text file.) thanks!
                  What are you using to view your text file? THAT is the program that determines how it is going to render every ascii code in the file. If you put a chr(32) in the file, that's what's in the file. Oracle has no knowledge of how any particular program will render that character.