Not everybody knows that in SQL Plus the character # (called hash or pound) has a special use that I discovered it myself not much time ago. Well, it’s not really a secret, you can read about it on the official SQL Plus manual, but it’s rarely used. The sign #, in fact, is the default character used as SQLPREFIX during a SQL Plus session and can be changed executing the command

SET SQLPRE[FIX] prefix_char

But what does it do? It tells SQL Plus to execute a SQL Plus command while in the middle of entering a SQL statement or a Pl/Sql block of code.
For example you are writing a select statement

and, while writing down the list of columns to extract, you don’t remember the name of one of them. Normally you would quit writing the query, execute a desc of the table and start writing it again from scratch. But the # sign give us another possibility. All you have to do is enter a new line, digit the magic sign # and write the desc you need

As you can see SQL Plus executes the desc command, returns the output and goes back exactly to where you left your original query. This way you can finish writing your statement and execute it

At this point I bet you are asking yourself: do I really need this? Well, honestly nowadays not many people write their code in SQL Plus because several useful IDEs are available for that, even for free like Sql Developer from Oracle. That’s true if we only consider the development phase but it’s very frequent to see automated processes that rely on SQL Plus to deploy Pl/Sql code to the database. And this can open your system to some problems. Why am I saying that? What does have this special sign # to do with automation? Let me tell you a story, sit comfortably and read.
John is a DBA, he creates a Unix shell that deploys some script to a database in order to enable a particular feature. He is very precise and at the beginning of the shell writes some comments for documentation purposes (note: in Unix-like environments lines with # at the beginning are commented)

Paul is the Database Developer responsible for the development of that XYZ feature. He just finished writing the Pl/Sql procedures needed to make this new feature work. Paul decides to include in the main package, as a comment, the header from John’s shell. The result looks like

During the night the automated batch that releases all the new Pl/Sql code compiles this package using SQL Plus.
Do you want to know what happens? Enjoy the view!

Read lines 22 through 32

Those drop and truncate table commands have been executed! But Paul carefully put them inside a Pl/Sql comment, surrounded by /* and */, how is it possible? It is true that those commands were inside a comment but SQL Plus says “Hey mate, that’s #, MY special sign! So you know what? I will execute that command no matter what!”. Small disaster. All this does not happen if you add one or more spaces before # (the SQLPREFIX has to be in first place to trigger). The following code

does not cause any problem.
Moral of the story: be very careful when dealing with # inside your Pl/Sql code. In general, always test locally your code before any release, always using the same exact deployment methodology that will be used later (here SQL Plus and not SQL Developer for example).
But this is something we know very well, don’t we?

Share this post on Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *