Result Size Limits

Data Collage runs your SQL through Oracle BI Publisher (BIP), and BIP puts two independent ceilings on how much a single report can return. Whichever you reach first wins, and they show up very differently: one is a loud error, the other is silent. This page explains both so you can recognise them and plan around them.

Every number below is a setting on your Fusion pod, controlled by your BI administrator. The values we quote come from a demo pod and from Oracle’s documented defaults. Your pod may be higher or lower.

Limit 1 — data size

Before BIP writes a single CSV row, it generates the whole result as XML in memory. That XML has a size cap, and the cap for an online query (which is what Data Collage runs) is much lower than the cap for a scheduled job.

	Online query	Scheduled job
Oracle’s default	300 MB	500 MB
Demo pod we tested	500 MB	5 GB
Oracle’s maximum	1 GB	2 GB (some pods run higher)

When you hit it, the query fails immediately with an error like this:

oracle.xdo.memoryguard.XDODataSizeLimitException: Stopped processing the report.
Report data size of 524288016 bytes exceeds the maximum limit 524288000 bytes for online reports.
Schedule this report.

The “Schedule this report” line is Oracle’s own suggestion: run the same report through the BIP scheduler, which has the higher limit. Data Collage cannot do that for you today. It’s the next feature we’re building, and until then the fix is to make the result smaller.

Why column count matters more than row count

The XML wraps every value in every row with the column name as a tag, so a row’s XML cost is dominated by how many columns you select and how long their names are, not by the data itself. On the demo pod:

Query shape	CSV bytes per row	XML bytes per row	Rows before the 500 MB online limit
SELECT * from GL_JE_LINES (about 70 columns)	~450	~1,800	~290,000
The same table, 17 named columns	~200	~800	~650,000
Two ID columns only	~11	~50	far beyond the row limit below

A wide SELECT * costs about four times its CSV size in XML. Naming only the columns you need is the single most effective way to fit more rows under the limit. Narrowing the WHERE clause is the other.

Limit 2 — maximum CSV rows

Separately from the byte cap, BIP stops writing CSV output after a fixed number of rows. Oracle’s default is 1,000,000; the maximum an administrator can set is 2,000,000. The demo pod we tested is at 2,000,000.

This limit is silent. The query does not fail. BIP writes exactly the maximum number of rows, then appends one more line to the file that reads:

 Flatten data truncated due to MaxRows=2000000 has been reached.

Today Data Collage shows that line as the last row of the grid, and the row count in the toolbar includes it. If your result lands on a suspiciously round number and the last row looks like a sentence rather than data, this is what happened. We’re adding detection for this line so the app warns you instead.

You’ll only reach this limit with narrow queries. A wide query hits the data-size limit first, usually well under a million rows.

What to do when you hit a limit

In rough order of effectiveness:

1. Select fewer columns. Replace SELECT * with the columns you actually need. This can double or quadruple the rows that fit.
2. Filter harder. Add or tighten the WHERE clause: a period, a ledger, a business unit, a date range.
3. Use the row limit while you iterate. The per-tab row limit (default 200) keeps exploratory runs fast. Switch to “No limit” only for the final extract.
4. Split the extract. Run the query once per period, ledger, or ID range and combine the files. Each run gets its own full allowance.
5. For a true bulk extract, schedule the report from the BIP Report Jobs page in Fusion, which uses the higher scheduled-job ceiling, or use an integration tool built for volume such as BICC.

The gateway timeout

There is a third failure that looks similar but has a different cause. If a query runs for several minutes and then fails with an error mentioning HTTP 504 or “Gateway Timeout”, the Fusion web tier gave up waiting for BIP. The query was too slow rather than too large, and BIP may keep running it on the server for a while after you see the error. The fixes are the same as above, plus the usual query tuning: make sure your joins and filters hit indexed columns, and avoid sorting enormous intermediate sets.

Where to learn your pod’s values

Your BI administrator can read and change these under BI Publisher Administration → Runtime Configuration → Properties → Memory Guard & Data Model in Fusion. The relevant properties are Maximum Report Data Size for Online Reports, Maximum Data Size Limit for Data Generation, and Maximum Rows for CSV Output. Raising them affects every BIP report on the pod, so administrators are rightly cautious about it.