I am too lazy to keep setting debugging messages or pagesize for every session in SQLPLUS. Also, I would like to set the SQLPROMPT look much like the UNIX Prompt. There are essentially two places where I can do it. Either use a file glogin.sql (oracle creates a glogin.sql file - nothing but a shell - at $ORACLE_HOME/sqlplus/admin ) or create a login.sql at the directory where I start sqlplus.
Note: In order to change glogin, super user access is needed. If you do not have super user access, create a login.sql at a local directory.
To display
debugging messages on, use
SET SERVEROUTPUT ON
To set the output size (below 10g)
SET SERVEROUTPUT ON SIZE 10000. (for 10g & above), it is "
SET SERVEROUTPUT ON SIZE UNLIMITED
To set the sqlprompt, enter
SET SQLPROMPT "Srini" - the value given between the double quotes is displayed as SQL Prompt. If you want it to be generic, then type,
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE >"
The above command displays the user, database name, connected (sysdba or normal)
Screen shot of my glogin.sql (
Screen -1) is displayed below:
After modifying the login file, the effects are shown in the
Screen -2 &
Screen -3.
Note: If the
SERVEROUTPUT is not set right, Oracle will raise ORA-20000 error as shown below (
Screen -4):
References: To learn more about SQLPLUS commands click
here