Using a string with embedded quotes in PLSQL

Add one more single quote next to the one you want to escape.

SQL> select 'the sopranos'''
2  from dual
3  /

'THESOPRANOS'
-------------
the sopranos'

Or use Chr():

SELECT 'alan'||chr(39)||'s database' FROM Dual

PLSQL is Weird!