Home » SQL & PL/SQL » SQL & PL/SQL » UTL_TCP package
UTL_TCP package [message #229218] Fri, 06 April 2007 01:31 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

I am trying to send attachment through the UTL_TCP package. I got the dummy code from net I have made changes according to mine requirement but I am not able to send attachment in zip format any body can suggest me how can I convert attachment file in zip format with the help UTL_TCP or suggest me some other way.


--Yash
Re: UTL_TCP package [message #229235 is a reply to message #229218] Fri, 06 April 2007 03:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think you got some things mixed up here. UTL_TCP is NOT for sending emails.
Re: UTL_TCP package [message #229237 is a reply to message #229235] Fri, 06 April 2007 03:32 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
This is the code ...i think it is sending mail through the UTL_TCP..might be i am wrong then tell me how can we zip the file in pl/sql

CREATE OR REPLACE PACKAGE BODY ccm_dev_search_send_mail_pkg

AS

PROCEDURE send_mail_attachment(

msg_from IN VARCHAR2,

msg_to IN VARCHAR2,

msg_subject IN VARCHAR2,

msg_text IN VARCHAR2

)

AS

c UTL_TCP.connection;

rc INTEGER;

crlf VARCHAR2 (2) := CHR (13) || CHR (10);

mesg LONG;

v_txt VARCHAR2 (2500);
v_header_txt VARCHAR2(1000):='';

heading LONG;

flag VARCHAR2 (20);

START_TIME DATE ;

v_recepient VARCHAR2(500);

v_user VARCHAR2(500);

lv_cnt INTEGER :=0;
lv_new_cnt INTEGER :=0;

cursor c1_cur

IS

SELECT

distinct cch.pcn_number, cch.cisco_log, ccaa.case_id, ccaa.business_unit

FROM

ccm_ccn_header cch,

ccm_ccn_cisco_part_details cccpd,

ccm_ccn_assembly_affected ccaa,

ccm_ccn_case_detail cccd,

ccm_ccn_priority ccp,

ccm_ccn_sample_data ccsd

WHERE

cch.cisco_log = ccaa.cisco_log (+) and

ccaa.case_id = cccd.case_id (+) and

cch.cisco_log = ccp.cisco_log (+) and

cch.cisco_log = cccpd.cisco_log (+) and

cccd.case_id = ccsd.CASE_ID (+) AND

cch.status = 'Open' AND

cccd.status = 'Open' AND

cch.creation_date >= '13-NOV-2006' AND

cch.creation_date <= '22-MAR-2007';

BEGIN

SELECT SYSDATE INTO START_TIME FROM dual;

IF INSTR(msg_to,'@cisco.com') = 0

THEN

v_recepient:=TRIM(msg_to)||'@cisco.com';

v_user:= msg_to;

ELSE

v_recepient:=msg_to;

v_user:= SUBSTR(msg_to, 0, INSTR(msg_to,'@')-1 );

END IF;



c := UTL_TCP.open_connection ('mailman.cisco.com', 25);

----- OPEN SMTP PORT CONNECTION

rc := UTL_TCP.write_line (c, 'Helo mailman.cisco.com');

----- PERFORMS HANDSHAKING WITH SMTP SERVER

dbms_output.put_line(utl_tcp.get_line(C, TRUE));

rc := UTL_TCP.write_line (c, 'Helo XP email server');

----- 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: ' || msg_from);

----- MAIL BOX SENDING THE EMAIL

dbms_output.put_line(utl_tcp.get_line(C, TRUE));

rc := UTL_TCP.write_line (c, 'RCPT TO: ' || v_recepient);

----- 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: ' || msg_from || ' <' || msg_from || '>');

rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');

-- rc := utl_tcp.write_line(C, 'To: '||msg_to||' <'||msg_to||'>');

rc := UTL_TCP.write_line (c, 'To: ' || v_recepient);

rc := UTL_TCP.write_line (c, 'Subject: ' || msg_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, 'Dear '||v_recepient||' ,'||CR||CR||'The batch processing you requested is complete. Please see attached the results.'||CR||CR||msg_text||CR||CR||'- eITMS Team'||CR||CR||'P.S. This is a system generated email. Please do not reply to this mail.'); ----- 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: application/ms-excel;');

rc := UTL_TCP.write_line (c, ' name="Processed_Records1.xls"');

rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');

rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');

----- INDICATES THAT THIS IS AN ATTACHMENT

rc := UTL_TCP.write_line (c, ' filename="Processed_Records1.xls"');
rc := UTL_TCP.write_line (c, '')

v_header_txt:='Cisco MCN Number'||CHR(9)||'Case No'||CHR(9)||'BU MCN Number'||CHR(9)||'CE Case Creation Date'||CHR(9)||'PCN Number';

rc := UTL_TCP.write_line (c, v_header_txt);

FOR c1 IN c1_cur
LOOP
if(MOD(lv_cnt,1000)=0)THEN
--End previous excel and Generate new Excel File
rc := UTL_TCP.write_line (c, '-------SECBOUND');
lv_new_cnt:=lv_cnt+1;
rc := UTL_TCP.write_line (c, 'Content-Type: application/ms-excel;');
rc := UTL_TCP.write_line (c, ' name="Processed_Records1.xls"');
rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');
rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');
rc := UTL_TCP.write_line (c, ' filename="Processed_Records'||lv_new_cnt||'.xls"');
rc := UTL_TCP.write_line (c, '')


--Print Header
rc := UTL_TCP.write_line (c, v_header_txt);
END IF;
v_txt:=c1.pcn_number||CHR(9)||c1.cisco_log||CHR(9)||c1.case_id||CHR(9)||c1.business_unit;
rc := UTL_TCP.write_line (c, v_txt);
lv_cnt:=lv_cnt+1;

END LOOP;

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

dbms_output.put_line ('I ... am .... here.... 4');

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20000, SQLERRM);

END send_mail_attachment;

END ccm_dev_search_send_mail_pkg;

/




--Yash
Re: UTL_TCP package [message #229251 is a reply to message #229237] Fri, 06 April 2007 04:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but do YOU think this is readable?
Read the stickies (first two topics of the forum) to see how to format your code.
Re: UTL_TCP package [message #229253 is a reply to message #229251] Fri, 06 April 2007 04:39 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
I agree with you but i just past the code because you said like that ...UTL_TCP can not send the mail. I think at least you will get the idea it is possible or not in UTL_TCP ...but my main question is very clear.How can I zip the xls file in pl/sql that’s it ....


--Yash
Re: UTL_TCP package [message #229257 is a reply to message #229253] Fri, 06 April 2007 04:52 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi
Quote:
frank wrote
Read the stickies (first two topics of the forum) to see how to format your code.


he wants it something like this.


CREATE OR REPLACE PACKAGE BODY ccm_dev_search_send_mail_pkg

AS

PROCEDURE send_mail_attachment(

msg_from IN VARCHAR2,

msg_to IN VARCHAR2,

msg_subject IN VARCHAR2,

msg_text IN VARCHAR2

)

AS

c UTL_TCP.connection;

rc INTEGER;

crlf VARCHAR2 (2) := CHR (13) || CHR (10);

mesg LONG;

v_txt VARCHAR2 (2500);
v_header_txt VARCHAR2(1000):='';

heading LONG;

flag VARCHAR2 (20);

START_TIME DATE ;

v_recepient VARCHAR2(500);

v_user VARCHAR2(500);

lv_cnt INTEGER :=0;
lv_new_cnt INTEGER :=0;

cursor c1_cur

IS

SELECT

distinct cch.pcn_number, cch.cisco_log, ccaa.case_id, ccaa.business_unit

FROM

ccm_ccn_header cch,

ccm_ccn_cisco_part_details cccpd,

ccm_ccn_assembly_affected ccaa,

ccm_ccn_case_detail cccd,

ccm_ccn_priority ccp,

ccm_ccn_sample_data ccsd

WHERE

cch.cisco_log = ccaa.cisco_log (+) and

ccaa.case_id = cccd.case_id (+) and

cch.cisco_log = ccp.cisco_log (+) and

cch.cisco_log = cccpd.cisco_log (+) and

cccd.case_id = ccsd.CASE_ID (+) AND

cch.status = 'Open' AND

cccd.status = 'Open' AND

cch.creation_date >= '13-NOV-2006' AND

cch.creation_date <= '22-MAR-2007';

BEGIN

SELECT SYSDATE INTO START_TIME FROM dual;

IF INSTR(msg_to,'@cisco.com') = 0

THEN

v_recepient:=TRIM(msg_to)||'@cisco.com';

v_user:= msg_to;

ELSE

v_recepient:=msg_to;

v_user:= SUBSTR(msg_to, 0, INSTR(msg_to,'@')-1 );

END IF;



c := UTL_TCP.open_connection ('mailman.cisco.com', 25);

----- OPEN SMTP PORT CONNECTION

rc := UTL_TCP.write_line (c, 'Helo mailman.cisco.com');

----- PERFORMS HANDSHAKING WITH SMTP SERVER

dbms_output.put_line(utl_tcp.get_line(C, TRUE));

rc := UTL_TCP.write_line (c, 'Helo XP email server');

----- 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: ' || msg_from);

----- MAIL BOX SENDING THE EMAIL

dbms_output.put_line(utl_tcp.get_line(C, TRUE));

rc := UTL_TCP.write_line (c, 'RCPT TO: ' || v_recepient);

----- 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: ' || msg_from || ' <' || msg_from || '>');

rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');

-- rc := utl_tcp.write_line(C, 'To: '||msg_to||' <'||msg_to||'>');

rc := UTL_TCP.write_line (c, 'To: ' || v_recepient);

rc := UTL_TCP.write_line (c, 'Subject: ' || msg_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, 'Dear '||v_recepient||' ,'||CR||CR||'The batch processing you requested is complete. Please see attached the results.'||CR||CR||msg_text||CR||CR||'- eITMS Team'||CR||CR||'P.S. This is a system generated email. Please do not reply to this mail.'); ----- 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: application/ms-excel;');

rc := UTL_TCP.write_line (c, ' name="Processed_Records1.xls"');

rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');

rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');

----- INDICATES THAT THIS IS AN ATTACHMENT

rc := UTL_TCP.write_line (c, ' filename="Processed_Records1.xls"');
rc := UTL_TCP.write_line (c, '')

v_header_txt:='Cisco MCN Number'||CHR(9)||'Case No'||CHR(9)||'BU MCN Number'||CHR(9)||'CE Case Creation Date'||CHR(9)||'PCN Number';

rc := UTL_TCP.write_line (c, v_header_txt);

FOR c1 IN c1_cur
LOOP
if(MOD(lv_cnt,1000)=0)THEN
--End previous excel and Generate new Excel File
rc := UTL_TCP.write_line (c, '-------SECBOUND');
lv_new_cnt:=lv_cnt+1;
rc := UTL_TCP.write_line (c, 'Content-Type: application/ms-excel;');
rc := UTL_TCP.write_line (c, ' name="Processed_Records1.xls"');
rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');
rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');
rc := UTL_TCP.write_line (c, ' filename="Processed_Records'||lv_new_cnt||'.xls"');
rc := UTL_TCP.write_line (c, '')


--Print Header
rc := UTL_TCP.write_line (c, v_header_txt);
END IF;
v_txt:=c1.pcn_number||CHR(9)||c1.cisco_log||CHR(9)||c1.case_id||CHR(9)||c1.business_unit;
rc := UTL_TCP.write_line (c, v_txt);
lv_cnt:=lv_cnt+1;

END LOOP;

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

dbms_output.put_line ('I ... am .... here.... 4');

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20000, SQLERRM);

END send_mail_attachment;

END ccm_dev_search_send_mail_pkg;

/



frank i guess i read the sticky correct? Razz


regards
shanth.

[Updated on: Fri, 06 April 2007 04:54]

Report message to a moderator

Re: UTL_TCP package [message #229276 is a reply to message #229257] Fri, 06 April 2007 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
frank i guess i read the sticky correct?

Almost, but not quite.
The code still is totally unreadable, because it is not formatted. In the first sticky, there is a link to this site's formatter.
Re: UTL_TCP package [message #229277 is a reply to message #229257] Fri, 06 April 2007 07:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Along with some formatting (using our online formatting tool) could make many folks gleeful Smile
/*Notice: Formatted SQL is not the same as input*/

CREATE OR REPLACE PACKAGE BODY ccm_dev_Search_Send_Mail_pkg
AS
  PROCEDURE Send_Mail_Attachment
       (msg_From     IN VARCHAR2,
        msg_To       IN VARCHAR2,
        msg_Subject  IN VARCHAR2,
        msg_Text     IN VARCHAR2)
  AS
    c             utl_tcp.Connection;
    rc            INTEGER;
    crlf          VARCHAR2(2) := chr(13)
                                 ||chr(10);
    Mesg          LONG;
    v_txt         VARCHAR2(2500);
    v_Header_txt  VARCHAR2(1000) := '';
    Heading       LONG;
    Flag          VARCHAR2(20);
    Start_Time    DATE;
    v_recePient   VARCHAR2(500);
    v_User        VARCHAR2(500);
    lv_cnt        INTEGER := 0;
    lv_New_cnt    INTEGER := 0;
    CURSOR c1_Cur IS 
      SELECT DISTINCT cch.pcn_Number,
                      cch.cIsco_Log,
                      ccaa.Case_Id,
                      ccaa.Business_Unit
      FROM   ccm_ccn_Header cch,
             ccm_ccn_cIsco_Part_Details cccpd,
             ccm_ccn_Assembly_Affected ccaa,
             ccm_ccn_Case_Detail cccd,
             ccm_ccn_Priority ccp,
             ccm_ccn_Sample_Data ccsd
      WHERE  cch.cIsco_Log = ccaa.cIsco_Log (+) 
             AND ccaa.Case_Id = cccd.Case_Id (+) 
             AND cch.cIsco_Log = ccp.cIsco_Log (+) 
             AND cch.cIsco_Log = cccpd.cIsco_Log (+) 
             AND cccd.Case_Id = ccsd.Case_Id (+) 
             AND cch.Status = 'Open'
             AND cccd.Status = 'Open'
             AND cch.Creation_Date >= '13-NOV-2006'
             AND cch.Creation_Date <= '22-MAR-2007';
  BEGIN
    SELECT SYSDATE
    INTO   Start_Time
    FROM   Dual;
    
    IF Instr(msg_To,'@cisco.com') = 0 THEN
      v_recePient := TRIM(msg_To)
                     ||'@cisco.com';
      
      v_User := msg_To;
    ELSE
      v_recePient := msg_To;
      
      v_User := Substr(msg_To,0,Instr(msg_To,'@') - 1);
    END IF;
    
    c := utl_tcp.Open_Connection('mailman.cisco.com',25);
    ----- OPEN SMTP PORT CONNECTION
    
    rc := utl_tcp.Write_Line(c,'Helo mailman.cisco.com');
    ----- PERFORMS HANDSHAKING WITH SMTP SERVER
    
    dbms_Output.Put_Line(utl_tcp.Get_Line(c,True));
    
    rc := utl_tcp.Write_Line(c,'Helo XP email server');
    ----- 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: '
                               ||msg_From);
    ----- MAIL BOX SENDING THE EMAIL
    
    dbms_Output.Put_Line(utl_tcp.Get_Line(c,True));
    
    rc := utl_tcp.Write_Line(c,'RCPT TO: '
                               ||v_recePient);
    ----- 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: '
                               ||msg_From
                               ||' <'
                               ||msg_From
                               ||'>');
    
    rc := utl_tcp.Write_Line(c,'MIME-Version: 1.0');
    -- rc := utl_tcp.write_line(C, 'To: '||msg_to||' <'||msg_to||'>');
    
    rc := utl_tcp.Write_Line(c,'To: '
                               ||v_recePient);
    
    rc := utl_tcp.Write_Line(c,'Subject: '
                               ||msg_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, 'Dear '||v_recepient||' ,'||CR||CR||'The batch processing you requested is complete. Please see attached the results.'||CR||CR||msg_text||CR||CR||'- eITMS Team'||CR||CR||'P.S. This is a system generated email. Please do not reply to this mail.'); ----- 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: application/ms-excel;');
    
    rc := utl_tcp.Write_Line(c,' name="Processed_Records1.xls"');
    
    rc := utl_tcp.Write_Line(c,'Content-Transfer_Encoding: 8bit');
    
    rc := utl_tcp.Write_Line(c,'Content-Disposition: attachment;');
    ----- INDICATES THAT THIS IS AN ATTACHMENT
    
    rc := utl_tcp.Write_Line(c,' filename="Processed_Records1.xls"');
    
    
rc := utl_tcp.Write_Line (c, '')

v_Header_txt:='Cisco MCN Number'||chr(9)||'Case No'||chr(9)||'BU MCN Number'||chr(9)||'CE Case Creation Date'||chr(9)||'PCN Number';
    
    rc := utl_tcp.Write_Line(c,v_Header_txt);
    
    FOR c1 IN c1_Cur LOOP
      IF (mod(lv_cnt,1000) = 0) THEN
      --End previous excel and Generate new Excel File
      
        rc := utl_tcp.Write_Line(c,'-------SECBOUND');
        
        lv_New_cnt := lv_cnt + 1;
        
        rc := utl_tcp.Write_Line(c,'Content-Type: application/ms-excel;');
        
        rc := utl_tcp.Write_Line(c,' name="Processed_Records1.xls"');
        
        rc := utl_tcp.Write_Line(c,'Content-Transfer_Encoding: 8bit');
        
        rc := utl_tcp.Write_Line(c,'Content-Disposition: attachment;');
        
        rc := utl_tcp.Write_Line(c,' filename="Processed_Records'
                                   ||lv_New_cnt
                                   ||'.xls"');
        
        
rc := utl_tcp.Write_Line (c, '')
        --Print Header
        


--Print Header
        --Print Header
rc := utl_tcp.Write_Line (c, v_Header_txt);
      END IF;
      
      v_txt := c1.pcn_Number
               ||chr(9)
               ||c1.cIsco_Log
               ||chr(9)
               ||c1.Case_Id
               ||chr(9)
               ||c1.Business_Unit;
      
      rc := utl_tcp.Write_Line(c,v_txt);
      
      lv_cnt := lv_cnt + 1;
    END LOOP;
    
    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
    
    dbms_Output.Put_Line('I ... am .... here.... 4');
  EXCEPTION
    WHEN OTHERS THEN
      Raise_Application_Error(- 20000,SQLERRM);
  END Send_Mail_Attachment;
END ccm_dev_Search_Send_Mail_pkg;
/
Re: UTL_TCP package [message #229279 is a reply to message #229277] Fri, 06 April 2007 07:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And Frank said that a second before me Smile.
I am slow, as usual.

[Updated on: Fri, 06 April 2007 07:18]

Report message to a moderator

Re: UTL_TCP package [message #229282 is a reply to message #229279] Fri, 06 April 2007 08:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bottom line is still that utl_tcp is not the (easiest) way to send mail. Search for email and attachment in this forum to find ways to do what you do much easier.
Re: UTL_TCP package [message #229284 is a reply to message #229282] Fri, 06 April 2007 08:21 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi frank and maheer,

i missed to read the first sticky Embarassed
and i was not aware of that formatting tool. Embarassed
will give it a better try next time,in my posts.


regards,
shanth
Re: UTL_TCP package [message #229298 is a reply to message #229284] Fri, 06 April 2007 09:39 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Well, regardless of all the comments about formatting your information, I'd recommend you search for information on UTL_SMTP, which is designed to format and send email. I don't know how it would work with attachments, however, it might give you some help along the way.

Ron
Re: UTL_TCP package [message #229300 is a reply to message #229218] Fri, 06 April 2007 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25034
Registered: January 2009
Location: SoCal
Senior Member
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_mail.htm
Re: UTL_TCP package [message #229576 is a reply to message #229300] Mon, 09 April 2007 03:42 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Experts,

Still I am not able to zip the file but I can do that same thing in Java.I heard that we can use java code in oracle. I am using oracle 9i. Where I need to store the class files in oracle server and how can call the java method in oracle. Any one can suggest the step


--Yash
Previous Topic: retrieving image from database
Next Topic: calculate hours:minutes:seconds (merged 4 cross-posts)
Goto Forum:
  


Current Time: Sat Dec 03 13:57:36 CST 2016

Total time taken to generate the page: 0.11124 seconds