6 Replies Latest reply on May 29, 2018 3:07 PM by thatJeffSmith-Oracle

    SQLcl & SQL Developer - Dynamic cursor + Quote Syntax - string containing 'N' replaced with 'u'


      Oracle Database 11g Enterprise Edition Release - 64bit Production

      SQL*Plus: Release

      SQLcl: Release 17.3.0

      Oracle SQL Developer Version


      An unusual requirement led our team to using a dynamic cursor (due to multiple conditions determining how to structure the query to return our row set).

      In our cursor's 'open for' clause, we enclosed a string of dynamic sql using the q' syntax. If the string contained the characters 'N' or 'n' a rather unusual behaviour was observed...

      When the procedure containing the cursor was executed, the sql executed by the database appeared to have replaced our 'N' (or 'n') character with 'u' instead!

      Interestingly this only happened on every other occurrence of 'N' (or 'n') starting with the first occurrence.


      The following anonymous block suitably replicates our actual procedure:



      str varchar2(30);

      c sys_refcursor;


      open c for

      q'{select 'N'||'N'||'n'||'n' str from dual}';

      fetch c into str;


      close c;




      If the code is executed in SQL Developer or SQLcl (versions above), the output observed is uNun rather than the expected NNnn.

      If the code is executed in SQLplus (version above) however, the expected output is returned.


      N.B. The issue only appear to occur with the q' syntax. Using double single quotes to escape works as expected.


      Message was edited by: 3521933 enclosing q'{ }' had been missed out of example block