1 COMMENT_ON Adds or changes a comment about the following database objects: o Catalog o Collating sequence o Column o Constraint o Domain o Database o Function o Index o Index partition o Module o Outline o Procedure o Profile o Role o Schema o Sequence o Storage map o Storage map partition o Synonym o Table o Trigger o User o View SQL displays the comments on these objects when you issue a SHOW statement. 2 Environment You can use the COMMENT ON statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module o In dynamic SQL as a statement to be dynamically executed 2 Format COMMENT ON --+--> objects-1 -+-> IS -+-> 'string' -+-> +--> objects-2 -+ +---- / <-----+ objects1 = -+> -> ( + IS +-> 'string' -++> ) --+-> | | +---- / <-----+| | | +----------- , <---------------+ | +> CATALOG ---------------------------------+ +> COLLATING SEQUENCE -----------------+ +> COLUMN . -------------------------+ +> CONSTRAINT ---------------------------+ +> DATABASE -+------------------------+--------------------+ | +-> ALIAS --+ | +> DOMAIN -----------------------------------+ +> FUNCTION -------------------------------+ +> INDEX -+-------------------------------+---+ | +-> PARTITION -+ | +> MODULE -----------------------------------+ +> PROCEDURE -----------------------------+ +> PROFILE ---------------------------------+ objects2 = -+> ROLE -----------------------------------------+--> +> SCHEMA -------------------------------------+ +> SEQUENCE ---------------------------------+ +> STORAGE MAP +--------------------------------+-+ | +--> PARTITION -+ | +> SYNONYM -----------------------------------+ +> TABLE ---------------------------------------+ +> TRIGGER -----------------------------------+ +> USER -----------------------------------------+ +> VIEW -----------------------------------------+ 2 Arguments 3 CATALOG Names the catalog for which you want to create a comment. If the catalog is not in the default schema, you must qualify the catalog name in the COMMENT ON statement with an alias name. 3 COLLATING_SEQUENCE Names the collating sequence for which you want to create a comment. If the collating sequence is not in the default schema, you must qualify the collating sequence name in the COMMENT ON statement with an alias name. 3 COLUMN Names the column for which you want to create a comment. You must qualify the column name with a table name. If the column is not in a table in the default schema, you must qualify the column name in the COMMENT ON statement with both a table name and an alias name. 3 CONSTRAINT_constraint-name Names the constraint for which you want to create a comment. If the constraint is not in the default schema, you must qualify the constraint name in the COMMENT ON statement with an alias name. 3 DATABASE This statement writes the new comment to the database. If the ALIAS clause is omitted, the default database is used. The alias- name must be an alias specified by an ATTACH or CONNECT statement during this session. 3 DOMAIN Names the domain for which you want to create a comment. If the domain is not in the default schema, you must qualify the domain name in the COMMENT ON statement with an alias name. 3 FUNCTION Names the function for which you want to create a comment. If the function is not in the default schema, you must qualify the function name in the COMMENT ON statement with an alias name. 3 INDEX Syntax options: INDEX index-name PARTITION partition-name Names the index and, optionally, a partition in the named index, for which you want to create a comment. If the index is not in the default schema, you must qualify the index name in the COMMENT ON statement with an alias name. 3 IS_string Specifies the comment. SQL displays the text when it executes a SHOW statement in interactive SQL. Enclose the comment within single quotation marks (') and separate multiple lines in a comment with a slash mark (/). 3 MODULE Names the module for which you want to create a comment. If the module is not in the default schema, you must qualify the module name in the COMMENT ON statement with an alias name. 3 PROCEDURE Names the procedure for which you want to create a comment. If the procedure is not in the default schema, you must qualify the procedure name in the COMMENT ON statement with an alias name. 3 PROFILE Names the profile for which you want to create a comment. If the profile is not in the default schema, you must qualify the profile name in the COMMENT ON statement with an alias name. 3 ROLE Names the role for which you want to create a comment. If the role is not in the default schema, you must qualify the role name in the COMMENT ON statement with an alias name. 3 SCHEMA Names the schema for which you want to create a comment. You must create the schema first. If the schema is not in the default schema, you must qualify the schema name in the COMMENT ON statement with an alias name. 3 SEQUENCE Names the sequence for which you want to create a comment. If the sequence is not in the default schema, you must qualify the sequence name in the COMMENT ON statement with an alias name. 3 STORAGE_MAP Syntax options: STORAGE MAP map-name PARTITION partition-name Names the storage map and, optionally, a vertical or horizontal partition within that storage map, for which you want to create a comment. If the storage map is not in the default schema, you must qualify the storage map name in the COMMENT ON statement with an alias name. 3 SYNONYM This performs the same function as the ALTER SYNONYM . . . COMMENT IS syntax. The synonym-name must be the name of an existing synonym. A database alias can be used to select a database other than the default database alias. 3 TABLE Names the table for which you want to create a comment. If the table is not in the default schema, you must qualify the table name in the COMMENT ON statement with an alias name. 3 table-name_col-name Names the table and the column or columns in that table for which you want to create a comment. 3 TRIGGER Names the trigger for which you want to create a comment. If the trigger is not in the default schema, you must qualify the trigger name in the COMMENT ON statement with an alias name. 3 USER Names the user (created with the CREATE USER statement) for which you want to create a comment. If the user is not in the default schema, you must qualify the user name in the COMMENT ON statement with an alias name. 3 VIEW Names the view for which you want to create a comment. If the view is not in the default schema, you must qualify the view name in the COMMENT ON statement with an alias name. 2 Examples Example 1: Specifying a comment for columns and tables SQL> -- Change the comment for the WORK_STATUS table: SQL> COMMENT ON TABLE WORK_STATUS IS cont> 'Links a status code with 1 of 3 statuses' ; SQL> SHOW TABLE WORK_STATUS Information for table WORK_STATUS Comment on table WORK_STATUS: Links a status code with 1 of 3 statuses . . . SQL> -- Create a comment for the DEPARTMENT_CODE SQL> -- column in the DEPARTMENTS table: SQL> COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_CODE IS cont> 'Also used in JOB_HISTORY table'; SQL> SHOW TABLE DEPARTMENTS Information for table DEPARTMENTS Comment on table DEPARTMENTS: information about departments in corporation Columns for table DEPARTMENTS: Column Name Data Type Domain ----------- --------- ------ DEPARTMENT_CODE CHAR(4) DEPARTMENT_CODE_DOM Comment: Also used in JOB_HISTORY table . . . Example 2: Specifying a comment containing more than one string literal SQL> COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS cont> '1: Used in SALARY_HISTORY table as Foreign Key constraint' / cont> '2: Used in JOB_HISTORY table as Foreign Key constraint'; SQL> SHOW TABLE (COL) EMPLOYEES; Information for table EMPLOYEES Columns for table EMPLOYEES: Column Name Data Type Domain ----------- --------- ------ EMPLOYEE_ID CHAR(5) ID_DOM Comment: 1: Used in SALARY_HISTORY table as Foreign Key constraint 2: Used in JOB_HISTORY table as Foreign Key constraint Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID LAST_NAME CHAR(14) LAST_NAME_DOM FIRST_NAME CHAR(10) FIRST_NAME_DOM MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL_DOM ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1_DOM ADDRESS_DATA_2 CHAR(20) ADDRESS_DATA_2_DOM CITY CHAR(20) CITY_DOM STATE CHAR(2) STATE_DOM POSTAL_CODE CHAR(5) POSTAL_CODE_DOM SEX CHAR(1) SEX_DOM BIRTHDAY DATE VMS DATE_DOM STATUS_CODE CHAR(1) STATUS_CODE_DOM Example 3: Adding a Comment to a Trigger SQL> COMMENT ON TRIGGER EMPLOYEE_ID_CASCADE_DELETE IS cont> 'When an employee is deleted from EMPLOYEES, delete'/ cont> 'corresponding records from the other tables in the'/ cont> 'database.'; SQL> SHOW TRIGGER EMPLOYEE_ID_CASCADE_DELETE EMPLOYEE_ID_CASCADE_DELETE Source: EMPLOYEE_ID_CASCADE_DELETE BEFORE DELETE ON EMPLOYEES (DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW (DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW (DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW -- Also, if an employee is terminated and that employee -- is the manager of a department, set the manager_id -- null for that department. (UPDATE DEPARTMENTS D SET D.MANAGER_ID = NULL WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW. . . . Comment: When an employees is deleted from EMPLOYEES, delete corresponding records from the other tables in the database. Example 4: Adding Comments to Multiple Columns in a Table SQL> COMMENT ON JOBS (JOB_CODE is 'Required column', cont> WAGE_CLASS is 'Valid values are: 1, 2, 3, or 4'); SQL> SHOW TABLE (COLUMNS) JOBS; Information for table JOBS Columns for table JOBS: Column Name Data Type Domain ----------- --------- ------ JOB_CODE CHAR(4) JOB_CODE Comment: Required column Missing Value: None WAGE_CLASS CHAR(1) WAGE_CLASS Comment: Valid values are: 1, 2, 3, or 4 JOB_TITLE CHAR(20) JOB_TITLE Missing Value: None MINIMUM_SALARY INTEGER(2) SALARY MAXIMUM_SALARY INTEGER(2) SALARY