Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
关于login.sql
 
    最近遇到一个问题,在sqlplus首次登录时,会调用login.sql,显示指定的SQLPROMPT,但是当再次使用connect命令连接其他用户时,显示的SQLPROMPT一直无法改变,而且重新connect之后所有在login.sql中设置的属性也全部丢失。找了很久原因之后才发现原来是因为Oracle的版本问题,9i中只能在初次登录sqlplus时才会去调用login.sql,而在10g之后,每次connect都会调用该脚本。
 
    下面看一个转载的文章,说的比较清楚:注意红色粗体部分。
 
---------------------------------------------------------------------------------------------------------
 
Storing settings for SQL*PLUS (login.sql and glogin.sql)
 
glogin.sql

    Whenever SQL*PLUS starts up, it looks for a file namedglogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and the containing statements executed. This allows to store settings (such as linesize) accross SQL*PLUS sessions.
 
    New in Oracle 10g: Oracle also reads glogin.sql and login.sql at a connect in sql*plus.

 
login.sql

    Additionally, after reading glogin.sql, sql*plus also looks for a file named login.sql in the directory from where SQL*PLUS was and in the directory that the environment variable SQLPATHpoints to and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql
 
    If the restriction level is set to 3, the login.sql is not read.

 
A common login.sql file
    set pagesize 0
    set linesize 190
    define _editor=gvim
 
    10g
    Since Oracle 10g, the login.sql is executed after a connect.
    This allows to have a prompt that displays the username.
    For that, the following line must be in the login.sql:
    set sqlprompt "&_user> "
 
------------------------------------------------------------------------------------------------------------
 
    同时在10g官方文档《SQLPlus User's Guide and Reference》里也有记载,设置SQLPLUSCOMPATIBILITY可以改变sqlplus某些属性,其中就包括glogin/login的读取:
 
 
SET SQLPLUSCOMPATIBILITY {x.y[.z]}
 
Value    Consequence                                                         When available
>=10.1   SHOW ERRORS sorts PL/SQL error messages using new                   10.1
         columns only available in Oracle Database 10g.
>=10.1   SPOOL Options CREATE, REPLACE, SAVE were added which                10.1
         may affect filename parsing on some platforms.
>=10.1   SET SQLPROMPT                                                       10.1
>=10.1   Whitespace characters are allowed in Windows file names that        10.1
         are enclosed in quotes. Some other special punctuation characters
         are now disallowed in Windows.
>=10.1   Glogin/login files are called for each reconnect.                   10.1
 <10.1   Uses the obsolete DOC> prompt when echoing /* comments.             10.1
 >=9.2   A wide column defined FOLD_AFTER may be displayed at the            9.2.
         start of a new line. Otherwise it is incorrectly put at the end
         of thepreceding line with a smaller width than expected.
 >=9.0   Whitespace before a slash ("/") in a SQL statement is ignored and   9.0.1.4.
         the slash is taken to mean execute the statement. Otherwise the
         slash is treated as part of the statement, for example, as a
         divisionsign.
 >=9.0   The length specified for NCHAR and NVARCHAR2 types is               9.0.1
         characters. Otherwise the length may represent bytes or
         characters depending on the character set.
 
 
posted on 2009-03-11 23:49 decode360 阅读(196) 评论(0)  编辑  收藏 所属分类: 05.SQL

只有注册用户登录后才能发表评论。


网站导航: