RE: SQL result formatting on TO_CHAR different in 12c
Date: Tue, 18 Apr 2017 04:17:44 -0700 (PDT)
Message-ID: <92f19981-0944-4ed1-9bc2-ee0b25355233_at_default>
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_at_rsiz.com]
Sent: Tuesday, April 18, 2017 3:18 AM
To: gogala.mladen_at_gmail.com; Sandra Becker <sbecker6925_at_gmail.com>
Cc: oracle-l <oracle-l_at_freelists.org>
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_at_freelists.org"oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_umajor:~$ sql scott/tiger_at_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 307902 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_at_gmail.com"gogala.mladen_at_gmail.com> 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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 18 2017 - 13:17:44 CEST