Hi all,
I have a requirement to extract the output of sql and email it(database is 11g), i got the below code from http://www.ryanboyer.net/e-mail-query-results-from-orac/, but when i execut it i get error
identifier 'DEMO_MAIL.BEGIN_MAIL' must be declared, i am running this as sys,i could not find DEMO_MAIL
create or replace procedure email_clm_tat as
boyer_date number := 0;
lv_subject VARCHAR2(200);
lv_sender VARCHAR2(200) := 'testl@test.com';---- Sender, but use servername@localdomain name if it doesn't have a corresponding mailbox
lv_recipients VARCHAR2(200) := 'test@test'; -- Recipients is comma-delimited
lv_priority PLS_INTEGER := NULL; -- Configurable
lv_last BOOLEAN := FALSE;
lv_count NUMBER := 0;
lv_message VARCHAR2(32000);
lv_conn utl_smtp.connection;
lv_mycolumns VARCHAR2(500) := 'ColumnA, ColumnB, ColumnC,ColumnD'||chr(13);
CURSOR cur_query
IS
/* Insert your query here */
SELECT
segment_code as A, CLAIM_NO as B,registration_date as C,id as D
FROM CLAIMS CLM';
BEGIN
/* The following will get today's date */
select TO_CHAR(CURRENT_DATE, 'YYYYMMDD') into boyer_date FROM dual;
lv_subject := 'HClaims exceeding tat ' || boyer_date; -- Configure to taste
lv_conn:= demo_mail.begin_mail(lv_sender, lv_recipients, lv_subject, demo_mail.multipart_mime_type, lv_priority);
demo_mail.begin_attachment (lv_conn,'text/html',FALSE,'test.csv','7 bit');
demo_mail.write_text( lv_conn, lv_mycolumns);
FOR rec IN cur_query
LOOP
/* Depending on the number of columns being returned in the query, the lv_message could be quite large */
lv_message := rec.a||','||rec.b||','||rec.c||','||rec.d||chr(13);
demo_mail.write_text( lv_conn,lv_message);
END LOOP;
demo_mail.end_attachment(lv_conn,TRUE);
demo_mail.end_mail(lv_conn);
END;
I am getting the below error
Line: 24 Column: 12 Type: error Text: PLS-00201: identifier 'DEMO_MAIL.BEGIN_MAIL' must be declared
Line: 24 Column: 1 Type: error Text: PL/SQL: Statement ignored
Line: 25 Column: 1 Type: error Text: PLS-00201: identifier 'DEMO_MAIL.BEGIN_ATTACHMENT' must be declared
Line: 25 Column: 1 Type: error Text: PL/SQL: Statement ignored
Line: 26 Column: 1 Type: error Text: PLS-00201: identifier 'DEMO_MAIL.WRITE_TEXT' must be declared
Line: 26 Column: 1 Type: error Text: PL/SQL: Statement ignored
Line: 31 Column: 7 Type: error Text: PLS-00201: identifier 'DEMO_MAIL.WRITE_TEXT' must be declared
Line: 31 Column: 7 Type: error Text: PL/SQL: Statement ignored
Line: 33 Column: 4 Type: error Text: PLS-00201: identifier 'DEMO_MAIL.END_ATTACHMENT' must be declared
Line: 33 Column: 4 Type: error Text: PL/SQL: Statement ignored
Line: 34 Column: 4 Type: error Text: PLS-00201: identifier 'DEMO_MAIL.END_MAIL' must be declared
Line: 34 Column: 4 Type: error Text: PL/SQL: Statement ignored
how can i use demo_mail package? do i need to create it from script