2

I am trying to change a CLOB column to VARCHAR(2000) in oracle using EF 6 migration. When EF scaffold the migration file, I have the following line of code in my Up() method: 

AlterColumn("MTA.PLAN_SHEETS", "PLANSHEET_NAME", c => c.String(maxLength: 2000, unicode: false));

When I run the migration I get this error

invalid modification of columns

I know that in Oracle we cannot change a Clob to Varchar directly:
How to change a dataype CLOB TO VARCHAR2(sql)

What is the best approach to change a Clob to Varchar in Entity Framework migration?

Community
  • 1
  • 1
Ehsan
  • 357
  • 5
  • 22
  • Does anyone have an answer to this question? I inadvertently allowed EF6 to create CLOBs instead of NVARCHAR2. Now I need to change them to VARCHAR2, but I don't want to lose the data that's in the columns now – oscilatingcretin Jan 19 '17 at 22:23
  • You will need to do this in 2 or 3 migrations using the basic steps in the link above. First add the varchar2 column, rename the CLOB column and add script command to copy the data. Then you could do a 2nd migration to rename the varchar2 to the original CLOB column name. Test it and when you are happy add a 3rd migration to drop the CLOB. – Steve Greene Jan 20 '17 at 16:15

2 Answers2

1

There is the same problem in probably all RDBMSs. I always use a code similar to this and I think (I'm quite sure) that this is the only way to do it.

AddColumn("MTA.PLAN_SHEETS", "TMP_COLUMN", c => c.String(maxLength: 2000, unicode: false));
Sql(@"UPDATE MTA.PLAN_SHEETS SET TMP_COLUMN = PLANSHEET_NAME");
DropColumn("MTA.PLAN_SHEETS", "PLANSHEET_NAME");
RenameColumn("MTA.PLAN_SHEETS", "TMP_COLUMN", "PLANSHEET_NAME");
bubi
  • 6,414
  • 3
  • 28
  • 45
  • It's a migration question so these steps will be run in a batch. Easy way is to run multiple migrations. Another way is to use an [extension](http://stackoverflow.com/questions/17105716/adding-go-statements-to-entity-framework-migrations) to insert 'GO' statements after each. – Steve Greene Jan 21 '17 at 21:27
0

you can use

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

for example :

select dbms_lob.substr( x, 4000, 1 ) from T;

mohsen.b
  • 436
  • 2
  • 8