RE: Need details of Tables being deleted from PL/SQL Package

You could try
AUDIT DELETE TABLE IN SESSION CURRENT; Mike Tefft

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Asad Sent: Monday, October 31, 2022 9:07 PM
To: Andy Sayer <andysayer_at_gmail.com> Cc: oracle-l_at_freelists.org; oracle-l-admins_at_freelists.org Subject: Re: Need details of Tables being deleted from PL/SQL Package

Thanks Andy for the answer , can I enable logging like this in the package itself :

--execute immediate 'delete from '|| p_table_name ||' where '||p_pk_column_name||' = :1' using p_id_tab(i);

      execute immediate 'select from '|| l_detail_rec.table_name||' where '|| l_detail_rec.fk_column ||' in ('||l_con_condition_clause||')';
      dbms_output.put_line(' records (s) deleted form table '||l_detail_rec.table_name );

Will this display all the tables ?

On Tue, Nov 1, 2022 at 6:30 AM Andy Sayer <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> wrote: Hi,

Follow the logic of the package to see what values end up in those variables. Ideally you would be logging the final statement somewhere with your existing instrumentation.

I would do this sort of logging at the same point that I’m doing checks that the input values are not sneaky SQL injection (And if you’re not doing that then you need to).

Of course, you can just check what’s in v$sql with a program_id of your package’s object_id and a program_line# of the execute immediate statement. This relies on all the relevant cursors still being in memory. If you do this, don’t forget about SQL injection.

Thanks,
Andy

On Mon, Oct 31, 2022 at 5:43 PM, Asad <asad.hasan2004_at_gmail.com<mailto:asad.hasan2004_at_gmail.com>> wrote:

Hi All ,

I have a task to identify the tables from which data is being purged while executing a apps package .

I checked the package code it seems its doing the following :

execute immediate 'delete from '|| l_detail_rec.table_name||' where '|| l_detail_rec.fk_column ||' in ('||l_con_condition_clause||')';

How to get the list of tables being deleted ?

--

Asad Hasan
+91 9582111698

--

Asad Hasan
+91 9582111698
--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 02 2022 - 20:01:02 CET