Skip to main content
Solved

SQL Update statement does not work with SQLExecutor but works in SQLDeveloper


Forum|alt.badge.img

Hi,

I need to update a specific value in an Oracle database. However, I'm running into trouble when trying to execute an SQL Update statement using the SQLExecutor. The very same statement works like a charm when executed in SQLDeveloper. The statement :

FME_SQL_DELIMITER ;

update table_a set valeur = 'New_Value'

where id in (

select av.id

from table_a av

join other a on a.id = av.idother and a.libelle = 'PARTNER A'

join table_p p on p.id = av.idproject and p.CODE = 'xxx'

);

When I execute this from FME, he connects to Oracle, but never finishes. The log get stuck at this moment :

Oracle database version is '11.2.0.1.0'

Environment variable ORACLE_HOME is not set.

Connecting to ORACLE database `ORCL' with user name of `TESTL' and password of `***'

Connected to ORACLE database `ORCL'

I've tried to put the SQL in one single line without success. I've also set the environment variable ORACLE_HOME without succes (although I'm not 100% i did it correctly even though the log told me the varible was set).

I use several SQLExecutors in the workspace to execute different Select statement and they all work. However, when trying to update a value... I'm not sure where to look next. Any ideas would be glady appreciated.

Best answer by austinh

You may try putting a COMMIT; at the end of your SQL. Oracle like s this

View original
Did this help you find an answer to your question?

7 replies

austinh
Contributor
Forum|alt.badge.img+4
  • Contributor
  • Best Answer
  • May 4, 2018

You may try putting a COMMIT; at the end of your SQL. Oracle like s this


erik_jan
Contributor
Forum|alt.badge.img+17
  • Contributor
  • May 4, 2018

Are you reading the same table in FME?

The table might be locked by the reader.


takashi
Influencer
  • May 5, 2018

Hi @rhansen, try executing a simple SQL statement such as "select * from table_a" with the SQLExecuter. If it would also fail with the same error message, you could have failed to setup environment for Oracle connection. See also these articles.

 


Forum|alt.badge.img
  • Author
  • May 14, 2018
takashi wrote:

Hi @rhansen, try executing a simple SQL statement such as "select * from table_a" with the SQLExecuter. If it would also fail with the same error message, you could have failed to setup environment for Oracle connection. See also these articles.

 

A simple SELECT statement works fine with SQLExecutor. When trying to execute an UPDATE, I've noticed that SQLExecutor really does update the record in Oracle, but the SQLExecutor does not push through the initiator --> which results in FME being stuck at that transformer.

 

 


Forum|alt.badge.img
  • Author
  • May 14, 2018
erik_jan wrote:

Are you reading the same table in FME?

The table might be locked by the reader.

No, I've isolated the SQLExecutor to try to execute the update but FME gets stuck

 

 


Forum|alt.badge.img
  • Author
  • May 14, 2018
austinh wrote:

You may try putting a COMMIT; at the end of your SQL. Oracle like s this

Excellent! This worked like a charm. SQLExecutor updates the record and push through the Initiator so the feature can continue. Thank you all for your comments.

 

 


takashi
Influencer
  • May 14, 2018
takashi wrote:

Hi @rhansen, try executing a simple SQL statement such as "select * from table_a" with the SQLExecuter. If it would also fail with the same error message, you could have failed to setup environment for Oracle connection. See also these articles.

 

That's normal. Since UPDATE statement won't generate result table, the SQLExecutor outputs no features from the Result port. If you need to continue processing with the initiator features after executing the SQL statement, consider using the features output from the Initiator port.

 

 


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