Thursday, July 13, 2017

Role of # in SQL*Plus

The # character is for commenting in SQL*Plus, right?

The character # has been mostly used for comments in many languages, such as shell scripts and python. Interestingly # is legal syntax in SQL scripting as well; but is it considered a comment? The answer is no; it's not. The purpose of # in SQL scripts is very different. and you should be very careful using it.

Entering # tells SQL*Plus to temporarily pauses what has been entered before and execute everything after that #sign, as if in a different session. Here is a usecase. Suppose you are writing this query:

SQL> select *
  2  from v$sesstat
  3  where

[Typographical Conventions: User inputs are shown bold. System outputs are not.]

At this time you are stuck. You don't remember the column names of V$SESSTAT. But you need to know that to use it as a predicate. What can you do? You could just press ENTER at this point, which causes the SQL*Plus prompt to be redisplayed. You can describe the view and that will be your answer. However, this means you will have lost all the input you have entered until then. In case of a complicated long query it is hardly desirable.

This is where the # symbol comes to help. Just type # and enter the command you want to be displayed. In this case you want to describe the view. So enter at the 4th line prompt of SQL*Plus:

  4  #desc v$sesstat

When you enter this command and press ENTER, SQL*Plus will halt the evaluation of all the command it is doing now and execute the desc v$sesstat command. Here will be the output:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 SID                                                NUMBER
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER
 CON_ID                                             NUMBER

After this display, the SQL*Plus prompt will show the 4th line as the prompt "4". Now that you know the column, enter the rest of the SQL statement you were entering

  4  con_id = 1 
  5  /

And that's it. Note how SQL*Plus didn't discard any of the commands already entered. Instead it merely paused the evaluation and started evaluating the command "desc v$sesstat" after the # symbol. This symbol "#" is not a comment character but a temporary new command without discarding the previous commands in play in the session.

Unintended Ramifications

This behavior of # character may result in some scenarios not at all intended. Take for instance the following scenario. We create a table, insert a row, do not commit and then check the number of records.

SQL> create table t (col1 number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> select count(1)
  2  from t
  3  where 1 > 0
  4  /


We got the results as expected. Now let's introduce a small line #roll in between the commands. Perhaps someone assumed it was a comment and left it there.

SQL> select count(1)
  2  #roll
Rollback complete.
  2  from t
  3  where 1 > 0
  4  /


Note how the counts came back as 0. Why? It's because the rollback occurred independently of the select command. By the time the select statement was completed, the rollback statement had rolled back the insert and hence there were no rows in the table.

In some cases this behavior might prove deadly. Here is another example I have seen, from a script that shuts down the database, takes a storage snapshot and restarts it.

shutdown abort

Later it was decided that shutdown abort is not desirable. It should be shutdown transactional. So, the DBA changed the script to the following. Assuming the # character to be comment, she thought she was commenting the line instead of removing it:

#shutdown abort
shutdown transactional

Now let's run the script:

SQL> @b
ORACLE instance shut down.
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
ORACLE instance started.

Total System Global Area 5117050880 bytes
Fixed Size                  8757424 bytes
Variable Size            1056968528 bytes
Database Buffers         4043309056 bytes
Redo Buffers                8015872 bytes
Database mounted.
Database opened.                                                                                                                    

What happened? The shutdown failed with "ORACLE not available". Why?

The answer is simple. The shutdown abort executed. The database was already down. Therefore the shutdown transactional failed.

Don't Like #?

Now that you see how some people may confuse # as a comment and make mistakes, you may not like to have this special property assigned to #. Instead, you may want another character, say "*". You can easily change it. The special setting in SQL*Plus called SQLPREFIX allows you to do that:

SQL> set sqlprefix *

Now, let's repeat the same example we used earlier but with * instead of #:

SQL> select count(1)
  2  * desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 COL1                                               NUMBER

  2  from t;


What if you use # character in this case?

SQL> select count(1)
  2  # desc t
  3  from t;
# desc t
ERROR at line 2:
ORA-00911: invalid character

You will not go anywhere. You will get an error. It's probably better than an assumption of # character as a comment.


  1. The # character is not a comment in SQL scripts.
  2. When SQL*Plus encounters a # character at the first position, it temporarily pauses the evaluation of the statements being entered and immediately executes the command after # and resumes evaluation of the previously entered commands afterwards.
  3. You can assign another character to exhibit this behavior by setting the SQLPREFIX to that character.

Monday, July 10, 2017

Enough with AFIEDT.BUF

You are in SQL*Plus. You entered a command and urgh, there was a typo. No worries, you bring up the command in an editor by typing:

SQL> ed

This opens up an editor, such as notepad.exe in Windows or vi in Unix, etc. And it puts the last SQL you entered in a file, oddly named, afiedt.buf. You don't like it and you want a name easier to type. Is it possible? Of course.

History of Afiedt.Buf

First a little bit of background information on the odd name. SQL*Plus as a tool evolved from another tool Oracle provided a long, long time ago, called--rather uncreatively--User Friendly Interface, or UFI. When the editor wanted to bring up a file for editing, the file had to be given a name unique enough so as not to conflict with anything else. Therefore the file was named ufiedt.buf, which roughly indicated UFI Editor Buffer.

Later as UFI was added new features, it was called Advanced UFI (well, "advanced" is relative). To keep pace, the generated file was called Advanced UFI Editor Buffer, or aufiedt.buf. However an operating system that Oracle supported couldn't handle a 7 character file name before the period before suffix. Therefore the name was shortened to afiedt.buf to keep it distinct from ufiedt.buf (which presumably was not as "advanced"). As the advancements in technology came, Oracle didn't bother to change it and the name stuck. Long live afiedt.buf!

Why Change It?

If ain't broken, don't fix it. Shouldn't that be the case? The name doesn't break anything; so why change it? There are three reasons to at least consider.
  1. The name may difficult to type.
  2. he extension of ".buf" is not automatically recognized by SQL*Plus as a SQL script.
  3. The file is created in the present directory. Suppose you are in a directory you don't have permission on, e.g. "/etc" in Unix or \Windows in Windows, you can't just bring up the editor as you won't have the permissions to create the file there. You will get this error:
SP2-0110: Cannot create save file "afiedt.buf"
  • What do you do? Exit from SQL*Plus, change to a proper directory and re-execute this command? Then you would lose all the settings you may have done in that session. So you would want to create the file in a location of your choosing.
So, while you mean no disrespect to Oracle's legacy, you would want to relegate afiedt.buf to where it belongs--to the footnotes of history.

How can I Change It?

It's ridiculously simple to change the file. A setting in SQL*Plus controls it.

SQL> set editfile arup.sql

After this when you type the editor command:

SQL> ed
Wrote file arup.sql

Look how the file created was named "arup.sql"; not afiedt.buf. Of course you can use any name as you find appropriate.

Changing Directory

Let's handle the second problem. You are in a directory where you don't have write permission and you want to edit the command. Well, you can use a full path to the file as well.

SQL> set editfile c:\temp\arup.sql
SQL> ed
Wrote file c:\temp\arup.sql

The file is created in the C:\temp directory instead of the default option of the current directory.

Changing the Editor

While on the subject, do you know that you can change the default editor as well? For instance, if you are on Windows, you don't have to have Notepad as the editor for SQL scripts. It can be notepad++, or, as I prefer, "vim", which is enhanced vi. In Unix, the default is not vi; it's "ed" and you may want to change it. How to do that?

Very simple; another setting _EDITOR allows us to do that. It's a variable; so you have to use DEFINE command. Use the following:

SQL> define _editor = vi

That's it; now when you enter "ed" command, it will bring up vi editor. Change to whatever you want to set.

Tips for Practical Uses

If you want to change the default editor and the default file for editing, put the commands in the glogin.sql file, which ensures their automatic execution when someone invokes SQL*Plus.

Afiedt.Buf is dead. Long live Afedit.Buf. Or, was it Afiedt.Buf? Oh, who cares?

Friday, July 07, 2017

Setting APPINFO in SQL*Plus

Ever used the MODULE column of V$SESSION view? If you haven't, you are missing out on a very important piece of instruemntation code built right into the Oracle database session management. This allows you to assign a completely arbitary name, as you would see will properly describe for your application. Later when you want to identify that session, you can check the MODULE column in V$SESSION. Even though the userid is the same for all these sessions; the module column will help you identify the session.

Let's see how it works by a little example. Here is how you check the module information.

select module
from v$session
where username = 'SYS'


In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:

SQL> exec dbms_application_info.set_module('MyModule','MyAction')

PL/SQL procedure successfully completed.

Now if you check the module:


As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.

But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.

There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:

SQL> show appinfo
appinfo is OFF and set to "SQL*Plus"

To set to a value you want, use the follwoing:

SQL> set appinfo MyApp

Now if you check the sessions from another sessions:

SQL> select module
2 from v$session
3 where username = 'SYS';


See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.

What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.

set appinfo "AcmeModule"

Now any session using that Oracle Home will have the Module column set to AcmeModule.

However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:

set appinfo "App1"

Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.

A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.

Hope you find use of this little known setting in SQL*Plus.