Home » Infrastructure » Windows » ORACLE error 29277 in FDPSTP Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation ORA-065
ORACLE error 29277 in FDPSTP Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation ORA-065 [message #584927] |
Mon, 20 May 2013 09:07 |
|
Praveen.reddy
Messages: 1 Registered: May 2013 Location: Hydrabad
|
Junior Member |
|
|
hi all
i am new one this form and i got following error while Running email program it well get only particular email's only. i do't know utl_smtp program and how it's work. can any one tell me how to resolved this
this is my log message..
ORACLE error 29277 in FDPSTP Cause:
FDPSTP failed due to ORA-29277: invalid SMTP operation
ORA-06512: at "SYS.UTL_SMTP", line 80
ORA-06512: at "SYS.UTL_SMTP", line 210
ORA-06512: at "SYS.UTL_SMTP", line 688
ORA-06512: at "SYS.UTL_SMTP", line 699
ORA-0651 +--------------
and program is..
===========================
CREATE OR REPLACE PROCEDURE APPS.xxamhi_email_notif_proc (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_po_num VARCHAR2,
p_directory VARCHAR2,
p_file_name VARCHAR2,
p_file_typ VARCHAR2,
p_from VARCHAR2,
p_recipient VARCHAR2,
p_recipient_cc VARCHAR2,
p_recipient_cc1 VARCHAR2,
p_subject VARCHAR2,
p_val_errors VARCHAR2,
p_status VARCHAR2
)
-- RETURN VARCHAR2
AS
/*LOB operation related varriables */
l_buffer RAW (54);
l_amount BINARY_INTEGER := 54;
lv_error VARCHAR2 (1000);
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
l_blob_len INTEGER;
v_amount INTEGER;
v_fname VARCHAR2 (50);
line_no NUMBER;
temp_os_file BFILE;
ex NUMBER;
v_cnt NUMBER;
--p_po_num varchar2(10) := 2121300069;
/* This cursor will pick the two images for attachment in the mail. */
CURSOR c_po_attachment
IS
SELECT fl.file_name, fl.file_data --, file_content_type file_type
FROM fnd_lobs fl
WHERE fl.file_id IN (
SELECT DISTINCT fad.media_id
FROM fnd_attached_docs_form_vl fad,
po_headers_all pha
WHERE fad.pk1_value = pha.po_header_id
AND pha.segment1 = p_po_num
AND category_description LIKE 'Special Conditions'
AND datatype_name NOT IN
('Short Text', 'Long Text')
AND user_entity_name = 'PO Header');
/*UTL_SMTP related varriavles. */
v_connection_handle UTL_SMTP.connection;
v_from_email_address VARCHAR2 (100) :='erpprod@apollomunichinsurance.com';--;
--'erpdev@apollomunichinsurance.com';
v_to_email_address VARCHAR2 (100) := p_recipient;
--'meerasachdeva@in.ibm.com';
v_smtp_host VARCHAR2 (30) := '172.16.25.10';
--My mail server, replace it with yours.
v_subject VARCHAR2 (100) := 'Purchase Order no - '||p_po_num;--p_subject;
--'Your Test Mail';
l_message VARCHAR2 (4000);
/* This send_header procedure is written in the documentation */
PROCEDURE send_header (pi_name IN VARCHAR2, pi_header IN VARCHAR2)
AS
BEGIN
UTL_SMTP.write_data (v_connection_handle,
pi_name || ': ' || pi_header || UTL_TCP.crlf
);
END;
BEGIN
l_message:= 'Dear Vendor,'|| UTL_TCP.crlf||UTL_TCP.crlf || 'Please find attached the Purchase Order '||p_po_num||UTL_TCP.crlf;
l_message := l_message||UTL_TCP.crlf||'Please quote the purchase order number in all the future correspondence / invoices raised with respect to this purchase order.'||UTL_TCP.crlf;
l_message := l_message||UTL_TCP.crlf||'With warm regards, '||UTL_TCP.crlf;
l_message := l_message||UTL_TCP.crlf||'Procurement Team'||UTL_TCP.crlf;
l_message := l_message||'Apollo Munich Health Insurance Company Limited'||UTL_TCP.crlf;
l_message := l_message||'10th Floor, Building No.10, Tower B,'||UTL_TCP.crlf;
l_message := l_message||'DLF Cyber City, DLF City Phase-2,'||UTL_TCP.crlf;
l_message := l_message||'Gurgaon-122002, Haryana, INDIA.'||UTL_TCP.crlf;
l_message := l_message||UTL_TCP.crlf||'Tel- +91-124-4584-214'||UTL_TCP.crlf;
l_message := l_message||'Tel- +91-124-4584-333'||UTL_TCP.crlf;
l_message := l_message||UTL_TCP.crlf||UTL_TCP.crlf||'Please do not reply to this email as this is a system generated email.'||UTL_TCP.crlf;
-- Opening the file PO Print
DBMS_LOCK.sleep (20);
fnd_file.put_line (fnd_file.LOG, p_po_num);
fnd_file.put_line (fnd_file.LOG, p_file_name);
line_no := 33;
DBMS_LOB.createtemporary (l_blob, TRUE);
line_no := 34;
temp_os_file := BFILENAME (p_directory, p_file_name);
line_no := 35;
ex := DBMS_LOB.fileexists (temp_os_file);
line_no := 1;
v_cnt := 1;
fnd_file.put_line (fnd_file.LOG, line_no);
IF ex = 1
THEN
line_no := 2;
DBMS_LOB.fileopen (temp_os_file, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (l_blob,
temp_os_file,
DBMS_LOB.getlength (temp_os_file)
);
DBMS_LOB.fileclose (temp_os_file);
END IF;
/*UTL_SMTP related coding. */
v_connection_handle := UTL_SMTP.open_connection (v_smtp_host, 25);
UTL_SMTP.helo (v_connection_handle, v_smtp_host);
UTL_SMTP.mail (v_connection_handle, v_from_email_address);
UTL_SMTP.rcpt (v_connection_handle, v_to_email_address);
UTL_SMTP.rcpt (v_connection_handle, p_recipient_cc);
IF p_recipient_cc1 IS NOT NULL
THEN
UTL_SMTP.rcpt (v_connection_handle, p_recipient_cc1);
END IF;
UTL_SMTP.open_data (v_connection_handle);
send_header ('From', '"Apollo-Munich"');
send_header ('To', v_to_email_address);
send_header ('CC', p_recipient_cc);
IF p_recipient_cc1 IS NOT NULL
THEN
send_header ('CC', p_recipient_cc1);
END IF;
send_header ('Subject', v_subject);
--MIME header.
UTL_SMTP.write_data (v_connection_handle,
'MIME-Version: 1.0' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Type: multipart/mixed; ' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
' boundary= "'
|| 'SAUBHIK.SECBOUND'
|| '"'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
-- Mail Body
UTL_SMTP.write_data (v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Type: text/plain;' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
' charset=US-ASCII' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection_handle, l_message || UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
BEGIN
-- Mail Attachment
UTL_SMTP.write_data (v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Type: application/octet-stream'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Disposition: attachment; ' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
' filename="' || p_file_name || '"' ||
--My filename
UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Transfer-Encoding: base64' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
/* Re-initializing the varriables. This is very important*/
l_buffer := NULL;
l_pos := 1;
l_amount := 54;
/* Writing the BLOL in chunks */
l_blob_len := DBMS_LOB.getlength (l_blob);
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data (v_connection_handle,
UTL_ENCODE.base64_encode (l_buffer)
);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
l_blob := EMPTY_BLOB;
fnd_file.put_line (fnd_file.LOG, 'file sent');
END;
--l_blob := EMPTY_BLOB;
/* Opening the cursor to loop through the images*/
OPEN c_po_attachment;
LOOP
FETCH c_po_attachment
INTO v_fname, l_blob;
EXIT WHEN c_po_attachment%NOTFOUND;
-- Mail Attachment
UTL_SMTP.write_data (v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Type: application/octet-stream'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Disposition: attachment; ' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
' filename="' || v_fname || '"' || --My filename
UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
'Content-Transfer-Encoding: base64' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
/* Re-initializing the varriables. This is very important*/
l_buffer := NULL;
l_pos := 1;
l_amount := 54;
/* Writing the BLOL in chunks */
l_blob_len := DBMS_LOB.getlength (l_blob);
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data (v_connection_handle,
UTL_ENCODE.base64_encode (l_buffer)
);
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.write_data (v_connection_handle, UTL_TCP.crlf);
l_blob := EMPTY_BLOB;
END LOOP; --End cursor loop.
CLOSE c_po_attachment; --Close the cursor.
-- Close Email
UTL_SMTP.write_data (v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.crlf
);
UTL_SMTP.write_data (v_connection_handle,
UTL_TCP.crlf || '.' || UTL_TCP.crlf
);
UTL_SMTP.close_data (v_connection_handle);
UTL_SMTP.quit (v_connection_handle);
fnd_file.put_line (fnd_file.LOG, 'file sent');
--DBMS_LOB.FREETEMPORARY(l_blob);
--RETURN ('SUCCESS');
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
UTL_SMTP.quit (v_connection_handle);
--DBMS_LOB.FREETEMPORARY(l_blob);
lv_error := SUBSTR ('Unable to send mail: ' || SQLERRM, 1, 500);
fnd_file.put_line (fnd_file.LOG, line_no);
-- RETURN (lv_error);
WHEN OTHERS
THEN
UTL_SMTP.quit (v_connection_handle);
--DBMS_LOB.FREETEMPORARY(l_blob);
lv_error := SUBSTR ('Unable to send mail: ' || SQLERRM, 1, 500);
fnd_file.put_line (fnd_file.LOG, line_no);
--RETURN (lv_error);
END;
/
=====================
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 08:32:59 CST 2025
|