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

Creating file of fixed size

I have created a procedure which fires a SQL and writes the result of the query into a file.

I want the file written to be of a fixed size say 1500 bytes.

To produce a file of fixed size, I will have to create a file of fixed width (columns) and height (rows)

For this, I have been able to produce fixed column size by using rpad on each column of the query.

As the result set produced by the query can vary on day to day basis, how do I produce a file of fixed height
  • 5
  • 3
  • 3
  • +1
1 Solution
johnsoneSenior Oracle DBACommented:
Since you posted this in Linux/Unix zones, I would suggest creating the full file.  Then I would use the split command to split it into the number of lines you need to make your 1500 byte files.

Man page for split:

Modify your query to write the results into the text file line by line. Since you know how many byte reside on one line you can calculaye number of lines which produces 1500 b.
gram77Author Commented:
Unknown routine:
Suppose I rpad my column to 500 characters.
I will be able to have 1500 characters in 3 rows.
Now suppose on a particular day my query produces 10 rows, I will need to report them too.

I want to limit the rows to say 100 and rows greater than this won't be printed

Can I do this in SQL.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

johnsoneSenior Oracle DBACommented:
If you want to limit the rows to 100, add this to your where clause:

ROWNUM <= 100
gram77Author Commented:

This will only work if my file is >100 rows.

Assuming one row is 500 characters : When my file is <100 rows the byte size of my file will start to vary.

For 1 row 500 bytes file
For 2 rows 1000 bytes file
3 rows 1500 bytes file
4 rows 2000 bytes file

I want the byte size to be constant
johnsoneSenior Oracle DBACommented:
That is why I would use the split command.  It is outside the database, but it will give you exactly what you want.  If you end up with a partial file at the end, there are many ways to pad it, it just depends on what you want to pad it with.

Inside the database, I would suppose you would have to write a procedure, keep track of the bytes on each line, and use UTL_FILE to write it out.
assuming you have a query like

(select col1 || col2 || col3 yourstring from yourtable)

then try this...  simply plug your real query into "yourquery"  and change "yourstring" to whatever string your query returns.

This will adjust the pad length and number of rows to get the most amount of data possible that will exactly fit 1500

SELECT RPAD(yourstring, padlen)
  FROM (SELECT maxstr, 1500 / FLOOR(1500 / maxstr) padlen, yourstring
          FROM (SELECT MAX(LENGTH(yourstring)) OVER () maxstr, yourstring FROM yourquery))
 WHERE ROWNUM <= 1500 / padlen
The above query does assume no one string will be longer than 1500
and it assumes you'll always have enough data to fill 1500 length.
oops floor isn't in the right place and it's not guaranteed to be produce exactly 1500.  Happened to work for some simple sample data I generated

but won't work as a general solution
gram77Author Commented:
Looks like we can pad a row of data to meet the desired string length, but can't pad to produce n number of rows in a file.

Such that even if a query produces a result set of 5 rows, the file is padded with 10 rows in oracle
you can pad to produce n rows

simply add

select * from
(select yourstring from
(select 1 n,yourstring from (yourquery)
union all
select 2 n, rpad(' ',500) from dual connect by dual <= 1234 -- change this to your number of rows
) order by n)
where rownum <= 1234
try this...

It will determine the smallest pad length that will divide into 1500 (or whatever target you want)  and then create enough rows to fill the 1500

If you have very few rows, it will simply make them pad bigger so you still fill 1500.
Change the 1500 in the "with" clause to whatever target size you want

WITH target
     AS (    SELECT 1500 targetsize, COLLECT(CASE WHEN MOD(1500, LEVEL) = 0 THEN LEVEL END) factors
               FROM DUAL
         CONNECT BY LEVEL <= 1500)
SELECT RPAD(yourstring, padlen), x.*
               (SELECT MIN(COLUMN_VALUE)
                  FROM TABLE(factors)
                 WHERE COLUMN_VALUE >= GREATEST(maxstr, targetsize / cnt))
          FROM (SELECT COUNT(*) OVER () cnt,
                       MAX(LENGTH(yourstring)) OVER () maxstr,
                  FROM yourquery, target)) x
 WHERE ROWNUM <= targetsize / padlen;

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now