CODEWITHSUNDEEP
oracle-databaseplsql
George Georgiou
George Georgiou

Reputation: 465

oracle escape single quote in string query

How can i escape a single character in a string query in a plsql block. I an tried escaping with double quotes but didn't seem to be working. I want to select data from a period of time and afterwards but the variable of date i have is in YYMM format and i am converting it to timestamp or datetime accordingly.

This is a sample of the code :

declare
datevar     varchar2(4);
stringquery varchar(1000);
begin
stringquery := 'select * from mytable where datetimecol > to_timestamp('||datevar||',''YYMM'')';

For i in stringquery Loop
  do sth

Upvotes: 0

Views: 3061

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Two single quotes are interpreted as one quote in the string, so your statement is relatively correct. Perhaps you want quotes around "datevar" as well:

stringquery := 'select * from mytable where datetimecol > to_timestamp('''||datevar||''',''YYMM'')';

You can easily test this:

select 'select * from mytable where datetimecol > to_timestamp('''||datevar||''',''YYMM'')'
from (select 'x' as datevar from dual) t

Upvotes: 1

Related Questions