Wednesday, April 20, 2016

Oracle PLSQL alert table add not null column with default value throws exception

I was trying to run following script:
alter table qrtz_blob_triggers add sched_name varchar(120) not null DEFAULT 'TestScheduler';

I kept receiving a following exception:
alter table qrtz_blob_triggers add sched_name varchar(120) not null DEFAULT 'TestScheduler'
Error report -
SQL Error: ORA-30649: missing DIRECTORY keyword
30649.0000 -  "missing DIRECTORY keyword"
*Cause:    DEFAULT DIRECTORY clause missing or incorrect.
*Action:   Provide the DEFAULT DIRECTORY.
I had to change the order of a query (default before not null):
alter table qrtz_blob_triggers add sched_name varchar(120) DEFAULT 'TestScheduler' not null;
I will keep this as an example for people that keep telling me that SQL is a declarative/functional language, and not something that Scarily Qualifies as a Language