• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2933
  • Last Modified:

Oracle UTL_FILE function - Divide the file into equal parts?

hi!

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!!!
0
farekat
Asked:
farekat
  • 4
  • 3
  • 2
1 Solution
 
Jinesh KamdarCommented:
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.
  ** EXCEPTIONS
  **   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);
  PRAGMA RESTRICT_REFERENCES(fgetattr, WNDS, RNDS, TRUST);

Open in new window

0
 
joebednarzCommented:
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.
0
 
joebednarzCommented:
Sorry.. command for split in Linux should read:

$ split --bytes=1m /path/to/large/file /path/to/output/file/prefix
0
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.

 
farekatAuthor Commented:
is there a way I can incorporate file size in that statement in UNIX?
0
 
farekatAuthor Commented:
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!
0
 
Jinesh KamdarCommented:
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 ] ]

Examples

       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).
0
 
farekatAuthor Commented:
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.
0
 
Jinesh KamdarCommented:
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.
0
 
Jinesh KamdarCommented:
Thanks for the points though i would have appreciated a split with Joe since using "split" command was his original idea :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now