Skip to main content
Question

Oracle Writer not working due to old Oracle 19c and massive tables. I need a workaround for delta files... help with FeatureReader please!

stu_home
Contributor
Forum|alt.badge.img+6

Hi,

Situation - Oracle Writer will not work, so I need a workaround to overcome this. The tables in the workspace have many attribute fields, 2100 or thereabouts, and the Oracle writer falls over. 😕 Safe recommend upgrading to a newer Oracle, so the client will get that sorted eventually… but meanwhile I need to get the workbench working.

The workbench prepares delta files to be written into the Oracle db. Once the data is ready for writing to the db I have used FeatureReader to read 13 Oracle tables into the workspace, then use a FeatureMerger with the existing workspace table to identify if the features already exist in the 13 db tables or not. If they are UsedSupplier they exist and are then designated to be an “Update”, if they are UnusedSupplier then they are new features and are sent as an “Insert”.
As a further complication, we need to write to CSV files to overcome the issue with not being able to write to Oracle from FME. So we need a batch of csv files for Insert, and another for Update. Oh yeah, this is a fun one! 😁

This worked ok to begin with, but as the db has grown in size it takes hours to read in those large Oracle tables.

The tables each contain 100,000s of features. The delta files may only be around 5 to 30 features that need writing across. Is there a way to use the WHERE clause in the Feature Reader to pick up the unique identifier (ASSET_ID) from the FME workspace table so that only those 5 to 30 features are read in by the FeatureReader… if they exist? I presume that will be quicker than bringing-in all 13 whole tables into the workbench? 

I’ve been using the AI engines to try to figure it out, and it seems that it is entirely achievable, but I can’t fathom out exactly how to do it! If you do have any bright ideas, please do not worry about overexplaining it, I need all the help I can get! 😳

Or is there a better way than using the FeatureReader? The AI engines reckon InLineQuerier is an option, but I am completely lost when trying to use that one. 

Thanks in advance for any help you can offer.

Stu

 

    mark2atsafe
    1 person upvoted this
    • mark2atsafe
      mark2atsafe

    2 replies

    AliAtSafe
    Safer
    Forum|alt.badge.img+23
    • AliAtSafeSafer
    • Safer
    • July 2, 2025

    Hello, ​@stu_home.

    Thank you for posting your question to the community. Also, I appreciate you giving a thorough explanation about where you are at, your workspace, and how you tried different approaches - this really helps 🙂.

    Please let me know where you are at with your scenario, since your question was posted a few days ago 😁.

    Could you let me know which build and version of FME Form/Desktop you are working with?

    Alrighty, so starting with these two question as a start in case you have already gotten ahead:

    Is there a way to use the WHERE clause in the Feature Reader to pick up the unique identifier (ASSET_ID) from the FME workspace table so that only those 5 to 30 features are read in by the FeatureReader… if they exist? I presume that will be quicker than bringing-in all 13 whole tables into the workbench? 

    Yep, you can limit what you are reading. If you know the uniqueIDs of the changed features a head of time this should save a tremendous amount of time. Would you know which features are changed prior to reading?

    Another option might be to create a view or a temp table where you do joining inside the database, so right away you would at the very least know which features are updated - but unsure (not tested).

    Seeing that there is some Oracle Change detection built into the database you could try that out - unsure though.

    Let me know if you have or haven’t tested this scenario: “quicker than bringing-in all 13 whole tables into the workbench”.

    Thanks!

    Curiosity conquers fear
    stu_home
    Contributor
    Forum|alt.badge.img+6
    • stu_homeContributor
    • Author
    • Contributor
    • July 7, 2025
    AliAtSafe wrote:

    Yep, you can limit what you are reading. If you know the uniqueIDs of the changed features a head of time this should save a tremendous amount of time. Would you know which features are changed prior to reading?

     

    Hi Ali,

    Thanks for looking at this, and yes, that’s what we ended up doing. This is a delta workspace so the numbers of the assets will change every time it’s run.
    In the yellow shaded bookmark below you can see we take those ASSET_ID numbers and prepare them for the where clause in the FeatureReader. The attribute manager creates a new field called ASSET_ID_CONCAT and adds the single quotes to each end ‘@Value(ASSET_ID)’, and then the Aggregator is set to Concatenate that field, adding a comma as a separator character. So that new field now has one row that looks like this ‘1234’,’5678’,’9101112’… 

    The FeatureReader is then set as follows, with the Feature Types to Read being the 13 Oracle tables (which I have covered up for privacy):
     

    This runs in just a few seconds now, rather than an hour or two! 

    This enables us to identify if those features can be read in by the FeatureReader. If they exist they are read in at this stage. In the screen shot example there are 447 records being fed in, and just 22 already exist.
    These 22 are then sent to the FeatureMerger to be combined with the 447 in our workbench table, the 22 existing are then classed as an UPDATE and the other 425 are INSERT.

    Job done! 🤓

    AliAtSafe
    geomancer
    2 people upvoted this
    • AliAtSafe
      AliAtSafe
    • geomancer
      geomancer

    Reply


    Helpful Members This Week

    Community Stats

    31,503
    Posts
    119,422
    Replies
    39,121
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.