CODEWITHSUNDEEP
sqloracle-databaseplsql
Prashant Mishra
Prashant Mishra

Reputation: 647

digest apostrophe in substitution variable

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;

Upvotes: 0

Views: 273

Answers (1)

MT0
MT0

Reputation: 168026

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;
/

Upvotes: 1

Related Questions