Can anybody shed some light on what the effect is of the OPTIMIZED and UNOPTIMIZED options for SET SERVEROUTPUT (introduced in 18.2)???
While looking at 20.2 I noticed something in the help for "set serveroutput" that I don't know and for which I can't find any other information.
Until version 18.1.1 the help said:
ERO@EVROCS>version
Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0
ERO@EVROCS>help set serveroutput
SET SERVEROUTPUT
SET SERVEROUT[PUT] {ON|OFF}
[SIZE {n | UNLIMITED}]
[FOR[MAT] {WRA[PPED] |
WOR[D_WRAPPED] |
TRU[NCATED]}]
ERO@EVROCS>
But from 18.2 upwards it says:
(Notice OPTIMIZED | UNOPTIMIZED)
ERO@EVROCS>version
Oracle SQLDeveloper Command-Line (SQLcl) version: 18.2.0.0
ERO@EVROCS>help set serveroutput
SET SERVEROUTPUT
SET SERVEROUT[PUT] { OPTIMIZED | UNOPTIMIZED | {ON|OFF}
[SIZE {n | UNLIMITED}]
[FOR[MAT] {WRA[PPED] |
WOR[D_WRAPPED] |
TRU[NCATED]}]}
ERO@EVROCS>
I never heard of the optimized and unoptimized options and they are not known to sqlplus.
The help does not explain what is does or how to use it.
"Optimized" adds the "optimized" keyword to the current setting of serveroutput without changing the setting of the other options (SIZE/FORMAT)
"Unoptimized" removes the "optimized" keyword from the current setting of serveroutput without changing the setting of the other options (SIZE/FORMAT)
ERO@EVROCS>show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WRAPPED
ERO@EVROCS>set serveroutput optimized
ERO@EVROCS>show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WRAPPED OPTIMIZED
ERO@EVROCS>set serveroutput unoptimized
ERO@EVROCS>show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WRAPPED
ERO@EVROCS>
Also, both optimized and unoptimized can be set while serveroutput is set to OFF, the setting will remain when setting serveroutput to ON
ERO@EVROCS>show serveroutput
serveroutput OFF
ERO@EVROCS>set serveroutput optimized
ERO@EVROCS>show serveroutput
serveroutput OFF OPTIMIZED
ERO@EVROCS>set serveroutput on
ERO@EVROCS>show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT TRUNCATED OPTIMIZED
ERO@EVROCS>
When setting Optimized or Unoptimized the other options van not be used in the same statement, they can only be used with ON or OFF.
Funny enough, when using Optimized or Unoptimized with one of the other options the errormessage claims that serveroutput must be set to ON or OFF
Apparently the errormessage hasn't been updated for the new syntax.
ERO@EVROCS>set serveroutput optimized size unlimited
SP2-0265: serveroutput must be set ON or OFF
ERO@EVROCS>
The above leads me to the conclusion that the syntax in the help is not entirely correct
I think it should be:
ERO@EVROCS>help set serveroutput
SET SERVEROUTPUT
SET SERVEROUT[PUT] { OPTIMIZED | UNOPTIMIZED | { {ON|OFF}
[SIZE {n | UNLIMITED}]
[FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
}
}
ERO@EVROCS>
Also, since it appears to add an extra option to the existing options, it seems to me that it should have been an option like SIZE and FORMAT, and not something that replaces ON/OFF.
As it is, if I want to set serveroutput on with size unlimited, format wrapped and optimized, it takes two SET statements, where it looks like it should be possible to do it in one.
Still not a clue what optimized does, so this last remark might be nonsense....