Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

The role each quote plays in this dynamic SQL generation for ALTER SYSTEM KILL SESSION

Y.RamletSep 15 2016 — edited Sep 19 2016

DB version: 11gR2

I wanted to dynamically generate ALTER SYSTEM KILL SESSION SQLs. After facing several "ORA-01756: quoted string not properly terminated" errors I managed to to create the below SQL after 15 minutes. I still don't understand how this works.

I gather that Q-quote mechanism is bit easier than the below mentioned 'traditional' method. But, I would like to know how the 'traditional' method works

I have used 10 quotes in total and I would like to know the role of each quote. I have numbered each quote.

I have added my comments below for each quote. Please let me know the correct reason/role of each quote.

             1                          234        5 6             789           10

            |                          |||        | |             |||           |

SQL> select 'alter system kill session '''||sid|| ','||serial# || ''' immediate;' from v$session where rownum < 4;

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'

--------------------------------------------------------------------------------

alter system kill session '5,32863' immediate;

alter system kill session '3,56757' immediate;

.

.

Quotes 1 and 2 : Generates the string literal alter system kill session<one space>

Quotes 3 and 4 : Don't know which one creates the literal single quote and which one serves as the escape character

Quotes 5 and 6 : Escapes the comma character (I think )

Quotes 7,8 and 9: Not sure which one makes the ending quote literal '  .

Quote 10 : It creates the string literal <one space>immediate;

BTW What is the role of concatenation operator || ?

This post has been answered by KayK on Sep 15 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 17 2016
Added on Sep 15 2016
13 comments
608 views