rockerchain rockerchain - 1 year ago 81
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 Source

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:

  varr varchar2(100);
  varr := 'aaa';
  varr := varr || q'[ upper(name_p) like Upper('%input_name%')]';

works on my machine.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download