Page 174
SQL statements may contain literal single quotes in them, such as when a possessive form of a noun is used (e.g., 'Roberts's Bike'). Until now, the literal quotes had to be double quoted to make it clear to the SQL or PL/SQL engine that they were literal (e.g., 'Robert''s Bike'). This tends to start to look really ugly in code, and can cause lots of errors that are sometimes hard to find.
Oracle Database 10g offers a solution to this problem in the form of user-specified quote character assignment. Now, the ' symbol can be replaced by just about any single- or multibyte delimiter or the character pairs [ ], { }, ( ), or < >.
The delimiter is defined by using the quote operator, q, followed by a quote and then the assigned replacement quote delimiter to be used. Here is an example that uses the bracket pair ([ ]) as quote delimiters:
SQL> INSERT INTO record VALUES 2 (q'[Robert's book is good isn't it?]'); SQL> SELECT * FROM record WHERE 2* the_value=q'XRobert's book is good isn't it?X'; THE_VALUE -------------------------------------------------- Robert's book is good isn't it?
In our this example, we inserted a record into the RECORD table. We used a bracket set as the delimiter. Then, we queried the same record, this time using the letter Xas our delimiter. In both cases, there are single quotes at the beginning after the q operator, and at the very end after the final delimiter.
Oracle Database 10g offers a number of new PL/SQL packages that you can use. I have introduced a number of them in this book already. In this section, I introduce two additional packages. The first, utl_compress, allows you to compress database data to be loaded within columns. The second, utl_mail, allows you to manage mail within the database much easier.
Oracle Database 10g introduces the ability to compress and decompress BLOB or RAW data using the Lempel Ziv compression algorithm. This is supported via the new Oracle-supplied package utl_compress. The utl_compress package provides