If I have a table with a field 'TIMESTAMP(6) DEFAULT SYSTIMESTAMP' and I want to see the DDL, I press F4 (Describe) and the following screen shows up.
The comma (field separator) is shown as a comment, and if I run the script, it will give an error.
But with the SQL Developer app, the script looks correct.
Toad for Oracle Base Edition (64-bit)
Add-Ons:
25.3.232.6895
Note: This error occurs when the object is in a 12c (12.1.0.2.0) database. In the 19c database, I do not get this error.
I would say that both scripts look wrong. There shouldn't be a comment in the middle of a column list.
I cannot reproduce this in a 12.1.0.2 database.
I think what has happened is that some extra characters have gotten into the data_default column of DBA_TAB_COLS. It should be easy to check.
And you can maybe fix it with
ALTER TABLE tablename
MODIFY(columnname DEFAULT SYSTIMESTAMP);
The table is for production, I cannot execute this command now.
Can you take a look in DBA_TAB_COLS to see if my guess is right?
There are 2 hyphens. It seems they got in there. In any case, it would be ideal for the DESCRIB option to detect it.
Thank you very much, John.
Hi Carlos.
I'm glad that was the problem. There are probably cases where a user intentionally puts a line feed in a default value (maybe with a varchar2 data type). In your case, it seems like an Oracle bug that the data line feed and comment marker is in there.
I don't think Toad should look at values in this column and try to determine what the user really wants. Toad should assume that the Oracle data dictionary is correct. Otherwise, we will probably cause some other problem.
-John
