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  /

  COUNT(1)
----------
         1

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  /

  COUNT(1)
----------
         0

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
startup

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
startup

Now let's run the script:

SQL> @b
ORACLE instance shut down.
ERROR:
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;

  COUNT(1)
----------
         0

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.

Takeaways

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.