This discussion is archived
6 Replies Latest reply: Nov 13, 2012 4:04 AM by mseberg RSS

sqlplus error when using easy connect syntax

greeny Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points