October » 2009 » OraExplorer

Oct 22 2009

Oracle Application Express (APEX) Certification Exam

Category: APEX — ittichai @ 7:24 am

Oracle is working toward having the first certification exam for Oracle APEX. This “Oracle Application Express Developer Certified Expert” pilot program is intended for intermediate and advanced APEX developers. You will have to indicate the relevant APEX trainings you’ve completed, years of APEX experiences and, of course, the agreement to provide feedback in order to participate. You can see details about this from Joel Kallman’s blog.

I think this is a good thing for APEX community because it shows Oracle’s commitment to this tool.

BTW, I’m very curious about how the tests will be conducted. Personally I think there should be more of actual development hands-on works rather than doing multiple choices.

Tags: APEX, Certification, Exam, Pilot

Comments (2)

Oct 19 2009

11gR2 New Feature – File Watchers

Category: 11g,Database — ittichai @ 9:19 pm

The File Watcher is a scheduler object that starts a job whenever files whose attributes met the defined criteria arrived on a system. These criteria include the name, location, and other properties of a file. When the file watcher detects the arrival of the designated file, it raises a file-arrival event. The event message, which has all information on the newly-arrived file, can then be used to process the file.

This new feature simplifies the configurations of the most common triggering event in the data load/batch processing which is to detect the arrival of files.

File Watcher configuration

Setup a new database account to manage the file watcher.

SQL> create user watcher_user identified by watcher_pwd
quota unlimited on users;
User created

SQL> grant connect to watcher_user;
Grant succeeded.

SQL> grant EXECUTE on SYS.SCHEDULER_FILEWATCHER_RESULT to watcher_user;
Grant succeeded.

Other grants needed to complete the tests:

grant create table, create procedure, create job to watcher_user;
grant execute on dbms_lock to watcher_user;
grant execute on dbms_system to watcher_user;
grant manage scheduler to watcher_user;

SQL> create or replace directory STAGING_DIR as '/home/oracle/staging';
Directory created.

SQL> grant read, write on directory staging_dir to watcher_user;
Grant succeeded.

Now as a new watcher_user, we will configure the File Watcher.

1. Create a credential using the OS privilege for file access.

begin
  dbms_scheduler.create_credential(
  credential_name => 'watch_credential',
  username => 'oracle',
  password => 'oracle');
end;
/

2. Create a table to store data processed from file.

create table t_staging_files(
  upload_timestamp  timestamp,
  file_name         varchar2(100),
  file_size         number,
  contents          clob
);

3. The procedure will process file data and put into a database table.

create or replace procedure process_files
(payload IN sys.scheduler_filewatcher_result)
is
  l_clob clob;
  l_bfile bfile;

  dest_offset  INTEGER := 1;
  src_offset   INTEGER := 1;
  src_csid     NUMBER  := NLS_CHARSET_ID ('AL32UTF8');
  lang_context INTEGER := dbms_lob.default_lang_ctx;
  warning      INTEGER;
begin
  insert into t_staging_files (
    upload_timestamp , file_name, file_size, contents)
  values(
    payload.file_timestamp,
    payload.directory_path || '/' || payload.actual_file_name,
    payload.file_size,
    empty_clob()
  ) returning contents into l_clob;

  l_bfile := bfilename('STAGING_DIR', payload.actual_file_name);
  dbms_lob.fileopen(l_bfile);
  dbms_lob.loadclobfromfile (
    l_clob,
    l_bfile,
    dbms_lob.getlength(l_bfile),
    dest_offset,
    src_offset,
    src_csid,
    lang_context,
    warning
  );
  dbms_lob.fileclose(l_bfile);
end;
/

4. Create a Program object with a Metadata argument.

begin
  dbms_scheduler.create_program (
    program_name        => 'file_watcher',
    program_type        => 'stored_procedure',
    program_action      => 'process_files',
    number_of_arguments => 1,
    enabled             => false);

  dbms_scheduler.define_metadata_argument (
    program_name        => 'file_watcher',
    metadata_attribute  => 'event_message',
    argument_position   => 1);

  dbms_scheduler.enable('file_watcher');

end;
/
PL/SQL procedure successfully completed.

5. Create a File Watcher

begin
  dbms_scheduler.create_file_watcher(
    file_watcher_name => 'my_file_watcher',
    directory_path    => '/home/oracle/staging',
    file_name         => '*',
    credential_name   => 'watch_credential',
    destination       => null,
    enabled           => false);
end;
/
PL/SQL procedure successfully completed.

6. Create an Event-Based Job that references the File Watcher.

begin
  dbms_scheduler.create_job(
    job_name        => 'staging_file_job',
    program_name    => 'file_watcher',
    event_condition => 'tab.user_data.file_size > 10',
    queue_spec      => 'my_file_watcher',
    auto_drop       => false,
    enabled         => false);

    dbms_scheduler.set_attribute('staging_file_job','parallel_instances',true);
end;
/

7. Enable all objects

begin
  dbms_scheduler.enable('my_file_watcher,staging_file_job');
end;
/

8. Perform validation

$ echo "Hello World Hello World" > /home/oracle/staging/test_file.txt

After waiting for about 10-15 minutes,

col UPLOAD_TIMESTAMP format a20
col FILE_NAME format a20
col CONTENTS format a20

select * from t_staging_files;

UPLOAD_TIMESTAMP     FILE_NAME             FILE_SIZE CONTENTS
-------------------- -------------------- ---------- -----------------------

13-OCT-09 01.42.04.0 /home/oracle/staging         23 Hello World Hello World
00000 PM             /test_file.txt

By default, the file watcher checks for the arrival of files every 10 minutes. You can adjust this interval as follows:

as SYS user

begin
 DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE','REPEAT_INTERVAL','FREQ=MINUTELY;INTERVAL=2');
end;
/

You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.

col FILE_WATCHER_NAME format a20
col DIRECTORY_PATH format a20
col FILE_NAME format a5
col CREDENTIAL_NAME format a17

SELECT file_watcher_name, directory_path, file_name, credential_name
FROM dba_scheduler_file_watchers;

FILE_WATCHER_NAME    DIRECTORY_PATH       FILE_ CREDENTIAL_NAME
-------------------- -------------------- ----- -----------------
MY_FILE_WATCHER      /home/oracle/staging *     WATCH_CREDENTIAL

References:

Oracle 11gR2 document: Starting a Job When a File Arrives on a System

Starting a Job When a File Arrives on a System

Tags: 11g, 11gR2, Batch, File, New Feature, Watcher

Comments (6)

Oct 18 2009

11gR2 New Feature – DBFS Database File System

Category: 11g,Database — ittichai @ 8:15 pm

The DBFS (Database File System), one of the new 11gR2 features, takes advantage of the SecureFiles feature (which is new in 11gR1). The SecureFiles provide powerful file storage features (including de-duplication, compression, etc.) which removes performance barrier to storing files in the database. This stands in contrast to LOB (now called the BasicFiles). Not only does the DBFS provide the standard file system interface (path names, directories and links) to store and access files in the database, its benefits also include security and performance from using SecureFiles.

DBFS configuration

1. Follow the DBFS prerequisites and installation instructions here.

2. Install the FUSE package.

Since oracle user will do sudo, add it into /etc/sudoers.

$ tar -xzvf fuse-2.7.3.tar.gz
$ cd fuse-2.7.3
$ ./configure --prefix=/usr/src/kernels/`uname -r`-`uname -p`
$ make
$ sudo su
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules

3. Create a database user and a tablespace to store data.

SQL> create user dbfs_admin identified by dbfs_admin;

SQL> grant create session, resource, create view to dbfs_admin;

SQL> grant dbfs_role to dbfs_admin;

SQL> create tablespace tbs_dbfs datafile '/u1/oradata/DB11LNX/dbfs.dbs' size 100m;

The tablespace must be the Automatic Segment Space Management (ASSM) in order to use the SecureFiles. This is the default setting in 11g.

3. Create a file system using dbfs_create_filesystem.sql (located at $ORACLE_HOME/rdbms/admin).

The dbfs_create_filesystem.sql creates a partitioned file system which stores data in the multiple physical segments. The files will be distributed randomly in these partitions. This way it gives the best performance and scalability.

$ cd $ORACLE_HOME/rdbms/admin

SQL> conn dbfs_admin/dbfs_admin

SQL> @dbfs_create_filesystem.sql tbs_dbfs staging_area

Note that the last argument (e.g., staging_area) will be visible as the name of the file system.

When creating a new file system, a new partitioned table will be created having its name from the file system’s name with T_ prefix.

SQL> select table_name, partition_name from user_tab_partitions
where table_name like '%STAGING_AREA';

TABLE_NAME                    PARTITION_NAME
----------------------------- ------------------------------
T_STAGING_AREA                SYS_P141
T_STAGING_AREA                SYS_P142
T_STAGING_AREA                SYS_P143
T_STAGING_AREA                SYS_P144
T_STAGING_AREA                SYS_P145
T_STAGING_AREA                SYS_P146
T_STAGING_AREA                SYS_P147
T_STAGING_AREA                SYS_P148
T_STAGING_AREA                SYS_P149
T_STAGING_AREA                SYS_P150
T_STAGING_AREA                SYS_P151
T_STAGING_AREA                SYS_P152
T_STAGING_AREA                SYS_P153
T_STAGING_AREA                SYS_P154
T_STAGING_AREA                SYS_P155
T_STAGING_AREA                SYS_P156

16 rows selected.

4. Verify by copying files into the exposed file system.

SQL> conn dbfs_admin/dbfs_admin
SQL> !cat ~/dbfs_show_content.sql
col pathname format a40
col pathtype format a10
col contents format a20

select pathname, pathtype,
utl_raw.cast_to_varchar2(filedata) as contents
from dbfs_content
order by std_creation_time;

This shows the default directories currently in database.

SQL> @~/dbfs_show_content.sql

PATHNAME                       PATHTYPE   CONTENTS
------------------------------ ---------- ------------------------------
/staging_area                  directory
/staging_area/.sfs             directory
/staging_area/.sfs/RECYCLE     directory
/staging_area/.sfs/attributes  directory
/staging_area/.sfs/content     directory
/staging_area/.sfs/snapshots   directory
/staging_area/.sfs/tools       directory

7 rows selected.

Let’s create a directory named test_dir. Please note that base directory name staing_area is from the file system created previously. The dbfs_client can be executed from any systems which meet the prerequisite requirements mentioned in the step 1.

{client}$ dbfs_client dbfs_admin@DB11LNX --command mkdir dbfs:/staging_area/test_dir
Password: dbfs_admin

A new directory test_dir is now visible as a new record.

SQL> @~/dbfs_show_content.sql

PATHNAME                       PATHTYPE   CONTENTS
------------------------------ ---------- ------------------------------
/staging_area                  directory
/staging_area/.sfs             directory
/staging_area/.sfs/RECYCLE     directory
/staging_area/.sfs/attributes  directory
/staging_area/.sfs/content     directory
/staging_area/.sfs/snapshots   directory
/staging_area/.sfs/tools       directory
/staging_area/test_dir         directory

8 rows selected.

Copy a file into it.

{client}$ echo "hello world" > /tmp/dbfs_file
{client}$ dbfs_client dbfs_admin@DB11LNX --command cp /tmp/dbfs_file dbfs:/staging_area/test_dir
Password: dbfs_admin
/tmp/dbfs_file -> dbfs:/staging_area/test_dir/dbfs_file

SQL> @~/dbfs_show_content.sql
PATHNAME                                 PATHTYPE   CONTENTS
---------------------------------------- ---------- ------------------------------
/staging_area                            directory
/staging_area/.sfs                       directory
/staging_area/.sfs/attributes            directory
/staging_area/.sfs/tools                 directory
/staging_area/.sfs/snapshots             directory
/staging_area/.sfs/RECYCLE               directory
/staging_area/.sfs/content               directory
/staging_area/test_dir                   directory
/staging_area/test_dir/dbfs_file         file       hello world

9 rows selected.

Optionally you can also mount this file system on the client, so the file operations can be done without invoking dbfs_client every time.

Create a mount point. (*)

{client}# mkdir /mnt/dbfs
{client}# chown oracle:dba /mnt/dbfs
{client}# chmod 755 /mnt/dbfs

$ dbfs_client dbfs_admin@DB11LNX /mnt/dbfs
password: dbfs_admin
:

Somehow on my test system, the prompt never returned even though the file system is mounted successfully. So in order to test it, I left this window open, and execute the remaining of commands in a new window.

The prompt will never return using above syntax. However, the following example will read the password from a file, mount a file system, and then free the terminal. (Thanks Simon for pointing this out.)

$ nohup dbfs_client dfs_admin@DB11LNX /mnt/dbfs  < passwordfile.f &

Note that now I can perform all standard Unix file/directory syntaxes to this mount point.

{client}$ echo "hello world 2" > /tmp/dbfs_file2
{client}$ cp /tmp/dbfs_file2 /mnt/dbfs/staging_area/test_dir

SQL> @~/dbfs_show_content.sql
PATHNAME                                 PATHTYPE   CONTENTS
---------------------------------------- ---------- ------------------------------
/staging_area                            directory
/staging_area/.sfs                       directory
/staging_area/.sfs/attributes            directory
/staging_area/.sfs/tools                 directory
/staging_area/.sfs/snapshots             directory
/staging_area/.sfs/RECYCLE               directory
/staging_area/.sfs/content               directory
/staging_area/test_dir                   directory
/staging_area/test_dir/dbfs_file         file       hello world
/staging_area/test_dir/dbfs_file2        file       hello world2

10 rows selected.

There are a lot of administrative options you can do from here including using Oracle wallet so no password will be prompted when mounting a DBFS store. You can also mount DBFS through the fstab. See instructions in the Oracle 11gR2′s DBFS File System Client.

One of the claimed benefits of using DBFS is I/O throughput performance in a range of 5-7 GB/sec. I plan to perform performance tests and report the results in the next post. Stay tune!.

(*) Initially, I received this error when trying to run dbfs_client.

dbfs_client: error while loading shared libraries: libfuse.so.2: cannot open shared object file: No such file or directory

By creating a softlink to the /lib folder, it’s solved the problem.

# ln -s /usr/src/kernels/2.6.18-92.el5-i686/lib/libfuse.so.2 /lib/libfuse.so.2

Tags: 11gR2, Database File System, DBFS, New Feature

Comments (4)

Oct 17 2009

OOW09 Summary and Rookie Mistake

Category: Events,OOW — ittichai @ 10:12 am

It is good to be back home. The OOW09 trip was exceedingly beyond expectation especially since this was my first OOW. In general, the technical sessions were very good. Most of the time, however, their contents were very basic, I suppose, to accommodate a large audience of diverse skill levels and interests. Besides networking with colleagues and vendors, I would say that the informal settings of the Unconference, and interactions with product managers and developers of DEMOgrounds are what I enjoyed the most about the conference. I made a rookie mistake of registering too many sessions with only short breaks between them. Because of that, at the end of each day, I felt very exhausted. I will definitely plan better for the next one.

Here are a couple of highlights from the conference:

One of the pictures I took at the event was used in the Oracle OpenWorld Blog’s Wrapping Things Up.

Attendees walking to the keynote

SF is decidedly an iPhone town.

In almost every conference I’ve been to, there are always groups of people dressing weird and shouting out something no one really understands or cares about. (You have to ask them in person if you want to know what they really want.) This one is no exception. I still have no idea what this group was doing on the street in front of the Moscone West.

????

I’m a pen-addict. I’ve not realized this until I found, as I was packing and getting ready to return home, almost 40 pens collected during the visits to vendor exhibits throughout the week. It is my habit to grab pens when stopping by and talking to the vendors. Please help.

A long line attracts people even if they have no clue what it is for. I’ve encountered this a couple of times when people came behind me as I was standing in line. After 5 minutes of waiting, they would ask me, “What are we waiting for?”

Tags: OOW, OOW09, OOW2009, Oracle Open World

Comments (2)

Oct 15 2009

OOW09 Day 4

Category: Uncategorized — ittichai @ 1:21 am

Today all is about interesting new features of Oracle 11gR2 including Grid Plug and Play (GPnP), Grid Naming Service (GNS), Simple Client Access Name (SCAN), DBFS (Database File System) and so on. I have to admit that all of these are very overwhelming. Some of new features will change the way we’ve done things in the past. It seems like the more you hear about them, the more questions you’re having.

Arup had a great tip about the availability of Oracle product managers, architect and developers at th DEMOgrounds. I had a chance to stop by at the database booths to ask about SCAN and DBFS. The developers there are very patiently helpful talking time to explain things to me clearly. One of them even gave me his business card so I can contact him directly. I’ve spent almost an hour there having lot of questions answered (but still lot of things I didn’t quite grasp it yet). I’ll soon blog about what I’ve learned after trying them out.

Oracle DEMOgrounds

You can view my OOW’s pictures on my Flickr page.

Tags: OOW, OOW09, OOW2009, Oracle Open World

Comments (0)

Oct 13 2009

OOW09 Day 3

Category: Events,OOW — ittichai @ 10:16 pm

Today I’ve decided to skip the morning’s keynotes and went for the Unconference. I’ve never attended an Unconference before, but have heard many good things about it from fellow bloggers. And yes they’re absolutely right!. Today’s morning session is the “What’s New in Eleven … Dot Two (that Oracle won’t be talking about)” conducted by Oracle ACE Director Daniel Morgan. (You can’t not go just by reading that session’s subject.) ;-) The informal atmosphere and a smaller number of participants encourage more interaction which is very great. Daniel is a great resource. His web site – Morgan’s Library has wealth of information about Oracle especially new features in 11g both R1 and R2 (see New in 11gR1 & 11gR2 tabs from his site). I’ll definitely go back for more of Unconferences.

On a lighter note, I (and a lot of people) came to one of the evening sessions 5 minutes late. But somehow the way seats were arranged, it made it very difficult to get into the empty chairs so most people including myself ended up standing in the back of the room. I would assume that in a conference room setting, they would arrange to have at least the access in the middle and on both sides. This way people can get in/out more easily. May be the facility folks thought that if we added more chairs on the sides (leaving no room on the wall side), we would be able to accommodate more people. Brilliant! Ha? This good intention backfired because now even though there are more chairs in the room but less access. Um… I don’t know where I’m going from here. But somehow I feel there is a lesson learned here that good intentions are not enough to accomplish a task, a good plan is needed too.

On a lighter note, I (and a lot of people) came to one of the evening sessions 5 minutes late. But somehow the way seats were arranged, it made it very difficult to get into the empty chairs so most people including myself ended up standing in the back of the room. I would assume that in a conference room setting, they would arrange to have at least the access in the middle and on both sides. This way people can get in/out more easily. May be the facility folks thought that if we added more chairs on the sides (leaving no room on the wall side), we would be able to accommodate more people. Brilliant! Ha? This good intention backfired because even though there are more chairs in the room, now there are more empty seats (less utilization) because of less access. Um… I don’t know where I’m going from here. But somehow I feel there is a lesson learned here that good intentions are not enough to accomplish a task, a good design is needed too.

Bad Seat Arrangement?

Tags: OOW, OOW09, OOW2009, Oracle Open World

Comments (0)

Oct 13 2009

OOW09 Day 2

Category: Events,OOW — ittichai @ 12:04 am

Today all my sessions are pretty much centered around Oracle ASM. Even though all sessions started with basic information about ASM (what is, why, and so on), I found the Q&A time at the end more interesting. One of the sessions – “Extending Oracle ASM in Release 11.2 to Manage All Data” has the panel of the ASM development team for Q & A. It is awesome to see these people who are responsible for this cool product face-to-face.

Oracle ASM Development Team

A common question I’ve heard over and over is that how many diskgroups Oracle recommends in a deployment. The panel said that regardless of the number of databases, still only two diskgroups are recommended as the best practice – one diskgroup for work area (e.g., data files), other for recovery (e.g., flash recovery). The exception of more-than-two diskgroups is if organization has ILM strategy – putting disks of different tiers into different diskgroups based on requirement and strategy.

A quick note from the ASM development team while they’re talking about the ACFS snapshot is that they’re working on more of data services around ACFS such as storage replication. We should hear about it soon.

Tags: 11gR2, ASM, OOW, OOW09, OOW2009

Comments (0)

Oct 12 2009

OOW09 Day 1

Category: Events,OOW — ittichai @ 12:22 am

My first day at OOW 2009 was filled with excitement intellectually and physically. Let’s talk about the physical part first. As I’m staying at Marriott hotel in Oakland, I have to take BART from Oakland to Powell station. Unfortunately, I did not realize that only on Sunday the train station near my hotel won’t be open until 8am. So by the time of a train ride and VERY fast walking (BTW, I’m just recovering from cold) to the Moscone center, I’ve arrived about 20 minutes late for the first session.

Anyway after that the rest of the day is fun.

My first session is the Demystifying Oracle RAC workload management by Alex Gorbachev. I really enjoyed his presentation especially demos on the connection load balancing (CLB). I’ve learned techniques how to control the CLB’s behaviors in both pre-10gR1 and post-10gR2. His presentation is a must-see if you did not attend his session. At least, you will know what “goodness” (or badness) in the context of CLB means.

Arup Nanda showed us in his Oracle RAC Performance Tuning the real case studies of RAC performance issues, and how he systematically tackled them. Besides good planning, he recommended that don’t jump into a conclusion by just looking at the AWR wait names without actual diagnostic (a specific sample in the session is that don’t get fooled by “gc” waits considering them as the interconnect issues).

At the end of the day, not only I stopped by at the demo of the full-rack of Exadata version 2 in the Moscone North to see this product in action, but I also had a chance to say hello to Dan as he is now a part of the X-Team.

Here are some pictures from the events:

Oracle Open World 2009 at Moscone Center

Oracle Open World 2009 at the Moscone Center

Oracle Banners outside the Moscone Center

Registration Counters

Moscone Center from Yerba Buena Garden Building

View of the Moscone Center from Yerba Buena Gardens

Attendees are walking to the keynote

Attendees are heading to keynote's conference room

Goodies at the Welcome Reception

Tags: OOW, OOW09, OOW2009, Oracle Open World

Comments (1)

Oct 10 2009

APEX Listener Released as an Early Adopters

Category: APEX — ittichai @ 8:21 am

Oracle APEX Listener has been released as an early adopters. It is an alternative to Oracle HTTP Server (OHS) and Oracle Embedded PL/SQL Gateway (EPG) as web server to APEX database engine. You may find more information about this at the Oracle’s APEX Listener Download site, or Dimitri’s or John’s blogs.

I just configured the APEX listener with Oracle WebLogic 11g as I would like to compare it with the existing OHS environment. The instructions coming with the download for WebLogic is very straightforward except the last one which asks to recursively copy the apex/images directory to the webserver’s /i. My initial setup shows the logon screen but no images displayed.

I have to admit that I’m a WebLogic novice. My simple workaround to fix this image issue is to make all APEX images into a new application called “i”. This simply can be done by creating a war file of all images under apex/images directory.

F:\DOWNLOAD\apex_3.1.2\apex\images>jar cvf i.war *

Using this i.war file, you just simply install it as a normal application.

If anyone out there have better approaches, please feel free to share.

[Updated: 4/6/2010] Made a correction to the jar syntax. Thanks Boasheng for your comment.

Tags: APEX listener early adopters

Comments (9)

Oct 06 2009

The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g

Category: 11g,Database,PL/SQL — ittichai @ 8:43 pm

This is one of the 11g features I read it once when it was first released but did not see its significance until now. Last week we just migrated an application from 9i to 11g. During a test of the send mail package using UTL_SMTP, we got this error, “ORA-24247: network access denied by access control list (ACL).” After a quick search, I’m in luck because I found a lot of articles written about this new 11g feature. However, I particularly find these two well-written concepts and samples from Arup Nanda’s Access Control Lists for UTL_TCP/HTTP/SMTP and Oracle-Base’s Fine-Grained Access to Network Services in Oracle Database 11g Release 1 very helpful.

My sample here is from our test case:

1. The send mail package which executes the UTL_SMTP failed.

TEST_USER SQL> exec pkg_LoadStatus.SendMail('user@company.com', 'Test Subject', 'Hello World');

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "pkg_LoadStatus", line 283
ORA-06512: at line 3

2. To fix it, an ACL has to be created.

The principal is the user or role to be added into this ACL. In this case, the TEST_USER account is added during the ACL creation. This field is case sensitive.

SQL> connect / as sysdba

begin
dbms_network_acl_admin.create_acl (
acl             => 'Mail_UTL_Access.xml',
description     => 'Mail UTL Network Access',
principal       => 'TEST_USER',
is_grant        => TRUE,
privilege       => 'connect',
start_date      => null,
end_date        => null
);

commit;
end;
/

The description of each variable is clearly described in the Oracle-Base’s article.

3. Verify a newly-created ACL.

SQL> SELECT any_path
     FROM resource_view
     WHERE any_path like '/sys/acls/%.xml';

ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/Mail_UTL_Access.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3684e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf36e4e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3724e24e04403ba6c65c6_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3764e24e04403ba6c65c6_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml

4. Optionally you can add more users or roles into this ACL by using the add_privilege procedure. This is similar to the create_acl procedure except no description. Sample shown here is to add ADMIN_ADMIN_ROLE role.

begin
dbms_network_acl_admin.add_privilege (
acl           => 'Mail_UTL_Access.xml',
principal     => 'APP_ADMIN_ROLE',
is_grant      => TRUE,
privilege     => 'connect',
start_date    => null,
end_date      => null);

commit;
end;
/

5. Add a host and port range allowed.

begin
dbms_network_acl_admin.assign_acl (
acl           => 'Mail_UTL_Access.xml',
host          => 'smtp.company.com',
lower_port    => 1,
upper_port    => 1024);

commit;
end;
/

6. Test the send mail package again. This time there is no error, and the recipient receives email.

TEST_USER SQL> exec pkg_LoadStatus.SendMail('user@company.com', 'Test Subject', 'Hello World');

PL/SQL procedure successfully completed

Tags: 11g, ACL, Network Service, UTL_HTTP, UTL_SMTP, UTL_TCP

Comments (5)