5 Replies Latest reply: Nov 22, 2012 5:50 PM by rp0428 RSS

    How to escape ' in dynamic sql

    Dinesh Rajasekharan-Oracle
      Dear Experts ,

      I am trying to escape single quote(') in dynamic sql but could not find a way , I used escape character / and two single quotes '' but it did not work .

      The following is dynamic SQL I am try to generate :

      SQL> select 'exec dbms_stats.gather_index_stats(ownname=>\'SYSADM\', indname=>\''||iname||'\', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' from test_missing_index;
      select 'exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>''||iname||'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' from test_missing_index
      *
      ERROR at line 1:
      ORA-00923: FROM keyword not found where expected


      The desired out put should be :

      exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>'test_index', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

      Any help will be highly appreciated
        • 1. Re: How to escape ' in dynamic sql
          sb92075
          Rajasekharan Dinesh wrote:
          Dear Experts ,

          I am trying to escape single quote(') in dynamic sql but could not find a way , I used escape character / and two single quotes '' but it did not work .

          The following is dynamic SQL I am try to generate :

          SQL> select 'exec dbms_stats.gather_index_stats(ownname=>\'SYSADM\', indname=>\''||iname||'\', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' from test_missing_index;
          select 'exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>''||iname||'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' from test_missing_index
          *
          ERROR at line 1:
          ORA-00923: FROM keyword not found where expected


          The desired out put should be :

          exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>'test_index', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

          Any help will be highly appreciated
          http://www.lmgtfy.com/?q=oracle+q+quote
          • 2. Re: How to escape ' in dynamic sql
            Frank Kulash
            Rajasekharan Dinesh wrote:
            Dear Experts ,

            I am trying to escape single quote(') in dynamic sql but could not find a way , I used escape character / and two single quotes '' but it did not work .
            Two single-quotes (with<b>out</b> aby kind of escape) is correct.
            Starting in Oracle 10, you might find Q-notation easier to use:
            select      Q'{exec dbms_stats.gather_index_stats(ownname=>'SYSADM', indname=>'}'
                ||  iname
                ||  Q'{', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);}' 
            from      test_missing_index;
            Read all about it in the SQL Language manual. Look up "Text literals"
            http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements003.htm#sthref337

            Using 2 single-quotes (in any version of Oracle), you could do it like this:
            select      'exec dbms_stats.gather_index_stats(ownname=>''SYSADM'', indname=>'''
                ||  iname
                ||  ''', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);' 
            from      test_missing_index;
            You want the string to have a single-quote right before inname. That will be inside a text literal, so it has to be doubled. That happens to be the very last thing in that text literal, so the single-quote that ends the literal comes right after it, resulting in 3 consecutive single-quotes.
            Likewise, the literal immediately after iname starts with a single quote, so, immediately after the single-quote that starts that literal, you have 2 single-quotes.

            Edited by: Frank Kulash on Oct 27, 2011 7:47 PM
            • 3. Re: How to escape ' in dynamic sql
              Dinesh Rajasekharan-Oracle
              Thanks a lot Frank
              • 4. Re: How to escape ' in dynamic sql
                axvelazq
                I have a similar issue, but the Q-quoting did not work for me, please help =)


                I have an script that receives an string as a parameter, for example:
                @C:/myScript.sql "AXEL";
                @C:/myScript.sql "AXEL DAVID";
                @C:/myScript.sql "o'neal";

                my scripts basically constructs a Parameterized query an execute it. That's all. It works most of the time, except when the parameter contains apostrophes
                DEFINE myparameter = &&1
                ...
                myquery := "Select * from myTable where x = :p1";
                EXECUTE IMMEDIATE myquery USING myparameter;
                ...
                When trying the following;
                @C:/myScript.sql "o'neal";
                I get the following error:
                Bind Variable "p1" is NOT DECLARED

                Of course if I change the parameter as "o''neal" It will work, but I will end up with other issues later on, so I would like to know how can I deal with apostrophe on dynamic queries.

                I also tried to use the following:
                ...
                   myquery := "Select ....  where x = " || Q'#:p1#';
                ...
                but not working.

                any hint will be appreciated =)
                • 5. Re: How to escape ' in dynamic sql
                  rp0428
                  HIJACKED THREAD!

                  Please do not hijack another users thread to ask your own question. This thread is over a year old and is already answered.

                  And you have already posted your question in two other threads.
                  Re: How to escape apostrophe in Oracle PL/SQL  dynamic queries
                  How to replace 2 single quotes to single quote

                  Posting duplicate threads and hijacking the threads of other users is not going to get you the help you want.