Re: SQL result formatting on TO_CHAR different in 12c

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: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l