RE: SQL result formatting on TO_CHAR different in 12c

  • From: Jeff Smith <jeff.d.smith@xxxxxxxxxx>
  • To: mwf@xxxxxxxx, gogala.mladen@xxxxxxxxx, Sandra Becker <sbecker6925@xxxxxxxxx>
  • Date: Tue, 18 Apr 2017 04:17:44 -0700 (PDT)

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 
bulk output.)

 

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 
reasonable options:

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.

 

mwf

 

From: HYPERLINK 
"mailto:oracle-l-bounce@xxxxxxxxxxxxx"oracle-l-bounce@xxxxxxxxxxxxx ;
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Monday, April 17, 2017 5:11 PM
To: Sandra Becker
Cc: oracle-l
Subject: Re: SQL result formatting on TO_CHAR different in 12c

 

Hi Sandra, 
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.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> 
 

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 
possible:

SQL> 
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. 
 
Elapsed: 00:00:00.148
 
 

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. 
 
Elapsed: 00:00:00.243
SQL> 

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.
Regards

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.

Sandy

 

On Sun, Apr 16, 2017 at 11:32 AM, Mladen Gogala <HYPERLINK 
"mailto:gogala.mladen@xxxxxxxxx"gogala.mladen@xxxxxxxxx> wrote:

Hi Sandra,
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
    ORACLE_HOME=/usr/lib/instantclient_12_1
    LD_LIBRARY_PATH=$ORACLE_HOME
    TNS_ADMIN=/usr/local/tns
    TWO_TASK=local
    PATH=$ORACLE_HOME:$PATH
    SQLPATH=$HOME/misc/SQL
    ORACLE_PATH=$SQLPATH
fi
EDITOR="vi"
NLS_DATE_FORMAT='YYYYMMDD'
PATH=$PATH:$HOME/sqlcl/bin
cd $HOME
stty erase '^?' intr '^C' susp '^Z' quit '^Y' kill '^X' echoe
unset LS_COLORS
TERM=vt100
tset -r
[ -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 12.1.0.2, 2-node RAC 

RHEL 5 

We recently upgraded from 11.2.0.4 to 12.1.0.2.  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 
desired format. 

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 
between columns. 

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. 

-- 

Sandy B. 

-- 
Mladen Gogala
Oracle DBA
Tel: HYPERLINK "tel:%28347%29%20321-1217"(347) 321-1217

-- 

Sandy B. 

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

Other related posts: