I want to add a comment on a column using the following sql sentence.
COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS "Primary key of employees table"
This is a very simple example taken from the sample schemas of Oracle 11g. The important thing to notice is that I'm specifying the actual comment in between double quotes. Which gives this error when executing the command.
ORA-00972: identifier is too long
*Cause: An identifier with more than 30 characters was specified.
*Action: Specify at most 30 characters.
But if I replace double quotes to single quotes, it runs with no errors even if the comment is more than 30 characters length.
COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS 'Primary key of employees table XD'
I don't know what the reason for this behavior is, can you help me?
A series of characters between single quotes is a string. A series of characters between double quotes is an identifier. An identifier would be a table name, column name, data type, etc.
If you try to use an identifier, Oracle tries to resolve that identifier. In this case, it throws an error that the identifier isn't valid (because it is too long) before it throws an error that the identifier isn't valid because there isn't a table named "Primary key of employees table" or before throwing an error that the identifier isn't valid at that particular point in that particular SQL statement because the comment statement expects a string. Theoretically, Oracle could throw any of those errors. It's just that from a workflow perspective, it makes more sense to do the operations that apply to all SQL statements (parsing out identifiers) before trying to analyze the syntax of a particular DDL statement.