3 Replies Latest reply on Oct 17, 2017 9:05 AM by Neil Clare

Accessing all application ids which are inserting data in a particular table

MM2587 Oct 12, 2017 4:25 AM

Hi friends, we have a requirement where in we want an audit report on a particular table.

We have a table 'x' in which we are inserting data through Apex Applications. Now we want to which all applications are using procedures/ DML to insert data in to this table.

Is there any default view or anything in apex dictionary which i can use to access the list? I tried some from the apex dictionary but none of them could give me perfect data for ex: select * from APEX_APPLICATION_PAGE_PROC.

In the above query there are two columns PROCESS_TYPE and PROCESS_TYPE_CODE but i want to know what query is inside the process_type_code.

Any help would be much beneficial. As we have around 250 applications in the schema and its really a hard task to go through all these applications.

1. Re: Accessing all application ids which are inserting data in a particular table

Neil Clare Oct 12, 2017 9:09 AM (in response to MM2587)

For processes where the process_type_code is PLSQL, the actual code can be found in the process_source field.
Its a CLOB, so you will need to use DBMS_LOB.instr to query it.

SELECT * FROM APEX_APPLICATION_PAGE_PROC
WHERE process_type_code = 'PLSQL'
AND DBMS_LOB.INSTR(process_source, 'MY_TAB') > 0

Don't forget that it is case sensitive.
Like Show 1 Likes(1)

Actions
2. Re: Accessing all application ids which are inserting data in a particular table

MM2587 Oct 15, 2017 8:27 AM (in response to Neil Clare)

Neil Clare is MY_TAB table name??
Like Show 0 Likes(0)

Actions
3. Re: Accessing all application ids which are inserting data in a particular table

Neil Clare Oct 17, 2017 9:05 AM (in response to MM2587)

Yes it is
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.