Handle Special Characters In Pl/sql Procedures.?

I'm using oracle 10g plsql procedures for inserting and listing, but if we have any special characters like ' (single quote ) and & etc. query fails. How to handle special char

Solution 1:

First how to handle a quote ' and an ampersand &:

SQL@xe> set define off
SQL@xe> select q'(foo's & bar's)'from dual;

Q'(FOO'S&BAR'
-------------
foo's & bar's

SQL@xe>

See also How do I ignore ampersands in a SQL script running from SQL Plus? and Text Literals for details of alternative quoting mechanism q''.

Second don't create SQL statements as strings but instead use PL/SQL Static SQL. Static SQL will handle the quoting automatically for you (and is also SQL injection safe). Like:

declare
  lCount number;
  iName varchar2(20) := q'(foo's & bar's)';
  iId number := 42;
begin
  select count(*) into lCount From dual where name = iName and Id= iId;
end;