Hi.. I am Planning to make email automation . mail should have attachment. Attachment is the result of query execution in oracle database .I am getting error ORA 06550 and PLS 00013.Can u help me solve this glitch..Here I Put My PACKAGE.. create table config2 ( TO_ADDRESS VARCHAR2(1000), SMTP_SERVER VARCHAR2(1000), FROM_ADDRESS VARCHAR2(1000), EMAIL_SUBJECT VARCHAR2(1000), EMAIL_TEXT VARCHAR2(1000)); **************************************** NTS00005.IMP-DOM.COM insert into config(TO_ADDRESS, SMTP_SERVER ,FROM_ADDRESS, EMAIL_SUBJECT ,EMAIL_TEXT)valueS('abc@ghj.com','xyxyxy.india.dom','xyz@abd.com','Hi','Hello'); ************************************************* create table GT_TEMP ( ID VARCHAR2(4000)); ****************************************************** CREATE OR REPLACE PACKAGE PCK_EMAIL_ATTACHMENT IS TYPE ref_cur_t IS REF CURSOR; g_query VARCHAR2 (32000); g_count NUMBER; name VARCHAR2(100); VALUE VARCHAR2(100); g_desc_tab DBMS_SQL.DESC_TAB; error NUMBER := 0; success NUMBER := 1; v_txt varchar2(4000); v_rc NUMBER ; varchar2_type CONSTANT PLS_INTEGER := 1; number_type CONSTANT PLS_INTEGER := 2; date_type CONSTANT PLS_INTEGER := 12; rowid_type CONSTANT PLS_INTEGER := 11; char_type CONSTANT PLS_INTEGER := 96; l_col_name varchar2(3000) := null ; long_type CONSTANT PLS_INTEGER := 8; raw_type CONSTANT PLS_INTEGER := 23; mlslabel_type CONSTANT PLS_INTEGER := 106; clob_type CONSTANT PLS_INTEGER := 112; blob_type CONSTANT PLS_INTEGER := 113; bfile_type CONSTANT PLS_INTEGER := 114; FUNCTION describe_columns(v_txt IN OUT VARCHAR2) RETURN NUMBER ; FUNCTION send_email (p_query VARCHAR2 :=NULL ,in_col_name VARCHAR2 :=NULL , in_html_append VARCHAR2 :=NULL , file_name VARCHAR2 :=NULL , v_txt IN OUT VARCHAR2 ) RETURN NUMBER; FUNCTION record_def(v_txt IN OUT VARCHAR2) RETURN VARCHAR2; FUNCTION ref_cur RETURN PCK_EMAIL_ATTACHMENT.ref_cur_t; FUNCTION process_def(v_txt IN OUT VARCHAR2) RETURN VARCHAR2 ; END; ************************************************* CREATE OR REPLACE PACKAGE BODY PCK_EMAIL_ATTACHMENT IS FUNCTION describe_columns(v_txt IN OUT VARCHAR2) RETURN NUMBER IS l_cur INTEGER; BEGIN dbms_output.put_line('inside function describe_columns '); l_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (l_cur, g_query, DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS (l_cur, g_count, g_desc_tab); DBMS_SQL.CLOSE_CURSOR (l_cur); Return success ; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('inside function describe_columns exception'); IF DBMS_SQL.IS_OPEN (l_cur) THEN DBMS_SQL.CLOSE_CURSOR (l_cur); END IF; v_txt := 'Error in function describe_columns :-'||SQLERRM ; Return error; END; FUNCTION process_def(v_txt IN OUT VARCHAR2) RETURN VARCHAR2 IS l_process_def VARCHAR2 (32000) := null; BEGIN l_process_def := 'TO_CHAR ('; FOR i IN 1 .. PCK_EMAIL_ATTACHMENT.g_count LOOP l_process_def := l_process_def || ' l_record.col_' || i || ' ||'','' || '; END LOOP; l_process_def := RTRIM (l_process_def, ' || '','' || ') || ');'; RETURN l_process_def; EXCEPTION WHEN OTHERS THEN v_txt := v_txt||'Error in during FUNCTION process_def :-'||SQLERRM; success := error; END; FUNCTION ref_cur RETURN PCK_EMAIL_ATTACHMENT.ref_cur_t IS l_ref_cur PCK_EMAIL_ATTACHMENT.ref_cur_t; BEGIN OPEN l_ref_cur FOR PCK_EMAIL_ATTACHMENT.g_query; RETURN l_ref_cur; EXCEPTION WHEN OTHERS THEN success := error; END; FUNCTION record_def(v_txt IN OUT VARCHAR2) RETURN VARCHAR2 IS l_record_def VARCHAR2 (32000); l_type VARCHAR2 (100); l_col_type PLS_INTEGER; l_col_max_len PLS_INTEGER; l_col_precision PLS_INTEGER; l_col_scale PLS_INTEGER; BEGIN l_col_name := null ; FOR i IN 1..g_count LOOP l_col_type := g_desc_tab(i).col_type; l_col_max_len := g_desc_tab(i).col_max_len; l_col_precision := g_desc_tab(i).col_precision; l_col_scale := g_desc_tab(i).col_scale; l_col_name := l_col_name || '<TD><B>' || g_desc_tab(i).col_name || '</B></TD>' ; IF l_col_type = varchar2_type THEN l_type := 'VARCHAR2(' || l_col_max_len || ')'; ELSIF l_col_type = number_type THEN l_type := 'NUMBER'; ELSIF l_col_type = date_type THEN l_type := 'DATE'; ELSIF l_col_type = rowid_type THEN l_type := 'ROWID'; ELSIF l_col_type = char_type THEN l_type := 'CHAR(' || l_col_max_len || ')'; END IF; l_record_def := l_record_def || ' col_' || i || ' ' || l_type || ','; END LOOP; l_record_def := RTRIM (l_record_def, ','); RETURN l_record_def; EXCEPTION WHEN OTHERS THEN v_txt := v_txt||'Error in during FUNCTION process_def :-'||SQLERRM; success := error; END; FUNCTION send_email (p_query VARCHAR2 :=NULL ,in_col_name VARCHAR2 :=NULL , in_html_append VARCHAR2 :=NULL , file_name VARCHAR2 :=NULL , v_txt IN OUT VARCHAR2 ) RETURN NUMBER IS l_statement VARCHAR2 (32000); v_email_text varchar2(1000) ; v_email_subject varchar2(1000) ; v_from_address varchar2(1000) ; v_smtp_server varchar2(1000) ; v_to_address VARCHAR2(1000); c utl_tcp.connection; rc integer; v_occu_count number ; v_file_name varchar2(4000); v_in_col_name varchar2(4000) := null; y varchar2(4000) := null; v_html_append varchar2(4000) := null; z varchar2(4000) := null; Type recordtab is table of varchar2(3000) index by binary_integer ; v_obj recordtab ; BEGIN UTL_TCP.CLOSE_ALL_CONNECTIONS; success := 1 ; v_file_name := file_name ; v_in_col_name := in_col_name ; v_html_append := in_html_append; BEGIN -- selecting data for email notification select VALUE into v_to_address from config2 where name = 'TO_ADDRESS'; select VALUE into v_smtp_server from config2 where name = 'SMTP_SERVER'; select VALUE into v_from_address from config2 where name = 'FROM_ADDRESS'; select VALUE into v_email_subject from config2 where name = 'EMAIL_SUBJECT'; select VALUE into v_email_text from config2 where name = 'EMAIL_TEXT'; EXCEPTION WHEN OTHERS THEN v_txt := v_txt||'Error selecting config2 value in send_email :-'||SQLERRM; RETURN error; END; c := utl_tcp.open_connection(v_smtp_server, 25); ----- OPEN SMTP PORT CONNECTION rc := utl_tcp.write_line(c, 'HELO 262.30.88.29'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'HELO 262.30.88.29'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'MAIL FROM: ' || v_from_address); ----- MAIL BOX SENDING THE EMAIL dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'RCPT TO: ' || v_to_address); ----- MAIL BOX RECIEVING THE EMAIL dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'DATA'); ----- EMAIL MESSAGE BODY START dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss')); rc := utl_tcp.write_line(c, 'From: ' || v_from_address || ' <' || v_from_address || '>'); rc := utl_tcp.write_line(c, 'MIME-Version: 1.0'); rc := utl_tcp.write_line(c, 'To: ' || v_to_address || ' <' || v_to_address || '>'); rc := utl_tcp.write_line(c, 'Subject: ' || v_email_subject); rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS rc := utl_tcp.write_line(c, ''); ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED. rc := utl_tcp.write_line(c, '-------SECBOUND'); rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit'); rc := utl_tcp.write_line(c, ''); rc := utl_tcp.write_line(c, v_email_text); ----- TEXT OF EMAIL MESSAGE rc := utl_tcp.write_line(c, ''); rc := utl_tcp.write_line(c, '-------SECBOUND'); rc := utl_tcp.write_line(c, 'Content-Type: text/plain;'); ----- 2ND BODY PART. -- plain to html rc := utl_tcp.write_line(c, ' name="TEMP/emp1.html"'); rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit'); -- if v_html_append is null then only attachement will be send in email if v_html_append is null then rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT rc := utl_tcp.write_line(c, ' filename="TEMP/'|| v_file_name ||'"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT end if ; rc := utl_tcp.write_line(c, ''); rc := utl_tcp.write_line(c, '<html>'); rc := utl_tcp.write_line(c, '<head>'); rc := utl_tcp.write_line(c, '<meta http-equiv="Content-Type" content="text/html">'); rc := utl_tcp.write_line(c, '<title>Title of the HTML File</title>'); rc := utl_tcp.write_line(c, '</head>'); rc := utl_tcp.write_line(c, '<body TEXT="#000000" BGCOLOR="#FFFFFF">'); rc := utl_tcp.write_line(c, '<TABLE border=1 bordercolor="#808080" cellspacing=0>'); rc := utl_tcp.write_line(c, '</TR>'); PCK_EMAIL_ATTACHMENT.g_query := p_query; v_rc := PCK_EMAIL_ATTACHMENT.describe_columns(v_txt); IF v_rc <> success THEN v_txt :=v_txt||'Error in function describe_columns :-'||SQLERRM; RETURN error; END IF; delete from gt_temp; l_statement := ' DECLARE' || ' TYPE record_t IS RECORD (' || PCK_EMAIL_ATTACHMENT.record_def(v_txt) || ');' || ' l_record record_t; l_var varchar2(4000);rc integer;' || ' l_ref_cur PCK_EMAIL_ATTACHMENT.ref_cur_t;' || ' BEGIN' || ' l_ref_cur := PCK_EMAIL_ATTACHMENT.ref_cur;' || ' LOOP' || ' FETCH l_ref_cur INTO l_record;' || ' EXIT WHEN l_ref_cur%NOTFOUND;' || 'l_var:='||PCK_EMAIL_ATTACHMENT.process_def(v_txt) || 'insert into gt_temp values (l_var); commit;'|| ' END LOOP;' || ' CLOSE l_ref_cur;' || ' END;'; -- dbms_output.put_line('before execute imme '); EXECUTE IMMEDIATE l_statement; If success = error then v_txt := v_txt||'Error in during EXECUTE IMMEDIATE stataement :-'||SQLERRM; Return error; End if; BEGIN rc := utl_tcp.write_line(c, '</TR>'); if v_in_col_name is null then rc := utl_tcp.write_line(c, PCK_EMAIL_ATTACHMENT.l_col_name ); else loop y := substr(v_in_col_name,1,instr(v_in_col_name,',')-1) ; v_in_col_name := substr(v_in_col_name,instr(v_in_col_name,',')+1) ; if y is null then exit ; else z := z || '<TD><B>' || y || '</B></TD>' ; end if ; end loop; z := z || '<TD><B>' || v_in_col_name || '</B></TD>' ; rc := utl_tcp.write_line(c, z ); End if; --pushing data into email from GTT FOR x in ( select id from gt_temp ) loop -- this will give us the total occurrence of ',' in the x.id field. v_occu_count := 1 ; loop If ( ((instr(x.id,',', 1, v_occu_count ))) = 0 ) then exit; End if; v_occu_count := v_occu_count+1; end loop ; v_occu_count := v_occu_count - 1; rc := utl_tcp.write_line(c, '</TR>'); for i in 1..v_occu_count loop v_obj(i) := substr(x.id,1,instr(x.id,',')-1) ; rc := utl_tcp.write_line(c,'<TD><B>' || v_obj(i) || '</B></TD>'); x.id := substr(x.id,instr(x.id,',')+1) ; end loop; rc := utl_tcp.write_line(c,'<TD><B>' || x.id || '</B></TD>'); End loop; commit; EXCEPTION WHEN OTHERS THEN v_txt := v_txt||'Error in during selecting data from Global temp table :-'||SQLERRM; Return error ; END; -- dbms_output.put_line('ending email'); rc := utl_tcp.write_line(c, '</TR>'); rc := utl_tcp.write_line(c, '</body>'); rc := utl_tcp.write_line(c, '</html>'); rc := utl_tcp.write_line(c, '-------SECBOUND--'); rc := utl_tcp.write_line(c, ''); rc := utl_tcp.write_line(c, '.'); ----- EMAIL MESSAGE BODY END dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION dbms_output.put_line(utl_tcp.get_line(c, TRUE)); utl_tcp.close_connection(c); ----- CLOSE SMTP PORT CONNECTION RETURN success; EXCEPTION WHEN OTHERS THEN v_txt := v_txt||'Error in function send_email :-'||SQLERRM; Return error ; END; END; @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Declare v_txt varchar2 (2000); Returned_value number; Begin Returned_value := FUNCTION send_email ( 'select * from config;', 'col1', 'HTML APPEND', 'mail.xls', 'v_txt'); Dbms_output.put_line ('Returned_value is:=' || Returned_value); end;

Software/Hardware used: