When using the ANSICONSOLE format, we ‘measure’ the data per page to get the
best fit effect – so there is a cost involved. Although for most people,
totally worth it, esp for ad hoc use.
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Tuesday, April 18, 2017 3:18 AM
To: gogala.mladen@xxxxxxxxx; Sandra Becker <sbecker6925@xxxxxxxxx>
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: RE: SQL result formatting on TO_CHAR different in 12c
set markup on html
should yield similar results from sqlplus 12.2.
There is also set markup on csv.
If you are doing output at volume, be sure to undertake elapsed time testing
for sqlcl versus sqlplus. Your mileage may vary (neither is really designed for
Prior to sqlplus 12.2, while trimspool works perfectly fine, there is a
character at a time gobbling cost (it apparently blank pads to the linesize and
then chews them off, so it is useful to have linesize big enough but correlated
with the actual output width if your output is big enough to care about speed.
With any variety of “markup” on, it apparently builds the lines without padding
on the right (at least the speed results would indicate that; I have NOT done
an execution reverse engineering.)
As for the increased width of existing output per column: There are two
Put user defined views casting the columns to your desired widths (a bunch of
objects at 30 instead of new larger sizes)
Put column definitions for sqlplus in your glogin.sql or login.sql for all the
columns you care about
#1 would require you to reference your user views.
#2 should be pretty much transparent unless your new column formats conflict
with what you have already defined. In that case YOUR behavior would be
preserved by leaving incumbent column format definitions in place.
NONE of this is intended to dissuade anyone from sqlcl or dispute any points
already made in this useful thread. sqlcl is a welcome and useful new tool.
If you’re trying to restore the sqlplus behavior prior to the exposed column
format changes, this might be the easiest thing to do. I prefer #2.
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Monday, April 17, 2017 5:11 PM
To: Sandra Becker
Subject: Re: SQL result formatting on TO_CHAR different in 12c
Here is why I love sqlcl:
mgogala@umajor:~$ sql scott/tiger@test122
SQLcl: Release 4.2.0 Production on Mon Apr 17 17:09:02 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Oracle Database 12c Enterprise Edition Release 220.127.116.11.0 - 64bit Production
First, let's do standard SQL*Plus stuff, which does the same thing as SQL*Plus.
The new client is written in such a way to be as compatible with SQL*Plus as
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
7369 SMITH CLERK 7902 19801217 800
7499 ALLEN SALESMAN 7698 19810220 1600 300
7521 WARD SALESMAN 7698 19810222 1250 500
7566 JONES MANAGER 7839 19810402 2975
7654 MARTIN SALESMAN 7698 19810928 1250 1400
7698 BLAKE MANAGER 7839 19810501 2850
7782 CLARK MANAGER 7839 19810609 2450
7788 SCOTT ANALYST 7566 19870419 3000
7839 KING PRESIDENT 19811117 5000
7844 TURNER SALESMAN 7698 19810908 1500 0
7876 ADAMS CLERK 7788 19870523 1100
7900 JAMES CLERK 7698 19811203 950
7902 FORD ANALYST 7566 19811203 3000
7934 MILLER CLERK 7782 19820123 1300
14 rows selected.
Now, let's turn on "special" formatting:
SQL> set sqlformat ansiconsole
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 19801217 800 20
7499 ALLEN SALESMAN 7698 19810220 1600 300 30
7521 WARD SALESMAN 7698 19810222 1250 500 30
7566 JONES MANAGER 7839 19810402 2975 20
7654 MARTIN SALESMAN 7698 19810928 1250 1400 30
7698 BLAKE MANAGER 7839 19810501 2850 30
7782 CLARK MANAGER 7839 19810609 2450 10
7788 SCOTT ANALYST 7566 19870419 3000 20
7839 KING PRESIDENT 19811117 5000 10
7844 TURNER SALESMAN 7698 19810908 1500 0 30
7876 ADAMS CLERK 7788 19870523 1100 20
7900 JAMES CLERK 7698 19811203 950 30
7902 FORD ANALYST 7566 19811203 3000 20
7934 MILLER CLERK 7782 19820123 1300 10
14 rows selected.
Basically, you have the SQL*Developer formatting options available from command
line. Also, from SQL*Plus, you can use HTML formatting to prevent line
wrapping. There is also a "repeat" command which can turn SQLcl into an instant
monitor. Yes, it would require some testing, but the wast majority of your
scripts would probably work as it is. You will have to change the scripts for
12c anyway. In addition to that, SQLcl has an excellent help and its principal
author is on this forum, very willing to answer questions.
On 04/17/2017 04:48 PM, Sandra Becker wrote:
We don't have SQLPATH or ORACLE_PATH set in these environments. Not sure about
using sqlcl; it would still require making changes to several environments.
Not something our users are keen on right now.
On Sun, Apr 16, 2017 at 11:32 AM, Mladen Gogala <HYPERLINK
I don't have anything with such large lines, so I cannot test, but I have
recently switched to sqlcl, which I find superior to SQL*Plus in many aspects.
Also, what used to be SQLPATH in releases before 12c is now called ORACLE_PATH.
A little bit of additional marketing doesn't hurt, I suppose. This is what I
use for my initialization script:
if [ -t 0 ]; then
stty erase '^?' intr '^C' susp '^Z' quit '^Y' kill '^X' echoe
[ -r $HOME/.aliases ] && source $HOME/.aliases
The beginning if -t 0 is a remnant from an old version of Red Hat which used to
be confused by LD_LIBRARY _PATH set to $ORACLE_HOME/lib and some GUI tools did
not work. As fas as I remember, there was an incompatible Python library in
$ORACLE_HOME/lib, which used to mess up Red Hat GUI.
On 04/13/2017 04:13 PM, Sandra Becker wrote:
Oracle EE 18.104.22.168, 2-node RAC
We recently upgraded from 22.214.171.124 to 126.96.36.199. We have several scripts
running out of crontab to monitor various aspects of our applications. The
analysts are seeing different formatting of the results of these scripts in 12c
than they did in 11g.
11g - all output was on one line - linesize set to 1000, only 10 columns, date,
timestamp, and number formats. In the script, they use TO_CHAR to get the
12c - no changes to the script; now each column is on a separate line. If I
set linesize to 10000, I see the expect behavior with a whole lot of whitespace
I still had copy of this production database that I used to practice the
upgrade. I went in and changed the parameter permit_92_wrap_format to false.
Behavior reverted back to what we saw in 11g. However, I'm not convinced this
is the right workaround.
Has anyone else see this behavior? We have a lot of scripts and changing all
of them to use a column alias and column formatting (which should have been
done in the first place) would be a major undertaking. I haven't found any
bugs on MOS related to this yet or any useful information in the Oracle docs.
My co-worker is opening an SR, but no response yet.
Thank you in advance for any guidance.
Tel: HYPERLINK "tel:%28347%29%20321-1217"(347) 321-1217
Tel: (347) 321-1217