I have the following function inside a package:
--Update APPERY_JTI_deleted_USERS table:
FUNCTION fn_updt_app_jti_dlt_usr(
p_update_co IN VARCHAR2,
p_appery_user_id IN APPERY_JTI_deleted_USERS.appery_user_id%TYPE,
p_outlet_code IN APPERY_JTI_deleted_USERS.outlet_code%TYPE)
RETURN NUMBER
AS
lv_sql VARCHAR2(4000);
lv_rowcount NUMBER := 0;
BEGIN
lv_sql := 'UPDATE APPERY_JTI_deleted_USERS SET '||p_update_co||' = 1 WHERE '||p_update_co||' = 0 AND OUTLET_CODE = '''||p_outlet_code||''' AND APPERY_USER_ID = '''||p_appery_user_id||'''';
--EXECUTE IMMEDIATE lv_sql;
EXECUTE IMMEDIATE 'BEGIN ' || lv_sql || '; :z := sql%rowcount; END; ' USING OUT lv_rowcount ;
RETURN lv_rowcount;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END fn_updt_app_jti_dlt_usr;
The function being invoked several times as part of a Job. Basically, the main function (Say M), invokes few functions ( say A1 ... A9). Each of these functions is doing something and updating the status using the function above. Hence, each of the nine functions will invoke the function above with different parameters.
The problem I'm facing is as follows:
First run, only first invoke for the function above will return positive result (sql$rowcount > 0). When I run M the second time, only first and second invokes for the function above will return positive result, and so on.
What odd is that, if I change the values again and run function M, it will start from the beginning. And what more odd is, the dynamic update statement is being executed correctly and data is being updated successfully. It is just sql%rowcount not returning the expected result.
I tried to run execute immediate without (Begin end). I also tried to run it without USING OUT. It is always the same. I am really confused what I am missing here.
I'm not sure if this is relevant. But for what it's worth, invoking the above function is updating the SAME rows in the table appery_jti_deleted_users only the columns are different. So for the entire job, the same row(s) need to be updated. Each function of (A1 ... A9) will invoke the function above to update different column of these rows.