I want to add multi-line table/column comment.
Normally this is used;
COMMENT ON TABLE USERS IS 'User table has the user data'
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'
User table has the user data
1- Name column has name
2- Number Column has the id
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: