31.1.07

APEX System Preferences

In a previous post I struggled with the differences between system preferences and database parameters. In this post I focus on system preferences again.

Oracle provides the package wwv_flow_platform to set and get the system preferences. With this query you get all the system preferences as well:

SELECT name, value, pref_desc FROM flows_020100.wwv_flow_platform_prefs
/

NAME                              VALUE         PREF_DESC
-------------------------------- ------------ --------------------------------------
SMTP_HOST_ADDRESS smtp.isp.com The address of the SMTP server
used to relay mail.
SMTP_HOST_PORT 25 The port that the SMTP server
listens for requests.
MAX_SCRIPT_SIZE 500000 The maximum allowable size for
a script text.
SQL_SCRIPT_MAX_OUTPUT_SIZE 200000 The maximum allowable size for
an individual script result.
WORKSPACE_MAX_OUTPUT_SIZE 2000000 The maximum space allocated for
script results.
ENABLE_TRANSACTIONAL_SQL Y The flag which determines if
Transactional SQL Commands are
enabled on this instance.
PLSQL_EDITING Y The flag used in SQL Workshop
Object Browser to enable user
to edit and compile PL/SQL.
WORKSPACE_PROVISION_DEMO_OBJECTS Y The flag used in Instance
Adminstration which determines
if demonstration applications
and database objects are created in
new workspaces.
DISABLE_ADMIN_LOGIN N The flag which determines if
Adminstration services are disabled.
DISABLE_WORKSPACE_LOGIN N The flag which determines if
workspace login is disabled.
ALLOW_DB_MONITOR Y The flag which determines if database
monitoring is enabled.
SERVICE_REQUEST_FLOW MANUAL Determines default provisioning
mode.

30.1.07

Unbreakable Materialized Views

Oracle8i: Invalidating a Materialized View

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DDL operation, such as a DROP or ALTER, on any dependency in the materialized view will cause it to become invalid.

Oracle9i: Invalidating Materialized Views

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as a INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

Oracle10g: Invalidating Materialized Views

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

RTFM

In 9i and 10g, a materialized view becomes automatically invalid when performing DML or DDL. Don't worry:

A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated.

Suppose you have a materialized view on EMP and your application aborts immediate after logon when invalid objects are found. The "early bird" user modifies the EMP table and all others users cannot start the application anymore. I hear the sound of a ringing phone...

29.1.07

Oracle Drive and Oracle Express

I read this post about Oracle Drive. Oracle Drive is a WebDav (Web-based Distributed Authoring and Versioning) client for use in an Oracle Portal environment. I installed the client and used my Oracle Express (XE) database as server. I mapped the N: drive to 127.0.0.1:8080.

Now I can use the following features:

  • I can work off line, I can change files while I am not connected with the database i.e. database is down or when I work at a remote location. Synchronization takes place when you connect to the database.

  • I can schedule a backup of Windows files, this backup copies the files (without compression) to the WebDav folder, the files are stored within the database.

  • I can put a lock on a file within a WebDav folder, other users can not change this file.

Oracle Drive works with XE and has some "nice to have" features at the cost of a little memory usage. Personally I like the built in Windows Explorer as described in this post a lot more.

14.1.07

Mail from Application Express again

I used a beta version of Oracle XE. The DMBS_EPG gateway did not work at all. So I decided to reinstall Oracle XE (production). After reading a post on the Application Express (APEX) forum I recreated my sendmail application from scratch. Everything went fine, except the litte detail of sending email did not work anymore. My first solution was to adjust the smtp hostname in the package body but still no mail.

Have look at the comments in the package specification of htmldb_mail.

--
--
-- Parameters p_smtp_hostname and p_smtp_portno remain for backward
-- compatibility. But they are ignored. The SMTP host name and
-- parameter are exclusively derived from system preferences
-- when sending mail.
--
procedure push_queue( p_smtp_hostname in varchar2 default null,
p_smtp_portno in varchar2 default null );

The SMTP hostname and portnumber are ignored while calling push_queue. The real settings are derived from system preferences.

I have never heard of system preferences before, people always talk about database parameters. And because htmldb_mail depends on utl_smtp, I thought that the startup parameter smtp_out_server did not have the correct value. In fact, the parameter value was null and after setting it to the correct value, the mail was still not sent.

It took a while surfing to find the system preferences. You can check these preferences as follows.
SELECT wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS') FROM dual
/
SELECT wwv_flow_platform.get_preference('SMTP_HOST_PORT')FROM dual
/
In APEX you can set these preferences in the main menu of application builder and choose the task email configuration.
The following screen appears:


You can set the smtp hostname and portnumber in the email preferences. Or you can manage the mail queue (the mail queue is like the outbox in your mail client) or you can manage the email log. When an item is sent, the header data is moved from the queue into the log.

Finally, the sendmail application works fine.

6.1.07

Mail from Application Express



On the OTN forum Application Express (APEX) somebody asked: how to send mail from a report. Assume you have a report with all employees, you can select one or more rows , push a button and your mail is sent. It takes just a few steps!

The first step is to create a page with a report region, a submit button, an after submit process and an after processing branch. In the after submit process we call a database package p_send_mail. Make sure the package specification exists before creating the process otherwise you will not be able to save your process.

The package specification is:

-----------------------------------------------------------
CREATE OR REPLACE PACKAGE p_send_mail IS
PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2);
END;
/
-----------------------------------------------------------
We will come back on this package later.

Building the report

We build a simply report based on the query.
SELECT ename                         ename_ro
, htmldb_item.checkbox(1,ename) ename_cb
FROM emp

The first column ename_ro displays the ename as a normal report field. The second column ename_cb displays a checkbox. The second column uses the function htmldb_item.checkbox. The first argument 1 is the form element name. The second argument is the value returned by the form element when checked. When the page is submitted APEX composes an array of employee names. The name of this array is htmldb_application.g_f01 (g_f01 corresponds with the form element name 1) and it only contains the employee names of the checked rows.

Create a submit button and an 'On submit - after Computations and Validations' process. Set the value of the when button pressed attribute to the name of the submit button. Don't forget to set the messages as well. Set the process source to:
BEGIN
p_send_mail.submit(p_cb_arr =>htmldb_application.g_f01);
END;

The procedure submit has an array of employee names as input. In the next paragraphs we will explain the package body.

Implementing the package body

The code of the procedure submit:

PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2)
IS
BEGIN
IF p_cb_arr.COUNT > 0 THEN
FOR i IN p_cb_arr.FIRST .. p_cb_arr.LAST
LOOP
send_mail(p_to => con_email_to,
p_body => 'Employee ' || p_cb_arr(i) || ' selected.',
p_subj => 'Mail from Application Express');
END LOOP;
ELSE
-- In the case there are no checkboxes checked inform the user
RAISE e_all_cb_unchecked;
END IF;
END submit;

In the procedure submit we check first If there are 0 elements in the array this means that none of the check boxes is checked. In this case raise an error to inform the user that there is no mail sent. Otherwise call for every employee the procedure send_mail.

The code for the procedure send_mail is:

PROCEDURE send_mail(p_to IN VARCHAR2, -- receiver
p_body IN VARCHAR2, -- email body (text)
p_subj IN VARCHAR2) -- subject
IS
BEGIN
-- Place your email in the outbox.
htmldb_mail.send(p_to => p_to,
p_from => con_email_from,
p_body => p_body,
p_subj => p_subj);
-- Send you email(s) now
htmldb_mail.push_queue(p_smtp_hostname => con_smtp_hostname,
p_smtp_portno => con_smtp_portno);
END send_mail;

APEX provides the HTMLDB_MAIL package. We use two procedures send and push_queue. The procedure send adds an email message to the queue. And procedure push_queue sends all messages in the queue immediately.

Besides the procedures the package body contains some constants. You will have to set these constants:
  • email_to is the receiver of the emails i.e scott@acme.com.

  • smtp_hostname is the SMTP hostname

  • smtp_portno is the portnumber of the SMTP host, default 25

The complete code of the package body is below and after compiling the package body it time to email!

Update: after reinstalling my Oracle XE database I discovered that Oracle changed the behaviour a little bit. Please read Mail from application express again as well.

-----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY p_send_mail IS
-- Change these settings
-- receiver
con_email_to constant VARCHAR2(2000) := 'aaa@bbb.cc';
-- sender
con_email_from constant VARCHAR2(2000) := 'XE';

-- You find the SMTP values in the account settings of your email client.
con_smtp_hostname constant VARCHAR2(2000) := 'smtp.xxx.yy';
con_smtp_portno constant VARCHAR2(4) := '25';

e_all_cb_unchecked EXCEPTION;

-- A simple procedure to create and send an email message.
PROCEDURE send_mail(p_to IN VARCHAR2, -- receiver
p_body IN VARCHAR2, -- email body (text)
p_subj IN VARCHAR2) -- subject
IS
BEGIN
-- Place your email in the outbox.
htmldb_mail.send(p_to => p_to,
p_from => con_email_from,
p_body => p_body,
p_subj => p_subj);
-- Send you email(s) now
htmldb_mail.push_queue(p_smtp_hostname => con_smtp_hostname,
p_smtp_portno => con_smtp_portno);
END send_mail;

-- This procedure is called from the page process.
-- It takes as argument an array with employee names
-- For each checked employee an email is send.
PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2)
IS
BEGIN
IF p_cb_arr.COUNT > 0 THEN
FOR i IN p_cb_arr.FIRST .. p_cb_arr.LAST
LOOP
send_mail(p_to => con_email_to,
p_body => 'Employee ' || p_cb_arr(i) || ' selected.',
p_subj => 'Mail from Application Express');
END LOOP;
ELSE
-- In the case there are no checkboxes checked inform the user
RAISE e_all_cb_unchecked;
END IF;
END submit;
END p_send_mail;
/
-----------------------------------------------------------

2.1.07

ApexSQL

APEX is the short name for Application Express, the successor of html-db. I've searched blogs with the keyword Apex. Type apex in the Blogger NavBar (the small blue bar at the top of this page) and click [SEARCH ALL BLOGS]. Do not forget to read the rest of this blog!

One of the results is Apex SQL Log 2005.03. Apex SQL Log is a powerful SQL database auditing and recovery tool that analyzes SQL Server's own Transaction Log to display information on data changes, including user (nt user), computer and application where the change originated. On the same page you find the slogan ApexSQL SQL Developer Essentials. "SQL Developer", I've heard that name before...