Thursday, November 14, 2013

How to properly mask qotation marks in an SQL statement

Example:

You want to insert the string

order_date=to_date('2013-11-14','YYYY-MM-DD')

into a column of a table.

For oracle to understand that you want the single quotes to be part of the inserted string, you need to mask it as below:

INSERT INTO SCOTT.FILTER_TABLE
       (filter_name,
        filter_priority,
        table_owner_pattern,
        table_name_pattern,
        column_name_pattern,
        sql_filter)
VALUES ('MYFILTER',
        3,
        'LXPROD%',
        '%',
        'ORDER_DATE',
        'order_date=to_date(''&&target_date'',''YYYY-MM-DD'')');

No comments:

Post a Comment