4

Today I noticed something interesting about the quote operator.

Here I'm running this code via SQL*Plus on a 12.1 database running on my Windows 10 laptop.

newline_CRLF.sql

set serveroutput on
declare
  l_str1   varchar2(100 char);
  l_str2   varchar2(100 char);
begin
  l_str1 := q'{This is a
two lines string}';
  l_str2 := 'This is a'||chr(13)||chr(10)||'two lines string';
  dbms_output.put_line('STR1: length='||length(l_str1)||', lengthb='||lengthb(l_str1));
  dbms_output.put_line('STR1: length='||length(l_str2)||', lengthb='||lengthb(l_str2));
end;
/

This code is in a file where the line terminator is CRLF as defaults in Windows.

SQL> @newline_CRLF
STR1: length=26, lengthb=26
STR1: length=27, lengthb=27

Procedura PL/SQL completata correttamente.

What I noticed here, is the line breake within the quote operator q'{}' is parsed as a Unix-style line feed.

I converted the script to use Unix-style line breakes using Notepad++ EOL Conversion utility, and on the next run I got the same result.

This DOES absolutely MAKE SENSE because it guarantees any code base is treated the same way, being the PL/SQL language EOL agnostic: if it weren't like that, it would have been possible run into bugs.

This behaviour fits perfectly on Unix-like systems, but on Windows can be a little annoying and require to explicitly use string concatenation.

This is not an actual problem, it is just out of my curiosity to ask:

"Do anybody know whether this is a just take it situation or there is the possibility to set up a parameter to change the way it behaves?"

Considering I'm getting a LF on a Windows platform and I've found nothing on MOS so far, I hardly think it's possible, but you never know...

Max
  • 2,508
  • 3
  • 26
  • 44
  • This also depends on the editor, whether SQL*Plus converts the line feeds to Unix style is unknown. I trust that it does since I doubt that Oracle is doing anything with them (any character is valid in a AQM string, line feeds are no exception be they Unix or Windows). If I were you I'd stick to the safe side and write `'this is a' || chr(13) || chr(10) || 'two lines string'` because I don't like OS/Editor choosing what my script produces. – bokibeg Aug 25 '19 at 16:36

1 Answers1

2

This is a "just take it" situation. Unless you have a specific bug you're dealing with, you should accept these quirks instead of avoiding multi-line strings.

In SQL and PL/SQL, I have literally never seen problems caused by multi-line string newlines. Theoretical concerns, yes, but no real-world problems. In fact, if you try to force everyone to use explicit newline characters you are much more likely to run into other, weirder problems.

For example, I've seen the below mistake made several times. If you accidentally only use CHR(13), in an environment like the Windows DOS prompt, it causes odd behavior. This output actually makes sense if you think about how old type writers used to work - you'd pull the bar back (13), and then move to the new line (10). But if you only pull the bar (13), and start typing, you'll type over the previous characters.

SQL> set serveroutput on;
SQL> begin
  2     dbms_output.put_line('Where did this first line go?'||chr(13)||'Nothing to see here.');
  3  end;
  4  /
Nothing to see here. line go?

PL/SQL procedure successfully completed.

SQL>

More importantly, code full of string concatenation is terrible to look at. A large part of the reason people hate dynamic SQL is because it looks ugly. But it only looks ugly because people concatenate and escape strings. With multi-line strings, the alternative quoting mechanism (which you're already using), and some REPLACE functions, we can make code dynamic and still readable.

Unlike other languages, dynamic code is a good thing in PL/SQL. Our programs will be seriously limited if we unconsciously avoid dynamic SQL because our concatenated strings look ugly.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • I totally agree. The only thing I don't like about the REPLACE solution is that it's a CPU consuming task I would have loved to avoid, but for sure it is a clean and simple solution. – Max Aug 25 '19 at 19:16