The variable _USER contains the current user name given by SHOW USER. In complex Oracle Database environments, where Database Adminstrators (DBAs) login into hundreds of databases, it will be useful to change default SQL prompt to some meaningful prompt, like ORACLE SID or database name. The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2Īnd _DATE, _PRIVILEGE and _USER were introduced in SQL*Plus 10.1. Will display privilege such as SYSDBA, SYSOPER, SYSASM, SYSDG, SYSBACKUP, SYSKM, SYSRAC
Will display editor name used by the EDIT command. TEXT can be predefined substitution variables which are prefixed with an underscore. To setup SQL prompt for session or permanently, use below SET command. $ORACLE_HOME/sqlplus/admin/glogin.sql or login.sql Settings from the login.sql take precedence over settings from glogin.sql.
So SQL prompt will be changed after connect command. From Oracle 10g, the login.sql file is not only executed at SQL*Plus startup time, but also at connect time as well. In Oracle9i, whenever a user connects through SQL*PLUS, Oracle will execute only glogin.sql, from 10g Oracle will execute login.sql as well. From Oracle 10g, after reading glogin.sql, SQL*PLUS also looks for a file named login.sql (User profile script), in the directory from where SQL*PLUS was and in the directory that the environment variable SQLPATH points to, and reads it and executes it. This allows to store settings across SQL*PLUS sessions. If the file is found, it is read and the containing statements executed.
#Oracle sql prompt plus#
Whenever SQL * PLUS starts up, it looks for a file named glogin.sql (Site profile script) under the directory $ORACLE_HOME/sqlplus/admin. Prior to Oracle9i, we used to do elaborate coding to get the information as the SQL prompt, from Oracle 9.2.0 we can use SET SQLPROMPT along with SQL*Plus predefined variables. The default prompt in SQL*Plus is SQL>, does not provide any information like who the user is and what the user is connected as.
0 kommentar(er)
