As IOUG’s
homage to this week’s kick-off of the 30th Olympiad – the 2012 Summer
Olympics in London, England UK, we take a look at the past – what allows
the delivery of this Podcast and Blog to you today – the birth of the
World Wide Web as recorded verbatim at the very first website –
info.cern.ch
IOUG Podcast 28-JUL-2012 London Olympics: Remembering the Birth of the Internet
Subscribe to this Podcast (RSS) or iTunes
Thursday 2 August 2012
Thursday 19 July 2012
Avoid escaping quotes by using user-defined quotes
You’re probably quite familiar with the practice of escaping quotes and other special characters in code. But it can sure be a pain if you or your program has to insert lengthy segments of text. Fortunately, Oracle10g eliminated the necessity of escaping quotes in SQL statements by introducing the ability to have user-defined quote characters.
Prior to Oracle10g, if you wanted to include quotes in text, you had to escape the quote with another quote such as:
SELECT 'Watch your p''s and q''s around mother' from dual;
Now with Oracle10g, you can rewrite this as:
SELECT Q'!Watch your p's and q's around mother!' from dual;
Note that the quoted strings starts with the letter Q, followed by a single quote and the new quote character. It ends with the new quote character and a single quote. I used a exclamation mark (!) as our quote character, but you can use other characters if you’d like. Now you can put any quoted text in between your quote characters.
Another feature is using opening and closing braces q'[text with ']' or q'{text with '}'. The choice is yours, but definately a lot simpler than trying to remember how many times you escaped the single quote.
You can use this feature in PL/SQL as well, like this:
CREATE or REPLACE PROCEDURE testquot (pi_name VARCHAR2)
IS
begin
DBMS_OUTPUT.PUT_LINE('Name is: 'pi_name);
end;
/
Then invoke the procedure in an anonymous block such as:
DECLARE
l_var varchar2(100):= Q'!Watch your p's and q's around mother!';
BEGIN
testquot(l_var);
END;
/
The output is:
Name is: Watch your p's and q's around mother
Prior to Oracle10g, if you wanted to include quotes in text, you had to escape the quote with another quote such as:
SELECT 'Watch your p''s and q''s around mother' from dual;
Now with Oracle10g, you can rewrite this as:
SELECT Q'!Watch your p's and q's around mother!' from dual;
Note that the quoted strings starts with the letter Q, followed by a single quote and the new quote character. It ends with the new quote character and a single quote. I used a exclamation mark (!) as our quote character, but you can use other characters if you’d like. Now you can put any quoted text in between your quote characters.
Another feature is using opening and closing braces q'[text with ']' or q'{text with '}'. The choice is yours, but definately a lot simpler than trying to remember how many times you escaped the single quote.
You can use this feature in PL/SQL as well, like this:
CREATE or REPLACE PROCEDURE testquot (pi_name VARCHAR2)
IS
begin
DBMS_OUTPUT.PUT_LINE('Name is: 'pi_name);
end;
/
Then invoke the procedure in an anonymous block such as:
DECLARE
l_var varchar2(100):= Q'!Watch your p's and q's around mother!';
BEGIN
testquot(l_var);
END;
/
The output is:
Name is: Watch your p's and q's around mother
Saturday 14 July 2012
Did you know you could build 3D Bar Charts in SQL Developer?
Bar Charts in SQL Developer
Very cool feature for some quick BI reports (in a FREE tool no less!)
Simple Bar Chart Reports with SQL Developer thatjeffsmith.com
Subscribe to:
Posts (Atom)