Oracle UTL_FILE function - Divide the file into equal parts?

Posted on 2007-11-20
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:  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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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 500 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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