Search code examples
sqloracle10gblobalter

Alter Table - add Default value for a COLUMN type BLOB

Executing the below SQL is giving this error .

alter table TABLE_NAME ADD FILE_DATA BLOB NULL default 'EMPTY_BLOB()'

Error starting at line 37 in command: alter table TABLE_NAME ADD FILE_DATA BLOB NULL default 'EMPTY_BLOB()' 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.

Could someone help me out ?

I can either create a new column of TYPE BLOB , or I can convert the same column created as a VARCHAR with DEFAULT value - and then change the type to BLOB . But I am not able to either of them .

Solution

  • Assuming you want the default value to be an empty BLOB rather than the string 'EMPTY_BLOB()', you'd just need to remove the quotes

    SQL> create table new_table (
  2    col1 number
  3  );

Table created.

SQL> alter table new_table
  2    add( file_data blob default empty_blob() );

Table altered.