rockerchain rockerchain - 26 days ago 10
SQL Question

PL/SQL, how to write single quote in a string/function?

In the Oracle PL/SQL I tried this way, it doesn't work:

....
sql_3 := sql_3 || q'[ upper(name_p) like Upper('%input_name%')]';


how to must use literal quoting? or the two quotes to denote a single quote?

Answer

You need to escape ' and also remove q before second part of string. Square bracket in sql command will also make a problem if you're going to execute the command in Oracle

sql_3 := sql_3 || ' upper(name_p) like Upper(''%input_name%'')';

That's a bit weird but:

declare
  varr varchar2(100);
begin
  varr := 'aaa';
  varr := varr || q'[ upper(name_p) like Upper('%input_name%')]';
  dbms_output.put_line(varr);
end;
/

works on my machine.

Comments