2 Replies Latest reply: Jan 27, 2012 9:50 AM by Jeff Chirco RSS

    Precision of Numbers lost on import of Erwin 7.3 model

    Jeff Chirco
      A couple weeks ago we switched from ERwin 7.3 to Oracle Data Modeler 3.1.0.691 and I imported my model from ERwin. Well we just realized that the import process removed all the number precision on my table columns. So if I had a table with a column defined as NUMBR(5,2), it shows in Data Modeler as NUMBER(5). This is extremely bad news because we have already started making changes in the new model and if we have to go back to ERwin it could be a giant headache. We have hundreds of tables in this model.
      I opened a bug with Oracle support but my question is; is there somehow a way to compare the model against my database and only find that tables with columns that have a mismatch in the precision and update them?

      11g database


      I am desperate for help here.

      Thanks
        • 1. Re: Precision of Numbers lost on import of Erwin 7.3 model
          Philip Stoyanov-Oracle
          to compare the model against my database and only find that tables with columns that have a mismatch in the precision and update them?
          filtering at that level is planed for DM 3.2
          you can use transformation script to connect to database and update scale for your columns. Here is an example:
          //
          function getColumn(tables,owner,tname,cname){
           for (var i = 0; i < tables.length; i++) {
            table = tables;
          schema = table.getSchemaObject();
          if(schema!=null){
          if(schema.getName().equals(owner) && table.getName().equals(tname)){
               return table.getElementByName(cname);
               }
          }
          }
          return null;
          }
          //====
          tables = model.getTableSet().toArray();
          java.lang.Class.forName ("oracle.jdbc.OracleDriver");
          // url - jdbc:oracle:thin:@host:port:sid
          //"user","pswd"
          conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:fdb11r1", "hr", "hr");
          stmt = conn.createStatement();
          rset = stmt.executeQuery("select owner,table_name,column_name,data_scale from all_tab_columns where owner='HR' and data_type ='NUMBER' and data_scale is not null and data_scale!=0");
          if(rset!=null){
          while(rset.next()){
          owner = rset.getString(1);
          tname = rset.getString(2);
          cname = rset.getString(3);
          scale = rset.getString(4);
          column = getColumn(tables,owner,tname,cname);
          if(column!=null){
          column.setDataTypeScale(scale);
          column.setDirty(true);
          }
          }
          rset.close();
          }
          stmt.close;
          you can change the query in order to fit to your need.
          
          Philip
          
          Edited by: Philip Stoyanov on Jan 25, 2012 9:11 AM