+1
BEGIN
insert into feature_test_error(id) values(999);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
...but the SQLExecutor does not return this as a SELECT statement. Is there a way to get the number rows inserted from the Result port of the SQLExecutor in the same SQLExecutor?
Thanks
You need to use the RETURNING INTO-clause:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm
You can then expose the returned attribute name.
David
+1
DECLARE
numberRecords NUMBER := 0;
BEGIN
UPDATE feature_test_error SET error_type='LABELTEXT' where id in ('1588919', '1588934')
returning count(*) into numberRecords;
END;
However, this is not a stored procedure but rather some SQL inside of an SQLExecutor and the variable numberRecords does not have a value that you would get in a SELECT statement.
If there is no PL/SQL that works in the SQLExecutor, it would be great if FME could return the number of records UPDATEd or DELETEd in the SQLExecutor.
+17
Another thought. I think it's also possible to get the number of records before and after inserting with two select statements. e.g.
-----
FME_SQL_DELIMITER ;
select count(*) as num from my_table;
<the insert statement ending with a semi-colon>
select count(*) as num from my_table;
-----
The SQLExecutor will output two features containing the number of records before/after inserting. You can then calculate the number of inserted records using those features.
Takashi
+1
select count(*) as num from my_table
where id in(123, 456, 789);
update my_table set error_type='LABELTEXT' where id in (123,456,789);
Depending on your WHERE clause, the actual number of records updated might different than the "select count(*) " statement selected.
As I said, it would be great if the SQLExecutor could tell you how many records were INSERTED, UPDATED, or DELETED.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
