OK - I’ve got a query that selects rows that, for my default linesize (133) ends up producing 3-lines of output for each row of data (4 counting the blank line).
This ends up with a single row displaying on two pages.
Is there an Oracle option that will keep everything for one row on one page? I could put in specific line breaks and set the pagesize such that it works for my defaults, but it is generally run from a terminal session, and I occasionally have need of non-default values (for instance, I’ll set the line very long and spool the output to use the data in a script).
And, of course, there is more than one query that has the same issue.
I’m running 11.2.0.4.0 if that makes a difference.
Try wrapping the three rows of fields in a repeating frame. Then inspect the frame’s properties. I can’t find a comprehensive list of all the settable properties of a repeating frame. But I’d bet that’s where you’ll find something like “Permit/Prohibit break across page boundary.” If it exists.
Thanks for the reply. I’m not at all familiar with frames, but assuming they map to “frameworks”, a quick Google makes it sound like way more work then I’m looking to do for queries that are basically used for troubleshooting problems.
Here is the query:
select eventNumber, topic, publisher, publishedIndr "pubInd",
createDateTime, lastPublishDateTime, removeDateTime,
checkPublishExceptionIndr "exInd", processGroup, publishCount "pubCnt",
contents
from eventQueue
where contents like 'identifier=%&1%'
and lastPublishDateTime between sysdate-5 AND sysdate
order by eventNumber;
‘contents’ is always a separate line (unless there is a ridiculously long linesize), and the other fields currently split after ‘publisher’. Note that, assuming there is a solution, I’d like it work for a ‘select *’ also.
As mentioned, there are other queries (against other tables) that have similar issues. I won’t be surprised if there is no easy/simple way to do this, in which case I’ll just continue reading around the headings in the middle of the screen.
Assuming you are talking about sqlplus, can’t you just size the columns smaller with the COLUMN … FORMAT option? I’m guessing one of the earlier column’s default size is pushing the total past your linesize setting, right? More info here: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12013.htm
Also, as a longer-term project, really consider not using sqlplus as your reporting tool, it’s primitive and not very good at it.
@Folacin: I’d assumed you were actually using Oracle Reports to format and display your data. That’s where you’ll get group- and page-level formatting controls like repeating frames. “Frameworks” are something altogether different.
If you’re just keystroking “SELECT <blah blah blah>” into a SQLPlus command line and watching what scrolls back on the console, then broadly speaking, no you don’t have any real control over the mess that spews forth.
As arseNal says just above you do have a teeny bit of control at the column level. But none at the row level.
Use the right tool to get the results you want the easy way.
As mentioned in my second post, these are generally ad-hoc queries (and I should have mentioned that it is SQL*Plus) that are used for trouble-shooting problems in the system. Real reports are created using a real tool.
I actually like the automatic line wrapping, because it lets me read things on my screen. I was hoping there was something (easy) along the lines of MS Word’s widow and orphan control, but it appears not (at least not in sqlplus).