0

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
George Georgiou
  • 455
  • 2
  • 14
  • 27
  • 2
    You might want to consider using a bind variable if you're using this for a cursor, rather than embedding the string value in the query. – Alex Poole Mar 22 '14 at 23:54
  • Also please show your actual code and value, with the error it generates; your comment on Gordon's answer suggests you're trying various things and getting various errors, but you need to show which error goes with which code attempt - mixing and matching, and being vague, don't help... – Alex Poole Mar 23 '14 at 10:17
  • Possible duplicate of [PL/SQL, how to escape single quote in a string?](https://stackoverflow.com/questions/11315340/pl-sql-how-to-escape-single-quote-in-a-string) – J. Chomel Jul 12 '17 at 12:03

1 Answers1

1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The funny this is that i check the parameter in plsql using dbms_output it prints me the value 1001 for example which is the correct (this will be interpreted to datetime). In plsql developer when i have select * from sth where datetimecol > to_timestamp('1001','YYMM') it is working BUT when using this in the plsql block stringquery := 'select * from mytable where datetimecol > to_timestamp('||datevar||',''YYMM'')'; It is not working either problems with expected - got number or plsql numeri value error etc – George Georgiou Mar 23 '14 at 06:43
  • @GeorgeGeorgiou . . . In the example in your comment, you need quotes around `datevar`. – Gordon Linoff Mar 23 '14 at 11:45
  • I have tried enclosing the datevar with 1 single, 2 single, 3 single quotes and with "'||datevar double quote but nothing seems to be working. Is there a workaround this? – George Georgiou Mar 23 '14 at 13:38
  • @GeorgeGeorgiou . . . print out `stringquery` and try running each one. You should quickly get one of the versions to work. – Gordon Linoff Mar 23 '14 at 13:42