Home » SQL & PL/SQL » SQL & PL/SQL » Sending an email with pdf attachments with size >32 K
Sending an email with pdf attachments with size >32 K [message #443251] Sun, 14 February 2010 02:21 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why it is not formatted and why there is no version number?

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ramoradba
Messages: 2454
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.
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 Go to previous messageGo to next message
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>
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 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
sorry i just copied the same again.....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
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 Go to previous messageGo to next message
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;

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 Go to previous messageGo to next message
ramoradba
Messages: 2454
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
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/
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 Go to previous messageGo to next message
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]
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 Go to previous messageGo to next message
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
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 Go to previous message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/t/88153/0/ => do #1 -> SEARCH!

http://www.orafaq.com/wiki/Oracle_directory
Previous Topic: Drop schema and reclaim disk space
Next Topic: If - Then - Else statment
Goto Forum:
  


Current Time: Sat Sep 24 16:21:21 CDT 2016

Total time taken to generate the page: 0.07649 seconds