How to Insert a Line Break in a PL/SQL?

Inserting a line break in a PL/SQL statement can make our output more readable and organized. Below is the method which is used to insert a line break in a PL/SQL VARCHAR/NVARCHAR string as given below:

1. By Making the string Multiline
2. Use CHR(10)

let’s Setting Up an Environment for Insert a Line Break in a PL/SQL

Let us create a table and insert string values into it. We will create a zoo table and keep some animals in it.

Query:

CREATE TABLE an 
(
    ID INT,
    NAME VARCHAR2(20),
    TYPE VARCHAR2(20),
    ABOUT VARCHAR2(100)
);

INSERT ALL
    INTO zoo VALUES (1, 'Tif', 'Frog', 'A green one.')
    INTO zoo VALUES (2, 'Bim', 'Sparrow', 'Blue Eyes.')
    INTO zoo VALUES (3, 'Kiz', 'Snake', 'Sizzling.')
SELECT * FROM DUAL;

SELECT * FROM zoo;

Output:

Explanation: We have inserted the strings into the table.

Note: Don’t worry about DUAL it is just a syntax requirement. DUAL is a dummy table with one row in it. It is used in cases where we want to just get the output of some operation like SELECT 3+4. we can run this statement, but in PL/SQL we need to select it out of a table like SELECT 3+4 FROM DUAL. The INSERT ALL command also requires a select statement, therefore dual is put there.

Let’s try to insert a new record having a newline character.

Query:

INSERT INTO zoo VALUES (4, 'Fie', 'Cow', 'A white one. \n Has black patches.');
SELECT * FROM zoo;

Output:

Explanation: The newline didn’t get inserted as a special character but as two separate normal characters. We will now look how to insert newline character into a string.

In PL/SQL, inserting line breaks into VARCHAR or NVARCHAR strings can be a good way to enhance the readability and presentation of our data. Whether we are formatting output for display or preparing text for storage, knowing how to insert line breaks is a helpful skill. In this guide, we’ll explore the various techniques and examples for inserting line breaks in PL/SQL VARCHAR/NVARCHAR strings.