9 Replies Latest reply: Mar 19, 2013 5:13 AM by User477708-OC RSS

    extract big table to a delimited file

    user-1221
      Hi Gurus,

      A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,
      the column delimiter is "&|" and row delimiter is "$#".
      I cannot do it from TOAD as it is hanging while extraction of big table.
      Any suggestion will be highly appreciated.
      Thanks in advance.
        • 1. Re: extract big table to a delimited file
          Osama_Mustafa
          You can do it using spool

          for example :
          spool location 
          
          select field1||', '||field2||', '||field3
          from table-name;
          spool off 
          • 2. Re: extract big table to a delimited file
            rp0428
            >
            You can do it using spool
            >
            Are you sure that will satisfy this requirement?
            >
            row delimiter is "$#".
            • 3. Re: extract big table to a delimited file
              rp0428
              >
              A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,
              the column delimiter is "&|" and row delimiter is "$#".
              I cannot do it from TOAD as it is hanging while extraction of big table.
              Any suggestion will be highly appreciated.
              >
              You will need to write your own code to do the unload.

              One possibility is to write a simple Java program and use JDBC to unload the data. This will let you unload the data to any client you run the app on.
              The other advantage of using Java for this is that you can easily ZIP the data as you unload it and use substantially less storage for the resulting file.

              See The Java Tutorials for simple examples of querying an Oracle DB and processing the result set.
              http://docs.oracle.com/javase/tutorial/jdbc/overview/index.html

              Another possibility is to use UTL_FILE. There are plenty of examples in the SQL and PL/SQL forum if you search for them.

              There is also a FAQ for 'How do I read of write an Excel file (note - this also includes delimited files).
              SQL and PL/SQL FAQ
              • 4. Re: extract big table to a delimited file
                Osama_Mustafa
                rp0428 wrote:
                >
                You can do it using spool
                >
                Are you sure that will satisfy this requirement?
                >
                row delimiter is "$#".
                >
                True, What i meant he can change the delimtar between field , also regarding to column what about set colsep ?
                • 5. Re: extract big table to a delimited file
                  user-1221
                  Hi,
                  I modified the delimiters and executed the select query while spooling it,
                  The table contains more than 50 Million rows, lost connection after fetching around 32 million rows,

                  I cannot write a java code as I am not familiar with Java,
                  Is there a way to implement piecewise fetches?
                  I tried to do with rownum but it is failing without any result.
                  258143789&|23825020&|2282&|04 02 2010, 13:29:55&|0&|87760300&|0&| &|300004273&|2281&|0$#
                  ERROR:
                  ORA-03135: connection lost contact
                  Process ID: 0
                  Session ID: 1053 Serial number: 2392
                  
                  
                  Elapsed: 15:26:10.00
                  
                  SQL> spool off;
                  • 6. Re: extract big table to a delimited file
                    rp0428
                    >
                    I cannot write a java code as I am not familiar with Java,
                    >
                    Too bad since it would only take about 30 lines of simple code including the JDBC code in that doc link I provided.
                    >
                    Is there a way to implement piecewise fetches?
                    I tried to do with rownum but it is failing without any result.
                    >
                    Sorry - we don't know what 'I tried to do' means. We can't evaluate code that we can't see.

                    You can do piecewise fetches a lot of ways but it depends on your data: select data for a date range (< 1/1/2013) and for the next piece use a different date range. Select data by an ID value or range of values.

                    We don't have your data; you haven't posted your query. How do you expect anyone to be able to help?
                    • 7. Re: extract big table to a delimited file
                      div
                      Hi
                      You can try using utl_file http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm. However it would still require you to write some code.
                      Also my recommnedation is to run it on the server you're extracting from or on a server where connection would not be lost. We have certain, VM's at the colo that we can rdp into and perfrom these kind of tasks. You may ask your DBA or admin if they have anything similar. This will ensure you don't lose connection.

                      HTH
                      Div.

                      Edited by: div on Mar 18, 2013 4:23 PM
                      • 8. Re: extract big table to a delimited file
                        Osama_Mustafa
                        ORA-03135: connection lost contact
                        ORA-03135: connection lost contact
                        • 9. Re: extract big table to a delimited file
                          User477708-OC
                          do this from database server. use sqlplus as mentioned in one of the first replies, and scp/ftp the file off when finished. not only is it more stable re connectivity, it will be faster and you're not clogging up network with 4gb over an inefficient odbc sql parser.

                          it really is the simplest way if you dont have the java coding skills.