I initialize my workflow with a SQLCreator that queries an Oracle Table, transforms the data and update the table back in Oracle.
When I insert a new record thru a sqlplus/Toad into the Oracle Table, after that I re-run the Workspace in FME Workbench, the records from the SQLCreator does not update, it keeps the same records as when I first ran it.
What am I doing wrong here? Any help is appreciated, I've been trying to run this for the last 2 days with no luck.
Just to be sure. Is Feature Caching turned on?
You can check this under Run, Enable Feature Caching.
I agree with @nielsgerrits that Feature Caching would be potential culprit no. 1.
If that still doesn't make a difference, verify that the table insert has been properly commited (i.e. no open transaction) before restarting the workspace.
Yes, Feature Caching is turned on.
Then I turned it off, re-run.
Turned it on again, and re-run.
And still no refresh.
I checked the Oracle DB with sqlplus, and did a SELECT COUNT(*) FROM Table1
And the record is counted.
Yes, Feature Caching is turned on.
Then I turned it off, re-run.
Turned it on again, and re-run.
And still no refresh.
I checked the Oracle DB with sqlplus, and did a SELECT COUNT(*) FROM Table1
And the record is counted.
I suggest that you leave feature caching OFF entirely as long as you debug this issue.
Next step would be to check that the newly inserted record is visible in both TOAD and SQLPlus, simultaneously.
Yes, Feature Caching is turned on.
Then I turned it off, re-run.
Turned it on again, and re-run.
And still no refresh.
I checked the Oracle DB with sqlplus, and did a SELECT COUNT(*) FROM Table1
And the record is counted.
Select count(*) will still count uncommited rows, but FME won't see them until they are committed
@nielsgerrits @david_r , thx for the help!
I checked the commit to the database as David suggested, the record was not committed, so the new record was not showing on the FME Tool when refreshed... duh ;)
Thank you, Markus.
@nielsgerrits @david_r , thx for the help!
I checked the commit to the database as David suggested, the record was not committed, so the new record was not showing on the FME Tool when refreshed... duh ;)
Thank you, Markus.
Great that you found the issue and thanks for sharing the solution!
Select count(*) will still count uncommited rows, but FME won't see them until they are committed
I'm assuming this is only the case if you're doing the select count(*) from the same session as the insert, right? I would assume that doing select count(*) from a different session would not include uncommitted rows.
Select count(*) will still count uncommited rows, but FME won't see them until they are committed
Yes, if from the same session.