Background
I'm familiar with databases but have never used Oracle in particular. In the process of trying to help someone with a homework assignment, I've hit a snag trying to understand the behavior of the BREAK command. In particular, the results of using the BREAK command seem to depend on the order of the columns in the query I'm using, which as far as I can tell is not reflected in the documentation in any way.
Setup for examples
I created a table where there are a couple of different colors, each with a few items of that color, like so:
CREATE TABLE products(product_id NUMBER, product_color VARCHAR(10), product_name VARCHAR(20));
INSERT INTO products(product_id, product_color, product_name) VALUES(1, 'Green', 'Green baseball cap');
INSERT INTO products(product_id, product_color, product_name) VALUES(2, 'Green', 'Green shirt');
INSERT INTO products(product_id, product_color, product_name) VALUES(3, 'Green', 'Grapes');
INSERT INTO products(product_id, product_color, product_name) VALUES(4, 'Orange', 'Orange baseball cap');
INSERT INTO products(product_id, product_color, product_name) VALUES(5, 'Orange', 'Traffic cone');
Example displaying expected behavior
When I run the following commands,
CLEAR COLUMNS;
CLEAR BREAKS;
CLEAR SCREEN;
SET LINESIZE 120;
COLUMN product_color NOPRINT NEW_VALUE the_color;
BREAK ON product_color SKIP PAGE;
TTITLE CENTER the_color;
SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
This appears to produce reasonable output, with one "page" per color showing the color at the top and showing the relevant records below:
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
Seemingly-identical example exhibiting unexpected behavior
However, if I simply change the order of the columns in the query, i.e. replacing the last line of the script above with something like:
SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
then the results are completely different, generating a page break after every single record for no apparent reason:
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
2 Green shirt
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
5 Traffic cone
Questions
- Why does changing the order of the columns in the query output result in different behavior from the
BREAKstatement? (Or is theBREAKcommand a red herring, with the page breaks in the second example being created for some unrelated reason?) - Is this actually documented somehow in the documentation, but in some way that I'm not understanding?
- Is there a term describing this behavior that I can search for to read more?
- Is there any better documentation for SQL*Plus than the official Oracle site? It seems to be fairly spotty and rarely to unambiguously specify what the commands do.
Possible leads
I have read through the documentation for the SQL*Plus BREAK command located at https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve009.htm#SQPUG030 but I'm not seeing anything relevant to the order that columns appear in.
I thought the issue might potentially have something to do with suppression of duplicates interfering with the detection of the field changing, but adding DUPLICATES to the BREAK command has no effect.
Of the six possible permutations, it looks like the ones that cause a problem are the two where product_color is the first column.
Similarly, changing the query to something like
SELECT 1, product_color, product_id, product_name FROM products ORDER BY product_color;
produces the expected behavior, suggesting that perhaps the leftmost column plays some special role here.
Also, I have discovered that removing NOPRINT from the COLUMN command makes this behavior go away, for reasons I don't understand.
Environment information
The oracle server reports that it is
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
I am using Oracle SQL Developer version 21.2.1.204, Build 204.1703 to connect.