-2

I'm extremely new to oracle and I've been tasked to send the result of a certain SELECT query through email and have it regularly scheduled as well. I've tried looking at other posts as well but I could not make sense of any of it.
Can someone show me how to do it in a simple way?

Anony
  • 1
  • 1

1 Answers1

1

Workflow

This is the general workflow for how to email your report

  • Generate a CLOB of the HTML Code
    • Use the HTF package to create the HTML code
  • Email the CLOB
    • If you have APEX Installed
      • The APEX_MAIL package fits this requirement nicely
    • If not
      • try to create something using the MAIL_UTL package
  • post a question asking why you aren't receiving emails
    • 20% of the time - this is due to missing ACL or firewall issues
    • 79% of the time - some system thinks you are a spammer
    • most likely, this question on DBA SA will be closed for being "too localized"

Design Notes

You need to have three separate code blocks (function or procedure)

  1. Code that generates the report in a specific format
  2. Code that sends that BLOB/CLOB ( UTL_MAIL or APEX_MAIL )
  3. Code that takes (1) and sends it to (2)

Further Details should be placed in a different question.

But, I'll show you how to generate the HTML anyways.

Create HTML

create or replace
function generate_html_reports return clob
    authid current_user
as
    html_clob clob;

    cursor c is select rownum rn, banner from v$version;
BEGIN
  -- top of the HTML Page
   html_clob := HTF.HTMLOPEN -- generates <HTML>
   || HTF.HEADOPEN -- generates <HEAD>
   || HTF.TITLE('My Report') -- generates <TITLE>Hello</TITLE>
   || HTF.HEADCLOSE -- generates </HEAD>
   || chr(10)
   || HTF.BODYOPEN -- generates <BODY>
   || chr(10)
   || HTF.HEADER(1, 'Hello')  -- generates <H1>Hello</H1>
   || chr(10);

   -- Table prefix
    html_clob := html_clob
    || htf.tableopen
    || htf.tablerowopen
        || htf.tabledata( curr.rn ) || htf.tabledata( curr.banner )  -- generates all of the <td>data</td> code

    || htf.tablerowclose
    || chr(10);

    -- table data
    for curr in c
    loop
    html_clob := html_clob
    || htf.tablerowopen  -- generates <tr>
        || htf.tabledata('Row') || htf.tabledata( 'Banner' )  -- generates all of the <td>data</td> code
    || htf.tablerowclose
    || chr(10); -- generats </tr>
    end loop;

    -- close of table and HTML
    html_clob := html_clob
    || htf.tableclose
    || HTF.BODYCLOSE -- generates </BODY>
    || HTF.HTMLCLOSE -- generates </HTML>
    || chr(10);

    return html_clob;
END;
Michael Kutz
  • 4,919
  • 1
  • 10
  • 14