This discussion is archived
5 Replies Latest reply: Nov 22, 2012 3:50 PM by rp0428 RSS

How to escape ' in dynamic sql

Dinesh Rajasekharan-Oracle Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot Frank
  • 4. Re: How to escape ' in dynamic sql
    axvelazq Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points