DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to Export Oracle Data to Excel Sheet ??

  1. #1
    Join Date
    Jun 2007
    Posts
    1

    How to Export Oracle Data to Excel Sheet ??

    HI,,

    I need to export some of Fields from my table to Excel sheet.. How to export this?

    Can any one give brief description.. PLease

    Vijay

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Metalink Note:436390.1
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2007
    Location
    New Delhi, India
    Posts
    1

    spooling to excel


  4. #4
    Join Date
    Jun 2007
    Posts
    4
    Hi Vijay,
    you can try to get data from a table to excel sheet by using Toad
    try it...

  5. #5
    Join Date
    Oct 2011
    Posts
    1

    how to move

    Quote Originally Posted by deeviju View Post
    HI,,

    I need to export some of Fields from my table to Excel sheet.. How to export this?

    Can any one give brief description.. PLease

    Vijay
    fateme

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by albaloo View Post
    fateme
    after four years? "fateme"? wot?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2011
    Posts
    2
    Hello,

    to export data from table to Excel you may use ora_excel package.
    If you just need to select some fields and export them then you may use query_to_sheet procedure otherwise you may also create formatted version of Excel worksheet.

    Short example about how to create excel:

    BEGIN
    ORA_EXCEL.new_document;
    ORA_EXCEL.add_sheet('Sheet name');
    ORA_EXCEL.query_to_sheet('select column1, column2, column3 from my_table');
    -- EXPORT_DIR is an Oracle directory with at least write permission
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');
    END;

    This code will create excel document with sheet "Sheet name" and put inside data returned from query "select column1, column2, column3 from my_table", excel will be save to file examle.xlsx within EXPORT_DIR (Oracledirectory)

    It's also possible to save generated Excel file to BLOB variable.

    More details you can find at: http://www.oraexcel.com/examples/pl-...o-sheet-export

    Cheers

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I usually do something like the following. This way you don't need to find the oracle_excel
    package and you can run it from a linux server and mail the output to anyone. I sent monthly
    rman reports using this method at a previous job.

    Code:
    set echo off
    set pagesize 0
    set linesize 170
    col text format a170
    set tab off
    set trim on
    set timing off
    spool /tmp/myexcelfile.csv
    
    select      '"'||table_name||'","'||tablespace_name
                       ||'","'||num_rows||'","'||compression||'"' text
      from user_tables where table_name like 'DBA%' order by 1;
    
    spool off;
    set echo on

Posting Permissions

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


Click Here to Expand Forum to Full Width