PL/SQL and the pesky single quote

You know when you're constructing strings in PL/SQL and you try and concatenate say a varchar variable with a string, but the variable needs to be enclosed in single quotes. You want something like,

SELECT x FROM table1 WHERE z = '123'

where 123 is held as a variable, so you have to do something like this :-


sql_select := 'SELECT x FROM table1 WHERE z = ' || '''' || :some_variable || '''';

How many single quotes do you need is it two, three - four?

Its messy so why not get the DECIMAL representation of a single quote and use that with the CHR function instead. Get the DECIMAL value of the single quote (or any other ASCII character) using:-

SQL> SELECT ASCII('''') FROM DUAL; -> Answer 39

Yup, 4 quotes required - see what I mean !


Now all you need is something like.

DECLARE
qte varchar2(1) := CHR(39);
BEGIN
sql_select := 'SELECT x FROM table1 WHERE z = ' || qte ||:some_variable || qte;
END;