​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking

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.

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