digest apostrophe in substitution variable

Question

Requirement is to pass a variable with single quote as argument to Execute Immediate.I have tried the below code snippet so far..

how to get the code below working ??

declare
v_val varchar2(5);
v_match varchar2(5);
v_query VARCHAR2(500);
begin
v_val:='a''a';
  dbms_output.put_line(v_val);
v_val:=replace(v_val,'''','''''');
  dbms_output.put_line(v_val);
  v_query:='select val from test where val='||''''||v_val||''''||' and 1=1';                    
  execute immediate v_query into v_match;
  if(v_match=v_val) then
  dbms_output.put_line('match found');
  else
  dbms_output.put_line(' no match found');
end if;
  end;

In other words: I want to accept an input with apostrophe into substitution variable and then use that variable in a select statement's where clause to extract data from table.

i/p value : a'a select * from tabl where keyval=&inputval;

After the `REPLACE` call v_val should have `a''a` in it, but your SELECT statement is looking for leading and trailing apostrophes which don't exist in v_val. Perhaps that'll give you an idea. Best of luck. — Bob Jarvis - Слава Україні, Sep 20 '15 at 21:09
possible duplicate of [PL/SQL, how to escape single quote in a string?](http://stackoverflow.com/questions/11315340/pl-sql-how-to-escape-single-quote-in-a-string) — Ollie, Sep 21 '15 at 07:58

score 1 · Answer 1 · answered Sep 20 '15 at 22:59

Use a bind variable:

declare
  v_val varchar2(5);
  v_match varchar2(5);
  v_query VARCHAR2(500);
begin
  v_val:='aa';
  dbms_output.put_line(v_val);
  v_val:=replace(v_val,'''','''''');
  dbms_output.put_line(v_val);
  v_query:='select val from test where val=:val and 1=1';                    
  execute immediate v_query into v_match using v_val;
  dbms_output.put_line('match found');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line(' no match found');
end;
/

or don't use dynamic SQL:

declare
  v_val   TEST.VAL%TYPE;
  v_match TEST.VAL%TYPE;
begin
  v_val:='a''a';
  dbms_output.put_line(v_val);
  v_val:=replace(v_val,'''','''''');
  dbms_output.put_line(v_val);
  select val INTO v_match from test where val=v_val;
  dbms_output.put_line('match found');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line(' no match found');
end;
/

digest apostrophe in substitution variable

1 Answers1