Home » SQL & PL/SQL » SQL & PL/SQL » blog image display in utl_mail (Oracle 10g)
blog image display in utl_mail [message #633175] Thu, 12 February 2015 01:31 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i am using utl_mail to send email from oracle , my query is how to display image into this utl_mail. I have created a table containing blob column and inserted the image .
Re: blog image display in utl_mail [message #633179 is a reply to message #633175] Thu, 12 February 2015 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You do not display in utl_mail, you send using utl_mail.
So what is the problem EXACTLY?

Re: blog image display in utl_mail [message #633185 is a reply to message #633179] Thu, 12 February 2015 02:10 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Actually we have a requirement of embeding our company logo in emails generated using utl_mail, whenever the email is sent using utl_mail it must contain both the text and the logo in mail.

Re: blog image display in utl_mail [message #633186 is a reply to message #633185] Thu, 12 February 2015 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, and your problem doing so is?
If you don't know how the mail is formatted then just take one of yours and see its source.

Re: blog image display in utl_mail [message #633207 is a reply to message #633186] Thu, 12 February 2015 04:37 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i just wanted to know the steps for doing that, first i uploaded the image in database table.Now i want to refer it in Mail body , how can i do it.
Re: blog image display in utl_mail [message #633208 is a reply to message #633207] Thu, 12 February 2015 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is what I said, to know how to do it you must FIRST know how it is done by those that already do it.
So post the source of one of your messages with the logo and you will know how to do it.

Re: blog image display in utl_mail [message #633210 is a reply to message #633208] Thu, 12 February 2015 05:15 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Hi Michael, i am using the following code to send email without any image,


DECLARE
   mail_message   VARCHAR2 (30000);
BEGIN
   mail_message :=
         'Dear Sir,'
      || CHR (10)
      || '  Transmittal NO  : '
      || :stmh_no
      || '  Weight :'
      || m_wt
      || 'No of Drgs '
      || m_drg_cnt
      || '    WO NO  : '
      || :stmh_wo_no
      || ' STRUCTURE NO  :'
      || :stmh_job_no
      || ''
      || CHR (10)
      || '   SUBJECT: Transmittal is Prepared by '
      || :stmh_cr_uid
      || ' for the Structure  '
      || :stmh_job_no
      || ' on  '
      || :stmh_dt
      || CHR (10)
      || '  .Aff Mark Nos are as follows :'
      || CHR (10)
      || m_mark_rev
      || CHR (10)
      || CHR (10)
      || 'Thanks and Regards'
      || CHR (10)
      || :GLOBAL.m_user_id;
   UTL_MAIL.send ('transuae@aicsteel-uae.com',
                  'arif@aicsteel-uae.com',
                  NULL,
                  NULL,
                  'Test Email',
                  mail_message
                 );
END;

-- i saw this useful link and now i have a procedure to get the image from the below link.

[url=http://oracle-base.com/articles/misc/html-with-embedded-images-from-plsql.php][/url]


CREATE OR REPLACE PROCEDURE get_enc_img_from_tab (p_image_name IN VARCHAR2,
                                                  p_clob       IN OUT NOCOPY CLOB)
AS
BEGIN
  SELECT p_clob || base64encode(image)
  INTO   p_clob
  FROM   images 
  WHERE  name = p_image_name;
END;
/

--now my query is how can i call this procedure . Do i need the following code below to first generate the file in html format.


DECLARE
  l_clob  CLOB;
BEGIN
  DBMS_LOB.createtemporary(l_clob, FALSE);

  -- Build the start of the HTML document, including the start of the IMG tag
  -- and place it in a CLOB.
  l_clob := '<html>
   <head>
     <title>Test HTML with Embedded Image</title>
   </head>
   <body>
     <h1>Test HTML with Embedded Image</h1>
     <p>And here it is:</p>
     <img src="data:image/gif;base64,'; 
 
  get_enc_img_from_fs (p_dir  => 'IMAGES',
                       p_file => 'site_logo.gif',
                       p_clob => l_clob);
 
  --get_enc_img_from_http (p_url  => 'http://oracle-base.com/images/site_logo.gif',
  --                       p_clob => l_clob);
 
  --get_enc_img_from_tab (p_image_name => 'site_logo.gif',
  --                      p_clob       => l_clob);
 
  -- Close off the IMG tag and complete the HTML document.
  l_clob := l_clob || '" alt="Site Logo" />
  <p>The end.</p>
  </body>
  </html>';
  
  -- The CLOB now contains the complete HTML with the embedded image, so do something with it.
  -- In this case I'm going to write it to the file system.
  create_file_from_clob (p_dir  => 'IMAGES',
                         p_file => 'EmbeddedImageTest.htm',
                         p_clob => l_clob);  


  DBMS_LOB.freetemporary(l_clob);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_LOB.freetemporary(l_clob);
    RAISE;
END;
/





[Updated on: Thu, 12 February 2015 05:21]

Report message to a moderator

Previous Topic: SQL output to HTML table in email notification - possible?
Next Topic: convert string values into rows
Goto Forum:
  


Current Time: Fri Mar 29 06:13:37 CDT 2024