Skip to Main Content
  • Questions

  • sql commands show different output from 11g to 12c

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Naveen Reddy.

Asked: July 18, 2017 - 9:09 pm UTC

Last updated: July 31, 2017 - 3:12 am UTC

Version: Oracle Database 12.1.0.2

Viewed 1000+ times

You Asked

sql commands show different output from 11g to 12c

Ex:
12.1.0.2
---------
SQL> sho parameter nls_date_format

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
nls_date_format string
DD-MON-YYYY
SQL>

11g
---
SQL> sho parameter nls_date_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-YYYY
SQL>

Note: We haven't set anything in glogin.sql anywhere and didn't even set any parameters while executing commands. This is even effecting our reports also by giving some wrong format.

and Connor said...

This is a change to sqlplus for security reasons.

SQL Plus no longer picks up login.sql in the current directory which probably explains the issue.

More details here

https://mikedietrichde.com/2017/04/17/security-change-in-oracle-database-12-2-with-login-sql/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Checking in further

A reader, July 18, 2017 - 11:36 pm UTC

Thank you for the update Connor!
FI: This is 12.1.0.2 env. And also this is working well in other same 12.1.0.2 env's but not in few. Looking to see if there is any parameter effecting this. Didn't find any parameter changes so far and also there is no login.sql or glogin.sql used.
Connor McDonald
July 19, 2017 - 11:04 pm UTC

It could possibly be the internal change in size to some columns in the data dictionary.

eg

SQL> desc user_objects
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OBJECT_NAME                            VARCHAR2(128)
 SUBOBJECT_NAME                         VARCHAR2(128)
 OBJECT_ID                              NUMBER
 DATA_OBJECT_ID                         NUMBER
 OBJECT_TYPE                            VARCHAR2(23)
 CREATED                                DATE
 LAST_DDL_TIME                          DATE
 TIMESTAMP                              VARCHAR2(19)
...


Notice that many dictionary names have changed from 30 bytes to 128. In 12.1, the limit is still 30, but the *dictionary* has changed to 128 (which was then made "public" in 12.2).

Solution worked out

Naveen Reddy Kancharla, July 26, 2017 - 6:19 pm UTC

Here is the parameter effected in giving different output for SQL commands and in Reports.
Parameter - "cursor_sharing"
The value was "FORCE" which caused issues in SQL output.
When we changed "EXACT" - SQL outputs and reports were normal.
Connor McDonald
July 31, 2017 - 3:12 am UTC

Ah...thanks for getting back to us with the solution. It will help others.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1817300100346392420
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.