OraFAQ Forum: SQL & PL/SQL » Sending an email with pdf attachments with size >32 K

Home » SQL & PL/SQL » SQL & PL/SQL » Sending an email with pdf attachments with size >32 K

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sending an email with pdf attachments with size >32 K [message #443251]

Sun, 14 February 2010 02:21

charuamit
Messages: 121
Registered: January 2009

Senior Member

Dear friends

I have been working on sending an email with a pdf attachment.I succeeded in pdf attachments with size <32 K.The steps I Used :

1.created a directory 'EMAIL' that points to the directory containing my pdf file.

2.

declare
vInHandle utl_file.file_type;
flen number;
bsize number;
ex boolean;
l_Output raw(32767);
fname varchar2(30) := 'labels.pdf';
vSender varchar2(30) := some1@hct.edu.om';
vRecip varchar2(30) := 'some2@hct.edu.om';
vSubj varchar2(50) := 'testing';
vAttach varchar2(50) := 'labels.pdf';
vMType varchar2(30) := 'text/plain; charset=us-ascii';
begin
utl_file.fgetattr('EMAIL', fname, ex, flen, bsize);
vInHandle := utl_file.fopen('EMAIL', fname, 'R');
utl_file.get_raw (vInHandle, l_Output);
utl_file.fclose(vInHandle);
utl_mail.send_attach_raw(sender => vSender
,recipients => vRecip
,subject => vsubj
,attachment => l_Output
,att_inline => false
,att_filename => fname);
end;

It send the email <32 k.But for >32 K it give me the error :

ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_ENCODE", line 243
ORA-06512: at "SYS.UTL_MAIL", line 118
ORA-06512: at "SYS.UTL_MAIL", line 324
ORA-06512: at "SYS.UTL_MAIL", line 414
ORA-06512: at line 18

I know it has something to do with blob or clob ....plz advise me where i can modify my code to work.

i have never used blob,clob,etc

please help me urgently
thanks and regards
Charu

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443254 is a reply to message #443251]

Sun, 14 February 2010 02:48

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
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" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session WITH LINE NUMBERS.

Regards
Michel

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443255 is a reply to message #443254]

Sun, 14 February 2010 02:53

charuamit
Messages: 121
Registered: January 2009

Senior Member

The SQL code is :

1 declare
2 vInHandle utl_file.file_type;
3 flen number;
4 bsize number;
5 ex boolean;
6 l_Output raw(32767);
7 fname varchar2(30) := 'task_rep.pdf';
8 vSender varchar2(30) := 'wajid-nawaz@hct.edu.om';
9 vRecip varchar2(30) := 'charu@hct.edu.om';
10 vSubj varchar2(50) := 'fresh try';
11 vAttach varchar2(50) := 'task_rep.pdf';
12 vMType varchar2(30) := 'application/pdf';
13 begin
14 utl_file.fgetattr('EMAIL', fname, ex, flen, bsize);
15 vInHandle := utl_file.fopen('EMAIL', fname, 'R');
16 utl_file.get_raw (vInHandle, l_Output);
17 utl_file.fclose(vInHandle);
18 utl_mail.send_attach_raw(sender => vSender
19 ,recipients => vRecip
20 ,subject => vsubj
21 ,attachment => l_Output
22 ,att_inline => false
23 ,att_filename => fname);
24* end;
25 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_ENCODE", line 243
ORA-06512: at "SYS.UTL_MAIL", line 118
ORA-06512: at "SYS.UTL_MAIL", line 324
ORA-06512: at "SYS.UTL_MAIL", line 414
ORA-06512: at line 18

Thanks and regards

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443257 is a reply to message #443255]

Sun, 14 February 2010 04:47

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why it is not formatted and why there is no version number?

Regards
Michel

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443306 is a reply to message #443257]

Mon, 15 February 2010 00:56

charuamit
Messages: 121
Registered: January 2009

Senior Member

Dear friends

please find the code to send an email with an attachment(size >32 k):

create or replace procedure send_blob (
p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
p_filename varchar2
) is

v_raw raw(57);
v_length integer := 0;
v_buffer_size integer := 57;
v_offset integer := 1;
mailhost VARCHAR2(64) := 'some.abc.test';
port constant number(2):=25;
timeout number :=180;
mail_conn utl_smtp.connection;
p_blob Blob;
temp_os_file bfile;
ex number;

begin
DBMS_LOB.CREATETEMPORARY(p_blob,true);
temp_os_file := BFILENAME ('EMAIL',p_filename);
ex := dbms_lob.fileexists(temp_os_file);
if ex = 1 then
dbms_lob.fileopen(temp_os_file,dbms_lob.file_readonly);
dbms_lob.loadfromfilep_blob,temp_os_file,dbms_lob.getlength(temp_os_file));
dbms_lob.fileclose(temp_os_file);
end if;
mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, p_sender);
utl_smtp.rcpt(mail_conn, p_recipient);
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'From'||':'|| p_sender || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'To'||':'|| p_recipient || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'Subject' ||':'|| p_subject || UTL_TCP.CRLF);
-- utl_smtp.write_data(mail_conn, text);
utl_smtp.write_data( mail_conn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);
utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
utl_smtp.write_data( mail_conn, utl_tcp.crlf );
v_length := dbms_lob.getlength(p_blob);
<<while_loop>>
while v_offset < v_length loop
dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );
utl_smtp.write_raw_data( mail_conn, utl_encode.base64_encode(v_raw) );
utl_smtp.write_data( mail_conn, utl_tcp.crlf );
v_offset := v_offset + v_buffer_size;
end loop while_loop;
utl_smtp.write_data( mail_conn, utl_tcp.crlf );
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(mail_conn);
raise;
when others then
raise;
end send_blob;
/

--- EMAIL z the directory created in oracle and points to the directory containing my pdf files

thanks (i still need to edit it for cc and body of the email)
charu

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443316 is a reply to message #443306]

Mon, 15 February 2010 01:41

maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal

Member

Use [code] and [/code] tags to post your code in the forum so that it becomes more readable.

[Updated on: Mon, 15 February 2010 01:44] by Moderator

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443317 is a reply to message #443316]

Mon, 15 February 2010 01:45

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

i guess..you are trying to say ...
Use [code] and [/code] tags to post your code in the forum so that it becomes more readable.

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443320 is a reply to message #443317]

Mon, 15 February 2010 02:04

charuamit
Messages: 121
Registered: January 2009

Senior Member

<code>
create or replace procedure send_blob (
p_sender varchar2,
p_recipient varchar2,
p_cc varchar2,
p_subject varchar2,
p_filename varchar2,
text varchar2) is
--c utl_smtp.connection;
v_raw raw(57);
v_length integer := 0;
v_buffer_size integer := 57;
v_offset integer := 1;
mailhost VARCHAR2(64) := 'mailsrv.hct.org';
port constant number(2):=25;
timeout number :=180;
mail_conn utl_smtp.connection;
p_blob Blob;
temp_os_file bfile;
ex number;
begin
DBMS_LOB.CREATETEMPORARY(p_blob,true);
temp_os_file := BFILENAME ('EMAIL',p_filename);
ex := dbms_lob.fileexists(temp_os_file);
if ex = 1 then
dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);
dbms_lob.loadfromfile(p_blob,temp_os_file, dbms_lob.getlength(temp_os_file));
dbms_lob.fileclose(temp_os_file);
end if;
mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, p_sender);
utl_smtp.rcpt(mail_conn, p_recipient);
utl_smtp.rcpt(mail_conn, p_cc);

utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'From'||':'|| p_sender || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'To'||':'|| p_recipient || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'CC'||':'|| p_cc || UTL_TCP.CRLF);

utl_smtp.write_data(mail_conn,'Subject' ||':'|| p_subject || UTL_TCP.CRLF);
-- utl_smtp.write_data(mail_conn,UTL_TCP.CRLF ||'Body' ||':'|| text || UTL_TCP.CRLF);

--utl_smtp.write_data(mail_conn,UTL_TCP.CRLF||text || UTL_TCP.CRLF );

utl_smtp.write_data( mail_conn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);
utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
utl_smtp.write_data( mail_conn, utl_tcp.crlf );
v_length := dbms_lob.getlength(p_blob);
<<while_loop>>
while v_offset < v_length loop
dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );
utl_smtp.write_raw_data( mail_conn, utl_encode.base64_encode(v_raw) );
utl_smtp.write_data( mail_conn, utl_tcp.crlf );
v_offset := v_offset + v_buffer_size;
end loop while_loop;
utl_smtp.write_data( mail_conn, utl_tcp.crlf );
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(mail_conn);
raise;
when others then
raise;
end send_blob;

</code>

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443322 is a reply to message #443320]

Mon, 15 February 2010 02:10

charuamit
Messages: 121
Registered: January 2009

Senior Member

sorry i just copied the same again..... Sad

Sad

anyways the code will work....only it sends a blank body

also this was done via SQL with user sys.
The same when i try to do with some other user it gives :

ORA-04067: not executed, package body "HRMS.UTL_SMTP" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "HRMS.SEND_BLOB", line 60

so i gave that user dba privileges,and then connected as user/pw@orcl as sysdba - then it executes.

But i will be sending an email from the forms so i cant let all the users connect as sysdba

so what is the alternate solution

please advise
thanks and regards
charu

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443323 is a reply to message #443322]

Mon, 15 February 2010 02:11

charuamit
Messages: 121
Registered: January 2009

Senior Member

and now the formatted code :

create or replace procedure send_blob (
      p_sender varchar2,
      p_recipient varchar2,
      p_cc varchar2,
      p_subject varchar2,
      p_filename varchar2,
      text varchar2) is     
    --c utl_smtp.connection;
    v_raw raw(57);
    v_length integer := 0;
    v_buffer_size integer := 57;
    v_offset integer := 1;
    mailhost    VARCHAR2(64) := 'mailsrv.hct.org';
    port constant number(2):=25;
    timeout number :=180;
    mail_conn  utl_smtp.connection;   
 p_blob Blob;
 temp_os_file bfile;
 ex number;  
begin  
   DBMS_LOB.CREATETEMPORARY(p_blob,true);
   temp_os_file := BFILENAME ('EMAIL',p_filename);
   ex := dbms_lob.fileexists(temp_os_file);
      if ex = 1 then
         dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);
         dbms_lob.loadfromfile(p_blob,temp_os_file, dbms_lob.getlength(temp_os_file));
         dbms_lob.fileclose(temp_os_file);
       end if;
   mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, p_sender);
   utl_smtp.rcpt(mail_conn, p_recipient);
   utl_smtp.rcpt(mail_conn, p_cc);


   utl_smtp.open_data(mail_conn);
  utl_smtp.write_data(mail_conn,'From'||':'|| p_sender || UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn,'To'||':'|| p_recipient || UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn,'CC'||':'|| p_cc || UTL_TCP.CRLF);


  utl_smtp.write_data(mail_conn,'Subject' ||':'|| p_subject || UTL_TCP.CRLF);
 -- utl_smtp.write_data(mail_conn,UTL_TCP.CRLF ||'Body' ||':'|| text || UTL_TCP.CRLF);

  --utl_smtp.write_data(mail_conn,UTL_TCP.CRLF||text || UTL_TCP.CRLF );



    utl_smtp.write_data( mail_conn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);
    utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
    utl_smtp.write_data( mail_conn, utl_tcp.crlf ); 
    v_length := dbms_lob.getlength(p_blob);     
    <<while_loop>>
    while v_offset < v_length loop
      dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );
      utl_smtp.write_raw_data( mail_conn, utl_encode.base64_encode(v_raw) );
      utl_smtp.write_data( mail_conn, utl_tcp.crlf );
      v_offset := v_offset + v_buffer_size;
    end loop while_loop;
    utl_smtp.write_data( mail_conn, utl_tcp.crlf );
    utl_smtp.close_data(mail_conn);
    utl_smtp.quit(mail_conn);
  exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
      utl_smtp.quit(mail_conn);
      raise;
    when others then
    raise;
  end send_blob;

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443325 is a reply to message #443323]

Mon, 15 February 2010 02:22

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

But i will be sending an email from the forms

.

Search in the forms forum ...

I am sure you will get the alternate.
There was one .fmb file for this kind of situation.

Goodluck
sriram Smile

Smile

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443349 is a reply to message #443322]

Mon, 15 February 2010 04:28

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If it's just a permissions problem, then you just need to connect as Sysdba and grant the execute privilege on SYS.UTL_SMTP to the users(s) who need access to UTL_SMTP

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #443462 is a reply to message #443349]

Mon, 15 February 2010 23:01

charuamit
Messages: 121
Registered: January 2009

Senior Member

it worked now

just say grant execute on sys.utl_smtp to user;

and then continue.......

thanks and regards
Charu

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #483908 is a reply to message #443323]

Thu, 25 November 2010 08:46

hvasco
Messages: 1
Registered: November 2010

Junior Member

The code worked. But I am not able to send the email with the message body ... When I put this information the attached code does not work. Do you know what I should do to solve this problem?

I'm waiting. Thanks.

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #483910 is a reply to message #483908]

Thu, 25 November 2010 08:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>When I put this information the attached code does not work. Do you know what I should do to solve this problem?

my car does not work.
please tell me how to make my car go.
In the future please post fewer details to make guessing at actual problem more challenging.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #483915 is a reply to message #483910]

Thu, 25 November 2010 09:21

mnitu
Messages: 159
Registered: February 2008
Location: Reims

Senior Member

BlackSwan wrote on Thu, 25 November 2010 15:50

...
please tell me how to make my car go.
...

How to make your car go [faster]

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #513861 is a reply to message #483915]

Wed, 29 June 2011 13:44

bobsnunhes
Messages: 1
Registered: June 2011
Location: Brasil

Junior Member

Good Evening for you all.
I have some questions :
1 - How you created the directory in the database?
2 - How you save de PDF file on the created directory ?
Thanks

Report message to a moderator

Re: Sending an email with pdf attachments with size >32 K [message #513863 is a reply to message #513861]

Wed, 29 June 2011 13:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/ => do #1 -> SEARCH!

http://www.orafaq.com/wiki/Oracle_directory

Report message to a moderator

Previous Topic:	Drop schema and reclaim disk space
Next Topic:	If - Then - Else statment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 09 06:33:46 CDT 2026