IndahnyaBerbagi

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;