Oracle PL/SQL Comments

In databases, comments refer to the text which is not executed as part of a command or query. Database comments are mainly used to add explanatory notes, documentation, or other meta information to a database and can help clarify the code’s purpose and intent.

You will also find the comments in various database contexts such as stored procedures, functions, triggers, and views. They can provide the explanations of code, document changes or modifications, or leave notes for the other developers who working on the same codebase.

In this tutorial, you will learn how to use and work with the various types of comments in PL/SQL language.

Single Line Comments

The first type of comment in Oracle databases is single-line comments. Single-line comments are comments that span a single line.

In PL/SQL, a single-line comment starts with two hyphens (–) and spans to the end of the line. The database engine ignores any text or characters that come after the hyphens when the code is executed.

The following shows the example of a single line comment in Oracle:

BEGIN
-- This is a single-line comment
DECLARE
name VARCHAR2(30);
BEGIN
name := 'Peter';
END;
END;

In this case, the text after the comment is treated as a comment and ignored during command execution.

Apart from adding the meta information to a given code block, single-line comments help disable a piece of code.

An example is as follows:

-- INSERT INTO products(product_name, quantity, price) VALUES ('Microsoft Volterra', 0, 599);

Although the previous statement is a valid PL/SQL command, the database engine does not execute it as it is treated as a comment.

Oracle Mult-Line Comment

The second type of comments in Oracle databases is multi-line comments. These are the type of comments that can span multiple lines.

A multi-line comment in Oracle starts with a forward slash and an asterisk (/*). The comment can then span as many lines as possible. To terminate a multi-line comment, start with an asterisk and a forward slash (*/).

Example:

BEGIN
/*
This is a multi-line comment.
It can span multiple lines and can be used to provide more detailed explanations or documentation.
*/
DECLARE
name VARCHAR2(30);
BEGIN
name := Peter';
END;
END;

We can also use a multi-line comment as a single-line comment:

SELECT * FROM employees; /* This is a multi-line comment that spans a single line */

There, you learned how to work with single-line and multi-line comments in PL/SQL.

It is good to note that Oracle does not support the nested multi-line comments. For example, the following code is invalid:

BEGIN
/* This is a multi-line comment
/* This is a nested multi-line comment */
*/
DECLARE
name VARCHAR2(30);
BEGIN
name := 'Peter';
END;
END;

The previous query fails since the nested multi-line comments are not supported.

If you wish to add a nested comment, you can combine the multi-line and single-line comments as shown in the following:

BEGIN
/* This is a multi-line comment
-- This is a single-line comment within the multi-line comment
*/
DECLARE
name VARCHAR2(30);
BEGIN
name := 'Peter';
END;
END;

Conclusion

In this tutorial, you learned how to use the various types of comments in PL/SQL to add a documentation to your code.

Oracle PL/SQL Comments

Single Line Comments

Oracle Mult-Line Comment

Conclusion

About the author

John Otieno