Friday, January 04, 2013

Quote Operator In Oracle PL/SQL

In our test instance, I was running one of the data fix script provided by Oracle. In the script I noted following statement

 l_sql_stmt :=
  ' UPDATE '||l_driver_tab||' dr '||
   q'$ SET dr.process_flag = 'E'
     WHERE dr.process_flag = 'Y'
          (SELECT 1
             FROM xla_events xe
            WHERE xe.application_id = 200
              AND xe.event_status_code = 'P'
              AND xe.event_id = dr.event_id) $';

I looked at q’$ and thought it is syntax error. But script did not fail, it worked perfectly then I searched Google for “Q’$” in Oracle and I found following link

This is a new way to quote strings in Oracle PL/Sql. The string can have any number of single quotes. So it makes writing dynamic sql very easy. In future when I need to write a dynamic sql and it has any single quote, I will use this mechanism

No comments:

Post a Comment