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

Logging to the dbms_output buffer isn’t going to be too helpful when the thing calling your package doesn’t check it. I would log the whole statement to a table, everyone has an opinion on what should go with it but I would include at least the date of when it was attempted and something related to your session (sid maybe). I would do the logging before the attempt, otherwise you won’t see anything that fails. Maybe check out Logger
https://github.com/OraOpenSource/Logger to get started.

Then, you just query your logging table separately.

Thanks,
Andy

On Mon, Oct 31, 2022 at 6:07 PM, Asad <asad.hasan2004_at_gmail.com> wrote:

> 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> 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> 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