PL/SQL: Send Email from the database

EMAIL_PKG

An Oracle PL/SQL package to facilitate the sending of email messages from an Oracle database.  This database stored package was designed to make it easier to send email messages from an Oracle database or an Oracle Forms application. 

I adapted this package from Oracle’s DEMO_MAIL package that is downloadable from the Oracle Technology Network (OTN). 

In order to utilize this package, you must make a few modifications to the code to configure the package to use your SMTP mail server.  I have seeded the package with a fictitious SMTP Server name and the package will compile in your Oracle database as is, but you will receive an Exception when you attempt to send an email because the SMTP server name is invalid.

Installation

1. Open the email_pkg.sql file in your favorite text editor.  (I prefer to use Notepad++, but you can use Windows Notepad if you like so long as the editor saves the files as a true text file.)
2. Review the “Additional Information:” section in the package header (Lines 13-29).
3. Search the email_pkg.sql file for the word, “REQUIRED”.   There are three lines that must be modified to support your environment.  These lines start on line 98:
1. smtp_host               CONSTANT VARCHAR2(256) := 'mail.myserver.com';
2. smtp_port               CONSTANT PLS_INTEGER := 25;
3. smtp_domain          CONSTANT VARCHAR2(256) := 'myserver.com';
4. Modify the above variable declarations to use your SMTP server host address, port number (typically port 25), and the domain name.
4. If you intend to send email attachments, you will need to create a an Oracle Directory Object (as described on lines 25-29 in the email_pkg.sql file) or you will need to modify the package and replace all references to the UPLOAD_DIR Directory Object with a hard-coded directory path. 

In my opinion it is never a good idea to use hard-coded values in code because the cost of changing these hard-coded values is too great and it is a maintenance nightmare.  By using an Oracle Directory Object, if you need to change the location on the database server where file attachments reside, it is a simple update to the database object rather than modify multiple lines of code.

Once you have made these changes you are ready to compile the email_pkg package in your database.  The user that will own the email_pkg must have execute privileges to the UTL_SMTP and UTL_ENCODE Oracle packages in order to successfully create this package in your database.

Example of how to use email_pkg.

DECLARE

            V_Message_Body   VARCHAR2(4000);

BEGIN

            V_message_body := ‘This is a sample email message body.’);

            Email_pkg.set_from(‘donot_reply@some_server.com’);

            Email_pkg.set_recipient(‘recipient1@email_address.com’);

            Email_pkg.set_cc(‘CarbonCopy@email_address.com’);

            Email_pkg.set_bcc(‘blindCC@email_address.com’);

            Email_pkg.set_subject(‘This is a sample email subject’);

            Email_pkg.set_message(v_message_body);

            Email_pkg.send;

END;

Download Email Package Script

Read / Write Text files using TEXT_IO

Question:  I want to be able to read and write to a PC client using text_io.  I cannot use utl_file because I do not have Oracle database installed on the remote client.  How do I invoke text_io?

Answer:  The Oracle text_io package can write to the local machine rather than server side (such as utl_file).

Here is an example of using the text_io utility to write across platforms.  This is invoked from the Oracle server and writes to the connected remote PC client:

declare
l_out_file text_io.file_type;
begin
l_out_file := text_io.fopen( 'c:\text.txt', 'w' ); -- w is write, r is read
text_io.put (l_out_file, 'New text' );
text_io.new_line( l_out_file, 1 ); -- 1 new line
text_io.fclose( l_out_file );
end;/

Here is an example of using the text_io utility to read across platforms.

declare
l_out_file text_io.file_type;
linelog varchar2(32767);
begin
l_out_file := text_io.fopen( 'c:\text.txt', 'r' ); -- w is write, r is read
Loop
  text_io.get_line( l_out_file, linelog ); -- get line
  Insert into tmptable(linelog);
End Loop;
text_io.fclose( l_out_file );
Commit;
end;/

Hide / Encrypt username and password while calling Oracle Reports

If you have not configured reports with Oracle Single Sign-on, Oracle report server explicitly requires username and password when report is called using Web.Show_document(). Username and password is required in report calling URL, for example following method calls a reports using Web.Show_Documnet().

Web.Show_Document('http://domainname.com:8090/reports/rwservlet?userid=username/password@db& server=ReportsServer_1&desformat=PDF&destype=cache&report=report.rdf&paramform=yes','_blank');

In above call username and password are visible in URL, causing security problem. Oracle has provided several methods to resolve this problem, one of these solution is to define key-mapping in CGICMD.DAT file. In Reports services 11g this file is located at following location

$DOMAIN_HOME/servers/WLS_REPORTS/stage/reports/reports/configuration/cgicmd.dat

In Oracle Reports services 10g this file can be located in reports conf directory.

To define a key mapping, append following line at the end of the file

userlogin: userid=username/password@db %*

Restart reports server/Managed server, now you can call your report using following URL

Web.Show_Document('http://domainname.com:8090/reports/rwservlet?userlogin&server=ReportsServer_1&desformat=PDF&destype=cache&report=report.rdf&paramform=yes','_blank');

You can define key mapping for as many parameter as you need using following syntax,

userlogin: userid=username/password@db server=ReportsServer_1 desformat=PDF destype=cache %*

If reports have been configured with SSO, simply pass ssoconn=config parameter in reports calling URL, here config is the Resource Access Descriptor defined in OID. This parameter will automatically get login information from Oracle Internet Directory.

Another scenario can be built by through PL/SQL Code:

Create a procedure in Program Unit of Form Builder for encryption of database User ID and Password with below mentioned code:

function encrypt_logon_data return varchar2 is
l_user varchar2(200);
l_user_encrypt varchar2(300);
begin
l_user := lower ( GET_APPLICATION_PROPERTY( username ) || '/' ||
GET_APPLICATION_PROPERTY( password ) || '@' ||
GET_APPLICATION_PROPERTY( connect_string ) );
for i in 1..length( l_user ) loop
l_user_encrypt := l_user_encrypt|| '%' || LTRIM (TO_CHAR (ASCII (SUBSTR (l_user, i, 1)), 'XX'));
end loop;
return ( l_user_encrypt );
end;

Now use above procedure while calling reports. 

(For Example):

DECLARE
vrepid report_object;
vrep VARCHAR2 (100);
rep_url varchar2(2000);
BEGIN
rep_url:='LOCALHOST/reports/rwservlet?erver=Report_server&report=D:\SampleReport'
||'&desformat=pdf&destype=cache&userid='

|| encrypt_logon_data
|| '&paramform=No'
|| '&maximize=Yes' ;
web.show_document (rep_url,'_blank');
END;

Key Mapping in Oracle Forms

Key mapping or key binding defines how an application should respond when mapped key is typed. Usually application executes some function in repose. For example in Forms 6i, when user types F9, List of value is displayed to the user. It means that key F9 is mapped to function “List of Values”.

In oracle Forms this key mapping is defined in fmrweb.res file. Oracle provided a tool named “Oracle Terminal” in Forms 6i to customize key mapping. In Forms 10g and Forms 11g this file is directly editable. In Forms 11g this file can be located at following location

$Instance_home/config/FormsComponent/forms/admin/resource/US

In Forms 10, frmweb.res can be located in \forms directory

Enabling Key Mappings

A key binding connects a key to an application function. When you bind a key to a function, the program performs that function when you type that keystroke. You define key bindings in the fmrweb.res file in the ORACLE_HOME/admin/resource/<language directory> directory in UNIX, for example ORACLE_HOME/forms/admin/resource/US. For Windows, the location is ORACLE_HOME\forms.

By defining key bindings, you can integrate a variety of keyboards to make an application feel similar on each of them.On some platforms not all keys are able to be re-mapped. For example, on Microsoft Windows, because keys are defined in the Windows keyboard device driver, certain keys cannot be re-mapped. Key combinations integral to Windows, such as Alt-F4 (Close Window) and F1 (Help) cannot be re-mapped. As a general rule, keys which are part of the ÒextendedÓ keyboard also cannot be re-mapped. These keys include the number pad, gray arrow and editing keys, Print Screen, Scroll Lock, and Pause.

Note: If running with different NLS_LANG settings a different resource file will be used. e.g. NLS_LANG=GERMAN_GERMANY=WE8ISO8859P1 fmrwebd.res file will be used.There is a resource file for each supported language. To override this, pass parameter term=fullpath\filename.res to the Oracle Forms Runtime process.

It is possible to pass this parameter directly within the URL. For example:

http://hostname/forms/f90servlet?Form=test.fmx&term=fullpath\filename.res

You can also set this parameter in the formsweb.cfg file, for example:

otherParams=term=fullpath\filename.res

Customizing fmrweb.res

fmrweb.res is a text file which can edited with a text editor such as vi in UNIX or Notepad or Wordpad on Windows. Unlike Oracle 6i Forms, Oracle Terminal editor is no longer required. The text file is self-documented.

Note: The customization is limited, particularly compared to character mode forms. You cannot edit fmrweb.res with Oracle Enterprise Manager Application Server Control.

Example change: Swapping Enter and Execute Mappings

In the section marked USER-READABLE STRINGS, find the entries with

122 : 0 : "F11" : 76 : "Enter Query"

122 : 2 : "Ctrl+F11" : 77 : "Execute Query"

and change them to:

122 : 2 : "Ctrl+F11" : 76 : "Enter Query"

122 : 0 : "F11" : 77 : "Execute Query"

Note: By default fmrweb.res does not reflect the Microsoft Windows client-server keyboard mappings. It reflects the key mapping if running client-server on Unix X-Windows/Motif.

A file called fmrpcweb.res has also been provided which gives the Microsoft Windows client-server keyboard mappings. To use this file, rename fmrpcweb.res e.g to fmrweb_orig.res, and copy fmrpcweb.res to fmrweb.res. Alternatively use the term parameter as described above.

Exceptions/ Special Key Mappings

The following examples show special key mappings:

• Section 1, "Mapping F2"
• Section 2, "Mapping for ENTER to Fire KEY-ENTER-TRIGGER"
• Section 3, "Mapping Number Keys"
• Section 4, "Mapping for ESC Key to exit out of a Web Form"

Mapping F2

To map F2, change the default entry for F2, "List Tab Pages", to another key. Here is an example of the default entry:

113: 0 : "F2" : 95 : "List Tab Pages"

This must be explicitly changed to another key mapping such as the following:

113: 8 : "F2" : 95 : "List Tab Pages"

To map the F2 function to the F2 key, comment out the lines that begin with "113 : 0" and "113 : 8" with a # symbol and add the following lines to the bottom of the resource file:

113: 0 : "F2" : 84 : "Function 2"

113: 8 : " " : 95 : " "

Since a new function has been added which uses F2 by default, it is necessary to explicitly map this new function to something else in order to map the F2 key. This function was added to allow for keyboard navigation between the tab canvas pages and it defaults to F2. Even if it is commented out and not assigned to F2, the F2 key cannot be mapped unless this function, Forms Function Number 95, is mapped to another key.

Mapping for ENTER to Fire KEY-ENTER-TRIGGER

By default, whether deploying client-server or over the web pressing the ENTER key takes the cursor to the next navigable item in the block. To override this default behavior it is necessary to modify the forms resource file to revise the key mapping details.

Modify FMRWEB.RES and change the Forms Function Number (FFN) from 27 to 75 for the Return Key. The line should be changed to the following:

10 : 0 : "Return" : 75 : "Return"

By default, the line is displayed with an FFN of 27 and looks as follows:

10 : 0 : "Return" : 27 : "Return"

This line should NOT fire the Key-Enter trigger since the Return or Enter key is actually returning the Return function represented by the FFN of 27. The FFN of 75 represents the Enter function and will fire the Key-Enter trigger.

Mapping Number Keys

The objective is to map CTRL+<number> keys in fmrweb.res for numbers 0 to 9 and there are no Java Function keys mentioned for the numbers in fmrweb.res. The steps to be performed along with an example that shows the steps needed to map CTRL+1 to 'Next Record'

1. List the java function key numbers that could be implemented in fmrweb.res file for the Key Mapping. For example:

   public static final int VK_1 = 0x31;
   

2. The hexadecimal values have to be converted to their decimal equivalents before their use in fmrweb.res.
   In step (1), 0x31 is a hexadecimal value that has to be converted to its decimal equivalent. (Note:1019580.6) e.g:

   
   SQL> select hextodec('31') from dual;
   HEXTODEC('31')
   --------------
   49
   
   
   
   

3. Use this decimal value for mapping the number key 1 in fmrweb.res For example, CTRL+1 can be mapped to 'Next Record' as:

   49 : 2 : "CTRL+1" : 67 : "Next Record"
   

Mapping for ESC Key to exit out of a Web Form

1. Make a backup copy of fmrweb.res
2. Open the fmrweb.res file present in the path ORACLE_HOME/FORMS and add the following entry in it:

   
   27 : 0 : "Esc" : 32 : "Exit"
   
   
   
   

3. Ensure that you comment or delete the old entry

   
   #115 : 0 : "F4" : 32 : "Exit"
   
   
   
   

   The first number (115) might differ on different versions or platforms. When you run the Web Form and press the ESC key, then the Form will exit.

LOV - List of values with split data

An easy technique to visualize data in LOV's like this:



Use multiple UNION ALL's to concatenate the data :

select '---new colleagues---' ename, NULL job, NULL hiredate
 from dual
UNION ALL
select ename, job, to_char (hiredate, 'DD.MM.YYYY')
 from emp where hiredate >= to_date ('01.07.1981', 'DD.MM.YYYY')
UNION ALL
select '---before 07/81---' ename, NULL job, NULL hiredate
 from dual
UNION ALL
select ename, job, to_char (hiredate, 'DD.MM.YYYY')
 from emp where hiredate < to_date ('01.07.1981', 'DD.MM.YYYY')

Compile or Compile All ?

What is the best way to compile a form? Before I answer this question here are some explanations:

Compile Incremental      :         Ctrl + K
Compile All              : Shift + Ctrl + K
Compile Module (Generate):         Ctrl + T
Run                      :         Ctrl + R

In the older versions of Forms the Compile Module was known as Generate. I prefer this, because the Ctrl+T generates the FMX.

During my daily work I open forms and maintain them. If I use the Ctrl+T to generate the FMX, then Oracle Forms starts implicitly a Compile Incremental before the Generate.

And that's the problem. In 9 of 10 cases the generated FMX is OK, but sometimes the automatically Compile Incremental didn't work properly. It results in non-reproducible errors at run-time.

My solution for this problem is:

After opening a form I start immediately a Compile All. Each Incremental Compile and each Generate now works without runtime-problems.

Faster compilation in Forms Builder

You can speed up the time for a "Compile All", when you close all nodes in the Object Navigator, so that the form shows only the name of the form. Then you press Ctrl+Shift+K for Compile All.

Compile-Times of a normal pl/sql-library with 70 program units

closed nodes :  2 sec
open nodes   : 18 sec

Compile-Times of a big pl/sql-library with 130 program units

closed nodes :  2 sec
open nodes   : 34 sec

Compile-Times of a medium form with 14 blocks

closed nodes :  3 sec
open nodes   : 12 sec

Compile-Times of a big form with 24 blocks and much sourcecode

closed nodes :  6 sec
open nodes   : 37 sec

With this little trick you can compile really fast !