You can add annotations to any supported schema objects with CREATE privilege and you can add or drop annotations using ALTER privilege, the object can have more than one annotation as well. The supported schema objects include tables, indexes, views, materialized views. An annotation will consist of a name and an optional value, which can be entered as freeform text fields.
You can drop the annotations, or it will be dropped when a schema object is dropped. You can query USER|ALL|DBA_ANNOTATIONS_USAGE to view annotations for an object.
Annotation has three clauses i.e ADD, DROP or REPLACE
ADD – create annotations for an existing object.
DROP – removes the annotation from an object.
REPLACE – change annotation_value. This clause used with ALTER statements.
In below example, we’re adding annotation both table and columns
CREATE TABLE emp (
emp_id number ANNOTATIONS (ColumnInfo ‘Employee ID’),
emp_name varchar2(30) ANNOTATIONS (ColumInfo ‘Employee Name’),
dept varchar2(20) )
ANNOTATIONS ( TableInfo ‘Employee Table’);
SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NULL;
ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
TABLEINFO Employee Table
You can drop annotation from an existing table using below example
SQL> ALTER TABLE emp ANNOTATIONS (DROP TableInfo);
You can add annotation to the table using below example, note that ADD keyword is optional.
SQL> ALTER TABLE emp ANNOTATIONS (ADD TableInfo ‘Employee Table’);
SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL;
ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
EMP_ID Employee ID
EMP_NAME Employee Name
In below example you can add, replace and drop annotations at column level. Note that ADD keyword is optional.
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (ADD ColumnInfo ‘Dept Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (REPLACE ColumnInfo ‘Deptartment Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (DROP ColumnInfo);
Thanks & Regards,
emp_id number ANNOTATIONS (ColumnInfo ‘Employee ID’),
emp_name varchar2(30) ANNOTATIONS (ColumInfo ‘Employee Name’),
dept varchar2(20) )
ANNOTATIONS ( TableInfo ‘Employee Table’);
SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NULL;
ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
TABLEINFO Employee Table
You can drop annotation from an existing table using below example
SQL> ALTER TABLE emp ANNOTATIONS (DROP TableInfo);
You can add annotation to the table using below example, note that ADD keyword is optional.
SQL> ALTER TABLE emp ANNOTATIONS (ADD TableInfo ‘Employee Table’);
SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL;
ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
EMP_ID Employee ID
EMP_NAME Employee Name
In below example you can add, replace and drop annotations at column level. Note that ADD keyword is optional.
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (ADD ColumnInfo ‘Dept Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (REPLACE ColumnInfo ‘Deptartment Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (DROP ColumnInfo);
Thanks & Regards,
https://oracleracexpert.com
