Automate change of datatype types and sizes after reverse engineering

Toad Data Modeler
1

Want to take an oracle Reverse engineer and change all varchar < 255 to 255 and all > 255 to max. Also want to change all numerics to (38,2) unless they have 0 scale and then I want them to be an integer.

Can’t seem to get the old script out in the script directory to do this…

2

Not a coder by trade, but I hacked together a solution.

function main()
{
var e, a, iterEntity, iterAttribute;
var DataType_varchar2 = Model.ModelDef.DataTypes.GetObjectByName(“Varchar2(%p1 %p2)”);
var DataType_numeric = Model.ModelDef.DataTypes.GetObjectByName(“Number(%p1,%p2)”);
var DataType_integer = Model.ModelDef.DataTypes.GetObjectByName(“Integer”);
Model.Lock();
for (e=0; e<Model.Entities.Count; e++) // iterate entities
{
iterEntity = Model.Entities.GetObject(e);
iterEntity.Lock();
Log.Information("Entity: "+iterEntity.Name);
for (a=0; a<iterEntity.Attributes.Count; a++) // iterate attributes
{
iterAttribute = iterEntity.Attributes.GetObject(a);
//if (iterAttribute.DataType != null)
{
// Change all varchar < 255 to to 255 and > 255 to max
if (iterAttribute.DataType.Name == “Varchar2(%p1 %p2)”)
if (iterAttribute.DataTypeParam1 > 1 && iterAttribute.DataTypeParam1 < 255)
{
iterAttribute.SetLinkedObject(“DataType”,DataType_varchar2);

//param1= iterAtttribute.DataType[3].substring((datastr[3].indexOf('('))+1,(datastr[3].indexOf(')')));

// iterAttribute.SetLinkedObject(“DataType”,“Varchar2(255)”;
iterAttribute.DataTypeParam1 = ‘255’
iterAttribute.DataTypeParam2 = ‘’;
///Log.Information(" – " + iterAttribute.Name); // do something
///Log.Information(" – " + iterAttribute.DataType.Name.substring(0,3));
}
// Make all varchar2 > 255 max
else if (iterAttribute.DataTypeParam1 > 255)
{ iterAttribute.DataTypeParam1 = ‘max’
iterAttribute.DataTypeParam2 = ‘’;}
// Change numbers with a scale > 0 to a 38,2
}
{if (iterAttribute.DataType.Name == “Number(%p1,%p2)” )
if (iterAttribute.DataTypeParam2 > 0 )
{
//iterAttribute.SetLinkedObject(“DataType”,DataType_numeric);
iterAttribute.DataTypeParam1 = ‘38’
iterAttribute.DataTypeParam2 = ‘2’;
//Log.Information(" – " + iterAttribute.Name);
//Log.Information(" – " + iterAttribute.DataType.Name);
//Log.Information(" – " +iterAttribute.DataTypeParam2);
}
// Make zero scale numbers integers
else if (iterAttribute.DataTypeParam2 == 0)
{ iterAttribute.SetLinkedObject(“DataType”,DataType_integer);
}
}
}

iterEntity.UnLock();
}
Model.UnLock();
Model.RefreshModel();
}

3

Great! Congratulations!