Export/copy data into text file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Export/copy data into text file

  1. #1
    Join Date
    Jul 2001
    Posts
    4

    Wink

    How to copy put data into text file, besides using "spool"?

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    try using supplied package UTL_FILE ...

  3. #3
    Join Date
    Jul 2001
    Posts
    4
    Grateful if you could explain on its usage coz I'am quite new.

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    1) in init.ora file :
    UTL_FILE_DIR=directory where you want to write to, or * for all directories
    2) using PL/SQL, you can write in a file using :

    DECLARE
    FILE_HANDLE UTL_FILE.FILE_TYPE;
    BEGIN
    DBMS_OUTPUT.ENABLE(100000);
    FILE_HANDLE:=UTL_FILE.FOPEN('directory','file','open mode (r,w,a)');
    UTL_FILE.PUTF(FILE_HANDLE,'%s',variable);
    UTL_FILE.FFLUSH(FILE_HANDLE);
    UTL_FILE.FCLOSE_ALL;
    END;
    /


    well, this is just an example, you'll have to personnalize it, and I recommend reading the docs about UTL_FILE for a better use of this package

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    Hi

    here is an example:

    create or replace procedure test
    is
    x utl_file.file_type; -- file handle of os flat file
    begin
    -- open file to write into and obtain its x
    x := utl_file.fopen('here you write the utl_fiel directory','myfile.txt','w');
    -- write a line of text out to the file.
    utl_file.put_line(x, 'this is line test');
    -- close the file.
    utl_file.fclose(x);
    -- open the same file to read from.
    x := utl_file.fopen('here you write the utl_fiel directory','myfile.txt','r');
    -- read a line from the file.
    utl_file.get_line (x, retrieved_buffer);
    -- close the file.
    utl_file.fclose(file_handle);
    exception
    when utl_file.invalid_path then
    when utl_file.read_error then
    when utl_file.write_error then
    when others then
    end;

    for more reading look at Note:33755.1 on yhr metalink

  6. #6
    Join Date
    Jul 2001
    Posts
    4
    Let say I have a table with 200,000 rows and I want to copy out these records into a text-file, column deliminated by ie. a comma. How would I accomplish this ?

  7. #7
    Join Date
    Jul 2000
    Posts
    243
    create or replace procedure test
    is
    cursor get_lines_data is
    select column1,column2,...
    from table_test
    where ...;
    -- cursore row type
    rec_line_data get_lines_data%rowtype;
    v_log_file utl_file.file_type; -- file handle of os flat file
    v_error varchar2(200);

    begin
    -- open file to write into and obtain its x
    v_log_file := utl_file.fopen('c:\temp','myfile.txt','w');
    open get_line_data;
    loop
    fetch get_line_data into rec_line_data;
    exit when get_line_data%notfound;
    begin
    -- chr(13), chr(10) for end of line
    utl_file.putf(v_log_file, rec_line_data.column1||rec_line_data.column2.%s%s', chr(13), chr(10));
    -- the utl_file utility has a roblem when in comes to writeing larg number of line in high speed.
    -- the close open is the way to get over it, ei, work around
    utl_file.fclose(v_log_file);
    v_log_file := utl_file.fopen('c:\temp', 'myfile.txt' ,'a');


    exception

    when utl_file.invalid_path
    then
    v_error := 'invalid path for the utl_file. c:\temp is invalid. you can get the right value from v$parameters';
    utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
    utl_file.fclose(v_log_file); -- close log file

    when utl_file.invalid_mode
    then
    v_error := 'invalid mode. the w (write) mode in not good.';
    utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
    utl_file.fclose(v_log_file);-- close log file

    when utl_file.invalid_operation
    then
    v_error := 'invalid operation';
    utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
    utl_file.fclose(v_log_file);-- close log file
    when others
    then
    v_error := 'when others';
    utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
    utl_file.fclose(v_log_file);-- close log file
    end;
    end loop;
    close get_line_data;

    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width