Oracle PL/SQL/Select Query/Quote

Alternative Quoting Mechanism in Oracle 10g: two quotes really means one quote

   <source lang="sql">

SQL> create table TestTable(

 2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
 3    Description           VARCHAR2(30 BYTE)
 4  )
 5  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. SQL> SQL> select * from TestTable; ID DESCRIPTION

---

------------------------------

1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> -- Alternative Quoting Mechanism in Oracle 10g SQL> SQL> -- two quotes really means one quote SQL> SQL> INSERT INTO testTable VALUES (99,"32 O""Neal Drive"); 1 row created. SQL> SQL> select * from testTable; ID DESCRIPTION

---

------------------------------

1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 99 32 O"Neal Drive 8 rows selected. SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL>

 </source>
   
  

q"{32 O"Hara Avenue}"): Use a "q" as the leading character after the parentheses

   <source lang="sql">

SQL> create table TestTable(

 2    ID                    VARCHAR2(4 BYTE)         NOT NULL,
 3    Description           VARCHAR2(30 BYTE)
 4  )
 5  /

Table created. SQL> SQL> SQL> insert into TestTable (ID, Description) values("1","1234 5th Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("2","1 Culloden Street"); 1 row created. SQL> insert into TestTable (ID, Description) values("3","1234 Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("4","33 Thrid Road"); 1 row created. SQL> insert into TestTable (ID, Description) values("5","One than another"); 1 row created. SQL> insert into TestTable (ID, Description) values("6","2003 Movie"); 1 row created. SQL> insert into TestTable (ID, Description) values("7","Start With Letters"); 1 row created. SQL> SQL> select * from TestTable; ID DESCRIPTION

---

------------------------------

1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 7 rows selected. SQL> SQL> SQL> --Uses a "q" as the leading character after the parentheses and allows specification of a "different" sequence to define quotes SQL> SQL> INSERT INTO testTable VALUES (99, q"{32 O"Hara Avenue}"); 1 row created. SQL> SQL> select * from testTable; ID DESCRIPTION

---

------------------------------

1 1234 5th Street 2 1 Culloden Street 3 1234 Road 4 33 Thrid Road 5 One than another 6 2003 Movie 7 Start With Letters 99 32 O"Hara Avenue 8 rows selected. SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>

 </source>
   
  

Quoted Identifiers

   <source lang="sql">
   

SQL> DECLARE

 2    "End" NUMBER := 1;
 3  BEGIN
 4    dbms_output.put_line("A quoted identifier End ["||"End"||"]");
 5  END;
 6  /

A quoted identifier End [1] PL/SQL procedure successfully completed. SQL>

 </source>
   
  

Quoting syntax is q"[ ... ]" where the [] is the user-defined

   <source lang="sql">
   

SQL> SQL> SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE("Colorado""s National Parks are BEAUTIFUL");
 3  END;
 4  /

Colorado"s National Parks are BEAUTIFUL PL/SQL procedure successfully completed. SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE(q"!Colorado"s National Parks are BEAUTIFUL!");
 3  END;
 4  /

Colorado"s National Parks are BEAUTIFUL PL/SQL procedure successfully completed. SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE(q"[Colorado"s National Parks are BEAUTIFUL]");
 3  END;
 4  /

Colorado"s National Parks are BEAUTIFUL PL/SQL procedure successfully completed. SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE(q"<Colorado"s National Parks are BEAUTIFUL>");
 3  END;
 4  /

Colorado"s National Parks are BEAUTIFUL PL/SQL procedure successfully completed. SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE(q"%Colorado"s National Parks are BEAUTIFUL%");
 3  END;
 4  /

Colorado"s National Parks are BEAUTIFUL PL/SQL procedure successfully completed.

 </source>
   
  

Using two single-quotes print the string correctly

   <source lang="sql">
   

SQL> SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE("C""s");
 3  END;
 4  /

C"s PL/SQL procedure successfully completed.

 </source>