insert a multiline string in Oracle with sqlplus

You can also use not-well-known feature of Oracle's SQL: Perl style quoted strings.

SQL> select q'[f dfgdfklgdfkjgd
  2  sdffdslkdflkgj dglk
  3  glfdglkjdgkldj ]'
  4  from dual;

Q'[FDFGDFKLGDFKJGDSDFFDSLKDFLKGJDGLKGLFDGLKJDGKLDJ]'
----------------------------------------------------
f dfgdfklgdfkjgd
sdffdslkdflkgj dglk
glfdglkjdgkldj

Another way of inserting newlines to a string is concatenating:

chr(13)||chr(10)

(on windows)

or just:

chr(10)

(otherwise)

SQL*Plus Manual

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

1. with a semicolon (;)
2. with a slash (/) on a line by itself
3. with a blank line

A blank line in a SQL statement or script tells SQL*Plus that you have finished entering the command, but do not want to run it yet. Press Return at the end of the last line of the command.

Turning SQLBLANKLINES on in this situation may be the answer, but even with it you still have to worry about the following SQL*Plus commands.

@  ("at" sign)        (Start of line) 
@@ (double "at" sign) (Start of line) 
#   SQLPREFIX         (Start of line)
.   BLOCKTERMINATOR   (Start of line and by itself)
/  (slash)            (Start of line and by itself)
;   SQLT[ERMINATOR]   (Start of line and by itself, or at the end)

SQLPREFIX is something that you cannot turn off; it's a feature of SQL*Plus. BLOCKTERMINATOR can be activated or disabled. Slash on the other hand if it appears at the start of a new line will cause it to execute the contents in the buffer. SQL[TERMINATOR] has a similar behavior.

Enable SQLBLANKLINES to allow blank lines in SQL statements. For example:

SET SQLBLANKLINES ON
insert into table(id, string) values (1, 'Line1goesHere 
Line2GoesHere 

blablablabla
');

The premise of this question is slightly wrong. SQL*Plus does allow multi-line strings by default. It is only blank lines that cause problems.