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  /

  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.

15 comments:

play school in hyderabad said...

Good Information
Sanjary kids is the best playschool, preschool in Hyderabad, India. Start your play school,preschool in Hyderabad with sanjary kids. Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and Teacher Training Program.
play school in hyderabad, India
Preschool in hyderabad, India
Preschool teacher training course in hyderabad, India
pre and primary teacher training course in hyderabad,India
early childhood teacher training course in hyderabad, India

UpdateNewth said...

Update News Maxi
Maxi

boy said...

This is my blog. Click here.
จุดได้เปรียบของนักเดิมพันในเกมบาคาร่าออนไลน์"

edok69 said...

Maybe the best site right now Terrible that no one is watching
myoceanhousecondo
ecurie-competition

Best E-Learning marketplace App Solution said...

I would like to thank you for the efforts you have made in writing this post. Thanks for posting Such Things. I should recommend your site to my friends.

Best E-Learning App Solution said...

Nice and interesting post, I appreciate your hard work, keep uploading more, Thank you for sharing valuable information.

chanee said...

A good website is useful.
ป๊อกเด้งออนไลน์ ฟรี
หวยออนไลน์ เว็บไหนดี
เล่นสล็อต เว็บไหนดี

milo said...

สล็อตแตกง่าย
Super post. Thanks for sharing.

BEO89 said...

Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for.สล็อต แตกง่าย

Anonymous said...

สล็อตออนไลน์เกมเดิมพันที่ได้รับความนิยมมากที่สุดในคาสิโน เกมเดิมพันออนไลน์รูปแบบใหม่ น่าเล่น รูปแบบการเล่นที่ไม่เหมือนใคร อีกทั้งยังมีฟังก์ชั่นที่จะช่วยให้ผู้เล่นทำเงินได้อย่างต่อเนื่อง วันนี้ทาง sagameallinone ได้รวบรวม เทคนิคในการทำเงินกับเกมสล็อตออนไลน์ สูตรสล็อตออนไลน์ที่ใช้ได้ทุกค่าย ใช้ได้ทุกเกม อ่านต่อ.

Zonahobisaya said...

laman web yang baik : Zonahobisaya
laman web yang baik : One Piece
laman web yang baik : lambang
laman web yang baik : One Piece
laman web yang baik : Zonahobisaya
laman web yang baik : One Piece
laman web yang baik : Zonahobisaya
laman web yang baik : Zonahobisaya

เล่นง่ายได้เงินจริง said...

สล็อต PGSLOTแตกง่าย เล่นง่ายได้เงินจริงผู้ให้บริการเกมสล็อต pg slot ออนไลน์บนโทรศัพท์เคลื่อนที่ที่มีเกมนานาประการให้เลือก เป็นเกมรูปแบบใหม่ที่ทำเงินให้ผู้เล่นได้เงินจริง 

เล่นง่ายจ่ายจริง said...

โปรโมชั่น pg slot มากมาย เล่นง่ายจ่ายจริง แตกจริง ต้อง PG-สล็อต เท่านั้น! เล่นสล็อต พีจีสล็อต เว็บไซต์ตรงผู้ให้บริการเกมสล็อตออนไลน์ชั้นหนึ่ง ทกลอง เล่น ฟรี พร้อมโบนัส

Edward said...

Good article about SQL+PLUS.



___________________________________
I work in geospatial analytics.

Vishal said...

"Your blog is an absolute gem! The way you effortlessly weave words together to create captivating content is truly remarkable. Each post is a delightful journey that leaves me craving for more.

Employee Recognition Software
Employee Rewards Software
Employee Recognition System
Employee Recognition Platform
Employee Rewards System
Employee Rewards platform"

Translate