":v_prcs" is a SQL variable. It should be added to the parameter list for the zz_cleanup_report_purge_table stored procedure.
Get rid of the WHEN OTHERS clause! If you get a fatal error, then you should fix the fatal error!
Code:
EXCEPTION
WHEN NO_DATA_FOUND then
--zz_dbms_output('No Child!');
return null;
WHEN OTHERS then
zz_dbms_output('Hmmm, needs debugging! '||SQLERRM);
return null;
--
END determine_nvs_expiration;
If you want to know what is being deleted use DBMS_OUTPUT to print the delete code along with SQL%ROWCOUNT to see how many rows were deleted.
Code:
v_sqlstmt :=
'delete ps_message_log where process_instance = :v_prcs';
execute immediate v_sqlstmt
using v_prcsinstance;
Also this code is just pointless!
Without is you will get better error messages.
Code:
EXCEPTION
WHEN OTHERS then raise_application_error
(-20000, SQLERRM, TRUE);
--
END zz_populate_report_purge;
I don't mean to be hash, but there are some serious issues with this code that need to be addresses. Get a copy of Steven Feuerstein's PL/SQL Best Practices and read it twice and you will see what I mean.