locked
bulk data transfer oracle 10.2G RRS feed

  • Question

  • User-14001990 posted

    Dear Friends,

    I want to do a bulk data transfer from Oracle 10.2G into CSV or Excel sheet.

    I would either want that an entire table's data be populated into EXcel/CSV or the result of a select query be so populated.

    1)Is there any stored procedure or function that can do this?

    I know of a function in postgres which does this kind of a thing.

    There could be something similar.

    The second thing is the reverse of it.

    I want to transfer the contents of a CSV file into a oracle table after truncating it.

    2) What sp or function is available for that?


    Thanks for your efforts.


    Wednesday, December 1, 2010 4:00 AM

Answers

  • User269602965 posted

    Oracle External Table allows you to treat an ASCII flat file external to Oracle as if it is an Oracle Table, with some limitations.

    After creation of the external table you can select on the table, but you cannot insert, update, or delete on the external table.

    External tables are useful for running Oracle ETL scripts against a flat file to load data into a staging Oracle database table

    with analysis and transformations of the data during import.

    The largest external table that I have connected to was a 14 gig, 38 million row, flat file located in an Oracle Directory Object. 

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN01507

     

    To export Oracle tables back to flat file, you can use the C# or VB.NET StreamWriter and OracleDataReader, and stream the Oracle table row by row to a flat file with delimiters that you choose.

     

    Exporting to excel is less useful for large datasets due to limitations of Excel row and column sizes.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 4, 2010 5:05 PM

All replies

  • User-14001990 posted

    Hi,

    For 1) I could not find a system SP which could transfer to Excel/CSV.

    FOr 2) I found SQL Loader/External tables.

    Does anybody have any idea abt how to use these?


    Saturday, December 4, 2010 1:02 PM
  • User269602965 posted

    Oracle External Table allows you to treat an ASCII flat file external to Oracle as if it is an Oracle Table, with some limitations.

    After creation of the external table you can select on the table, but you cannot insert, update, or delete on the external table.

    External tables are useful for running Oracle ETL scripts against a flat file to load data into a staging Oracle database table

    with analysis and transformations of the data during import.

    The largest external table that I have connected to was a 14 gig, 38 million row, flat file located in an Oracle Directory Object. 

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN01507

     

    To export Oracle tables back to flat file, you can use the C# or VB.NET StreamWriter and OracleDataReader, and stream the Oracle table row by row to a flat file with delimiters that you choose.

     

    Exporting to excel is less useful for large datasets due to limitations of Excel row and column sizes.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 4, 2010 5:05 PM