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 || ?