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.
CodePudding user response：
Strange. My environment is just the same as yours, and I don't see such a behaviour. I just removed clear screen from code you posted.
This is query that returns correct result for you (and for me).
Client 11.2.0.2.0
C:\Oracle\oraclexe112_64bit\app\oracle\product\11.2.0\server\bin>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.2.0 Production on ╚et Stu 11 08:03:58 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL>
SQL> SET LINESIZE 120;
SQL>
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL>
SQL> TTITLE CENTER the_color;
SQL>
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
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
SQL>
Now, query that produces unexpected breaks in your database (but doesn't in mine), with product_color being the 1st column in select column list:
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
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
SQL>
I tried different clients as well, no problem:
Client 11.2.0.1.0
SQL*Plus: Release 11.2.0.1.0 Production on ╚et Stu 11 08:10:56 2021
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL>
SQL> SET LINESIZE 120;
SQL>
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL>
SQL> TTITLE CENTER the_color;
SQL>
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
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
SQL>
Client 18.5.0.0.0
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Nov 11 08:12:20 2021
Version 18.5.0.0.0
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL>
SQL> SET LINESIZE 120;
SQL>
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL>
SQL> TTITLE CENTER the_color;
SQL>
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
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
SQL>
I believe that this is crucial:
I am using Oracle SQL Developer version 21.2.1.204, Build 204.1703 to connect.
SQL Developer. Me and Roberto used SQL Plus, Oracle's command-line tool.
When I tested your code that returns "invalid" result, so true!
columns cleared
breaks cleared
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
It is SQL Developer that produces "wrong" result. It has nothing to do with the database version, but the tool.
So, what to do? Use SQL*Plus instead :) Or, download SQLcl, the newest command-line tool Oracle provides.
CodePudding user response：
The behaviour of BREAK in your example is not right. I update this post thanks to @Littlefoot, and is due to the fact that you are using a SqlDeveloper, a java tool not intended for this kind of reporting.
I created your table and insert the records in both databases, then running the reports you did.
Demo 12cR2
SQL> select banner from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> CREATE TABLE products(product_id NUMBER, product_color VARCHAR(10), product_name VARCHAR(20));
Table created.
INSERT INTO products(product_id, product_color, product_name) VALUES(1, 'Green', 'Green baseball cap');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(2, 'Green', 'Green shirt');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(3, 'Green', 'Grapes');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(4, 'Orange', 'Orange baseball cap');
1 row created.
SQL> INSERT INTO products(product_id, product_color, product_name) VALUES(5, 'Orange', 'Traffic cone');
1 row created.
Now let's run the reports
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR breaks;
breaks cleared
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL> TTITLE CENTER the_color;
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
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
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
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
As you have seen, in 12.2 there is no unexpected behaviour in the BREAK command and it works as it should.
Demo 19c
SQL> select banner_full from v$version ;
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> CREATE TABLE products(product_id NUMBER, product_color VARCHAR(10), product_name VARCHAR(20));
Table created.
INSERT INTO products(product_id, product_color, product_name) VALUES(1, 'Green', 'Green baseball cap');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(2, 'Green', 'Green shirt');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(3, 'Green', 'Grapes');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(4, 'Orange', 'Orange baseball cap');
1 row created.
SQL> INSERT INTO products(product_id, product_color, product_name) VALUES(5, 'Orange', 'Traffic cone');
1 row created.
Now, let's run the reports
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL> TTITLE CENTER the_color;
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
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
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
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
So, same expected behaviour of the BREAK command.
Answering your questions
Why does changing the order of the columns in the query output result in different behaviour from the BREAK statement? (Or is the BREAK command a red herring, with the page breaks in the second example being created for some unrelated reason?)
It does not. As I said before, such behaviour can't be reproduced neither in 12c nor 19c ( not in 11g as @Littlefoot shown ). I assure you that the normal behaviour of BREAK is not affected by the order.
Is this actually documented somehow in the documentation, but in some way that I'm not understanding?
Not really, as you understands how the command works but not the behaviour you are getting, which clearly is not reproducible in 12c or higher versions. I have no option to test nothing in 11g as that's a very old and unsupported version, but in the other answer from @Littlefoot, you can see that the behaviour is the same.
Is there any better documentation for SQLPlus than the official Oracle site? It seems to be fairly spotty and rarely to unambiguously specify what the commands do.*
The reference is the best you can find. Obviously, Oracle, as every other major software provider, is not perfect documenting. I can't blame them for that, honestly, as it happens everywhere. Actually, I do think they have one of the best ones.
My advice: Use sqlplus and Upgrade your database. 11g was deprecated years ago. You can try with sqlcl the sql developer command line interface, but I never used against 11g, although I do think it should not be a problem
By the way, regarding I have discovered that removing NOPRINT from the COLUMN command makes this behaviour go away, for reasons I don't understand.
PRINT|NOPRINT Controls the printing of the column (the column heading and all the selected values). NOPRINT turns off the screen output and printing of the column. PRINT turns the printing of the column ON.
Not exactly. If you change NOPRINT by PRINT, it works as it should, so that you might print the column that is part of the BREAK. But, it only shows once in the results, because you break on it.
SQL> COLUMN product_color PRINT NEW_VALUE the_color;
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_CO PRODUCT_NAME
---------- ---------- --------------------
1 Green Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_CO PRODUCT_NAME
---------- ---------- --------------------
4 Orange Orange baseball cap
5 Traffic cone
Hope this long answer help you somehow. Feel free to ask any question or doubt you might have. But I am quite sure that the reason is your version.
UPDATE
As @Littlefoot as stated in his answer below, the issue might be related with SQLDeveloper, which is a Java tool. I did not realize you were using SQL Developer, even though you can run scripts, it is not the same for those sqlplus specific reporting commands. I was relying in your documentation links to sqlplus, so naturally I assume you were using it.