APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

Audit trail in APEX

Von Tobias Arnhold → 1.31.2009

In case you have the requirement to use audit trail functionality in your APEX application then look at that example:

First we need two tables:
- The orignial table (BOOKS)
- The audit trail table (AUDIT_BOOKS)


-- the orignial table (BOOKS)
create table BOOKS
(
  B_ID           NUMBER not null,
  B_NAME         VARCHAR2(100),
  B_DESCRIPTION  VARCHAR2(500)
);

-- the audit trail table (AUDIT_BOOKS) always
-- includes 3 more columns for the user, the date 
-- and the action (delete, update, insert)
-- this table shouldn't include any PK or FK
create table AUDIT_BOOKS
(
  B_ID           NUMBER not null,
  B_NAME         VARCHAR2(100),
  B_DESCRIPTION  VARCHAR2(500),
  AUDIT_USER VARCHAR2(50),
  AUDIT_DATE   DATE,
  AUDIT_ACTION   VARCHAR2(6)
);

Next step is to create a trigger which includes the audit trail functionality:


CREATE OR REPLACE TRIGGER trg_audit_books
  -- starts on every update, insert or delete command
  AFTER INSERT OR DELETE OR UPDATE ON books
  FOR EACH ROW
DECLARE
  -- variable which declares if update, delete or insert process
  v_trg_action VARCHAR2(6);
BEGIN
  IF updating
  THEN
    -- when update
    v_trg_action := 'UPDATE';
  ELSIF deleting
  THEN
    -- when delete
    v_trg_action := 'DELETE';
  ELSIF inserting
  THEN
    -- when insert
    v_trg_aktion := 'INSERT';
  ELSE
    -- if something else
    v_trg_action := NULL;
  END IF;
  
  IF v_trg_action IN ('DELETE','UPDATE') THEN
      -- if v_trg_action is DELETE or UPDATE then insert old table values
   INSERT INTO audit_books
  ( B_ID,  B_NAME,  B_DESCRIPTION,
    AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
   VALUES
  (:OLD.B_ID, :OLD.B_NAME, :OLD.B_DESCRIPTION,
    UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  ELSE
      -- if v_trg_action is INSERT then insert new table values
   INSERT INTO audit_books
  ( B_ID,  B_NAME,  B_DESCRIPTION,
    AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
   VALUES
  (:NEW.B_ID, :NEW.B_NAME, :NEW.B_DESCRIPTION,
    UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  END IF;
  -- about the insert command on the audit table
  -- for current apex user: v('APP_USER')
  -- for date: SYSDATE
  -- for sql command: v_trg_action   
END trg_audit_books;

This is all you need to use audit trail.
Special thanks to Roel and Anthony who helped me out a bit: http://forums.oracle.com/forums/message.jspa?messageID=3240814

APEX examples

Using individual error/success messages in APEX

Von Tobias Arnhold → 1.25.2009

There are several ways about how to use own error/success messages in APEX page processes. I want to show how to use the internal APEX process.

To create an own alert message in a page process use that syntax:


apex_application.g_print_success_message := '<span style="color:green">Data from ' || :P1_DATE || ' successfully updated</span>';

With that function you replace the text of the internal APEX Process Success Message.

PL/SQL process example with own dynamic messages:


begin
IF :P1_COMPUTER like 'PC%'  THEN
INSERT INTO...;
COMMIT;
apex_application.g_print_success_message := '<span style="color:green">Computer created</span>';
ELSE
apex_application.g_print_success_message := '<span style="color:red">No valid computer name</span>';
END IF;
END;

Example with own alert messages for updateable reports in a page process:
(In this example we have an updateable report with time values like 'hh24:mi' = '06:30'. Every object needs to be checked via a function check_time before an update will be made. If the result is 0 then a error message should occur)


DECLARE
-- variables
v_error      number;
BEGIN

FOR i IN 1 .. APEX_APPLICATION.g_f50.COUNT -- count number of rows in this report
 LOOP
   -- UPDATE procedure
   CASE WHEN check_time(APEX_APPLICATION.g_f01(i)) = 0 THEN
             apex_application.g_print_success_message := '<span style="color:red">Value ' || APEX_APPLICATION.g_f01(i) || ' is wrong.</span>';
             ROLLBACK;
             v_error := 1; EXIT;
             ELSE
 -- now an update command could be set
 null;
             END IF;
   CASE WHEN check_time(APEX_APPLICATION.g_f02(i)) = 0 THEN
             apex_application.g_print_success_message := '<span style="color:red">Value ' || APEX_APPLICATION.g_f02(i) || ' is wrong.</span>';
             ROLLBACK;
             v_error := 1; EXIT;
             ELSE
  -- now an update command could be set
     null;
             END IF;
   CASE WHEN check_time(APEX_APPLICATION.g_f03(i)) = 0 THEN
             apex_application.g_print_success_message := '<span style="color:red">Value ' || APEX_APPLICATION.g_f03(i) || ' is wrong.</span>';
             ROLLBACK;
             v_error := 1; EXIT;
             ELSE
  -- now an update command could be set
     null;
             END IF;
    -- CASE ... 
    -- depending on the amount of columns
    END CASE;
END LOOP;
-- no commit if error occurred, error only occur when validation went wrong
IF v_error is null then
COMMIT;
apex_application.g_print_success_message := '<span style="color:green">Updates successfully applied</span>';
END IF;
END;

That updateable process should just show how particular you can use these error/success messages in your application. With some javascript you could also change the object which is wrong. For example with a red object background.

Here are some posts to that topic in the Oracle forum:
http://forums.oracle.com/forums/thread.jspa?messageID=2717151
http://forums.oracle.com/forums/thread.jspa?messageID=2792051

APEX examples

Multiple APEX regions in the same size (width)

Von Tobias Arnhold → 1.12.2009

First of all (I know its a bit late) I wish everybody a healthy and successfully year 2009.
Now let's talk about APEX...

Have you every came about the problem that you have several reports on a page and they all show up in a different size. Most annoying is the different sizes of the width.

What you can do to bring them all into the same width is to edit the template region or to copy it to a new one and change some html attributes. Unfortunately every theme has there own unique source code. So you need to find out the right settings yourself. In my example I used the theme Centered - 7 and changed the following definition line under Home > Application Builder >Application #ID# > Shared Components > Edit Region Template > Defintion:


<td align="center" colspan="2">#BODY#</td>

To:


<td align="center" colspan="2" #REGION_ATTRIBUTES#>#BODY#</td>

Next is to edit your regions. Add style="width:600px" at the region attributes.

Here an example: http://apex.oracle.com/pls/otn/f?p=25472:29

Just a tip:
To find out where you need to add the attribute #REGION_ATTRIBUTES# use Firefox with Firebug.