A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
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;