July 31st, 2012, 04:56 AM
Precautions for converting a table field from long to clob
I am more of a C/C++ guy and relatively amateur in oracle. I have to update a table field from "Long" to "CLOB". I have planned to do a simple alter table, and as far as I know there won't be any issues.
1. Although I have triple checked, is there any scenario under which there can be any data loss during the data type change? The data is very critical and no data loss can be entertained.
2. Is there any easy way to update all the related views without having to do so manually?
3. Any particular precautions I should take before introducing the change?
Any help will be greatly appreciated. Thanks guys.
July 31st, 2012, 12:57 PM
Suggestions for questions:
Q1. There is the obvious "backup first" could be a proper backup or simply create
a temporary table with all the old data. Make sure you take care of the
storage specs for the altered column.
Q2. Depending upon what tools you use, I think many support you selecting items
(i.e. views) and doing a "Compile all with dependencies". Only caveat (and I am not
sure if it was Oracle). I have had situations where Instead of triggers
just get wiped away.
Q3. See Q2. regarding "instead of" triggers.
August 1st, 2012, 12:12 AM
Thanks clivew for your reply. The triggers won't be a problem, as there are none.
To update the views i was looking at the refresh view proc, but if it can be done using an IDE I will look into it.
BTW anybody has any idea how to enter more than 60000 characters into a long field. I know the field can take the data, but can't find a way to enter the same.