how to - extract query result set into multiple small CSV
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to - extract query result set into multiple small CSV

  1. #1
    Join Date
    May 2015
    Posts
    1

    how to - extract query result set into multiple small CSV

    I have a query which returns millions of rows. I need to extract those rows in CSV files. Having said that i want to keep these files under a limit of 500 MB . Result set can be distributed among multiples files but each file can not be > 500 MB in size.
    How to achieve this?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,092
    Steven Feuerstein talks about using Bulk collect with the limit keyword to only fetch a certain number of rows at a time.
    If you know the average rowsize you can approximate the number of rows that it would take to have 500MB of data.
    Then you can set that number to your limit and use utl_file to write each to a different file. For example you can have a variable
    that you increment every time you get more rows and use that to build the file_name: I.e. my_file_01.csv. When you
    open the file you would want to left pad the increment variable with zeroes so that the files sort the way you want.

    http://www.oracle.com/technetwork/is...ql-095155.html
    this space intentionally left blank

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