CREATE OR REPLACE PROCEDURE certification.send_html_mail_p1(p_recepient IN character varying, p_recpnt_cc IN character varying, p_email_identifier IN numeric, p_attach_name IN character varying, p_attach_msg IN character varying,
p_parameter1 IN character varying, p_parameter2 IN character varying, p_parameter3 IN character varying, p_parameter4 IN character varying, p_parameter5 IN character varying, p_parameter6 IN character varying,
p_parameter7 IN character varying, p_parameter8 IN character varying, p_parameter9 IN character varying, p_parameter10 IN character varying, p_err_ind OUT character varying) AS

declare
conn UTL_SMTP.CONNECTION;
msg VARCHAR2;
v_reply utl_smtp.reply;
v_temp VARCHAR2(4000);
v_name VARCHAR2(4000);
v_pos PLS_INTEGER := 1;
mailhost character varying ;
portno PLS_INTEGER := 25;
sender VARCHAR2(50) ;
v_mail_subject EMAIL_MST.email_subject%TYPE;
v_mail_body EMAIL_MST.email_body%TYPE;
v_err_mesg LnD_email_err_log.pg_err_msg%TYPE;
v_reply1 utl_smtp.reply;
v_temp1 VARCHAR2(4000) := '';
v_name1 VARCHAR2(4000) := '';
v_pos1 NUMBER := 1;
v_err_code LnD_email_err_log.pg_err_cd%TYPE;
crlf CHAR(2) := CHR(13) || CHR(10);
v_recpnt_cc character varying ;
--v_err_code VARCHAR2(4000);
begin
SELECT icon.property_value
INTO mailhost
FROM notification_properties icon
WHERE icon.property_name = 'SMTPServer';

SELECT to_number(icon.property_value)
INTO portno
FROM notification_properties icon
WHERE icon.property_name = 'SMTPPort';

SELECT icon.property_value
INTO sender
FROM notification_properties icon
WHERE icon.property_name = 'sender_address';

conn := utl_smtp.open_connection(mailhost,portno);
--v_reply := utl_smtp.open_connection(mailhost,portno,conn);
utl_smtp.helo(conn,mailhost);
utl_smtp.mail(conn,'sender.Test');

-- Need to discuss ,this can be used in place of below
for email_rec in (select rec from regexp_split_to_table(p_recepient,E';') as rec)
loop
dbms_output.put_line(' Testing <=:::::::::::=> '||email_rec.rec);
UTL_SMTP.rcpt(conn,email_rec.rec);
dbms_output.put_line(' Testing <-::::::::::-> '||email_rec.rec);
end loop;

/*IF v_reply.code <> 250
THEN
utl_smtp.quit(conn);
RETURN;
END IF;*/

--Need to discuss ,this can be used in place of below
v_recpnt_cc := p_recpnt_cc;

IF v_recpnt_cc IS NOT NULL then
for email_rec in (select rec from regexp_split_to_table(v_recpnt_cc,E';') as rec)
loop
dbms_output.put_line(' Testing <=:::::::::::=> '||email_rec.rec);
UTL_SMTP.rcpt(conn,email_rec.rec);
dbms_output.put_line(' Testing <=:::::::::::=> '||email_rec.rec);
end loop;
END IF;

/*IF v_reply1.code <> 250 THEN
utl_smtp.quit(conn);
RETURN;
END IF;*/

SELECT bb.email_subject,
format(bb.email_body,p_parameter1,p_parameter2,p_p arameter3,p_parameter4,p_parameter5,p_parameter6,p _parameter7,p_parameter8,p_parameter9,p_parameter1 0)
INTO v_mail_subject,v_mail_body
FROM EMAIL_MST bb
WHERE bb.email_identifier = p_email_identifier;

msg := 'Return-Path: ' || sender || crlf ||
'Sent: ' || to_char(SYSDATE,'mm/dd/yyyy hh24:mi:ss') ||crlf ||
'From: ' || sender || crlf ||
'Subject: ' || v_mail_subject ||crlf ||
'To: ' || p_recepient || crlf ||
'Cc: ' || v_recpnt_cc || crlf ||
'MIME-Version: 1.0' || crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed; boundary="inner-boundary"' || crlf || --MIME.Bound really should be a randomly generated string
crlf ||'--inner-boundary' || crlf ||
'Content-Type: text/html; charset=UTF8' || crlf ||
'Content-Disposition: inline;' || crlf ||
'Content-Transfer_Encoding: 7bit' || crlf ||' '||v_mail_body ||' '|| crlf ;

dbms_output.put_line(' Testing <=========> '||mailhost||':'||portno||':::::'||conn);
utl_smtp.open_data(conn);
--dbms_output.put_line(' Testing <=========>123 ');
utl_smtp.write_data(conn,msg);
--utl_smtp.data(conn,'');
dbms_output.put_line(' Testing <=========>1234 ');

/*IF p_attach_name IS NOT NULL THEN
utl_smtp.write_data(conn,'Content-type: text/plain;' || crlf ||
'name="'||p_attach_name||'"'|| crlf ||
'Content-Disposition: attachment;'||crlf ||
p_attach_msg||CHR(13));
END IF;

utl_smtp.close_data(conn);*/
utl_smtp.quit(conn);

/*EXCEPTION

WHEN OTHERS THEN
v_err_mesg := substr(SQLERRM,1,500);
--p_err_ind := 'Y';
v_err_code := SQLCODE;
dbms_output.put_line( v_err_code ||':'|| v_err_mesg);*/
end



But when executing the procedure it is failing with below error-


Testing <=:::::::::::=> Receiver.Test@dev.com
Testing <-::::::::::-> Receiver.Test@dev.com
Testing <=:::::::::::=> Receiver_cc.Test@dev.com
Testing <=:::::::::::=> Receiver_cc.Test@dev.com
Testing <=========> 172.17.9.236:25::::172.17.9.236,25,,29,"
",4)
ERROR: Syntax error, command "XXXX" unrecognized



CONTEXT: edb-spl function certification.send_html_mail_p1(character varying,character varying,numeric,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 118 at procedure/function invocation statement
********** Error **********

ERROR: Syntax error, command "XXXX" unrecognized
SQL state: XX000
Context: edb-spl function certification.send_html_mail_p1(character varying,character varying,numeric,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying) line 118 at procedure/function invocation statement

looks it is failing at Failing at utl_smtp.open_data(conn);

Any quick help will be appreciated.

utl_smtp is available is PostgreSQL,and if you see below code wont fail -

conn := utl_smtp.open_connection(mailhost,portno);
--v_reply := utl_smtp.open_connection(mailhost,portno,conn);
utl_smtp.helo(conn,mailhost);
utl_smtp.mail(conn,'sender.Test');

it fails at utl_smtp.open_data(conn);