APEX-PLUGIN.COM

..the apex plugin directory

an initiative of

Add your APEX Plugin info

Describe your Plugin’s features. You can upload the Plugin’s code and documentation, or, alternatively, submit the URLs to your Website, GitHup Repository or Blog.

You are here:
Follow us on Twitter
Process Type Plugin Audit

Audit

8
Categories: Process Type Plugin
Author: Ralf Beckmann

Journalizes all changes of a form in a journal tab

Audit

Journalizes all changes of a form in a journal table.

About the plug/in:

The audit process Plug/In is a process type Plug/in that can be used in an APEX form. It extends the functionality auf the dml-process. When the plug/in is started at the first time, it generates an audit table. After that, all changes of the source data will be journalized in this table.

Download then Plug/in 

How to use the plug/in

1.      Install the Plug/in.

2.      Open the Form-Site in the application builder

3.      Create a new process.

4.      Choose the processtype: plug/ins

5.      Select the Audit-Process plug/in.

6.      Give it a name.

7.      Set the properties: Tabelle (table): <database source table>, ID_Item: <item name> (e.g. P10_ID), ID-Spalte (Id-Column): <the primary key column> (e.g. ID )

8.    Create the plug/in

More …

I’ve developed this and some more Plug/Ins as  examples for my book “Oracle Application Express in der Praxis”. (ISBN: 978-3-446-43896-5, http://www.amazon.de/gp/product/3446438963 ). Have a look at this book for more information an the technical background.

License: 

Use the Audit Plug/In under the MIT license.

The MIT License (MIT)

Copyright (c) 2013 Ralf Beckmann (Eslohe, Germany)

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.


Keywords
audit process type history trace apex mit

Oracle APEX Plugin
Company:
Date added: 6.11.2013
Views: 3976
Votes: 3
Reviews: 4
Min. APEX Version:
4.2
Share on Facebook
Digg! Share on Digg
Save This Page
Link to:
Download
download apex plugin
If something is not correct please report it here: Report Listing
Do you have a question about this Plugin? Want to write a Review or Comment?
Login first.
Reviews / Questions / Comments are e-mailed to the author of the Plug-in.
0 
AW: Love your plug in
by Ralf Beckmann
on March 27, 2014
Hi Eleanor,

I am glad that the plug/in further helps you.

I looked in my source code. You must change the following code pieces.

PROCEDURE create_hist_table
[...]

l_sql := 'create table ' || p_table || '_H as SELECT sysdate hist_timestamp, ' || '''' || 'INITIAL' || '''' || ' as state ,'
|| '''' || ' ' || '''' || ' as user_id , x.* FROM ' || p_table || ' x';

[...]

This procedure create the Hist-Table by the first call.

PROCEDURE fill_hist_table
[...]

IF l_cnt_del = 0 THEN
l_state := 'DEL';
l_sql := 'INSERT INTO ' || p_table || '_H (HIST_TIMESTAMP, STATE, ' || p_id_column || ') VALUES ( sysdate,' || '''' || l_state || '''' ||', ' || v(p_id_item) || ' ,'
|| '''' || v(APP_USER) || '''' ||')';
ELSE
APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : p_id_item ' || v(p_id_item), p_level => 1);
-- Wurde ein Datensatz geändert ?
l_state := 'CHANGED';

l_sql := '
INSERT INTO ' || p_table || '_H SELECT sysdate,
' || '''' || l_state || '''' ||','
|| '''' || v(APP_USER) || '''' ||',
x.*
FROM ' || p_table || ' x
WHERE ' || p_id_column || ' = ' || v(p_id_item);
END IF;

APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : ' || l_sql, p_level => 1);
EXECUTE immediate l_sql;
END IF;
[...]

The second procedure inserts the data into the hist-table, if the source has been changed.

I hope, that the informations help.

Ralf
0 
Love your plug in
by Eleanor Porteous
on March 19, 2014
Hi Ralf
I have just been using your plug in and its exactly what I need apart from one little item I need the user id to be added to the history table as well as the date and state
I though I would kindly ask you to point me in the right direction before I go through the sql and try and work it out myself
I know that there is a fault with the apex_user as it currently adds exactly " apex_user" that to the journal table instead of the correct user name
Keep up the good work
Eleanor Porteous
0 
Only Inserts? - No, als DML-processes
by Ralf Beckmann
on January 28, 2014
Hello Efren,

no that's not correct.

First the plug/in creates a hist-table.

create table _h as select HIST_TIMESTAMP, STATE,a.* from a

After this "install"-Step the plug/in executes the procedure fill_hist_table.

If you delete a row in the source table, the plug/in inserts a copy of this row in the hist-table and sets the STATE to 'DEL".
Otherwise the plug/in set the STATE to 'CHANGED' and inserts the row to the hist table.

Here is the corresponding code snippet ...

[..]
l_sql := 'select count(*) from ' || p_table || ' x WHERE ' || p_id_column || ' = ' || v(p_id_item);

EXECUTE immediate l_sql INTO l_cnt_del;

IF l_cnt_del = 0 THEN
l_state := 'DEL';
l_sql := 'INSERT INTO ' || p_table || '_H (HIST_TIMESTAMP, STATE, ' || p_id_column || ') VALUES ( sysdate,' || '''' || l_state || '''' ||', ' || v(p_id_item) || ')';
ELSE
APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : p_id_item ' || v(p_id_item), p_level => 1);
-- Wurde ein Datensatz geändert ?
l_state := 'CHANGED';

l_sql := '
INSERT INTO ' || p_table || '_H SELECT sysdate,
' || '''' || l_state || '''' ||',
x.*
FROM ' || p_table || ' x
WHERE ' || p_id_column || ' = ' || v(p_id_item);
END IF;

APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : ' || l_sql, p_level => 1);
EXECUTE immediate l_sql;
[..]

Ralf
6 
Only Inserts?
by Efren Ramirez
on January 27, 2014
This plug-in only journalizes the insert. Correct?

Efren.
Powered by Sigsiu.NET RSS Feeds

Disclaimer: The APEX Plugins on this site are not supported by Oracle Support Services.

If you have a question about a Plugin or need support: Login and select the plugin detail page. At the bottom you can enter your Question / Review / Remark.
Your message will be sent to the author of the plugin.