Re: SQL result formatting on TO_CHAR different in 12c

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

-- 
Sandy B.

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