Monday, March 16, 2009

q Function: Escape Single Quotes

Last year Scott Spendolini gave our company an excellent 3 day training session on APEX. He also showed us a very nifty function (new in 10g I think) to help not having to escape single quotes when defining strings. This is not APEX specific, however it will help you if you ever have to write a block of PL/SQL to return an SQL statement for reports.

Instead of writing out a long description here's an example:
DECLARE
  v_sql VARCHAR2 (255);
  v_result VARCHAR2 (255);
BEGIN
  v_sql := 'select ''hello'' into :a from dual';

  EXECUTE IMMEDIATE v_sql
               INTO v_result;

  DBMS_OUTPUT.put_line (v_result);
END;

Notice how I had to put 2 single quotes around "Hello" to escape the single quote characters?

Now using the q function I don't need to do that:
DECLARE
  v_sql VARCHAR2 (255);
  v_result varchar2(255);
BEGIN
  v_sql := q'!select 'hello' into :a from dual !';

  EXECUTE IMMEDIATE v_sql
               INTO v_result;

  DBMS_OUTPUT.put_line (v_result);
END;

Notice now how "Hello" is wrapped as it would appear if it were not in variable definition function?

This can save you a lot of time by avoiding having to escape single quotes in strings!

Update:  q Function Inside a q Function

3 comments:

  1. This was very helpful. Thanks!

    ReplyDelete
  2. Thank you, Martin.

    I found that this technique works for report-specific custom column attributes as in this example:
    DECLARE
    v_sql VARCHAR2(3000);
    v_result VARCHAR2(3000);
    BEGIN
    v_sql := q'!select 'Crew
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -12), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -11), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -10), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -9), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -8), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -7), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -6), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -5), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -4), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -3), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -2), 'Mon yyyy')||'
    :Total WOs Due '||to_char(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM'),'YYYY-MM'), -1), 'Mon yyyy')
    from dual!';

    EXECUTE IMMEDIATE v_sql INTO v_result;
    RETURN v_result;
    END;

    ReplyDelete
    Replies
    1. Hi Scott,

      Glad that it helped out. On a side note you could modify your query to the following to make it easier for maintenance:

      SELECT to_char(ADD_MONTHS(SYSDATE, -level + 1), 'Mon YYYY')
      FROM dual
      connect by level <= 12;

      Hope this helps,

      Martin

      Delete