NCLOB in Alarm Journal - not in DB tables (Oracle)

Ignition
2

Ran this series of commands to change it from CLOB to VARCHAR. Picked a length of 500 to leave room for longer descriptions (my longest was 293).

--create new column
alter table ALARM_EVENT_DATA add ("strvalue2" varchar2(500));
--copy clob to varchar
update ALARM_EVENT_DATA set "strvalues" = dbms_lob.substr("strvalue",500,1);
--rename clob, then rename new column
alter table ALARM_EVENT_DATA rename column "strvalue" to "strvalueClob";
alter table ALARM_EVENT_DATA rename column "strvalue2" to "strvalue";

I was hesitant to drop the CLOB just yet, but it will happen once I'm content this is working.

Since this is a system created table, perhaps the folks at IA can make these changes for future releases?

Cheers!

Edit:
Used this command to get the max clob length for determining my new VARCHAR size.

SELECT max(dbms_lob.getlength("strvalue")) FROM ALARM_EVENT_DATA

1 Like