2 Replies Latest reply: Mar 18, 2013 9:00 AM by sushaant RSS

    export table to a text file

    user-1221
      Hi,
      We need to export a big table into a text file with Column delimiter '&|' and row delimiter '$#'.
      DB Version is 10.2.0.2, the table size is around 4 GB.
      Thanks in Advance.
        • 1. Re: export table to a text file
          Richard Harrison .
          Hi,
          You can do this either with a simple sql script within sqlplsu or write some pl/sql procedure using utl_file - there is no default tool that explicitly is used for unloading data into text file.

          Try this as a start point in sqlplus - just update the SQL you want to use.

          set colsep '&|'
          set pages 0
          set heading off
          set feedback off
          spool output.sql
          select 'XX','XX','$#' from dual;
          XX&|XX&|$#
          spool off;

          Regards,
          Harry
          • 2. Re: export table to a text file
            sushaant
            You can use UTL FIle concept. Sample code as below. make sure you are providing both read and right grants to dba_directory and also make sure you have enough buffer size for UTL. You can flush out utl buffer at regular interval inside script


                 declare
                 OUTPUT_File VARCHAR2(provide size):= 'filename.txt';
                 

                 FILE_OUTPUT UTL_FILE.FILE_TYPE;
                 
                 BEGIN

                 FILE_OUTPUT := UTL_FILE.FOPEN ('DBA_DIRECTORY_NAME',OUTPUT_File,'W');
                           
                 FOR CUR_OUT IN (

                 SELECT COL1 || 'Delimeter' || .. COLn record_set from tablename
                           ) loop
                                
                           UTL_FILE.PUT_LINE(FILE_OUTPUT,CUR_OUT.Record_set);

                           end loop;

            UTL_FILE.FCLOSE (FILE_OUTPUT);

                 end ;