handle special characters in PL/SQL procedures.?

Question

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 characters in plsql?

before:

    lquery := 'select count(id) into lCount
                        From
                            dual
                        where
                            name = '||iName||'
                         and Id= '||iId

after:

     select into lCount
                        From
                            dual
                        where
                            Id= iId
                         and name = iName;

after changing the query its working fine. Problem is if we keep variable like name value inside single quotes some times query wont execute for special characters like ' , " etc after changing query its working fine.

Please edit your post to include an example of code which fails. Also include information on how you're invoking this code as that may have a bearing on the answer. Thanks. — Bob Jarvis - Слава Україні, Apr 02 '14 at 11:00
You shouldn't concatenate your queries like that. Use parameters instead `select ... where id = :1 and name = :2` — , Apr 02 '14 at 12:03

score 2 · Accepted Answer · edited May 23 '17 at 12:05

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;

handle special characters in PL/SQL procedures.?

1 Answers1

Linked