Why Doesn't Oracle Allow Consecutive Newline Characters in Commands

Why doesn't ORACLE allow consecutive newline characters in commands?

I don't know about the why, but a completely blank line terminates a command in SQL*Plus.

Quote from the SQL*Plus docs :

Ending a SQL Command:
You can end a SQL command in one of three ways:

with a semicolon (;)
with a slash (/) on a line by itself
with a blank line

You can also change how blank lines are treated with SET SQLBLANKLINES

SQLBL[ANKLINES] {ON|OFF}

Controls whether SQL*Plus allows blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.

line breaks in sqlplus

Try adding:

set sqlblanklines on

to the start of your sql script.

Trim Whitespaces (New Line and Tab space) in a String in Oracle

How about the quick and dirty translate function?

This will remove all occurrences of each character in string1:

SELECT translate(
           translate(
               translate(string1, CHR(10), '')
           , CHR(13), '')
       , CHR(09), '') as massaged
FROM BLAH;

Regexp_replace is an option, but you may see a performance hit depending on how complex your expression is.

difference in & and : (method of binding variable)

What you have encountered is a text replacmenet mechanism of SQLplus (and tools that implement the same extension).

You can turn it of with:

SET DEF OFF

Or you can switch to a different character (the plus sign in this example):

SET DEF +

The Oracle tool you're using doesn't implement the SQLplus extension as far as I know. But SQL Developer (a free GUI tool from Oracle) does.

Is it safe to add a new line charactor (\n) in the sql query?

Yes, it is perfectly fine to add newlines (\n or \r\n) to your query. Most parsers consider newlines as whitespace (just like a space or tab), and so does the one used by Oracle (and all other database I have used).

You have already proven this, because based on your comments your company already does this (and probably has so for years). I'm curious as to why you think it could be unsafe. Because obviously it works, and if it would be unsafe, Oracle would disallow it with a clear error.

Although very subjective, it could be better than not doing it, as it allows for a easier visual inspection if all lines end in whitespace. Consider the difference between:

Oops, no space
```
"select *" +
"from table"  
```
Space
```
"select * " +
"from table"
```
Linebreak
```
"select *\n" +
"from table"
```

The difference between 1 and 2 is smaller than between 1 and 3. Especially in long queries this might matter to see if you forgot whitespace which might either lead to a syntax error, or worse to incorrect query behavior.

On the other hand, the newlines are extra visual clutter that might distract from reading the query. When in doubt, go with what is the norm or codestyle in your company.

Replace a newline in TSQL

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

How do I extract data between two strings based on a pattern in Oracle SQL

Here is how I would do this. Note a couple of things:

The output preserves newlines that existed in the input. You didn't
say anything about removing them; however, your output doesn't show
them. In any case - they can be removed, if needed, but that is an
unrelated process.
You say "word" but obviously you are using that in a sense different
from the common usage in regular expressions. In regexp, "word
characters" are only letters, digits and underscore; yet your
"words" include brackets, equal sign, and who knows what else. I interpreted the term "word" to mean any
sequence of consecutive non-whitespace characters.

Here is how we can recreate your data. When you ask a question here, this is how you should provide sample data - not as an image that we can't copy and paste in an SQL editor.

CREATE TABLE sample_data( col_a varchar2(20), col_b CLOB );

INSERT INTO sample_data VALUES
('12345', to_clob(
'Created:2/28/2019
Updated:1/19/2021
LIST:[ABC][DEF][GHI]
[LMNO][PQRST]
[Location=BLAH].[City=BLAH]'));

INSERT INTO sample_data VALUES
('12346', to_clob(
'Created:2/28/2019
Updated:1/19/2021
LIST:[ABC][DEF][GHI]
[LMNO][PQRST]
[SOC].[RAW]'));

commit;

Then here is the query and the output. Note that, depending on your interface (in my case: SQL Developer, which uses a SQL*Plus-like interface), you may need to change some settings so that the output is not truncated. In particular, in SQL*Plus, CLOB columns are truncated to 80 characters by default; I had to

set long 100

So - query and output:

select col_a, col_b,
       regexp_substr(col_b, '(\s|^)(LIST:[^.]*?)\s+\S+\.', 1, 1, null, 2)
         as result
from   sample_data
;

COL_A COL_B                          RESULT                        
----- ------------------------------ ------------------------------
12345 Created:2/28/2019              LIST:[ABC][DEF][GHI]          
      Updated:1/19/2021              [LMNO][PQRST]                 
      LIST:[ABC][DEF][GHI]                                         
      [LMNO][PQRST]                                                
      [Location=BLAH].[City=BLAH]                                  

12346 Created:2/28/2019              LIST:[ABC][DEF][GHI]          
      Updated:1/19/2021              [LMNO][PQRST]                 
      LIST:[ABC][DEF][GHI]                                         
      [LMNO][PQRST]                                                
      [SOC].[RAW]

The regular expression matches a single whitespace character or the beginning of the string ((\s|^)), then the characters LIST: followed by as few consecutive, non-period characters (this will match spaces and newline characters, in particular) as needed to allow a match - which continues with one or more whitespace characters, followed by a single word (string of 1 or more non-whitespace characters) and a literal period (\.).

The expression we must return is enclosed in parentheses, so that we can return it from regexp_substr. Such an expression is called a "capture group". The regexp includes another capture group, (\s|^), out of necessity (alternation), so the capture group we must return is the second in the regexp. This is what the last argument to regexp_substr does: it instructs the function to return the second capture group.

Note a peculiar thing about the period (related to the much more general concept of escaping within bracket expressions): the period must be escaped to represent a literal period, rather than "any character", at the end of the regular expression; however, within the (negated) bracket expression [^.]*?, the period - representing a literal period, not "any character" - is not escaped. Oracle follows the ERE (extended regular expressions) dialect of the POSIX standard, and that standard says that escape sequences are invalid within bracket expressions. This is different from other regular expression dialect, and confuses a lot of users.

h:outputText does not break \r\n characters into new lines

Linebreaks in HTML are represented by <br /> element, not by the \n character. Even more, open the average HTML source code by rightclick, View Source in browser and you'll "see" \n over all place. They are however not presented as such in the final HTML presentation. Only the <br /> will.

So, yes, you need to replace them by <br />. You can use JSTL functions for this:

<... xmlns:fn="http://xmlns.jcp.org/jsp/jstl/functions">

<h:outputText value="#{fn:replace(bean.text,'\n','<br/>')}" escape="false" />

Note: when using Apache EL instead of Oracle EL, double-escape the backslash as in \\n.

<h:outputText value="#{fn:replace(bean.text,'\\n','<br/>')}" escape="false" />

Otherwise you will face an exception with the message Failed to parse the expression with root cause org.apache.el.parser.ParseException: Encountered <ILLEGAL_CHARACTER>.

This all is however ugly and the escape="false" makes it sensitive to XSS attacks if the value comes from enduser input and you don't sanitize it beforehand. A better alternative is to keep using \n and set CSS white-space property to preformatted on the parent element. If you'd like to wrap lines inside the context of a block element, then set pre-wrap. Or if you'd like to collapse spaces and tabs as well, then set pre-line.

E.g.

<h:outputText value="#{bean.text}" styleClass="preformatted" />

.preformatted {
    white-space: pre-wrap;
}

sql loader- files with carriage return, line feed load into oracle with cr/lf

Here is the solution that works for me.
Instead of replacing the carriage return/line feed(cr/lf) in the extracted flat file with [#crlf#] I retain the cr/lf in the extracted data file.
And then I changed my ctl file to handle the cr/lf with INFILE Clause with file name and " str '\n' ". For Unix env we need \n where in for windows we can use either \n or \r\n.

see below

load data INFILE 'filename.dat' "str '\n'" 
    truncate 
    CONTINUEIF NEXT preserve (1:7) <> "[#BOR#]"
    into table sch1.tbl1
    fields terminated by '[#EOC#]'
    trailing nullcols (
      field       filler,   
      a_id        integer external,
      h_id        integer external,
      title       char(128),
      descn       char(4000),
      risk        char(4000),
      comment     char(4000) terminated by '[#EOR#]')

I tested it and data loaded with cr\lf.. I need to do more detailed testing, as of now I have tested one table I have many more.
Meanwhile if any one has better solution I would be more than happy to try it out.

Why Doesn't Oracle Allow Consecutive Newline Characters in Commands