Error ORA 06550

15 pts.
Tags:
ORA-06550
Oracle error messages
PLS-00013
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;

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please tell us in what line are you getting those errors ? The package is too large to try to guess where the problem could be.
    70,220 pointsBadges:
    report
  • Nidi9
    when I run Declare it gives Error ORA 06550 and PLS 00013 ..
    15 pointsBadges:
    report
  • carlosdl
    Have you tried displaying the contents of l_statement before executing it to see if the code is being constructed correctly ? I'd display it, and run it in SQL*Plus directly, to discard errors with the constructed code.
    70,220 pointsBadges:
    report
  • Kccrosser
    You are dynamically constructing strings and sql expressions in the procedure. I would try the following: Replace each of the dynamically constructed expressions with a fixed literal and try again - if that works, then one of your constructions is generating invalid SQL. This area looks a bit odd, but without knowing the intentions, it is hard to say what is wrong: ' 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;'; Output your SQL that you are trying to execute before you execute it and then try to run the SQL interactively, as in the following: -- dbms_output.put_line('before execute imme '); dbms_output.put_line(substr(l_statement, 1, 255); EXECUTE IMMEDIATE l_statement; If the statement is longer than 255 characters, output the statement in chunks, then reassemble and test manually.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following