Fme not reading all the features coming out of sql executor. surprised!!!

I would start by further analyzing your data and your workspace, e.g.

• Are you using the exact same SQL select statement in FME as in TOAD?
• Are you connecting to the same database instance in FME and in TOAD?
• If your SQL is dynamic based on incoming attributes, are the attributes always present and do they contain the expected values?
• Try generating the complete SQL statements in e.g. an AttributeCreator and send the statement to the log and copy/paste to TOAD: does the result there match the result in FME?

Using breakpoints in FME is very helpful when problems like these occur.

For what it's worth, I've never had any problems with the SQLExecutor not returning all the records, even for multi-million record counts.

Just a general note: the HTTPCaller will send a call out for every feature that's being passed through it, as soon as the feature passes through it. I've done that with much less features on a server in a local network and effectively did a denial-of-service attack on it. I'm not saying that this is the case here, but it may be. In that case a Decelerator can be used to slow things down (max features per second or a per-feature delay, but with 1.6M features you probably don't want that)

@david_r

David My answers below..

• Are you using the exact same SQL select statement in FME as in TOAD?
• yes
• Are you connecting to the same database instance in FME and in TOAD?
• yes both are same
• If your SQL is dynamic based on incoming attributes, are the attributes always present and do they contain the expected values?
• count of records in the table not changing
• Try generating the complete SQL statements in e.g. an AttributeCreator and send the statement to the log and copy/paste to TOAD: does the result there match the result in FME?
• yes matches

Moreover when i remove http caller from my fmw. I am able to receive all the records from sql executor.

When I checked this issue with my colleague , even he had this issue before.

Using breakpoints in FME is very helpful when problems like these occur.

For what it's worth, I've never had any problems with the SQLExecutor not returning all the records, even for multi-million record counts.

Now i used feature holder transformer after sqlexecutor and i got all the 1.6 million records from the sql executor. and it is running now. i need to check whether all the output features from feature holder are going thru http caller or not

it is in the process...

i will tell you the result. will see

• Did the entire translation complete successfully?

Same issues here. I am reading features from Oracle table using SQL Creator but it is not reading all of the features for whatever reasons. Log file doesn't reports any errors. Has it something to do with the WorkspaceRunner which does some processing and updates the same rows at the end?

Was the root cause found?

Hi @danaumov - Do you only have a SQLCreator in the workspace? Can you try with an empty workspace and use only an SQLCreator to see if this can read all the features. Also can you paste the complete SQL used here?

I would recommend to add a Attributecreator before the SQLExecutor and a Logger also. This way you can see the actual query being used in the logfile on FME Server also:

In the Oracle trace I found this error. ORA-01555: snapshot too old. This is the root cause but strangely FME doesn't pick this up and doesn't report the error in the FME log, quite the opposite it says everything was a success.

@danaumov - did you see this on the "Oracle side" of things? Havent used Oracle a lot so sorry for the stupid questions :).

Yes, in the Oracle logs but it doesn't show up in the FME log. Oracle errors usually do show up in FME logs but for whatever reason this one wasn't caught by FME.

Before we blame FME, don't forget that there's also the added layer of the Oracle client. I'm curious to know if you can execute the same query using e.g. SQLDeveloper on the same client and not get any errors?

Yes you will get the same error in SQL Developer. It is an Oracle error which has nothing to do with FME but the issue is that FME is not capturing it.

That sounds very weird (and a tad unlikely, I must admit). Regardless, it sounds like a case for Safe support.