Forum Stats

  • 3,757,060 Users
  • 2,251,192 Discussions


Refresh Physical Schema in Oracle ODI 12 c

User_C81BA Member Posts: 9 Green Ribbon

My physical schema for my target table has been changed with different data type. I am trying to see if there is a way that I can refresh this in oracle odi process. I have gone through all the attributes and changed where applicable but still my target table does not reflect correctly as per the source table. I came to know that even though I changed logical schema oracle keeps physical schema as a data store.

My question is how do we refresh this for one particular column in a table ? As you can see on attached pic, Logical schema data length attributes set to 200 but physical format is still 100 and its greyed out.

Any help would be appreciated. Thank you



  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    Hi. I'm sorry but your question is a little hard to understand since you have mentioned a lot of things that don't really make a lot of sense in a single issue. Do you mean that a column datatype has changed, and you want to correct it?

    If so, locate your target table in your mode and double click it.

    Then select the Attribute panel and you can directly change the datatype or length of your column:

  • User_C81BA
    User_C81BA Member Posts: 9 Green Ribbon

    Hi, cheers for answering. Apologies, I am new to oracle so just researching and exploring so may be I had post too much info.

    Btw, yes you are correct. The target column datatype should have been varchar(200) but it was varchar(100) resulting truncating values after 100 characters. I have changed target table attributes like you shown above and also in target table in our external database. It shows the Col1 - varchar(200) as expected but after running the mapping thought this should fix but still it truncates value at 100 length at target table.

    After digging through, I came to know that datastore needs to be refreshed as you can see here even though I changed the attributes still it does not change the length at Physical Format as shown below for this specific column. Because, during the IKM process Oracle looks at this Physical schema datastore which has varchar(100) and I had this confirmed by looking at the operator - then sql code logs for creation of temp table for target table. Here you can see, oracle sql for this specific column in question was varchar(100) so it does not look at the attributes where I changed. Hope it makes sense.

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    Can you share a screenshot of your mapping? Are you using "co_name" in a distinct/aggregate/expression component before you load the target? If so, it might be this part of your mapping that needs changing as they hold their own attributes.

    You could also try reverse engineering your table completely and see if that aligns your table as expected.

    In your model, double click on your model folder

    Then open the Selective Revese Engineering Panel.

    Tick the box for existing datastores, and objects to reverse engineer.

    Select only the table that you want and then click the "reverse engineer" button

  • User_C81BA
    User_C81BA Member Posts: 9 Green Ribbon

    Thanks. I can confirm there are no specific component or transformation happening for this "co_name" column.

    But I think you are correct in terms of reverse engineering the table as this should refresh the datastore element within Oracle ODI. I did look at this and I could see only one table - Any ideas why only one when I have so many?

    But can I refresh this specifc table "s_company" via clicking at the table and do reverse engineer here as the other tables are fine?

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    In your screenshot you are running a selective reverse engineer on the model folder STG.

    You see only one datastore because either a) that is the only ODI table that you have so far imported, or b) you have a mask applied in the reverse engineer panel that is filtering out other tables (but it's only a mask and you can constantly change this to help refine the list of tables).

    If you have only one table selected, it will only reverse engineer that table. It won't touch any other tables.

  • User_C81BA
    User_C81BA Member Posts: 9 Green Ribbon

    Brilliant. You are correct. It does have a mask applied in the reverse engineer panel. Can you please elaborate when you say "its only a mask and you can constantly change this to help refine the list of tables).

    Okay, I shall give that a go in test for that one table in question.

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge
    edited Jul 5, 2021 2:23PM

    If I were to go and change the mask to "TB%", when I looked at the selective reverse engineer panel and results I would only see objects that started with TB. The % is a wildcard.

    If I changed that mask value to "%EXAMPLE%" it would show me all the objects with "example" in the name:

    If I reverse engineered the whole mode it would only reverse engineers objects that matched that mask value.

    If I'm using selective reverse engineering, it would only show me the objects that matched that mask value.

    They still exist, it's just a way to filter out the object that you want to affect,

  • User_C81BA
    User_C81BA Member Posts: 9 Green Ribbon

    Very much appreciate the help you provided here.

    Thanks a lot for explaining in detail and I shall give that table in my test tomorrow.

  • User_C81BA
    User_C81BA Member Posts: 9 Green Ribbon
    edited Jul 6, 2021 2:53PM

    Hi, I have actually tested this and done the reverse engineering but still it does not change the value of the length of the data field to 200. Still showing 100? Any ideas?

    "s_company" is the table;

    "co_name" is the column field.

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge
    edited Jul 6, 2021 9:51PM

    This is bugging me - I've never had to make any changes to the physical format with one exception - when using files as a source/target.

    From the IKMs you are using I assumed that you were using a MSSQL database as your source and target. Am I correct?

    By any chance did you create your datastore by reverse engineering a file and then moved it to the MSSQL model?

    I have managed to find a solution for you, but it's long and messy, and I don't know why it works. If you follow this, test it heavily before you decide if it's correct for you. It does seem to work for me.

    In your model, right click on your datastore and make a copy. This is your backup.

    Move your original datastore from the correct model, to a model based on file technology.

    You will need to change the file config. The choice are irrelevant, just pick delimited, put a comma in the record separator and a double quote in the field separator.

    You will also need to change the attribute format if you have any date fields. Again, it doesn't really matter what you put so just put DDMMYY.

    Now change the physical length to 200 and save it.

    Once saved, move the datastore back into the correct model.

    For some reason, you should now be able to edit the physical format length of your attribute...

    I did test if you could simple move it to file and back again, but it seems you have to actually change the detail of it when it's in the file technology, before it works.