APEX Logs: Storing Mail Log Data

A while ago (almost a year ago) I wrote about how to permanently store APEX logs into your own tables (http://apex-smb.blogspot.com/2009/05/apex-logs-storing-log-data.html). I covered the APEX_WORKSPACE_ACCESS_LOG and APEX_WORKSPACE_ACTIVITY_LOG logs. I forgot to include how to permanently store the APEX_MAIL_LOG. You'll only need to do this if you use APEX_MAIL to send email.

1- Create the APEX Mail Log table

-- Mail Log
CREATE TABLE tapex_mail_log
AS  SELECT * FROM apex_mail_log;

2- Update the APEX Mail Log table

Note: You may want to store this in a procedure and run as a nightly scheduled job so you don't forget to update the tables

BEGIN
  -- apex_mail_log is linked to the workspace.
  -- Need to access all the mail sent for each workspace that is linked to this schema

  FOR x IN (SELECT workspace_id FROM apex_workspaces) LOOP
    -- Set the workspace ID
    wwv_flow_api.set_security_group_id (x.workspace_id);

    INSERT INTO tapex_mail_log (mail_to,
                                mail_from,
                                mail_replyto,
                                mail_subj,
                                mail_cc,
                                mail_bcc,
                                mail_send_error,
                                last_updated_on)
      SELECT aml.mail_to,
             aml.mail_from,
             aml.mail_replyto,
             aml.mail_subj,
             aml.mail_cc,
             aml.mail_bcc,
             aml.mail_send_error,
             aml.last_updated_on
        FROM tapex_mail_log x, apex_mail_log aml
       WHERE     NVL (aml.mail_to, -1) = NVL (x.mail_to(+), -1)
             AND NVL (aml.mail_from, -1) = NVL (x.mail_from(+), -1)
             AND NVL (aml.mail_replyto, -1) = NVL (x.mail_replyto(+), -1)
             AND NVL (aml.mail_subj, -1) = NVL (x.mail_subj(+), -1)
             AND NVL (aml.mail_cc, -1) = NVL (x.mail_cc(+), -1)
             AND NVL (aml.mail_bcc, -1) = NVL (x.mail_bcc(+), -1)
             AND NVL (aml.mail_send_error, -1) = NVL (x.mail_send_error(+), -1)
             AND aml.last_updated_on = x.last_updated_on(+)
             AND x.ROWID IS NULL;
  END LOOP;
END;

Restoring jQuery UI Theme from ThemeRoller

If you ever use jQuery's Theme Roller, you may find it frustrating to alter a custom theme. For example, if you created a theme, downloaded it, then decided to change your colors you may not know how to restore your changes.

Believe it or not there's a pretty easy way to restore a Theme Roller theme. Before you download a theme copy, or bookmark, the URL at the top of the browser. All the changes you have made are stored in the URL! If you forget to copy the URL before downloading, the URL is stored in ..\css\custom-theme\jquery-ui-1.8.custom.css (where 1.8 is the version number).

Here's an example of a theme modification where I changed the header background to red: Red Header.

APEX 4.0 will be using jQuery and some jQuery UI components, so I hope this helps when customizing the look and feel of your applications.

How To Reference Package Variables Outside of PL/SQL

When developing with PL/SQL you may store public variables in the package specification. This has many uses, none of which I will get into for this post. The only catch in Oracle is that you can not easily reference these values in SQL statements outside of PL/SQL. The following example demonstrates this:

- Create Package Spec with Variable

CREATE OR REPLACE PACKAGE pkg_var
AS
  c_my_var   CONSTANT VARCHAR2 (5) := 'hello';
END pkg_var;

- Reference the variable in a SQL statement in SQL*Plus

SQL> SELECT pkg_var.c_my_var x
  2    FROM DUAL;
SELECT pkg_var.c_my_var x
       *
ERROR at line 1:
ORA-06553: PLS-221: 'C_MY_VAR' is not a procedure or is undefined

This results in an Oracle error.


- Try the same code, but in an block of PL/SQL

SQL> DECLARE
  2    v_x   VARCHAR2 (5);
  3  BEGIN
  4    SELECT pkg_var.c_my_var x
  5      INTO v_x
  6      FROM DUAL;
  7
  8    DBMS_OUTPUT.put_line (v_x);
  9  END;
 10  /
hello

PL/SQL procedure successfully completed.

As you can see this worked.


So how can we refernce package variables in a non-PL/SQL setting? I created the following function to do so. It will handle values that are of type VARCHAR2. I've also removed any spaces from the parameter (pkg_name.var_name) to ensure that no SQL injection will occur.

-- **
-- * Returns Package Variable value
-- * Note: for demo purposes I broke this function into various steps
-- * 
-- * @param p_pkg_var_name fully qualified variable reference. Ex: pkg_x.var_y
-- * @return Varchar2 value
-- * @author Martin Giffy D'Souza: http://apex-smb.blogspot.com
-- **
CREATE OR REPLACE FUNCTION f_get_pkg_val_vc2 (p_pkg_var_name in varchar2)
  RETURN VARCHAR2
AS
  v_string          VARCHAR2 (4000);
  -- Full Variable Name (i.e. pkg.var)
  v_var_full_name   VARCHAR2 (61);                             -- Max of 61 chars since 30 + . + 30
BEGIN
  v_var_full_name := p_pkg_var_name;
  -- Remove any spaces to avoid SQL injections
  v_var_full_name := REGEXP_REPLACE (v_var_full_name, '[[:space:]]', '');

  EXECUTE IMMEDIATE 'begin :v_string := ' || v_var_full_name || '; end;'
              USING OUT v_string;

  RETURN v_string;
END f_get_pkg_val_vc2;

Now when you run in SQL*Plus you get the following:

SQL> SELECT f_get_pkg_val_vc2 ('pkg_var.c_my_var') x
  2    FROM DUAL;

X
-----------------------------------------------------

hello

How to Avoid Bot Spammers in APEX

If you've ever developed a public web application with a form on it you may notice that you may get bot spammers trying to enter information into your application.

There's a simple trick that a friend of mine, Sean Rabey, at Pump Interactive showed me which will help you reject submissions from bots.

Sean suggested that I use an input field and then hide it with CSS. Humans entering data into the form won't see the field and therefore won't enter anything into it. Bots on the other hand may try to fill out this field and can't detect whether or not it's visible in the browser. If your "special" field has data in it you can reject the submission since you know it's not a human entering the data.

Here's an example of how you can do this in APEX. You can view an example here: http://apex.oracle.com/pls/apex/f?p=20195:2900

- Create a "Dummy" item
Set "HTML Form Element Attributes" to class="hideMe"



- Configure "hideMe" style
Add the following in your application somewhere (or to a CSS file)

- Add validation to catch bot entries
Type: Exists
Validation Expression 1:

SELECT 1
  FROM DUAL
 WHERE :p2900_dummy IS NULL

Presenting at ODTUG Kaleidoscope 2010

I'll be presenting at ODTUG Kaleidoscope in Washington DC this year. If you haven't already signed up I suggest you do so before March 24th as they have an early bird special.

I've been to the past 2 Kaleidoscope conferences where I've learned a great deal about APEX and met some great people. It doesn't matter if you're a seasoned APEX developer or new to APEX, there's always something to learn and a lot of excellent presentations for all levels.

This year I'll be giving 2 presentations on APEX (http://www.odtugkaleidoscope.com/apex.html#dsouza)

Enhancing APEX Security: APEX has some excellent built-in configurable security features. This presentation will go over some extra functionality you can add to your APEX applications that will make it more secure both in the front-end and back-end. Primary focus will be on "enhanced session state protection" and "poor man's" VPD for Oracle XE.

How to be Creative: Using the APEX Dictionary to Create Solutions: The APEX dictionary is a very useful tool which can help enhance existing features in APEX. This presentation will cover how you can use the dictionary to resolve your problems. It will include some real-life issues and how the APEX dictionary was used to resolve them

Of course these presentations may be slightly altered based on the new features and functionality of APEX 4.0.

I look forward to seeing everyone in Washington.