Infoaddict Just another site for Infoaddict's

1Feb/140

Apache PHP and Oracle Howto

Posted by Infoaddict

Note: This is also a year old. But helps understand the basic principle of combining Apache, PHP and Oracle.

 

NOTE: NOTE: NOTE: REMOVE APACHE RPM, or else you will be pulling your hair afterwards.
YOU HAVE BEEN WARNED!

Oracle 10gR2 client was installed in /oracle as type: "Run Time"

After installation of Oracle software is completed, it is better to run ldconfig once.

~]# ldconfig

Apache was installed using :

~]# mkdir /www

~]# cd /tmp/httpd-2.2.4

httpd-2.2.4]# ./configure --prefix=/www --exec-prefix=/www --bindir=/www/bin --sysconfdir=/www/conf --libdir=/www/lib  --enable-module=so

httpd-2.2.4]# make && make install && echo "Apache Installation Success" || echo "Apache FAILED"

~]# cd /tmp/php-4.4.5

php-4.4.5]# export ORACLE_HOME=/oracle/product/10.2.0/db_1

php-4.4.5]# export ORACLE_SID=orcl

The following will work for "Administrator" and "Runtime" versions of Oracle Client software installation only :

php-4.4.5]# ./configure --prefix=/www/php --with-apxs2=/www/bin/apxs --with-config-file-path=/www/php --with-oci8=$ORACLE_HOME --enable-shared=$ORACLE_HOME/lib --disable-xml --without-pear --enable-sigchild

php-4.4.5]# make && make install && echo "PHP Installation Success" || echo "PHP FAILED"

php-4.4.5]# libtool --finish /tmp/php-4.4.5/libs

This step is not required:-

~]# chmod o+rx  /oracle -R

~]# vi /www/conf/httpd.conf

(Make the following changes:-)

ServerAdmin webmaster@yourdomain.com
ServerName dbserver.yourdomain.com

AddType application/x-httpd-php .php .phtml

DirectoryIndex index.php index.html index.html.var

LoadModule php4_module        modules/libphp4.so  # (Normally it already exists, you don't have to write yourself)

~]# vi /www/bin/envvars

# This file is generated from envvars-std.in
#
export ORACLE_HOME="/oracle/product/10.2.0/db_1"
export ORACLE_BASE="/oracle/"
export ORACLE_SID="orcl"
LD_LIBRARY_PATH="/www/lib:$LD_LIBRARY_PATH:$ORACLE_HOME/lib"
export LD_LIBRARY_PATH

To check PHP:

~]# vi /www/htdocs/index.php

~]# vi /www/htdocs/test.php

if ($conn=OCILogon("scott", "tiger", "orcl")) {
echo "

Active

";
}else {
$err = OCIError();
echo "

Failed

";
}
?>

( Test your entire setup by this script. Should show you "Active" on your web page. )

Now this is the part which made me too mad for a week. It gave nme all kind of weird messages, like: "unable to retrieve text", etc etc.

The application developers were over-riding the variable settings of my apache server

~]# vi /www/htdocs/dsn/conn.php

# #########################################################################################
# As you can see these settings from application were causing all stupid errors
# So I commented them and things become all ok.
# There is no need to setup these variables here as they are setup in /www/bin/envvars file
# #########################################################################################

#putenv("ORACLE_BASE=/u01/app/oracle/product/10.1.0/Db_1");
#putenv("ORACLE_HOME=/u01/app/oracle/product/10.1.0/Db_1");
#putenv("ORACLE_SID=FPSC");
#putenv("NLS_LANGUAGE=FRENCH_FRANCE.WE8ISO8859P1");
#putenv("TNS_ADMIN=/u01/app/oracle/product/10.1.0/Db_1/network/admin");
#putenv("TNS_ADMIN=/u01/app/oracle/product/10.1.0/Db_1");
#putenv("ORA_NLS33=/u01/app/oracle/product/10.1.0/Db_1/ocommon/nls/admin/data");
#putenv("LD_LIBRARY_PATH=/u01/app/oracle/product/10.1.0/Db_1/lib:/u01/app/oracle/product/10.1.0/Db_1/network");

# ##############################################################################################################

if(!$conn) {

$conn=OCILogon("scott", "tiger", "orcl");
if($error = OCIError()) {
die("ERROR!! Couldn't connect to server!");
}
}
?>

Now setup Apache to start at boot time.

~]# vi /etc/rc.local
/www/bin/apachectl -k start && echo "Apache startup OK" || echo "Apache startup FAILED" ; sleep 3

 

Or you can setup an init.d script for this.

Print Friendly
Filed under: Oracle, RHEL No Comments
24Jun/130

Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6

Posted by Infoaddict

This article describes the installation of Oracle Database 11g Release 2 (11.2) (64-bit) on Oracle Linux 6 (64-bit). The article is based on a server installation with a minimum of 2G swap, with SELinux set to permissive and the firewall disabled. The following package groups were included for this installation.

  • Base System > Base
  • Base System > Client management tools
  • Base System > Compatibility libraries
  • Base System > Hardware monitoring utilities
  • Base System > Large Systems Performance
  • Base System > Network file system client
  • Base System > Performance Tools
  • Base System > Perl Support
  • Servers > Server Platform
  • Servers > System administration tools
  • Desktops > Desktop
  • Desktops > Desktop Platform
  • Desktops > Fonts
  • Desktops > General Purpose Desktop
  • Desktops > Graphical Administration Tools
  • Desktops > Input Methods
  • Desktops > X Window System
  • Development > Additional Development
  • Development > Development Tools
  • Applications > Internet Browser

An example of this type of Linux installations can be seen here. Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software

Download the Oracle software from OTN or MOS depending on your support status.

Unpack Files

Unzip the files.

# 11.2.0.1
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

#11.2.0.2
unzip p10098816_112020_Linux-x86-64_1of7.zip
unzip p10098816_112020_Linux-x86-64_2of7.zip

#11.2.0.3
unzip p10404530_112030_Linux-x86-64_1of7.zip
unzip p10404530_112030_Linux-x86-64_2of7.zip

You should now have a single directory called "database" containing installation files.

Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>

For example.

127.0.0.1       localhost.localdomain  localhost
192.168.0.181   ol6-112.localdomain    ol6-112

Oracle Installation Prerequisites

Perform either the Automatic Setup or the Manual Setup to complete the basic prerequisites. The Additional Setup is required for all installations.

Automatic Setup

If you plan to use the "oracle-rdbms-server-11gR2-preinstall" package to perform all your prerequisite setup, follow the instructions at http://public-yum.oracle.com to setup the yum repository for OL, then perform the following command.

# yum install oracle-rdbms-server-11gR2-preinstall

All necessary prerequisites will be performed automatically.

It is probably worth doing a full update as well, but this is not strictly speaking necessary.

# yum update

Manual Setup

If you have not used the "oracle-rdbms-server-11gR2-preinstall" package to perform all prerequisites, you will need to manually perform the following setup tasks.

Oracle recommend the following minimum parameter settings.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

The current values can be tested using the following command.

/sbin/sysctl -a | grep <param-name>

Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

Install the following packages if they are not already present.

# From Oracle Linux 6 DVD
cd /media/cdrom/Server/Packages
rpm -Uvh binutils-2*x86_64*
rpm -Uvh glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
rpm -Uvh glibc-2*i686* nss-softokn-freebl-3*i686*
rpm -Uvh compat-libstdc++-33*x86_64*
rpm -Uvh glibc-common-2*x86_64*
rpm -Uvh glibc-devel-2*x86_64*
rpm -Uvh glibc-devel-2*i686*
rpm -Uvh glibc-headers-2*x86_64*
rpm -Uvh elfutils-libelf-0*x86_64*
rpm -Uvh elfutils-libelf-devel-0*x86_64*
rpm -Uvh gcc-4*x86_64*
rpm -Uvh gcc-c++-4*x86_64*
rpm -Uvh ksh-*x86_64*
rpm -Uvh libaio-0*x86_64*
rpm -Uvh libaio-devel-0*x86_64*
rpm -Uvh libaio-0*i686*
rpm -Uvh libaio-devel-0*i686*
rpm -Uvh libgcc-4*x86_64*
rpm -Uvh libgcc-4*i686*
rpm -Uvh libstdc++-4*x86_64*
rpm -Uvh libstdc++-4*i686*
rpm -Uvh libstdc++-devel-4*x86_64*
rpm -Uvh make-3.81*x86_64*
rpm -Uvh numactl-devel-2*x86_64*
rpm -Uvh sysstat-9*x86_64*
rpm -Uvh compat-libstdc++-33*i686*
rpm -Uvh compat-libcap*
cd /
eject

Note. This will install all the necessary 32-bit packages for 11.2.0.1. From 11.2.0.2 onwards many of these are unnecessary, but having them present does not cause a problem.

Create the new groups and users.

groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
groupadd -g 504 asmadmin
groupadd -g 506 asmdba
groupadd -g 505 asmoper

useradd -u 502 -g oinstall -G dba,asmdba,oper oracle
passwd oracle

Note. We are not going to use the "asm" groups, since this installation will not use ASM.

Additional Setup

Set the password for the "oracle" user.

passwd oracle

Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Once the change is complete, restart the server.

If you have the Linux firewall enabled, you will need to disable or configure it, as shown here or here.

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login as root and issue the following command.

xhost +<machine-name>

Login as the oracle user and add the following lines at the end of the ".bash_profile" file.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=ol6-112.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller

Proceed with the installation of your choice. The prerequisites checks will fail for the following version-dependent reasons:

  • 11.2.0.1: The installer shows multiple "missing package" failures because it does not recognize several of the newer version packages that were installed. These "missing package" failures can be ignored as the packages are present. The failure for the "pdksh" package can be ignored because we installed the "ksh" package in its place.
  • 11.2.0.2: The installer should only show a single "missing package" failure for the "pdksh" package. It can be ignored because we installed the "ksh" package in its place.
  • 11.2.0.3: The installer shows no failures and continues normally.

You can see the type of installation I performed by clicking on the links below to see screen shots of each stage.

  1. Configure Security Updates
  2. Select Install Option
  3. System Class
  4. Node Selection
  5. Select Install Type
  6. Typical Install Configuration
  7. Create Inventory
  8. Perform Prerequisite Checks
  9. Summary
  10. Install Product
  11. Database Configuration Assistant
  12. Database Configuration Assistant 2
  13. Execute Configuration Scripts
  14. Finish

Post Installation

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

DB11G:/u01/app/oracle/product/11.2.0/db_1:Y

 

Print Friendly
Filed under: Oracle, RHEL No Comments
19Feb/120

Identifying Host Names and IP Addresses

Posted by Infoaddict

This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

UTL_INADDR
SYS_CONTEXT
V$INSTANCE
V$SESSION

UTL_INADDR

The UTL_INADDR package was introduced in Oracle 8.1.6 to provide a means of retrieving host names and IP addresses of remote hosts from PL/SQL.

The GET_HOST_ADDRESS function returns the IP address of the specified host name.

SQL> SELECT UTL_INADDR.get_host_address('bart') FROM dual;

UTL_INADDR.GET_HOST_ADDRESS('BART')
--------------------------------------------------------------------------------
192.168.2.4

SQL>

The IP address of the database server is returned if the specified host name is NULL or is omitted.

SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
--------------------------------------------------------------------------------
192.168.2.5

SQL>

An error is returned if the specified host name is not recognized.

SQL> SELECT UTL_INADDR.get_host_address('banana') from dual;
SELECT UTL_INADDR.get_host_address('banana') from dual
*
ERROR at line 1:
ORA-29257: host banana unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

SQL>

The GET_HOST_NAME function returns the host name of the specified IP address.

SQL> SELECT UTL_INADDR.get_host_name('192.168.2.4') FROM dual;

UTL_INADDR.GET_HOST_NAME('192.168.2.4')
--------------------------------------------------------------------------------
bart

SQL>

The host name of the database server is returned if the specified IP address is NULL or omitted.

SQL> SELECT UTL_INADDR.get_host_name FROM dual;

GET_HOST_NAME
--------------------------------------------------------------------------------
C4210gR2

1 row selected.

SQL>

An error is returned if the specified IP address is not recognized.

SQL> SELECT UTL_INADDR.get_host_name('1.1.1.1') FROM dual;
SELECT UTL_INADDR.get_host_name('1.1.1.1') FROM dual
*
ERROR at line 1:
ORA-29257: host 1.1.1.1 unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

SQL>

SYS_CONTEXT

The SYS_CONTEXT function is able to return the following host and IP address information for the current session:

TERMINAL - An operating system identifier for the current session. This is often the client machine name.
HOST - The host name of the client machine.
IP_ADDRESS - The IP address of the client machine.
SERVER_HOST - The host name of the server running the database instance.

The following examples show the typical output for each variant.

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------------
marge

1 row selected.

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------
marge

1 row selected.

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------
192.168.2.3

1 row selected.

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------
C4210gr2

1 row selected.

SQL>

V$INSTANCE

The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;

HOST_NAME
------------------------------------------------
C4210gR2

1 row selected.

SQL>

V$SESSION

The V$SESSION view contains the following host information for all database sessions:

TERMINAL - The operating system terminal name for the client. This is often set to the client machine name.
MACHINE - The operating system name for the client machine. This may include the domain name if present.

The following examples show the typical output for each column.

SQL> SELECT terminal, machine FROM v$session WHERE username = 'TIM_HALL';

TERMINAL                       MACHINE
------------------------------ ----------------------------------------------------
MARGE                          ORACLE-BASE\MARGE

1 row selected.

SQL>

 

For more information see:

Print Friendly
Tagged as: No Comments
19Feb/120

Fine-Grained Access to Network Services in Oracle Database 11g Release 1

Posted by Infoaddict

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.

Access control lists can be created, amended and deleted in the XML DB repository directly using FTP or WebDav. In addition, Oracle provide the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow ACL management from PL/SQL. These APIs are the subject of this article.

Create an Access Control List (ACL)
Assign an ACL to a Network
ACL Views
Checking Privileges
Test the ACL
Other Security Considerations
Open ACL

Create an Access Control List (ACL)

Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL procedure uses the following parameters to create a new ACL:

acl - The name of the access control list XML file, generated relative to the "/sys/acls" directory in the XML DB Repository.
description - A description of the ACL.
principal - The first user account or role being granted or denied permissions. The text is case sensitive.
is_grant - TRUE to grant, FALSE to deny the privilege.
privilege - Use 'connect' for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access. Use 'resolve' for UTL_INADDR name/IP resolution. The text is case sensitive.
start_date - Default value NULL. When specified, the ACL will only be active on or after the specified date.
end_date - An optional end date for the ACL.

The following code creates two test users to act as principals, then creates a new ACL.

CONN sys/password@db11g AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CONNECT TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CONNECT TO test2;

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl          => 'test_acl_file.xml',
description  => 'A test of the ACL functionality',
principal    => 'TEST1',
is_grant     => TRUE,
privilege    => 'connect',
start_date   => SYSTIMESTAMP,
end_date     => NULL);

COMMIT;
END;
/

Once created, the ACL is visible in the "http://host:port/sys/acls/" directory.

Additional users or roles are added to the ACL using the ADD_PRIVILEGE procedure. Its parameter list is similar to the CREATE_ACL procedure, with the omission of the DESCRIPTION parameter and the addition of a POSITION parameter, which sets the order of precedence.

BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl         => 'test_acl_file.xml',
principal   => 'TEST2',
is_grant    => FALSE,
privilege   => 'connect',
position    => NULL,
start_date  => NULL,
end_date    => NULL);

COMMIT;
END;
/

Each principal is defined as a separate access control element (ACE), within the ACL. When multiple principles are defined, they are evaluated in order from top to bottom, with the last relevant reference used to define the privilege. This means a role that denies access to a resource can be granted to a user, but if the user is defined as a principal further down the file, that definition will override the role definition for that user. Use the POSITION parameter to ensure privileges are evaluated in order.

Privileges are removed using the DELETE_PRIVILEGE procedure. If the IS_GRANT or PRIVILEGE parameters are NULL, all grants or privileges for the ACL and principal are removed.

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl         => 'test_acl_file.xml',
principal   => 'TEST2',
is_grant    => FALSE,
privilege   => 'connect');

COMMIT;
END;
/

ACLs are deleted using the DROP_ACL procedure.

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl         => 'test_acl_file.xml');

COMMIT;
END;
/

Assign an ACL to a Network

Access control lists are assigned to networks using the ASSIGN_ACL procedure, whose parameters are listed below:

acl - The name of the access control list XML file.
host - The hostname, domain, IP address or subnet to be assigned. Hostnames are case sensitive, and wildcards are allowed for IP addresses and domains.
lower_port - Defaults to NULL. Specifies the lower port range for the 'connect' privilege.
upper_port - Defaults to NULL. If the lower_port is specified, and the upper_port is NULL, it is assumed the upper_port matches the lower_port.

The code below shows the ACL created previously being assigned to a specific IP address and a subnet.

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl         => 'test_acl_file.xml',
host        => '192.168.2.3',
lower_port  => 80,
upper_port  => NULL);

DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl         => 'test_acl_file.xml',
host        => '10.1.10.*',
lower_port  => NULL,
upper_port  => NULL);

COMMIT;
END;
/

Only one ACL can be assigned to a specific host and port-range combination. Assigning a new ACL to a specific host and port-range results in the deletion of the previous assignment. You must take care when making a new assignment that you are not opening ports that were closed by a previous ACL assignment, or you could be opening yourself to attack. When wildcard usage causes overlapping assignments, the most specific assignment will take precedence, so an ACL assigned to 192.168.2.3:80 takes precedence over once assigned to 192.168.2.* etc.

The UNASSIGN_ACL procedure allows you to manually drop ACL assignments. It uses the same parameter list as the ASSIGN_ACL procedure, with any NULL parameters acting as wildcards.

BEGIN
DBMS_NETWORK_ACL_ADMIN.unassign_acl (
acl         => 'test_acl_file.xml',
host        => '192.168.2.3',
lower_port  => 80,
upper_port  => NULL);

COMMIT;
END;
/

ACL Views

The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings. The expected output below assumes none of the delete/drop/unassign operations have been performed.

The DBA_NETWORK_ACLS view displays information about network and ACL assignments.

COLUMN host FORMAT A30
COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------
10.1.10.*                                            /sys/acls/test_acl_file.xml
192.168.2.3                            80         80 /sys/acls/test_acl_file.xml

2 rows selected.

SQL>

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.

COLUMN acl FORMAT A30
COLUMN principal FORMAT A30

SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;

ACL                            PRINCIPAL                      PRIVILE IS_GR START_DATE  END_DATE
------------------------------ ------------------------------ ------- ----- ----------- -----------
/sys/acls/test_acl_file.xml    TEST1                          connect true  02-APR-2008
/sys/acls/test_acl_file.xml    TEST2                          connect false

2 rows selected.

SQL>

The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings.

CONN test1/test1@db11g

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status
FROM   user_network_acl_privileges;

HOST                           LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.*                                            connect GRANTED
192.168.2.3                            80         80 connect GRANTED

2 rows selected.

SQL>

CONN test2/test2@db11g

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status
FROM   user_network_acl_privileges;

HOST                           LOWER_PORT UPPER_PORT PRIVILE STATUS
------------------------------ ---------- ---------- ------- -------
10.1.10.*                                            connect DENIED
192.168.2.3                            80         80 connect DENIED

2 rows selected.

SQL>

Checking Privileges

In addition to the ACL views, privileges can be checked using the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions of the DBMS_NETWORK_ACL_ADMIN package.

CONN sys/password@db11g AS SYSDBA

SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('test_acl_file.xml', 'TEST1', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;

PRIVILE
-------
GRANTED

1 row selected.

SQL>

COLUMN acl FORMAT A30
COLUMN host FORMAT A30

SELECT acl,
host,
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST2', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM   dba_network_acls;

PRIVILE
-------
DENIED

1 row selected.

SQL>

The DBMS_NETWORK_ACL_UTILITY package contains functions to help determine possible matching domains. The DOMAINS table function returns a collection of all possible references that may affect the specified host, domain, IP address or subnet, in order of precedence.

SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('oel5-11g.localdomain'));

COLUMN_VALUE
-------------------------------
oel5-11g.localdomain
*.localdomain
*

3 rows selected.

SQL>

SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('192.168.2.3'));

COLUMN_VALUE
-------------------------------
192.168.2.3
192.168.2.*
192.168.*
192.*
*

5 rows selected.

SQL>

The DOMAIN_LEVEL function returns the level of the specified host, domain, IP address or subnet.

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('oel5-11g.localdomain')
FROM   dual;

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL('OEL5-11G.LOCALDOMAIN')
-------------------------------------------------------------
2

1 row selected.

SQL>

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('192.168.2.3')
FROM   dual;

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL('192.168.2.3')
----------------------------------------------------
4

1 row selected.

SQL>

These functions may be useful for when querying the ACL views for possible matches to a specific host, domain, IP address or subnet.

SELECT host,
lower_port,
upper_port,
acl,
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid,  'TEST1', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE
FROM   dba_network_acls
WHERE  host IN (SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('10.1.10.191')))
ORDER BY
DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;

HOST                           LOWER_PORT UPPER_PORT ACL                            PRIVILE
------------------------------ ---------- ---------- ------------------------------ -------
10.1.10.*                                            /sys/acls/test_acl_file.xml    GRANTED

1 row selected.

SQL>

Test the ACL

The TEST1 and TEST2 users have the ACL allowed and denied respectively. This means we can test the ACL functionality by comparing their responses to calls to external network services. The following code grants execute permission on the UTL_HTTP package to both users, then attempts to access a web page from each user.

CONN sys/password@db11g AS SYSDBA
GRANT EXECUTE ON UTL_HTTP TO test1, test2;

CONN test1/test1@db11g

DECLARE
l_url            VARCHAR2(50) := 'http://192.168.2.3:80';
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
BEGIN
-- Make a HTTP request and get the response.
l_http_request  := UTL_HTTP.begin_request(l_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.end_response(l_http_response);
END;
/

PL/SQL procedure successfully completed.

SQL>

CONN test2/test2@db11g

DECLARE
l_url            VARCHAR2(50) := 'http://192.168.2.3:80';
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
BEGIN
-- Make a HTTP request and get the response.
l_http_request  := UTL_HTTP.begin_request(l_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.end_response(l_http_response);
END;
/
DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 7

SQL>

From this we can see that the TEST1 user was able to access the web page, while the TEST2 user was denied access by the ACL.

The default action of the server is to deny access to external network service, as shown by the following test on a new user.

CONN sys/password@db11g AS SYSDBA

CREATE USER test3 IDENTIFIED BY test3;
GRANT CONNECT TO test3;
GRANT EXECUTE ON UTL_HTTP TO test3;

CONN test3/test3@db11g

DECLARE
l_url            VARCHAR2(50) := 'http://192.168.2.3:80';
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
BEGIN
-- Make a HTTP request and get the response.
l_http_request  := UTL_HTTP.begin_request(l_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.end_response(l_http_response);
END;
/
DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 7

SQL>

This may cause some confusion when upgrading databases that access external network services from 10g to 11g. In these situations, it will be necessary to implement suitable access control lists before your original functionality is possible.
Other Security Considerations

Pete Finnigan commented on his blog and in his security presentations about the fact that the ACLs are not tied to a specific package. This means opening a port on a server with the 'connect' privilege makes it accessible by UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP. With this in mind there are some things to consider:

The use of fine-grained access to network services is not an excuse to ignore basic security measures, like revoking unnecessary privileges on network service related packages.
Control over the services you make available is possible by limiting access to the specific ports. If you only need HTTP access to port 80, specify the port rather than opening access to all ports on the server.
Wildcards can be dangerous as you may be granting access to more servers that you should.
You must protect your ACLs. If people can alter them, they become useless as a protection mechanism. Prevent direct access to the ACLs in the XML DB repository and make sure users don't have access to the management APIs.

Thanks to Pete Finnigan for his input.
Open ACL

From a security standpoint, it's not a good idea to allow complete network access from the database, but for testing features I sometimes find it useful to create an open ACL for an instance.

CONN / AS SYSDBA
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl          => 'open_acl_file.xml',
description  => 'A test of the ACL functionality',
principal    => 'TEST',
is_grant     => TRUE,
privilege    => 'connect',
start_date   => SYSTIMESTAMP,
end_date     => NULL);

DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl         => 'open_acl_file.xml',
host        => '*',
lower_port  => 1,
upper_port  => 9999);

COMMIT;
END;
/

For more information see:

Print Friendly
Tagged as: No Comments
19Feb/120

Email From Oracle PL/SQL (UTL_SMTP)

Posted by Infoaddict

The UTL_SMTP package was introduced in Oracle 8i and can be used to send emails from PL/SQL.

Simple Emails
Multi-Line Emails
HTML Emails
Emails with Attachments
BLOB Attachment
CLOB Attachment
Miscellaneous

Related articles.

UTL_MAIL in Oracle 10g
HTML with Embedded Images from PL/SQL
Fine-Grained Access to Network Services in Oracle Database 11g Release 1

Simple Emails

In it's simplest form a single string or variable can be sent as the message body using the following procedure. In this case we have not included any header information or subject line in the message, so it is not very useful, but it is small.

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
p_from      IN VARCHAR2,
p_message   IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn   UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
/

The code below shows how the procedure is called.

BEGIN
send_mail(p_to        => 'me@mycompany.com',
p_from      => 'admin@mycompany.com',
p_message   => 'This is a test message.',
p_smtp_host => 'smtp.mycompany.com');
END;
/

Multi-Line Emails

Multi-line messages can be written by expanding the UTL_SMTP.DATA command using the UTL_SMTP.WRITE_DATA command as follows. This is a better method to use as the total message size is no longer constrained by the 32K limit on a VARCHAR2 variable. In the following example the header information has been included in the message also.

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
p_from      IN VARCHAR2,
p_subject   IN VARCHAR2,
p_message   IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn   UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/

The code below shows how the procedure is called.

BEGIN
send_mail(p_to        => 'me@mycompany.com',
p_from      => 'admin@mycompany.com',
p_subject   => 'Test Message',
p_message   => 'This is a test message.',
p_smtp_host => 'smtp.mycompany.com');
END;
/

HTML Emails

The following procedure builds on the previous version, allowing it include plain text and/or HTML versions of the email. The format of the message is explained here.

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
p_from      IN VARCHAR2,
p_subject   IN VARCHAR2,
p_text_msg  IN VARCHAR2 DEFAULT NULL,
p_html_msg  IN VARCHAR2 DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_html_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_html_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/

The code below shows how the procedure is called.

DECLARE
l_html VARCHAR2(32767);
BEGIN
l_html := '<html>
<head>
<title>Test HTML message</title>
</head>
<body>
<p>This is a <b>HTML</b> <i>version</i> of the test message.</p>
<p><img src="http://www.oracle-base.com/images/site_logo.gif" alt="Site Logo" />
</body>
</html>';

send_mail(p_to        => 'me@mycompany.com',
p_from      => 'admin@mycompany.com',
p_subject   => 'Test Message',
p_text_msg  => 'This is a test message.',
p_html_msg  => l_html,
p_smtp_host => 'smtp.mycompany.com');
END;
/

Emails with Attachments

Sending an email with an attachment is similar to the previous example as the message and the attachment must be separated by a boundary and identified by a name and mime type.
BLOB Attachment

Attaching a BLOB requires the binary data to be encoded and converted to text so it can be sent using SMTP.

CREATE OR REPLACE PROCEDURE send_mail (p_to          IN VARCHAR2,
p_from        IN VARCHAR2,
p_subject     IN VARCHAR2,
p_text_msg    IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host   IN VARCHAR2,
p_smtp_port   IN NUMBER DEFAULT 25)
AS
l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step        PLS_INTEGER  := 24573;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
END LOOP;

UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/

The code below shows how the procedure is called.

DECLARE
l_name images.name%TYPE := 'site_logo.gif';
l_blob images.image%TYPE;
BEGIN
SELECT image
INTO   l_blob
FROM   images
WHERE  name = l_name;

send_mail(p_to          => 'me@mycompany.com',
p_from        => 'admin@mycompany.com',
p_subject     => 'Test Message',
p_text_msg    => 'This is a test message.',
p_attach_name => 'site_logo.gif',
p_attach_mime => 'image/gif',
p_attach_blob => l_blob,
p_smtp_host   => 'smtp.mycompany.com');
END;
/

CLOB Attachment

Attaching a CLOB is similar to attaching a BLOB, but we don't have to worry about encoding the data because it is already plain text.

CREATE OR REPLACE PROCEDURE send_mail (p_to          IN VARCHAR2,
p_from        IN VARCHAR2,
p_subject     IN VARCHAR2,
p_text_msg    IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_clob IN CLOB DEFAULT NULL,
p_smtp_host   IN VARCHAR2,
p_smtp_port   IN NUMBER DEFAULT 25)
AS
l_mail_conn   UTL_SMTP.connection;
l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step        PLS_INTEGER  := 24573;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
END LOOP;

UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/

The code below shows how the procedure is called.

DECLARE
l_clob CLOB := 'This is a very small CLOB!';
BEGIN
send_mail(p_to          => 'me@mycompany.com',
p_from        => 'admin@mycompany.com',
p_subject     => 'Test Message',
p_text_msg    => 'This is a test message.',
p_attach_name => 'test.txt',
p_attach_mime => 'text/plain',
p_attach_clob => l_clob,
p_smtp_host   => 'smtp.mycompany.com');
END;
/

Miscellaneous

For emails with multiple recipients, simply call the RCPT procedure once for each separate email address.

The UTL_SMTP package requires Jserver which can be installed by running the following scripts as SYS.

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

f you are attempting to use this method from Oracle 11g, you must remember to configure fine grained access to network services for the mail server.

Print Friendly
Tagged as: No Comments
19Feb/120

Images from Oracle Over HTTP

Posted by Infoaddict

This articles defines a process for getting images out of the database over HTTP but it could easily be extended to serve a variety of binary documents. The database functionality used is available in later versions of Oracle 8i also.

Database Access Descriptor Configuration
Create Schema
Create PL/SQL Code
Load Images
Test It

Database Access Descriptor Configuration (DAD)

First we must set up a Database Access Descriptor (DAD) to allow the webserver to connect to the database when a specific URL is requested.

Access the database HTTP server main page via a browser (http://yourServer:7777/). The correct port number is listed in the $ORACLE_HOME/Apache/Apache/setupinfo.txt file
Click on the "Mod_plsql Configuration Menu" link.
Click on the "Gateway Database Access Descriptor Settings" link.
Click on the "Add Default (blank configuration)" link.
Enter SCOTT as the Database Access Descriptor Name. This will be used in the requesting URL.
Enter the username (SCOTT), password (TIGER) and connect string (W2K1) for the desired database connection.
Select the "Basic" authentication mode.
Click the OK button at the top right of the screen.

The list of DADs will now contain the SCOTT entry, which can be edited if necessary by clicking the edit icon next to it.
Create Schema

Next we create a schema to hold our images.

CREATE TABLE images (
id     NUMBER(10)    NOT NULL,
name   VARCHAR2(50)  NOT NULL,
image  BLOB          NOT NULL
)
/

ALTER TABLE images ADD (
CONSTRAINT images_pk PRIMARY KEY (id)
)
/

ALTER TABLE images ADD (
CONSTRAINT images_uk UNIQUE (name)
)
/

CREATE SEQUENCE images_seq
/

CREATE OR REPLACE DIRECTORY image_dir AS 'C:'
/

Create PL/SQL Code

Next we create the code to load and retrieve the images.

CREATE OR REPLACE PACKAGE images_api AS

PROCEDURE load (p_name  IN  images.name%TYPE);
PROCEDURE get  (p_name  IN  images.name%TYPE,
p_type  IN  VARCHAR2 DEFAULT 'gif');

PROCEDURE get_gif  (p_name  IN  images.name%TYPE);
PROCEDURE get_jpeg (p_name  IN  images.name%TYPE);

END;
/

CREATE OR REPLACE PACKAGE BODY images_api AS

PROCEDURE Load (p_name  IN  images.name%TYPE) IS
v_bfile  BFILE;
v_blob   BLOB;
BEGIN
INSERT INTO images (id, name, image)
VALUES (images_seq.NEXTVAL, p_name, EMPTY_BLOB())
RETURN image INTO v_blob;

v_bfile := BFILENAME('IMAGE_DIR', p_name);
DBMS_LOB.fileopen(v_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(v_blob, v_bfile, DBMS_LOB.getlength(v_bfile));
DBMS_LOB.fileclose(v_bfile);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;

PROCEDURE Get (p_name  IN  images.name%TYPE,
p_type  IN  VARCHAR2 DEFAULT 'gif') IS
v_blob  BLOB;
v_amt   NUMBER := 30;
v_off   NUMBER := 1;
v_raw   RAW(4096);
BEGIN
SELECT image
INTO   v_blob
FROM   images
WHERE  name = p_name;

OWA_UTIL.mime_header('image/' || p_type);

BEGIN
LOOP
DBMS_LOB.read(v_blob, v_amt, v_off, v_raw);

HTP.prn(UTL_RAW.cast_to_varchar2(v_raw));
v_off := v_off + v_amt;
v_amt := 4096;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END;

PROCEDURE get_gif  (p_name   IN images.name%TYPE) IS
BEGIN
get (p_name, 'gif');
END;

PROCEDURE get_jpeg (p_name  IN  images.name%TYPE) IS
BEGIN
get (p_name, 'jpeg');
END;

END;
/

Load Images

Next we load some images into the database.

EXEC images_api.load('OCP.gif');
EXEC images_api.load('opn_logo2.gif');
EXEC images_api.load('Title.gif');
EXEC images_api.load('Google.gif');

Test It

With the DAD configured and the PL/SQL code in place the XML document can be retrieved by entering the correct URL into a browser.

http://yourServer:7777/pls/SCOTT/Images_API.Get_Gif?p_name=OCP.gif

For security reasons you may wish to access the data over SSL.

https://yourServer:443/pls/SCOTT/Images_API.Get_Gif?p_name=OCP.gif

The SSL port is listed in the $ORACLE_HOME/Apache/Apache/setupinfo.txt file.

Print Friendly
Tagged as: No Comments
19Feb/120

UTL_MAIL

Posted by Infoaddict

The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package, but this required knowledge of the SMTP protocol.

The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

With the configuration complete we can now send a mail.

BEGIN
UTL_MAIL.send(sender     => 'me@domain.com',
recipients => 'person1@domain.com,person2@domain.com',
cc         => 'person3@domain.com',
bcc        => 'myboss@domain.com',
subject    => 'UTL_MAIL Test',
message    => 'If you get this message it worked!');
END;
/

The package also supports sending mails with RAW and VARCHAR2 attachments.

Print Friendly
Tagged as: No Comments
19Feb/120

Oracle Database 10g R2(10.2.0.1) on RHEL5

Posted by Infoaddict

I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5). The article is based on a server installation similar to this, with a minimum of 2G swap, secure Linux disabled and the following package groups installed.

GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Development Libraries
Development Tools
Legacy Software Development
Server Configuration Tools
Administration Tools
Base
Legacy Software Support
System Tools
X Window System

Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software
Unpack Files
Hosts File
Set Kernel Parameters
Setup
Installation
Post Installation

Download Software

Download the following software.

Oracle Database 10g Release 2 (10.2.0.1) Software

Unpack Files

Unzip the files.

unzip 10201_database_linux32.zip

You should now have a single directory containing installation files. Depending on the age of the download this may either be named "db/Disk1" or "database".
Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>

Set Kernel Parameters

Add the following lines to the "/etc/sysctl.conf" file.

#kernel.shmall = 2097152
#kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
#fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

*               soft    nproc   2047
*               hard    nproc   16384
*               soft    nofile  1024
*               hard    nofile  65536

Add the following line to the "/etc/pam.d/login" file, if it does not already exist.

session    required     pam_limits.so

Disable secure linux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=disabled

Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.
Setup

Install the following packages.

# From RedHat AS5 Disk 1
cd /media/cdrom/Server
rpm -Uvh setarch-2*
rpm -Uvh make-3*
rpm -Uvh glibc-2*
rpm -Uvh libaio-0*
cd /
eject

# From RedHat AS5 Disk 2
cd /media/cdrom/Server
rpm -Uvh compat-libstdc++-33-3*
rpm -Uvh compat-gcc-34-3*
rpm -Uvh compat-gcc-34-c++-3*
rpm -Uvh gcc-4*
rpm -Uvh libXp-1*
cd /
eject

# From RedHat AS5 Disk 3
cd /media/cdrom/Server
rpm -Uvh openmotif-2*
rpm -Uvh compat-db-4*
cd /
eject

Create the new groups and users.

groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle.oinstall /u01

Login as root and issue the following command.

xhost +<machine-name>

Edit the "/etc/redhat-release" file replacing the current release information (Red Hat Enterprise Linux Server release 5 (Tikanga)) with the following:

redhat-4

Login as the oracle user and add the following lines at the end of the ".bash_profile" file.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller

During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.

Select Installation Method
Specify Inventory Directory and Credentials
Select Installation Type
Specify Home Details
Product-Specific Prerequisite Checks
Select Configuration Option
Select Database Configuration
Specify Database Configuration Options
Select Database Management Option
Specify Database Storage Option
Specify Backup and Recovery Options
Specify Database Schema Passwords
Summary
Install
Configuration Assistants
Database Configuration Assistant
Database Configuration Assistant Password Management
Execute Configuration Scripts
End Of Installation

Post Installation

Edit the "/etc/redhat-release" file restoring the original release information.

Red Hat Enterprise Linux Server release 5 (Tikanga)

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

TSH1:/u01/app/oracle/product/10.2.0/db_1:Y

Print Friendly
Tagged as: , No Comments
19Feb/120

Oracle Database 11g R2(11.2)(64-bit) on Oracle Linux 5 (64-bit)

Posted by Infoaddict

The installation of Oracle Database 11g Release 2 (11.2) (64-bit) on Oracle Linux 5 (64-bit). The article is based on a server installation with a minimum of 2G swap, secure Linux disabled. Oracle recommend a default server installation, but for these installations the following package groups installed:

GNOME Desktop Environment
Editors
Graphical Internet
Text-based Internet
Development Libraries
Development Tools
Server Configuration Tools
Administration Tools
Base
System Tools
X Window System

An example of this type of Linux installations can be seen here. Alternative installations may require more packages to be loaded, in addition to the ones listed below.

Download Software
Unpack Files
Hosts File
Set Kernel Parameters
Setup
Installation
Post Installation

Download Software
Download the following software.

Oracle Database 11g Release 2 (11.2) Software (64-bit)

Unpack Files
Unzip the files.

unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

You should now have a single directory called "database" containing installation files.
Hosts File
The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>

For example.

127.0.0.1       localhost.localdomain  localhost
192.168.2.181   ol5-11gr2.localdomain  ol5-11gr2

Set Kernel Parameters
Oracle recommend the following minimum parameter settings.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

The current values can be tested using the following command.

/sbin/sysctl -a | grep <param-name>

Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   10240

Disable secure linux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=disabled

Alternatively, this alteration can be done using the GUI tool (Applications > System Settings > Security Level). Click on the SELinux tab and disable the feature. If SELinux is disabled after installation, the server will need a reboot for the change to take effect.
Setup
Install the following packages if they are not already present.

# From Oracle Linux 5 DVD
cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
rpm -Uvh numactl-devel-2*
cd /
eject

Create the new groups and users.

groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin

useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle

Note. We are not going to use the "asmadmin" group, since this installation will not use ASM.

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login as root and issue the following command.

xhost +<machine-name>

Login as the oracle user and add the following lines at the end of the ".bash_profile" file.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=ol5-11gr2.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:

DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:

./runInstaller

Proceed with the installation of your choice. You can see the type of installation I performed by clicking on the links below to see screen shots of each stage.

Configure Security Updates
Select Install Option
System Class
Node Selection
Select Install Type
Typical Install Configuration
Create Inventory
Perform Prerequisite Checks
Summary
Install Product
Database Configuration Assistant
Database Configuration Assistant 2
Execute Configuration Scripts
Finish

Post Installation

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

DB11G:/u01/app/oracle/product/11.2.0/db_1:Y

Print Friendly
Tagged as: , No Comments