Oracle SQL adding multi-line table comment or column comment

comments, multiline, oracle, sql

Solution

You can simply put line feeds inside the single-quotes of your comment declaration, for example:

COMMENT ON COLUMN MYTABLE.MYCOLUMN
IS
'Line 1
Line 2.
Line 3';

Note, however, that in SQL Developer (and perhaps other tools) this will not always display as expected. With the following query ...

SELECT *
FROM USER_COL_COMMENTS
WHERE
  TABLE_NAME = 'MYTABLE'
  AND COMMENTS IS NOT NULL;

... you'll get exactly what you're looking for in Script Output (i.e., highlight the query, right-click, select "Run Script"):

TABLE_NAME COLUMN_NAME COMMENTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
---------- ----------- --------------
MYTABLE    MYCOLUMN    Line 1
                       Line 2
                       Line 3
MYTABLE    OTHERCOLUMN Other comments

But in a Query Result (i.e., highlight the query, right-click, select "Run Statement"), or when opening the table and looking at the Columns tab, the full comment will be run together on a single line.

Note: The tables in which these comments can be queried are:

- Comments on tables: `USER_TAB_COMMENTS`

- Comments on columns: `USER_COL_COMMENTS`

Problem

I want to add multi-line table/column comment. Normally this is used; ``` COMMENT ON TABLE USERS IS 'User table has the user data' ``` What I need is a way to insert the new-line inside the single quotation marks like; ``` COMMENT ON TABLE USERS IS 'User table has the user data <smthg_here_for_new_line> 1- Name column has name <smthg_here_for_new_line> 2- Number Column has the id' ``` So that table comments will be seen like; ``` User table has the user data 1- Name column has name 2- Number Column has the id ``` Anybody knows how add multi-line table/column comments?

Original source