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
|
1 |
SQL> select empno, ename, |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select empno, ename, 2 # desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 2 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SQL> select empno, ename, 2 # desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 2 sal from emp; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 EMPNO ENAME SAL ---------- ---------- ---------- 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected. |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
################################################################# # Author: John Goodman # Deployment of feature XYZ # Use: thisshell <dbconn> <enable> # # This shell installs the XYZfeature on the specified database # In case of problems, to manually remove the feature execute # the following command in SQL environment: # # drop table XYZ_TAB_1; # drop table XYZ_TAB_2; # truncate table GENERAL_LOG; |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
create or replace package XYZ_MAIN as /* Main package for XYZ. This package is called by the Unix shell ABC: ################################################################# # Author: John Goodman # Deployment of feature XYZ # Use: thisshell <dbconn> <enable> # # This shell installs the XYZfeature on the specified database # In case of problems, to manually remove the feature execute # the following command in SQL environment: # # drop table XYZ_TAB_1; # drop table XYZ_TAB_2; # truncate table GENERAL_LOG; # */ function fun1(p1 varchar2) return number; end XYZ_MAIN; / create or replace package body XYZ_MAIN as function fun1(p1 varchar2) return number is begin return 1; end fun1; end XYZ_MAIN; / |
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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
SQL> create or replace package XYZ_MAIN as 2 3 /* Main package for XYZ. This package is called by the Unix shell ABC: 4 5 ################################################################# SP2-0734: unknown command beginning "##########..." - rest of line ignored. 5 # Author: John Goodman SP2-0734: unknown command beginning "Author: Jo..." - rest of line ignored. 5 # Deployment of feature XYZ SP2-0734: unknown command beginning "Deployment..." - rest of line ignored. 5 # Use: thisshell SP2-0734: unknown command beginning "Use: thiss..." - rest of line ignored. SP2-0044: For a list of known commands enter HELP and to leave enter EXIT. 5 # 5 # This shell installs the XYZfeature on the specified database SP2-0734: unknown command beginning "This shell..." - rest of line ignored. 5 # In case of problems, to manually remove the feature execute 6 # the following command in SQL environment: SP2-0734: unknown command beginning "the follow..." - rest of line ignored. 6 # 6 # drop table XYZ_TAB_1; Table dropped. SQL> # drop table XYZ_TAB_2; Table dropped. SQL> # truncate table GENERAL_LOG; Table truncated. SQL> # SQL> SQL> */ SP2-0042: unknown command "*/" - rest of line ignored. SQL> SQL> function fun1(p1 varchar2) return number; SP2-0734: unknown command beginning "function f..." - rest of line ignored. SQL> SQL> end XYZ_MAIN; SP2-0734: unknown command beginning "end XYZ_MA..." - rest of line ignored. SQL> / Table truncated. SQL> create or replace package body XYZ_MAIN as 2 3 function fun1(p1 varchar2) return number 4 is 5 begin 6 return 1; 7 end fun1; 8 9 end XYZ_MAIN; 10 / Package body created. SQL> |
Read lines 22 through 32
|
1 2 3 4 5 6 7 8 9 10 11 |
6 # drop table XYZ_TAB_1; Table dropped. SQL> # drop table XYZ_TAB_2; Table dropped. SQL> # truncate table GENERAL_LOG; Table truncated. |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
create or replace package XYZ_MAIN as /* Main package for XYZ. This package is called by the Unix shell ABC: ################################################################# # Author: John Goodman # Deployment of feature XYZ # Use: thisshell <dbconn> <enable> # # This shell installs the XYZfeature on the specified database # In case of problems, to manually remove the feature execute # the following command in SQL environment: # # drop table XYZ_TAB_1; # drop table XYZ_TAB_2; # truncate table GENERAL_LOG; # */ function fun1(p1 varchar2) return number; end XYZ_MAIN; / create or replace package body XYZ_MAIN as function fun1(p1 varchar2) return number is begin return 1; end fun1; end XYZ_MAIN; / |
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?
