[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Oracle UTL_FILE function - Divide the file into equal parts?

Posted on 2007-11-20
Medium Priority
Last Modified: 2013-12-19

I have a batch job I am running where I am writing a large amount of data to one file.  I need to be able to split this file into 4 equal size parts before I ftp it due to it's large size.  Is there anyway I can do this in Oracle? I cannot do the splitting while I am querying for the data because the data is always changing.  That's why I need to split it after the data is written to the file.  Any help would be appreciated!!!
Question by:farekat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20320157
Use the API UTL_FILE.fgetattr to (spec. given below) get the size of the file and then read the source file and write into the 4 target files by looping them thru the total size / 4 for each file.
  ** FGETATTR - Get file attributes
  ** IN  location     - directory location of file
  ** IN  filename     - file name (including extention)
  ** OUT fexists      - true or false, for exists or doesn't exist.  Note:
  **                      the following parameters have no meaning if the file
  **                      doesn't exist, in which case, they return NULL.
  ** OUT file_length  - length of the file in bytes.
  ** OUT block_size   - filesystem block size in bytes.
  **   invalid_path      - not a valid file handle
  **   invalid_filename  - file not found or file name NULL
  **   file_open         - file is not open for writing/appending
  **   access_denied     - access to the directory object is denied
  PROCEDURE fgetattr(location    IN VARCHAR2,
                     filename    IN VARCHAR2,
                     fexists     OUT BOOLEAN,
                     file_length OUT NUMBER,
                     block_size  OUT BINARY_INTEGER);

Open in new window


Expert Comment

ID: 20320234
I would work on the file after it is created.  If you are Windows, you'll have to find a file splitter.  (here is an old but good one:  http://www.pc-tools.net/win32/filesplit/).  However, if you are using Linux, simply issue the command:

$ split –bytes=1m /path/to/large/file /path/to/output/file/prefix

Unfortunately, the Linux version does not allow a "split into 4 equal sizes option" -- just uses file sizes.

Expert Comment

ID: 20320240
Sorry.. command for split in Linux should read:

$ split --bytes=1m /path/to/large/file /path/to/output/file/prefix
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

ID: 20320762
is there a way I can incorporate file size in that statement in UNIX?

Author Comment

ID: 20321696
Or even better is there a way I can do the split by number of lines in the big file I'm trying to split (it will not always be a number divisible by 4 of course).  thanks in advance!
LVL 18

Accepted Solution

Jinesh Kamdar earned 2000 total points
ID: 20321803
To Split a File Into Multiple Files Containing a Specified Number of Lines
       split [ -l LineCount ] [ -a SuffixLength ] [ File [ Prefix ] ]

To Split a File Into Multiple Files Containing a Specified Number of Bytes
       split -b Number [ k | m ] [ -a SuffixLength ] [ File [ Prefix ] ]


       1    To split a file into 1000-line segments, enter:

            split book

            This example splits book into 1000-line segments named xaa, xab, xac, and so forth.

       2    To split a file into 50-line segments and specify the file-name prefix, enter:

            split -l 50 book sect

            This example splits book into 50-line segments named sectaa, sectab, sectac, and so forth.

       3    To split a file into 2KB segments, enter:

            split -b 2k book

            This example splits the book into 2*1024-byte segments named xaa, xab, xac, and so forth.

       4    To split a file into more than 676 segments, enter:

            split -l 5 -a 3 book sect

            This example splits a book into 5-line segments named sectaaa, sectaab, sectaac, and so forth, up to sectzzz (a maximum of 17,576 files).

Author Comment

ID: 20322015
What if the file size is constantly change?

For example, I have a file that has 713 lines.

Id like to split it up into four different files with the following number of lines:

File 1: 178
File 2: 178
File 3: 178
File 4: 179

Doesnt matter which file is the bigger file.  I tried to do it by obtaining (line count divided by 4)+1 but then it gives me this result:

File 1: 179
File 2: 179
File 3: 179
File 4: 176

I'd like for the results to be as even as possible, I tried using 178 but it created a 5th file which I dont want.
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20322090
I am not sure if that is possible since every file will be created to hold the specified no. of rows only - there's no exception even for the last file. And 1 line more or less is not going to be a big issue as far as performance goes.
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20325696
Thanks for the points though i would have appreciated a split with Joe since using "split" command was his original idea :)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question