3 Replies Latest reply on Apr 26, 2019 5:38 PM by Gamblesk

    sqlcl not showing some columns

    Gamblesk

      SQLcl: Release 19.1 Production

      18c upgraded database.

       

      We just recently upgraded our first database to 18c and when connecting with sqlcl 19.1 I am seeing odd results.. Curious if anyone else has seen anything like this.

      There are columns that I just do not get any data back for at all and desc is not working as expected.

       

      DETL is the 18c database.

       

      /dba_scripts/bin/sqlcl/bin/sql /@DETL

           SQLcl: Release 19.1 Production on Thu Apr 18 11:46:59 2019

           Copyright (c) 1982, 2019, Oracle.  All rights reserved.

           Connected to:

           Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

       

           select open_mode from v$database;

            OPEN_MODE

             ____________

       

       

       

            desc v$session

            ERROR:

            ORA-04043: object "NULL"."NULL" does not exist

       

       

      TETL is the 11.2 database that has not been upgraded.. same version of sqlcl though.

       

      /dba_scripts/bin/sqlcl/bin/sql /@TETL

           SQLcl: Release 19.1 Production on Thu Apr 18 11:56:55 2019

           Copyright (c) 1982, 2019, Oracle.  All rights reserved.

           Connected to:

           Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

           With the Automatic Storage Management option

       

           select open_mode from v$database;

               OPEN_MODE

                _____________

                READ WRITE

       

           desc v$session

                                                          Name    Null?            Type

           ____________________________ ________ _______________

            SADDR                                 RAW(8 BYTE)

            SID                                   NUMBER

       

      Just to show that the value is there here is the same query against the 18c database with sqlplus.

       

      sqlplus /@DETL

      SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 18 11:53:40 2019

      Copyright (c) 1982, 2014, Oracle.  All rights reserved.

      Last Successful login time: Thu Apr 18 2019 11:47:00 -05:00

      Connected to:

      Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

       

      SQL>

      select open_mode from v$database;

       

      OPEN_MODE

      --------------------

      READ WRITE

       

      desc v$session

      Name                                      Null?    Type

      ----------------------------------------- -------- ----------------------------

      SADDR                                              RAW(8)

      SID                                                NUMBER

       

       

      Here is another example of a table I just created.

       

           create table scott_test (a number, b varchar2(10));

           Table SCOTT_TEST created.

           insert into scott_Test values (1,'scott');

           1 row inserted.

            commit;

            Commit complete.

            select * from scott_test;

              A    B

           ____ ____

              1

       

      Message was edited by: Gamblesk editted to include correct query in first example

        • 1. Re: sqlcl not showing some columns
          Gamblesk

          I had moved this to the sqlcl space but did not get a response.  Figured I would try one more time here.

           

          No luck so far with an Oracle SR.. they are unable to reproduce what I am seeing happen.

           

          Although I did find out that if I unset ORACLE_HOME the output does display correctly. That is not really a viable work around as I use a wallet to connect to my databases and without ORACLE_HOME set the wallet is not working correctly.

          • 2. Re: sqlcl not showing some columns
            Gaz in Oz

            If it "works" by unsettling the ORACLE_HOME variable, that suggests it is an issue with library files, java version or something like that.

            Are you connecting remotely or are you on the database server when running sqlcl?

            • 3. Re: sqlcl not showing some columns
              Gamblesk

              The connections were remote and it does appear to be an issue with the installed 12.1.0.2 client.

              Yesterday afternoon I installed an 18 instant client and that works perfectly.

               

              Not sure what is wrong with that client but that is definitely the problem.

               

              Scott