trying to figure out what a PL/SQL package is doing

[jayjabour]

I company has this PL/SQL package that is cleaning up tables. I have been assigned the task of figuring out why it deletes what it deletes. I was wondering if any one can help me. I have figured out so far what tables it is deleting but I don't know what stuff it is deleting. For example it deletes from a table ps_message_log where process_instance = :v_prcs but I don't know what :v_prcs means, I would figure that earlier in the code it declares what :v_prcs means I just can't find in the code where it does that. I have attached the code. Any help would be GREATLY appreciated.

[gandolf989]

":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!

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.

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.

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.

[jayjabour]

thanks for the help. I was told the creator of this code is no longer with the company. Who ever wrote it left no documentation. I was assigned the fun task of looking at it. Having no pl/sql knowledge I am banging my head against the wall.