Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2001
    Posts
    4

    Unanswered: Export oracle data in comma separated value format

    Is there any way to export data into a comma separated value file format in Oracle?? Thanks for any and all help.

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Hi,

    You can use the tool SQL Loader for that.
    For a manual on how it works look here:
    http://technet.oracle.com/docs/produ.../ch03.htm#2436
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Apr 2002
    Location
    China
    Posts
    2

    SQL Loader can not be used to export data!

    It can only load data into Oracle.

    You can export data in text format by using Excel, TOAD etc.

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    just do a sql script!
    Code:
    set termout off
    set hea off
    set pagesize 0
    
    spool c:\whatever.csv
    
    select a.a||','||a.b||','||a.c
    from a
    where a.a="whatever";
    
    spool off
    should be quite fast. remeber the ; at the end of the sql select

  5. #5
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191

    Re: SQL Loader can not be used to export data!

    Originally posted by fromeast
    It can only load data into Oracle.
    Ahh.. duh.. should read better, before answering
    Thought it was for reading comma seperated files. Sorry

    Yes, the way rhs98 mentiones is the best I guess.
    Ruud
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  6. #6
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    PL/SQL Developer can do it ease
    db2oracle

  7. #7
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    oh yeah, remeber if your using this more than once, to use bind variables in the sql; as it can/does speed things up alot

  8. #8
    Join Date
    May 2002
    Posts
    11
    I would suggest following ways to perform export data from Oracle:

    1> Use serveroutput settings and DBMS_OUTPUT.PUT_LINE
    Disadvantage: Every line is buffered to a buffer. Minimum buffer is of 2000 bytes and maximum is of 1,000,000 bytes. Performance is screwed up and has limitation. Besides I think DBMS_OUTPUT.PUT_LINE should be used to find out logical errors, but if this is a temporary project, go for it. DBMS_OUTPUT is not designed to be a report writer. It is meant to be used for debugging. It is not really powerful enough for much more than this. The procedures in this package provide much the same functionality as the printf() function in C.

    2> Use Select from sqlplus:
    If you want to create a production job using select, seek some other profession or retire yourself from database field. Again if this is a temporary project, rather than spending your organizations’ valuable time in coding “C”, or sqlJ, go with this solution.

    3> Best and simple way use Pro*C. U can manipulate, twist, grind do whatever you want to using "C".

    4> Use SqlJ

    There are many ways for doing this. It depends on to what level your supervisor is stupid/not-technical. Usually in a non-professional organization the supervisor would suggest you to do in a very simple way not really knowing the correct, appropriate ways of doing and ignoring the long-term consequences. If you work for a professional technical supervisor, he/she will advise any one of the above or may be some other ways that I haven’t listed above.

    Hope this answers your question. Feel free to comment to my above discussion.
    Last edited by rayvid123; 05-12-02 at 18:30.

  9. #9
    Join Date
    Aug 2004
    Posts
    18

    fast export oracle tables into flat file

    Quote Originally Posted by luvcal
    Is there any way to export data into a comma separated value file format in Oracle?? Thanks for any and all help.
    If your tables in Oracle are not big or you need to perform oracle data extract in test environment, then all suggestions above should work.

    If you large tables (millions of rows or even terabytes of data), blob type, chained rows,.. etc, you may want consider third party tool. Especially if you need to unload data quickly from production server with no adding cpu overhead. I would suggest wisdomforce Fastreader www.wisdomforce.com .

    Fastreader is really fast, support all features that production DBA need and also generates loaders for not just Oracle, but also DB2, MySQL, SQL Server. This is very convinient for automating migrate process.

  10. #10
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Wow, you just answered a 7 year old post

    Thanks anyway
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Yeh.. this looks like spam And AskTom should be reserved for that other Tom
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  13. #13
    Join Date
    Aug 2012
    Posts
    2
    I have a table - RF_FORMS - in Production that contains data that needs to be exported to our Dev environment. It is a small table - contains only 23 rows. It has been recommended that I do this via a .csv file. I am not familiar with how to do this in Oracle 8i. Any help on how this can be done is appreciated. I consider myself more of a rookie in this environment and need help because available documentation does not exist to assist.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Aug 2012
    Posts
    2
    anacedent,

    I see where you have shown me how to use Google. That would imply using the same search engine to figure out how to get the data in the .csv file to load into the target environment. Seems simple enough. Is using a .csv file the easiest way to get table data from a table in one environment to another table in another environment or is this already the optimal solution?

Posting Permissions

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