SQL*PLUS Tips and Tricks

What Database Are You Working In?
Working in the wrong database is a common problem for database experts as well as their less experienced counterparts. How many times have YOU found yourself running statements in the wrong environment? Feel free to include me in that not so select group. The operating system command SET can be used in Windows systems to display environment variables. The ENV command can be used to display the environment variables in UNIX. Many seasoned database administrators change their UNIX shell prompt in their.profile to display the current Oracle SID. Displaying the current Oracle SID in the shell's prompt provides a continuous reminder to the DBA of the database they are working in.

GLOGIN.SQL and LOGIN.SQL
Administrators are able to use two operating system files, GLOGIN.SQL and LOGIN.SQL to customize their SQL*PLUS environment (including the prompt). When SQL*PLUS starts, it will look for GLOGIN.SQL in $ORACLE_HOME/SQLPLUS/admin and will execute its contents as soon as the connection to the database is complete.

Once the GLOGIN.SQL file has been executed, the LOGIN.SQL file will be executed. Oracle will look for the LOGIN.SQL file in the current working directory (where you started SQL*Plus) and the operating system environment variable SQLPATH. Remember that LOGIN.SQL can overwrite the actions of GLOGIN.SQL. The exact names of these files may be different on some operating systems. Check the Oracle installation and administration guides provided for your operating system for the exact names.

Here's an example of my GLOGIN.SQL file on my PC that displays the time and instance name in my SQL*PLUS prompt. The file also contains a few formatting commands to format SQL*PLUS output. In this example, I'm using a SELECT statement to populate a variable that is displayed in the prompt. I'll show you how to use a SQL*PLUS pre-defined variable to do the same thing later in this blog.

COLUMN file_name FORMAT a44
COLUMN tablespace_name FORMAT a20
COLUMN owner FORMAT a15
COLUMN segment_name FORMAT a20
set lines 132
set pages 100
set termout off
col dbname new_value prompt_dbname
select instance_name dbname from v$instance;
set sqlprompt "&&prompt_dbname> "
set termout on
set time on

The Problem in Releases Prior to 10G
When I first tested this feature many years ago, I thought to myself "hey, this is a pretty cool feature." A fellow administrator walked by and saw that I was changing my prompt. He told me "you'll have to watch that because if you connect to another database, you won't execute the GLOGIN.SQL file again. That means you'll still have the original database name displayed in the prompt but you will be connected to the new database. There's a few other tricks you can do but just putting the display commands in GLOGIN won't work and its actually kind of dangerous."

10G SQL*PLUS comes to the rescue. GLOGIN.SQL and LOGIN.SQL are now executed after each successful connection to a target database. This is in addition to the files being run during the initial execution of the SQL*PLUS tool. Each time you connect to a different database in 10G SQL*PLUS, the prompt will change accordingly. The two demos below will show you the differences between 10G and 9I SQL*PLUS. Both 9I and 10G installations have the script above embedded in the GLOGIN.SQL file.

10G SQL*PLUS DEMO

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 22 07:14:10 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

07:14:11 orcl9i> * Prompt shows database I connected
07:14:17 orcl9i> * to when I activated SQL*PLUS
07:14:18 orcl9i>
07:14:18 orcl9i>
07:14:18 orcl9i>
07:14:18 orcl9i> connect system/maxwell@orcl
Connected.
07:14:26 orcl>
07:14:28 orcl> * Prompt changes when I connect
07:14:28 orcl> * to a different database
07:14:28 orcl>

9I SQL*PLUS DEMO

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Jun 22 07:16:01 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

07:16:02 orcl9i> * Prompt shows database I connected
07:16:03 orcl9i> * to when I activated SQL*PLUS
07:16:03 orcl9i>
07:16:04 orcl9i>
07:16:04 orcl9i>
07:16:04 orcl9i>
07:16:04 orcl9i> connect system/maxwell@orcl.com
Connected.
07:16:14 orcl9i>
07:16:15 orcl9i> * Notice that the prompt doesn't change
07:16:16 orcl9i>
07:16:16 orcl9i> select name from v$database;

NAME
---------
ORCL

07:16:23 orcl9i>

Pre-Defined Variables
Pre-defined SQL*PLUS variables can also be used to customize a user's prompt. Currently, SQL*PLUS provides the following pre-defined variables (variables that are new in 10G are highlighted in bold):

• _CONNECT_IDENTIFIER - Connection identifier used to establish the database connection
  
  
• _DATE - Current date, or a user defined fixed string
  
  
• _EDITOR - Specifies the editor that will be used when the EDIT command is executed
  
  
• _O_VERSION - Oracle database version
  
  
• _O_RELEASE - Full release number of the Oracle Database
  
  
• _PRIVILEGE - The privilege level the current user has assigned to them
  
  
• _SQLPLUS_RELEASE - Full release number of the SQL*PLUS program being used
  
  
• _USER - Account name used to make the connection

For example, the command below could be embedded in GLOGIN.SQL or LOGIN.SQL to display the account used to connect to the database and the connection string:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

The demo below shows the results:

>
>
>
>SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
SYSTEM@orcl >
SYSTEM@orcl >
SYSTEM@orcl >

I don't care how experienced of a DBA you are, you are always in danger of working in the wrong database. It takes but a momentary distraction to lay the groundwork for a catastrophic error. Anything I can do to reduce my anxiety when I execute that 'DROP USER…. CASCADE" command, makes me a more efficient and more productive administrator. Taking five minutes to customize my prompt is well worth the effort - when you compare it to the disastrous results that can occur as the result of administering the wrong database. Now I can finally quit executing "SELECT NAME FROM V$DATABASE" to tell me what database I'm in.

Enhancements to the SPOOL Command
The SPOOL command tells SQL*PLUS to send all output to the specified flat file. Think how many times you have used the SPOOL command to save your output for later review. Well, 10G improves the usablity of the SPOOL command by adding the following syntax:

• APPEND - Appends output data to an existing file. The command will create a new file if the specified file is not found
  
  
• CREATE - Creates a new output file and will return an error if the file already exists
  
  
• REPLACE - This is the default option. REPLACE will replace an existing file or create it if it is not found

DBMS_OUTPUT Line Length and Output Limit Increases
Oracle provides the DBMS_OUTPUT package to display debugging information from PL/SQL code. The package was not designed to display reports or format output to standard output. But there are times when developers need to pump out large amounts of diagnostic information to help them debug their programs.

But developers using 10G R1 and earlier versions were frequently constrained by DBMS_OUTPUT's 255 byte single line limit and total session limitation of 1 million bytes. As a result, when using SQL*PLUS to debug their PL/SQL code, developers often received the following error messages:

ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
Single line limit of 255 bytes exceeded.

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
Maximum amount of 1 million bytes per session exceeded.

10G R2 increases the single length line limit to 32,767 bytes and the overall session limit is removed altogether.

Using PUPBLD.SQL
PUPBLD.SQL can be used for more than stopping those irritating "PRODUCT PROFILE TABLE NOT LOADED" messages from occurring when you log on to SQL*PLUS. PUPBLD.SQL creates a product profile table that is read by SQL*PLUS when users log on to the product.

Front-end applications can be designed to prevent users from updating data incorrectly. Other applications don't require database accounts because the user logs on the application and not the database. But there are still numerous applications that require database defined accounts and passwords. What happens if the user logs on to the database using their database account and password using SQL*PLUS? The application code is not available to prevent them from making incorrect or unwarranted changes to the database data.

Administrators can prevent this from happening by inserting rows into the product profile table to disable a SQL*PLUS user's ability to execute:

• SQL*PLUS commands - COPY, EDIT , EXECUTE, EXIT, GET ,HOST (or your operating system's alias for HOST, such as $on VMS, and ! on UNIX),QUIT, PASSWORD, RUN, SAVE, SET, SPOOL, START
  
  
• SQL statements ALTER, ANALYZE, AUDIT, CONNECT, CREATE, DELETE, DROP, GRANT, INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, SET ROLE, SET TRANSACTION, TRUNCATE, UPDATE
  
  
• PL/SQL commands - BEGIN, DECLARE

SQL*PLUS reads restrictions from PRODUCT_USER_PROFILE when a user logs on using SQL*PLUS and maintains those restrictions for the duration of the session. To disable a SQL or SQL*PLUS command for a given user, insert a row containing the user's username in the USERID column, the command name in the ATTRIBUTE column, and DISABLED in the CHAR_VALUE column.

Conclusion
I hope you enjoyed this blog on SQL*PLUS.

Thanks for reading.

Chris Foot
Oracle Ace