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'

    3521933

      Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

      SQL*Plus: Release 12.2.0.1.0

      SQLcl: Release 17.3.0

      Oracle SQL Developer Version 17.2.0.188

       

      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:

       

      declare

      str varchar2(30);

      c sys_refcursor;

      begin

      open c for

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

      fetch c into str;

      dbms_output.put_line('str='||str);

      close c;

      end;

      /

       

      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