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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.