IndahnyaBerbagi

Applies to:

Goal

Solution

The business requirements appear to be straight-forward but it is not easy to implement in forms
due to the fact that COMMIT_FORM built-in is restricted e.g coding a POST-RECORD trigger as follows:

if :SYSTEM.RECORD_STATUS in ( 'CHANGED', 'INSERT' ) then
if Disp.YesNo ( 'Save ?' ) then
commit_form;
else
raise form_trigger_failure;
end if;
end if;

This does not work because COMMIT_FORM is a restricted built-in and cannot be called in a
POST-RECORD trigger.

A solution is to use a timer so the commit_form is executed outside of triggers like WHEN-VALIDATE-RECORD, POST-RECORD in which the built-in is restricted.

Example Code as follows (This simple example works in a Master Detail form based on demo tables DEPT and EMP)

1. Create
an alert called MYCOMMIT
Properties:

Message: Do you want to save the changes?
Alert Style: Stop
Button 1 Label : Yes
Button 2 Label: No

2. Code a WHEN-VALIDATE-RECORD trigger at form level

DECLARE
timer_id Timer;
/* set timer to a very lower value - number is in milliseconds */
timer_period NUMBER(5) := 60;
alert_button NUMBER;
BEGIN
IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
/* Display custom alert */
alert_button := SHOW_ALERT('mycommit');
IF alert_button = ALERT_BUTTON1 THEN
/* User has clicked 'Yes' to save changes, start timer when timer expires the commit_form will
execute outside of the WHEN-VALIDATE-RECORD trigger in the WHEN-TIMER-EXPIRED trigger */
timer_id := CREATE_TIMER('emp_timer', timer_period, NO_REPEAT);
END IF;
IF alert_button = ALERT_BUTTON2 THEN
/* User has clicked 'No', therefore raise form trigger failure so cursor remains in the current
record / item */
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
END;

commit_form;

A.) This note documents how to change the 'Do you want to save the changes you
have made?' alert that is displayed when exiting an application when there is
uncommited changes on the form.

By default, when exiting a form, Forms checks the status of the form, and
if there are uncommited changes, will display the alert asking if the user
wants to commit the data. Then, depending on the choice the user makes, forms
will commit or rollback the changes and exit the form, or do nothing if cancel
is chosen.

In order to customise this behaviour it is necessary to perform this
functionality yourself. The following steps are necessary to implement the
customised box:-

1: When exiting form, Check the form status.

2: If the status is changed, then display the customised alert.

3. Depending upon the user input either:-
a: commit the changes and exit the form.
b: Exit the form without saving the changes.
c: Do nothing if the user has canceled exiting the form.

The Code
--------

Create an alert ASK-COMMIT with the following attributes:-

Message: 'There are unsaved changes. Save them?'
button1: 'YES'
button2: 'CANCEL'
button3: 'NO'
Alert Style: 'CAUTION'

Then, in a KEY-EXIT trigger at form level, enter the following code:-
---------------------------------------------------------------------------

declare
button_val number;
begin
-- Perform validation at item level to update :SYSTEM.FORM_STATUS.
validate(ITEM_SCOPE);
-- Check the record status
if(:SYSTEM.FORM_STATUS = 'CHANGED') then
button_val := show_alert('ask_commit');
if button_val = ALERT_BUTTON1 then
-- If user chooses to commit changes...
commit_form;
exit_form;
elsif button_val = ALERT_BUTTON2 then
-- Do nothing if user cancels...
null;
elsif button_val = ALERT_BUTTON3 then
-- Exit without committing...
Exit_form(No_commit);
end if;
else
-- There have been no changes, just exit...
exit_form;
end if;
end;

----------------------------------------------------------------------------

Notes
-----

The validate(ITEM_SCOPE) line at the start is a useful bit of code that
updates the :SYSTEM.FORM_STATUS, :SYSTEM.BLOCK_STATUS or :SYSTEM.RECORD_STATUS.

In the example code above, if a user has entered some data and then tried to
exit without navigating out of the field, The form status will still be
'QUERY'. The validate built-in ensures the form status will be 'CHANGED'.

This code is generic and can be used in other places such as before issuing a
CALL_FORM in a WHEN-BUTTON-PRESSED trigger in order to get a user to
commit/rollback changes before navigating to a new form.
----------------------------------------------------------------------------


B.) HOW TO SUPPRESS "DO YOU WANT TO SAVE CHANGES" PROMPT IN MASTER-DETAIL?


Problem Description:
====================

A form is run that has a master and detail block. The user may navigate to
the detail block and make a change and then navigate back to the master block
and try to go to the next master record.

Oracle Forms 4.x or higher recognizes that the detail block has been changed and prompts
the user:

Do you want to save the changes you have made?

SQL*Forms 3.0 recognizes that the detail block has been changed and prompts
the user:

Do you want to commit the changes you have made?


Problem Explanation:
====================

How can you suppress this message/prompt? The user wants to automatically go
down to the next master record regardless of any changes made in the detail
block.



Solution Description:
=====================

Oracle Forms 4.x or higher:
---------------------------

Modify the CLEAR_ALL_MASTER_DETAILS procedure body. Change the existing
CLEAR_BLOCK(ASK_COMMIT) to CLEAR_BLOCK(DO_COMMIT).

SQL*Forms 3.0:
--------------
Modify the procedure CLEAR_DETAILS. Change the existing CLEAR_BLOCK(OPT) to
CLEAR_BLOCK(DO_COMMIT).

**Note: This is the quicker way to change the code. Trigger KEY-DELREC calls
clear_masterblockname_details(FALSE, NO_COMMIT) which in turn executes
clear_details('detailblockname', 'masterblockname', opt). If you modify the
clear_details procedure, it will always do clear_block(do_commit) since it is
hardcoded. The better way to change the functionality would be to change all
the trigger text to do clear_masterblockname_details(TRUE, NO_COMMIT) instead
of (TRUE, ASK_COMMIT) in triggers key-clrblk, key-clrrec, key-crerec,
key-down, key-entqry, key-exeqry, key-nxtrec, key-nxtset, key-prevrec,
key-scrdown, key-scrup and key-up. If you modify the procedure clear_details
to do clear_block(no_commit) then you just need to change the code in the
procedure.

Solution Explanation:
=====================

When you create a master-detail relationship form, certain triggers and
procedures are automatically created for you. To change the default
functionality, you need to change the clear_block statement.

The default parameter for CLEAR_BLOCK is ASK_COMMIT.

The options for clear_block are:

o ask_commit - Oracle Forms prompts the operator to commit the
changes during clear_block processing.
o do_commit - Oracle Forms validates the changes, performs a commit,
and flushes the current block without prompting the
operator.
o no_commit - Oracle Forms validates the changes and flushes the
current block without performing a commit or prompting
the operator.
o no_validate - Oracle Forms flushes the current block without
validating the changes, committing the changes, or
prompting the operator.


C.)HOW TO GET FROM NORMAL TO EXECUTE QUERY MODE WITHOUT BEING PROMPTED BY FORMS?

Problem Description:
====================

You are receving the following message from Oracle Forms Runtime when you try
to get into Enter Query mode or use Execute Query:

'Do you want to commit the changes you have made?'


Problem Explanation:
====================

How do I get into Query mode from normal processing mode to perform an Enter
Query or Execute Query without being prompted with this message? In normal
processing mode you can insert and update data in a block.

Solution Description:
====================

Use the Oracle Forms CLEAR_BLOCK built-in with commit mode DO_COMMIT,
NO_COMMIT or NO_VALIDATE before calling ENTER_QUERY or EXECUTE_QUERY.

For Example:
------------

You want to put the user in Enter Query mode or Execute a Query by pressing a
button. You have two buttons on your form: ENTER_QUERY_BUTTON and
EXEC_QUERY_BUTTON.

Create the following When-Button-Pressed triggers:

Item Level When-Button-Pressed trigger on ENTER_QUERY_BUTTON:
BEGIN
CLEAR_BLOCK(NO_COMMIT);
ENTER_QUERY;
END;

If you wish to save a value in one of the items in the block to include it as
query criterion for the EXECUTE_QUERY, also create a Pre-Query trigger.

Item Level When-Button-Pressed trigger on EXEC_QUERY_BUTTON:
BEGIN
/* Save the form item value in a global variable to use in */
/* Pre-Query trigger. */
:GLOBAL.item_name := :block.fieldname;

CLEAR_BLOCK(NO_COMMIT);
EXECUTE_QUERY;
END;

Block Level Pre-Query trigger:
BEGIN
:block.fieldname := :GLOBAL.item_name;
END;


Make sure that the Mouse Navigate property = FALSE for both buttons, otherwise
FRM-40202 will occur when the button is pressed.


Solution Explanation:
====================

Specifying NO_COMMIT or NO_VALIDATE with the CLEAR_BLOCK built-in causes
Oracle Forms to flush the current block without performing a commit
or prompting the operator.

Please be aware that any changes made to the data in the block will be cleared
and NOT SAVED to the database.


.

Locking Within SQL*Forms 3.0


SQL*Forms performs implicit locks on database blocks so that the users need
not code their own routines for data consistency. SQL*Forms also provides
the ON-LOCK trigger and LOCK_RECORD packaged procedure to allow further
control of the locks performed within the application.

With RDBMS V6.0 and higher, row level locking is now performed. Whole
table locks are no longer necessary to guarantee data integrity. It is
possible to lock an entire table (using the LOCK TABLE SQL command) but
this is usually undesirable since other users will not be able to update
any records within the table until the lock is released, either through
a COMMIT or a ROLLBACK.

SQL*Forms Use of Locking
~~~~~~~~~~~~~~~~~~~~~~~~

By default, data queried into a database block is not locked, unless an
ENTER_QUERY(FOR_UPDATE) or EXECUTE_QUERY(FOR_UPDATE) is used. The data
displayed is a snapshot of the data in the database at the time of the
query. The actual data can be changed "underneath" the user. The values
of the records obviously do not change as they are being viewed. Only
a subsequent requery will show any data that has been changed by other
users.

Let's say we have two users on the system, A and B. Both query the same
record on the screen. If user A types a character into any of database
fields, a

SELECT { all database fields in the block }
FROM { table which the block is based on }
WHERE rowid = { current row }
FOR UPDATE OF { all database fields in the block }

is performed. The FOR UPDATE option attempts to lock the record selected
so that no other users may change the information at the same time. If
the record is already locked, then the following error occurs:

Attempting to reserve record for update or delete (^C to cancel)...

or on some Unix platforms:

Could not reserve record (3 tries). Continue? ( Yes ) ( No )

Another "locking" issue that can occur is if user A and B query the same
record and user A modifies it and commits the change. User B, has a snapshot
of the "old" data on the screen. Obviously, B should not be allowed to
change the data since B has an "outdated" representation of the data. If
user B attempts to update the record, the following error message results:

FRM-40654: Record changed by another user. Re-query to see change.

No modification of the record can be done at this point until the record is
requeried to show the new changed data.

To review: when a user updates a record, SQL*Forms issues a SELECT ...
FOR UPDATE to determine if the record is currently locked. If it is not,
then SQL*Forms compares the values on the form to the values currently
in the database. If they are equal, then the record has not been changed
"underneath" the user. If they are not equal, then another user has committed
a change.



Locking Facilities Within SQL*Forms 3.0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The ON-LOCK trigger fires whenever SQL*Forms would normally attempt to
lock a row. The default functionality for this trigger is LOCK_RECORD;
Users may modify the ON-LOCK trigger to :

* bypass lock attempts on single user systems.

* lock the entire table by issuing the LOCK TABLE command.

* lock other rows in different tables as necessary.

The LOCK_RECORD packaged procedure "attempts to lock the row in the
database that corresponds to the current record." Other situations which
will cause a lock to be performed:

* Modification of database field on queried data

* ENTER_QUERY(FOR_UPDATE), EXECUTE_QUERY(FOR_UPDATE)

* DELETE_RECORD

Note that in block mode, the edit is allowed but SQL*Forms will place
an exclusive lock on the row during record validation. The ENTER_QUERY
(FOR_UPDATE) and EXECUTE_QUERY(FOR_UPDATE) lock only the records that have
actually been fetched (SQL*Forms queries in batches of records when they
are needed as specified by the ARRAY SIZE). The rest of the records can be
locked by either scrolling through the records or performing a LAST_RECORD
command. Note that an ENTER_QUERY(ALL_RECORDS,FOR_UPDATE) will fetch and
lock all the records satisfying the query conditions immediately.

Locks are released when:

* A commit performs successfully

* A full rollback (rollback to savepoint does NOT)

* The form aborts.

Note that unlike version 5, a CLEAR_RECORD or CLEAR_BLOCK does not
release any of the locks that may have been issued.


Using PL/SQL for greater control:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A common request of users is to be able to tell whether a row has been
locked or not, since the "Record Locked" message cannot be trapped via
ON-ERROR/MESSAGE triggers. The below PL/SQL block (written by Steve
Muench) takes advantage of the PL/SQL PRAGMA EXCEPTION which allows the user
to define their own exception based on the ORACLE error code. The -54
below results when an attempt is made to lock an already locked record.


DECLARE
dummy CHAR(1);
could_not_obtain_lock EXCEPTION;
PRAGMA EXCEPTION_INIT (could_not_obtain_lock,-54);
BEGIN
SELECT 'X' INTO dummy
FROM emp
WHERE rowid= :emp.rowid
FOR UPDATE OF empno NOWAIT;
MESSAGE('Locked the Current Row Successfully');
EXCEPTION
WHEN could_not_obtain_lock THEN
MESSAGE('Record is Already Locked. Please Try Later ...');
RAISE form_trigger_failure;
END;

The PL/SQL User's Guide and Reference contains information on user defined
exceptions using EXCEPTION_INIT.

Possibly Unexpected Results With Locking
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the same record is queried and modified from two blocks on the same form
(therefore the same user) only the change on the block with the highest
sequence number in the block definition will be committed to the database.

a) a block level PRE-INSERT/UPDATE/DELETE modifying a database field in
a previous block. What happens is that during a commit, SQL*Forms
processes information block by block in the same order as in the
block definition screen. One or many SQL statements are produced for
each database block that has at least one database field changed.
When it finishes the SQL statement for the current block it moves
on to the next block. The PRE- triggers also fire on a block by block
basis and thus it is possible to modify a field using a PRE- trigger
on a previously processed block. In other words, the SQL statement
produced may be inaccurate. For example, a form has two database
blocks ONE and TWO that has an update ( :one.field='X') and an insert
pending respectively. When a COMMIT is performed, the PRE-UPDATE is
fired for block ONE and the SQL Statement is produced.

UPDATE one SET column=:one.field WHERE rowid= { current rowid }
^
= 'X' (the actual value)

Then SQL*Forms moves on to the next block and fires its PRE-INSERT.
If the PRE-INSERT contains code like:

:one.field := 'Y';

Then the error FRM-40654 will appear if the user attempts to update
block ONE after the COMMIT. The reason is that by the time PRE-INSERT
fires, block ONE's SQL Statement and block status have already been
set. What actually gets committed is the 'X' value even though 'Y'
shows up on the screen. If the user subsequently attempts to modify
block ONE, a

SELECT { fields in block ONE }
FROM one
WHERE rowid= { the rowid }
FOR UPDATE OF {fields in block ONE }

will be successful (since the record is no longer locked after the
commit). However, SQL*Forms will give the error after it determines
that the representation within the database ('X') does not match with
the current field value. It is not recommended to change fields on
different blocks in PRE-INSERT/DELETE/UPDATE triggers.

b) Another situation can arise with number precision. This can
probably best be described by an example. If table "x" contains a
column of type NUMBER (5,2). Then, create a form accessing that
field and type in a number with greater precision, (e.g. 10.243).
Commit the information and then attempt to edit the field. FRM-40654
results since the value that gets stored to the database (10.24)
is not equal to the value on the form (10.243).

c) A more complicated situation is documented in PCR # 93159.
Two users A and B query up the same record. User A modifies and
commits record. User B attempts to modify but gets correctly the
FRM-40654 error. However, if user A attempts to modify his/her
record, the error "Attempting to Reserve Record for Update/Delete"
occurs. The reason is that when User B attempts to modify the
record, a SELECT ... FOR UPDATE is issued first. B locks the
record successfully since A has already committed the change.
However, SQL*Forms then compares the field values and finds that
the representation within the database (modified by A) is different
from B's representation, thus the "Record has been changed" error.
But, the lock is NOT released by B. Thus if A attempts to remodify
the record he/she had just modified, they will be locked out!

d) A strange situation can also occur by clearing a lock in a called form
on an unsuspecting record in the calling form. Given that the calling
form is named FORM_A and the called form is named FORM_B, user A may
make a change in one of the database fields and CALL FORM_B before
validating the field (and therefore letting forms recognize the
change). The lock created by the first keystroke in that field is
still preserved. Note that a commit in FORM_B will now clear that
lock. User A now has misrepresented data on the screen. If a user
B modifies the same record, whoever commits last will have their
change committed. The workaround is to let SQL*Forms be aware that
a change has been made since SQL*Forms does not force validation on
CALL's and SET_INPUT_FOCUS(MENU)'s. Before the CALL, an ENTER; or
some navigation packaged procedure can be invoked to force validation.
The user will then receive the correct message in FORM_B on commit:

FRM-40403: A calling form has un-posted changes. Commit not allowed.

DECLARE
errm VARCHAR2(800);
errcode NUMBER := ERROR_CODE;
errtxt VARCHAR2(800) := ERROR_TEXT;
errtyp VARCHAR2(3) := ERROR_TYPE;

-- dbmserrcode NUMBER; --:= DBMS_ERROR_CODE;
dbmserrtext VARCHAR2(200) := DBMS_ERROR_TEXT;

v_alert_bttn NUMBER;
BEGIN
--FRM-40202: Field must be entered.
--FRM-40400: Transaction complete: %d records applied and saved.
--FRM-40401: No changes to save.
--FRM-40405: No changes to apply.
--FRM-40501: ORACLE error: unable to reserve record for update or delete.
--FRM-40508: ORACLE error: unable to INSERT record.
--FRM-40509: ORACLE error: unable to UPDATE record.
--FRM-40600: Record has already been inserted.
--FRM-40654: Record has been updated by another user. Re-query to see change.
--FRM-40656: Update cannot be made due to prior rollback. Clear the record.
--FRM-40700: No such trigger: %s.
--FRM-40800: User exit %s does not exist.
--FRM-41050: You cannot update this record.
--FRM-41830: List of Values contains no entries.
--FRM-50016:Legal character are 0-9- +E.

Set_Alert_Property ('ALERT2', TITLE, 'Error');
-- Error Insert and Update
IF errcode IN (40501, 40508, 40509, 40600, 40656) THEN
Set_Alert_Property ('ALERT2', Alert_Message_Text,
errtyp ||'-'||TO_CHAR(errcode)||': '||errtxt || CHR(10) ||
DBMS_ERROR_TEXT);
v_alert_bttn := Show_Alert ('ALERT2');
ELSIF errcode IN (40654, 40700,40800,40401,41050,40202,40405,40400) THEN
-- ignore fnd user exit n invalid trigger message
null;
ELSIF errcode IN (41830) THEN
Set_Alert_Property ('ALERT2', Alert_Message_Text,
'List of Values contains no entries.');
v_alert_bttn := Show_Alert ('ALERT2');
ELSE
APP_STANDARD.EVENT('ON-ERROR');
END IF;
END;

pesan('User ID '||Fnd_Global.USER_ID||chr(13)||chr(10)||'User Name '||Fnd_Global.USER_NAME);

Purpose
-------

This article shows how to send email using Java Stored Procedures (JSP)
and the Sun Microsystems JavaMail package. Attachments can also be sent.

****THIS NOTE IS ONLY SUPPORTED AGAINST AN 8I DATABASE AND NOT SUPPORTED
WHEN RUNNING WITHIN A 9I OR 10G DATABASE because the J2EE features
were deprecated with 9i so Java Mail went away. ****


Caution
-------

The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.


Scope & Application
-------------------

This article can be used as a guideline by any programmer who needs to
implement the functionality of sending e-mail from the Oracle 8i database.

****THIS NOTE IS ONLY SUPPORTED AGAINST AN 8I DATABASE AND NOT SUPPORTED
WHEN RUNNING WITHIN A 9I OR 10G DATABASE****

Introduction
------------

This article references JavaMail, a Java package provided by Sun Microsystems
that implements the mail functionality. For additional details, refer to:

http://java.sun.com/products/javamail

An interface to JavaMail is presented below in the form of a Java Stored
Procedure. Installing the Java class files using the loadjava utility is
also presented.


Advantages of JavaMail over mime++
----------------------------------

- No need for C++ compiler licenses
- Required Java class files are contained inside the database; no reliance on
external libraries
- Portability of JavaMail to any platform where Oracle 8i is available
- Easier to interface between PL/SQL and Java, than PL/SQL, C, and C++
- Everything can be compiled from one SQL*PLUS script
- JavaMail is free software provided by Sun Microsystems
- Robust and well-designed class suite
- Documentation available about JavaMail


Installation
------------

Download JavaMail (v1.1.3 or later) from http://java.sun.com/products/javamail
and the Javabeans(tm) Activation Framework. The archive consists of
documentation, demos, and a .jar file containing the required java classes.
Those .jar files must be extracted and loaded into the server using the
loadjava utility.

loadjava -user user/password -resolve -synonym activation.jar

loadjava -user user/password -resolve -synonym mail.jar

** Warning **
-------------------------------------------------------------------------

If you are loading classes into Oracle V816, you will most likely receive a
verifier warning during the loadjava process. The error appears as follows:

ora-29552: verification warning: at offset 12 of void (java.lang.String,
java.lang.String): cannot access class$java$io$InputStream
verifier is replacing byte code at void java.lang.String,
java.lang.String):12 by a throw
...
...

This is a Sun Microsystems bug that was uncovered when Oracle upgraded its
JDK from version 1.1 (Oracle V815) to 1.2.1 (Oracle V816).

This is only a warning: the classes will load. Our limited testing of this
mail package has not produced any runtime errors resulting from this bug,
however, you should test this thoroughly before relying on it.

-------------------------------------------------------------------------


************************************************************************
THIS NOTE IS ONLY SUPPORTED AGAINST AN 8I DATABASE AND NOT SUPPORTED
WHEN RUNNING WITHIN A 9I OR 10G DATABASE
************************************************************************


Once the classes have been loaded, you may need to resolve permission issues
using the following statements:

-- Grant needed permissions based on the Java 2 Security Standard

-- Allows access to system properties
exec dbms_java.grant_permission('SCOTT',
'java.util.PropertyPermission',
'*',
'read,write');

-- Allows access to sockets
exec dbms_java.grant_permission('SCOTT',
'java.net.SocketPermission',
'*',
'connect, resolve');

-- Allows access to local files
exec dbms_java.grant_permission('SCOTT',
'java.io.FilePermission',
'/dir/dir/attachments/*',
'read, write');

Next, the following SQL*PLUS script should be executed. It simply creates a
Java class named SendMail with only one member function called Send(), and a
PL/SQL package SendMailJPkg. These form an interface to JavaMail. At the
end of the script, an anonymous PL/SQL block tests the whole program.

-- SendMail with attachment, the java way;
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SendMail" AS
import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail {
// Sender, Recipient, CCRecipient, and BccRecipient are comma-
// separated lists of addresses;
// Body can span multiple CR/LF-separated lines;
// Attachments is a ///-separated list of file names;
public static int Send(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[],
String Attachments) {

// Error status;
int ErrorStatus = 0;

// create some properties and get the default Session;
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);

try {
// create a message;
MimeMessage msg = new MimeMessage(session);

// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}

// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,
TheAddresses);
}

// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,
TheAddresses);
}

// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,
TheAddresses);
}

// subject field;
msg.setSubject(Subject);

// create the Multipart to be added the parts to;
Multipart mp = new MimeMultipart();

// create and fill the first message part;
{
MimeBodyPart mbp = new MimeBodyPart();
mbp.setText(Body);

// attach the part to the multipart;
mp.addBodyPart(mbp);
}

// attach the files to the message;
if (null != Attachments) {
int StartIndex = 0, PosIndex = 0;
while (-1 != (PosIndex = Attachments.indexOf("///",
StartIndex))) {
// create and fill other message parts;
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex,
PosIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
PosIndex += 3;
StartIndex = PosIndex;
}
// last, or only, attachment file;
if (StartIndex < Attachments.length()) {
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
}
}

// add the Multipart to the message;
msg.setContent(mp);

// set the Date: header;
msg.setSentDate(new Date());

// send the message;
Transport.send(msg);
} catch (MessagingException MsgException) {
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) !=
null)
ErrorMessage[0] = ErrorMessage[0] + "\n" +
TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
}
/
show errors java source "SendMail"

CREATE OR REPLACE PACKAGE SendMailJPkg AS
-- EOL is used to separate text line in the message body;
EOL CONSTANT STRING(2) := CHR(13) || CHR(10);

TYPE ATTACHMENTS_LIST IS
TABLE OF VARCHAR2(4000);

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING DEFAULT '',
BccRecipient IN STRING DEFAULT '',
Subject IN STRING DEFAULT '',
Body IN STRING DEFAULT '',
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST DEFAULT NULL)
RETURN NUMBER;
END SendMailJPkg;
/
show errors

CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS

PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
AttachmentList OUT VARCHAR2) IS
AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
BEGIN
-- boolean short-circuit is used here;
IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
AttachmentList := Attachments(Attachments.FIRST);

-- scan the collection if there is more than one element. If there
-- is not, skip the next part for parsing elements 2 and above. If there
-- is, skip the first element since it has been already processed

IF Attachments.COUNT > 1 THEN

FOR I IN Attachments.NEXT(Attachments.FIRST) .. Attachments.LAST LOOP
AttachmentList := AttachmentList || AttachmentSeparator ||
Attachments(I);
END LOOP;
ELSE
-- whe have to terminate the list with the one element with /// for the java function

AttachmentList := AttachmentList || AttachmentSeparator;
END IF;
ELSE
AttachmentList := '';
END IF;
END ParseAttachment;

-- forward declaration;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER;

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail(SMTPServerName,
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;

-- JSendMail's body is the java function SendMail.Send();
-- thus, no PL/SQL implementation is needed;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'SendMail.Send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String[],
java.lang.String) return int';
END SendMailJPkg;
/
show errors

var ErrorMessage VARCHAR2(4000);
var ErrorStatus NUMBER;

-- enable SQL*PLUS output;
SET SERVEROUTPUT ON
-- redirect java output into SQL*PLUS buffer;
exec dbms_java.set_output(5000);
BEGIN
:ErrorStatus := SendMailJPkg.SendMail(
SMTPServerName => 'gmsmtp03.oraclecorp.com',
Sender => 'cesare.cervini@oracle.com',
Recipient => 'ccervini@ch.oracle.com',
CcRecipient => '',
BccRecipient => '',
Subject => 'This is the subject line: Test JavaMail',
Body => 'This is the body: Hello, this is a test' ||
SendMailJPkg.EOL || 'that spans 2 lines',
ErrorMessage => :ErrorMessage,
Attachments => SendMailJPkg.ATTACHMENTS_LIST(
'/export/home/osupport/on.lst',
'/export/home/osupport/sqlnet.log.Z'
)
);
END;
/
print


Conclusion
----------

This article shows the real benefit behind the whole Java concept: portability
and easy deployment.


Related Documents
-----------------

Note 73321.1 "Mail Processing from within JSP / PL/SQL" (another approach)

http://java.sun.com/products/javamail

JavaMail and JavaBean Extension Framework documentation from Sun Microsystems.

Java Stored Procedures Developer's Guide (8.1.6)

Java Developer's Guide (8.1.6)

Problem Description
-------------------

You want to select multiple values in your LOV.


Solution Description
--------------------

It is not possible with the standard list of values to select multiple values.
You can do this by creating your own lov in forms.
In the next example we will create an LOV with the same look and feel
as the standard LOV of forms

Your screen, during runtime, should look like this :

search item
-------------------
| |
-------------------

list5 item
-------------------
| |
| |
| |
| |
| |
| |
| |
| |
------------------

------ ---- --------
|find| |ok| |cancel|
------ ---- --------

Items on your form :
* two list items : list5 and list6
list6 List Style = TList
visible = NO
Elements in List => put one item with space
list5 List Style = TList
visible = YES
Elements in List => put one item with space

* textItem : search
Initial value = %

* 3 buttons : find, ok and cancel
* 2 parameters : x_postion and y_position
* record group : empgroup
select distinct to_char(empno), ename from emp
* 2 visual attributes : color and default
for default leave all the properties to unspecified
for color change the background color
* Put all items on a canvas
1. Create canvasX
2. Select all items (position on first - hold shift -
select last item)
3. Press F4 or select Tools -> Property Palette from the menu
4. Put canvas property to canvas X
* Put the labels on the button and position items on canvas
* save your form as 'LOVFORM'
* Put the form in the formsxx_path or later hardcode path in
the key-listval trigger in your calling form.


Triggers :

When-new_form-instance

DECLARE
Error_Flag number;
BEGIN
set_window_property('WINDOW1',X_POS,to_number(:PARAMETER.x_position));
set_window_property('WINDOW1',Y_POS,to_number(:PARAMETER.y_position)+30);
Clear_List('list5');
Clear_List('list6');
Error_Flag := POPULATE_GROUP('EMPGROUP');
IF Error_Flag = 0 THEN
POPULATE_LIST('list5', 'EMPGroup');
ELSE
MESSAGE('Error while populating list group');
END IF;
END;


/* for list5*/
When-list-changed

declare
total_list_count number;
i number;
double number;
begin
double := 0;
i := 1;
total_list_count := Get_List_Element_Count('list6') + 1;
while i < total_list_count loop
if :list5 = Get_List_Element_value('list6',i) then
double := 1;
delete_list_element('list6',i);
end if;
exit when double = 1;
i := i + 1;
end loop;
if double = 0 then
total_list_count := Get_List_Element_Count('list6');
total_list_count := total_list_count + 1;
Add_List_Element('list6',total_list_count,:list5,:list5);
Set_Item_Property('list5',VISUAL_ATTRIBUTE,'color');
else
Set_Item_Property('list5',VISUAL_ATTRIBUTE,'default');
end if;
end;


/* for find button */
When-button-pressed

DECLARE
errcode NUMBER;
thequery varchar2(100);
BEGIN
thequery := 'select to_char(empno), ename from emp where empno like '''
|| :search || '%'||''' order by empno';
errcode := populate_Group_with_Query( 'EMPGROUP',thequery );
clear_list('list5');
populate_list('list5','empgroup');
END;


/* for ok button */
When-button-pressed

declare
total_list_count number;
i number;
my_elements varchar2(250);
begin
i := 1;
total_list_count := Get_List_Element_Count('list6') + 1;
while i < total_list_count loop
my_elements := my_elements || ','||Get_List_Element_value('list6',i);
i := i + 1;
end loop;
:global.my_elements := substr(my_elements,2);
exit_form;
end;


/* for cancel button */
When-button-pressed

exit_form;


Once you have done this you can call this form in the key-listval
of another form.

Create new form with one item : text_item

Triggers :

When-window-activated :

:text_item := :global.my_elements;


When-new-form-instance

:global.my_elements := ' ';


/* for text_item */
Key-Listval

declare
pl_id ParamList;
pl_name varchar2(10) := 'tempdata';
x_position number;
y_position number;
begin
/* Get position of item*/
x_position := get_item_property(:system.cursor_item,X_POS);
y_position := get_item_property(:system.cursor_item,Y_POS);

/* Create parameter list to give position to lov form*/
pl_id := Get_Parameter_List(pl_name);
IF not Id_Null(pl_id) THEN
destroy_parameter_list(pl_id);
end if;
pl_id := Create_Parameter_List(pl_name);
IF Id_Null(pl_id) THEN
Message('Error creating parameter list '||pl_name);
RAISE Form_Trigger_Failure;
END IF;
Add_Parameter(pl_id,'x_position',TEXT_PARAMETER,to_char(x_position));
Add_Parameter(pl_id,'y_position',TEXT_PARAMETER,to_char(y_position));

open_form('lovform', ACTIVATE, NO_SESSION,NO_SHARE_LIBRARY_DATA, pl_id);
end;

declare
type tobj is RECORD (
ld_id number,
qty number);
type tstrarr is table of tobj index by binary_integer;
strarr tstrarr;
begin
strarr(1).ld_id:='1';
strarr(1).qty:='11';
strarr(2).ld_id:='2';
strarr(2).qty:='22';
for arr in 1..strarr.count loop
dbms_output.put_line(strarr(arr).ld_id);
dbms_output.put_line(strarr(arr).qty);
end loop;
end;