This discussion is archived
9 Replies Latest reply: Mar 19, 2013 3:13 AM by User477708-OC RSS

extract big table to a delimited file

user-1221 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    >
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    ORA-03135: connection lost contact
    ORA-03135: connection lost contact
  • 9. Re: extract big table to a delimited file
    User477708-OC Journeyer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points