Geek asks and answers

Oracle SQL adds multiline table comment or column comment

I want to add a multiline table / column comment.

This is usually used;

COMMENT ON TABLE USERS IS 'User table has the user data'

I need a way to insert a new line inside single quotes, for example:

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'

Thus, comments on the table will be visible as follows:

User table has the user data
1- Name column has name
2- Number Column has the id

Does anyone know how to add multi-row table / column comments?

+3
source share
2 answers

You can simply put line channels inside single comment declaration quotes, for example:

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

Note, however, that in SQL Developer (and possibly in 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;

... , Script Output (.. , , "Run Script" ):

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

(.. , , " " ), , "", .

. , , :

  • : USER_TAB_COMMENTS
  • : USER_COL_COMMENTS
+3

SQLPlus concat chr (10) ( chr (13) || chr (10) Microsoft Windows):

'User table has the user data' || chr(10) || '1- Name column has name...'

, , SQLBLANKLINES ON:

SET SQLBLANKLINES ON
COMMENT ON TABLE USERS IS 'User table has the user data
1- Name column has name
2- Number Column has the id'
0

More articles:


All Articles