Solved

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


Badge

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.

icon

Best answer by austinh 4 May 2018, 16:49

View original

7 replies

Badge

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

Userlevel 2
Badge +12

Are you reading the same table in FME?

The table might be locked by the reader.

Userlevel 2
Badge +17

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.

 

Badge

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.

 

 

Badge

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

 

 

Badge

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.

 

 

Userlevel 2
Badge +17

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.

 

 

Reply