Skip to main content
  • EN

I am trying to use SQLExecutor to either insert or update a record in a table stored in Oracle - please see sql below. It gave me the following error. any ideas why?

 

declare

counts int;

begin

select count(*) into counts FROM employee where emp_id = '@Value(empid)';

case

when counts = 0 then INSERT INTO employee VALUES ('@Value(empid)','@Value(firstname)');

when counts = 1 then UPDATE employee SET firstname = '@Value(firstname)' WHERE emp_id = '@Value(empid)';

end case;

end;

 

An error occurred while accessing a table result for feature type `'; message was `Execution of statement '

......

did not succeed; error was `ORA-06550: line 7, column 14:

PL/SQL: ORA-00933: SQL command not properly ended

ORA-06550: line 6, column 30:

PL/SQL: SQL Statement ignored

ORA-06550: line 9, column 4:

PLS-00103: Encountered the symbol ";" when expecting one of the following:

I believe you will need to add this at the top of your window:

FME_SQL_DELIMITER ;

I believe you will need to add this at the top of your window:

FME_SQL_DELIMITER ;

thanks for your reply. now it threw "did not succeed; error was `ORA-00900: invalid SQL statement'. "

Was this error raised for the first feature, or during further processing?

Could it be hat you have a value of firstname that contains a single quotation mark or some other character that could interfere with the SQL statement?

You can also try putting the following at the very top (do NOT use ; for the delimiter in this case):

FME_SQL_DELIMITER #

This will ensure that FME doesn't try to split up the SQL statement before sending it to Oracle.

@wgsl2005

 

I think your syntaxis is off.

The ";" between the "when" should go and insert a "else"

Or add a "case" before the second "when" if the delimiter signifies a new sql snippet.

 

Row starting with just "when" seems not valid.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

Was this error raised for the first feature, or during further processing?

Could it be hat you have a value of firstname that contains a single quotation mark or some other character that could interfere with the SQL statement?

You can also try putting the following at the very top (do NOT use ; for the delimiter in this case):

FME_SQL_DELIMITER #

This will ensure that FME doesn't try to split up the SQL statement before sending it to Oracle.

Thank You So Much! i have debugged for a few hours for frustration and your solution worked.

so the solution is to add FME_SQL_DELIMITER #  on the top. the SQL statements are correct. 

Reply


Community Stats

31,821
Posts
120,861
Replies
39,452
Members

Latest FME

Terms & ConditionsCookie 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.