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
No comments:
Post a Comment