Re: SQL result formatting on TO_CHAR different in 12c

A couple of things to note on this. If you have scripts that explicitly set such formatting, then maintaining them is a part of the cost of upgrade.

The second thing, is that I see Stephan has an Oracle Cloud signature which presumably implies 12.2. If the scripts you are referring to are login.sql then there is a behaviour change in 12.2 which means they won't run. See Franck Pachot at
https://blog.dbi-services.com/oracle-12cr2-changes-for-login-sql/ for the details.

On 14 Apr 2017 02:44, "Raza Siddiqui" <raza.siddiqui_at_oracle.com> wrote:

> One thing to bear in mind when using a glogin.sql - any settings set
> therein can be superseded in the individual scripts, if set there.
>
> Lookout for a FREE video by Oracle's Chris Saxon, who discusses changes to
> column name lengths etc in 12c.
>
> The video is titled "*12 Things Developers Will Love About Oracle
> Database 12c Release 2". *
>
> On Apr 13, 2017, at 1:50 PM, Stephan Uzzell <stephan.uzzell_at_oracle.com>
> wrote:
>
> Hey Sandra,
>
>
>
> A lot of columns got bumped up significantly in size in 12c. desc
> dba_objects in a 12c and 11g database to see. Owner, subobject_name, and
> edition_name have all been bumped from 30 to 128.
>
>
>
> I’m almost certain that’s what you’re running into. I don’t know an easy
> workaround other than, as you described, formatting the columns.
>
>
>
> We’ve been playing with changing them in glogin.sql that we can then push
> out to all our servers, rather than updating each and every script.
>
>
>
> Hope that helps,
>
> stephan
>
>
>
> <image001.png>
>
> *Stephan Uzzell* | Sr. Database Engineer | +1 410.227.6732
> <http://+1%20410.227.6732>
>
> Oracle Cloud for Industry | Cloud Database Operations
>
> Swarthmore, PA | US
>
>
>
> *From:* Sandra Becker [mailto:sbecker6925_at_gmail.com
> <sbecker6925_at_gmail.com>]
> *Sent:* Thursday, April 13, 2017 4:13 PM
> *To:* oracle-l <oracle-l_at_freelists.org>
> *Subject:* SQL result formatting on TO_CHAR different in 12c
>
>
>
> 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.
>
>

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