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 Go to next message
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;
/
=====================
Re: ORACLE error 29277 in FDPSTP Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation ORA-065 [message #584928 is a reply to message #584927] Mon, 20 May 2013 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

1/
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

2/ Always post your Oracle version, with 4 decimals.

3/ Do NOT rate your own question; we know you are proud of it, it is useless to mark it.

4/
ORA-29277: invalid SMTP operation
 *Cause:  The SMTP operation was invalid at the current stage of the SMTP
          transaction.
 *Action: Retry the SMTP operation at the appropriate stage of the SMTP
          transaction.


Regards
Michel
Re: ORACLE error 29277 in FDPSTP Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation ORA-065 [message #585125 is a reply to message #584928] Wed, 22 May 2013 06:34 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A quick scan of the code reveiled a "my mailserver, replace it with yours", Did you do that?

Otherwise one standard procedure for SMTP problems is to talk to the mail server admin to see if what you are trying to do is posssible with the users you are using.
Previous Topic: error regarding oracle connection
Next Topic: Connect Forms 6i with ms access database
Goto Forum:
  


Current Time: Thu Mar 28 06:26:07 CDT 2024