Solved

Oracle UTL_FILE function - Divide the file into equal parts?

Posted on 2007-11-20
9
2,819 Views
Last Modified: 2013-12-19
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
Comment
Question by:farekat
  • 4
  • 3
  • 2
9 Comments
 
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.

  ** 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
 
LVL 9

Expert Comment

by:joebednarz
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.
0
 
LVL 9

Expert Comment

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

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

Author Comment

by:farekat
ID: 20320762
is there a way I can incorporate file size in that statement in UNIX?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:farekat
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!
0
 
LVL 18

Accepted Solution

by:
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 ] ]

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
 

Author Comment

by:farekat
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.
0
 
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.
0
 
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 :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now