E-Mail The Results Of A Query

Ed_B

Hello Forum,

My name is Ed. I have been doing Database work since 2002. My background is primarily MS SQL. I'm fairly new to the Oracle way of doing things which brings me to this forum. I'm currently a Data Specialist for a school district. We use a Student Information System called PowerSchool (PS). PS uses Oracle SQL Developer 3.2 on the backend. One of the first task that I'm trying to accomplish is to e-mail the results of a query. I searched several articles and cannot seem to find a straight forward answer. I found the following code snippett but cannot seem to make any sense of certain sections within the code. If anyone could offer some advice on how to clean this up so I can make more sense of it, I would appreciate it. And no, I'm not asking anyone to do my job for me. I'm merely asking for some assistance.

declare

myRecipients varchar2(100) := '[email protected]';

mySubject varchar2(100) := 'This will appear in the subject line';

-- myColumns is used to populate column headers when there is data returned by the query

-- chr(9) is the tab character. 

myColumns varchar2(200):=

'Column1'

||chr(9)||

'Column2'

||chr(9)||

'ThirdColumn'

||chr(9);

-- The following cursor is the SELECT and FROM portions of your query which establishes what each row of the collection is comprised of.

-- You'll specify the full query later.  I include "WHERE ROWNUM =1;" just to limit the definition.

cursor c1 is select my.Column1, my.Column2, my.Column3 as thirdColumn

from myTable

-- Again - we're not running this query ever, so no point in returning more than 1 row. 

where rownum = 1;

-- Each "row" of collection will share datatypes of the collection.

type myResults is table of c1%rowtype;

allmyResults myResults;

-- 32767 is maximum size for varchar2

-- "xmessage" will be the Body of the e-mail.  We're populating it with the column names now in case our full query returns data.

xmessage varchar2(32767);

begin

SELECT select my.Column1, my.Column2, my.Column3 as thirdColumn

-- Tuck query results into the collection.

BULK COLLECT INTO allmyResults

FROM from myTable

WHERE my.Column1 = 'aCertainCriteria';

-- If there are results returned by the query then we'll want to format the body of the message with column headers.

if allmyResults.count > 0 then

xmessage :=

'There were rows returned by the message query, so this will be the first line of the message body.'

--chr(10)chr(13) are carriage return, line feed.  The pipe character (|'s) concatenate.

||chr(10)||chr(13)||

'Second line of message body when data is returned by the query.'

||chr(10)||chr(13);

'Third line of message.  With the following line being column headers (in the event rows were returned by your query).'

||chr(10)||chr(13)||

-- Adds column headers because your query returned data.

myColumns

||chr(10)||chr(13);

-- Loop through each element in the collection

for i in allmyResults.first..allmyResults.last

loop

-- In the declaration we populated the myColumns variable with the names of your columns.  The chr(9)'s insert tabs to delimit the data.

xmessage := xmessage ||

allmyResults(i).Column1

||chr(9)||

allmyResults(i).Column2

||chr(9)||

allmyResults(i).ThirdColumn

||chr(10)||chr(13);

end loop;

else

-- If the full query doesn't return rows you will follow this path.

xmessage :=

'There were no rows returned by the query and this will appear in the message body.'

||chr(10)||chr(13)||

'This is the second line of the message body when no rows are returned.'

||chr(10)||chr(13);

end if;

-- Here is what sends the e-mail.

sys.utl_mail.send( '[email protected]', myRecipients,null, null, mySubject, xmessage, 'text/plain; charset=us-ascii', 3);

END;