Get rid of addtional quotes with Quote operator "q" and DBMS_ASSERT.ENQUOTE_LITERAL

Hi,

Today's post is simple but valuable with Oracle 10g and 11g SQL and PL/SQL writing becoming easy and better with 10g's Quote operator "q" (Yes it is q only) you can get rid of concatenating multiple escape characters to make a text string like This is Oracle's World

Quote operator q
Usage:
You can use any single or multi-byte character or Paired with {} <> () or [] Any thing in between your escape character will display as it is.

Syntax:
q' [escape character] Your Text [Your escape character] '


Example:

select 
q'{This is Oracle's World}' Text1,
q'xThis is Oracle's Worldx' Text2,
q'!This is Oracle's World!' Text3,
q'.This is Oracle's World.' Text4
from dual;

DBMS_ASSERT
With Oracle11g Oracle has now DBMS_ASSERT package which makes life easier for DYNAMIC SQL developers. DBMS_ASSERT's procedure  ENQUOTE_LITERAL which takes a string input and adds leading and trailing single quotes to the output string mainly suitable for PL/SQL but u can use it in SQL as well

Example:
In SQL

 select dbms_assert.enquote_literal(ename)
 from scott.emp;

Output would be like
'SMITH'
'ALLEN'
'WARD'

In PL/SQL Dynamic SQL

 create or replace
procedure insert_into_dept(p_deptno number, p_dname varchar2, p_loc varchar2)
is
  vsql_text varchar2(500);
begin
  vsql_text := 'insert into scott.dept
                (deptno,
                 dname, 
                 loc)
                values
                ('||p_deptno||','||
                 dbms_assert.enquote_literal(p_dname)||','||
                 dbms_assert.enquote_literal(p_loc)||')';
  execute immediate vsql_text;
end;

To test this procedure execute following

select * from scott.dept;
exec insert_into_dept(50,'IT','SAN FRANSISCO'); 
select * from scott.dept;

Check the output of scott.dept procedure has insert rows successfully.

Hope it helps

Cheers,
Baig