6 Replies Latest reply: Nov 13, 2012 6:04 AM by mseberg RSS

    sqlplus error when using easy connect syntax

    greeny
      Version: SQL*Plus in version 11.2.0.3 on RHEL 5.4

      Technically , this is an SQL*Plus question which should be posted on SQL forum. But someone has already posted it there but didn't get any reply.

      Too long error in SQL*Plus

      I think RAC DBAs using easy connect syntax will only get this ignorable error. Hence posting it here.

      I am borrowing the same example from the above post.

      I am trying to connect to a RAC Service as shown below using easy connect syntax. I get the " +is too long. maximum size is 50 characters+" error .
      $ sqlplus W2ML_HLDP_DEV_01/W2ML_HLDP_DEV_01_123@rldpasbmlc-scan.ap.standardabsunited.com:1734/W2ML_HLDP_01.ap.standardabsunited.com
       
      SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 24 18:24:31 2012
       
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
       
       
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
      Data Mining and Real Application Testing options
       
      string beginning "'W2ML_HLDP..." is too long. maximum size is 50 characters.
      SQL>
      I get this only on the DB server. From my Windows XP laptop's sqlplus , I don't get this error !
        • 1. Re: sqlplus error when using easy connect syntax
          Iordan Iotzov
          There are two SQL*Plus programs in a typical Windows installation:
          sqlplusw – the Windows version.
          sqlplus – the command line version.

          You used sqlplus on the server. What version did you use on the Windows machine? If use user sqlplusw, try running sqlplus (but first going into command line mode with “cmd”).

          Iordan Iotzov
          http://iiotzov.wordpress.com/
          • 2. Re: sqlplus error when using easy connect syntax
            greeny
            From Windows , I used t the command line version (sqlplus ) . I don't get any error from Windows.
            I get the error only at DB server side which is in RHEL 5.4
            • 3. Re: sqlplus error when using easy connect syntax
              mseberg
              Hello;

              I believe your sqlprompt on the server is the issue:

              Was able to recreate on mine.
              SQL> set sqlprompt 'Every night and every morn some to misery are born> '
              string beginning "'Every nig..." is too long. maximum size is 50 characters.
              Test ( my 83 length string not shown )
              SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 12 11:24:20 2012
              
              Copyright (c) 1982, 2011, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> 
              Your connect string is 130 by my count.

              My 83 length string used this format without error

              sqlplus user/password@hostname:1521/servicename


              Best Regards

              mseberg

              Edited by: mseberg on Nov 12, 2012 11:25 AM
              • 4. Re: sqlplus error when using easy connect syntax
                585179
                Hi,

                The issue you got is not because easy connect but instead the sqlprompt set after you login. Check you glogin.sql setup

                When sqlprompt is not set
                [ora10g@test1 admin]$ sqlplus data1/data1@new.zealand.wellington.oracle.cluster.prod.database.11g.co.nz:1521/new.zealand.wellington.oracle.cluster.prod.database.11g.co.nz
                
                SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 15:00:39 2012
                
                Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                
                
                Connected to:
                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
                With the Partitioning and Data Mining options
                
                SQL> exit
                Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
                With the Partitioning and Data Mining options
                when sqlprompt is set
                [ora10g@test1 admin]$ vi glogin.sql
                [ora10g@test1 admin]$ sqlplus data1/data1@new.zealand.wellington.oracle.cluster.prod.database.11g.co.nz:1521/new.zealand.wellington.oracle.cluster.prod.database.11g.co.nz
                
                SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 15:00:49 2012
                
                Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                
                
                Connected to:
                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
                With the Partitioning and Data Mining options
                
                string beginning "'DATA1@new..." is too long. maximum size is 50 characters.
                SQL>
                Hope it helps


                Cheers
                • 5. Re: sqlplus error when using easy connect syntax
                  greeny
                  Thank you mseberg. Thank you Fiedi.

                  Yes. sqlprompt is set for the DBs which we have this issue.


                  This is from an 11.2.0.2 with the same issue

                  This is how glogin looks like
                  $ cat /optware/oracle/product/db/11.2.0.2.0/sqlplus/admin/glogin.sql
                  --
                  -- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
                  --
                  -- NAME
                  --   glogin.sql
                  --
                  -- DESCRIPTION
                  --   SQL*Plus global login "site profile" file
                  --
                  --   Add any SQL*Plus commands here that are to be executed when a
                  --   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
                  --
                  -- USAGE
                  --   This script is automatically run
                  --
                  SET sqlprompt '&_user:&_connect_identifier > '
                  I've noticed that that I don't get this error when I login as sys.
                  This is how it looks like , when I try to login in as SYS in DB server

                  $ sqlplus / as sysdba
                  
                  SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 13 10:51:46 2012
                  
                  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                  
                  
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                  With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                  Data Mining and Real Application Testing options
                  
                  SYS:CHSMCP1 > 
                  With normal business schemas , I get error
                  $ sqlplus EEM_UAT_01/EEM_UAT_01_psd@rldpasbmlc-scan.us.standardabsunited.com:1734/W2ML_HLDP_01.ap.standardabsunited.com
                  
                  
                  SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 13 10:52:15 2012
                  
                  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                  
                  
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                  With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                  Data Mining and Real Application Testing options
                  
                  string beginning "'EEM_UAT_..." is too long. maximum size is 50 characters.
                  SQL>
                  SQL>
                  So, what should i change in SET SQLPROMPT to fix this error ?

                  Why don't I get this error when I login as Sys ?
                  • 6. Re: sqlplus error when using easy connect syntax
                    mseberg
                    Hello again;

                    Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and the containing statements executed.

                    So I would change it back to its default :
                    SET sqlprompt '&_user:&_connect_identifier > '
                    
                    to
                    
                    -- SET sqlprompt '&_user:&_connect_identifier > '
                    If you have a login.sql file either supplement or override the preferences in glogin.sql. So its possible login.sql is "Protecting" the SYS user.

                    glogin.sql      preferences that apply to all users at your site
                    login.sql      contains only preferences for a particular user

                    Best Regards

                    mseberg